sqlite3 error when saving new user data

[dgvirtual]

I am using sqlite db and get this error when saving a new user. The user is actually saved fine...

Here is the full log entry for this error:

CRITICAL - 2022-10-12 12:11:24 --> SQLite3::exec(): UNIQUE constraint failed: auth_identities.type, auth_identities.secret
in SYSTEMPATH/Database/SQLite3/Connection.php on line 130.
 1 [internal function]: CodeIgniter\Debug\Exceptions->errorHandler(2, 'SQLite3::exec(): UNIQUE constraint failed: auth_identities.type, auth_identities.secret', 'SYSTEMPATH/Database/SQLite3/Connection.php', 130, [...])
 2 SYSTEMPATH/Database/SQLite3/Connection.php(130): SQLite3->exec('INSERT INTO `auth_identities` (`user_id`, `type`, `secret`, `secret2`, `created_at`, `updated_at`) VALUES (4, \'email_password\', \'vardas3@pavarde.lt\', \'$2y$10$kuRm8Cv/EZKMXAoxSKXWE.XyciS.LKOr987/0.qaX4G56piwG.HZu\', \'2022-10-12 12:11:24\', \'2022-10-12 12:11:24\')')
 3 SYSTEMPATH/Database/BaseConnection.php(693): CodeIgniter\Database\SQLite3\Connection->execute('INSERT INTO `auth_identities` (`user_id`, `type`, `secret`, `secret2`, `created_at`, `updated_at`) VALUES (4, \'email_password\', \'vardas3@pavarde.lt\', \'$2y$10$kuRm8Cv/EZKMXAoxSKXWE.XyciS.LKOr987/0.qaX4G56piwG.HZu\', \'2022-10-12 12:11:24\', \'2022-10-12 12:11:24\')')
 4 SYSTEMPATH/Database/BaseConnection.php(620): CodeIgniter\Database\BaseConnection->simpleQuery('INSERT INTO `auth_identities` (`user_id`, `type`, `secret`, `secret2`, `created_at`, `updated_at`) VALUES (4, \'email_password\', \'vardas3@pavarde.lt\', \'$2y$10$kuRm8Cv/EZKMXAoxSKXWE.XyciS.LKOr987/0.qaX4G56piwG.HZu\', \'2022-10-12 12:11:24\', \'2022-10-12 12:11:24\')')
 5 SYSTEMPATH/Database/BaseBuilder.php(1916): CodeIgniter\Database\BaseConnection->query('INSERT INTO `auth_identities` (`user_id`, `type`, `secret`, `secret2`, `created_at`, `updated_at`) VALUES (:user_id:, :type:, :secret:, :secret2:, :created_at:, :updated_at:)', [...], false)
 6 SYSTEMPATH/Model.php(279): CodeIgniter\Database\BaseBuilder->insert()
 7 SYSTEMPATH/BaseModel.php(748): CodeIgniter\Model->doInsert([...])
 8 SYSTEMPATH/Model.php(667): CodeIgniter\BaseModel->insert([...], true)
 9 VENDORPATH/codeigniter4/shield/src/Models/UserIdentityModel.php(67): CodeIgniter\Model->insert([...])
10 VENDORPATH/codeigniter4/shield/src/Entities/User.php(113): CodeIgniter\Shield\Models\UserIdentityModel->createEmailIdentity(Object(Bonfire\Users\User), [...])
11 VENDORPATH/lonnieezell/bonfire/src/Users/Controllers/UserController.php(190): CodeIgniter\Shield\Entities\User->createEmailIdentity([...])
12 SYSTEMPATH/CodeIgniter.php(902): Bonfire\Users\Controllers\UserController->save()
13 SYSTEMPATH/CodeIgniter.php(457): CodeIgniter\CodeIgniter->runController(Object(Bonfire\Users\Controllers\UserController))
14 SYSTEMPATH/CodeIgniter.php(340): CodeIgniter\CodeIgniter->handleRequest(null, Object(Config\Cache), false)
15 FCPATH/index.php(67): CodeIgniter\CodeIgniter->run()
16 SYSTEMPATH/Commands/Server/rewrite.php(46): require_once('FCPATH/index.php')

I am not sure what is causing this error. The screenshot says: "SQLite3::exec(): UNIQUE constraint failed: auth_identities.type, auth_identities.secret" - however, there is no unique constraint on those columns in the database, and while the type column content is not unique, the column "secret" has all unique values (the one that was inserted, despite this error message, was the last one, id = 4). Here is the relevant part of sql dump:

CREATE TABLE IF NOT EXISTS "auth_identities" (
    "id"    INTEGER PRIMARY KEY AUTOINCREMENT,
    "user_id"   int NOT NULL,
    "type"  varchar NOT NULL,
    "name"  varchar,
    "secret"    varchar NOT NULL,
    "secret2"   varchar,
    "expires"   datetime,
    "extra" text,
    "force_reset"   tinyint NOT NULL DEFAULT 0,
    "last_used_at"  datetime,
    "created_at"    datetime,
    "updated_at"    datetime,
    CONSTRAINT "auth_identities_user_id_foreign" FOREIGN KEY("user_id") REFERENCES "users"("id") ON DELETE CASCADE
);
INSERT INTO "auth_identities" ("id","user_id","type","name","secret","secret2","expires","extra","force_reset","last_used_at","created_at","updated_at") VALUES (1,1,'email_password',NULL,'user@website.lt','$2y$10$wGHX5cEL.j.0AcM0OXKW2O8zJsdCnhcIX.aasCeIvTZ.jMpWPIicq',NULL,NULL,0,'2022-10-12 09:12:39','2022-10-09 15:29:42','2022-10-12 10:52:00');
INSERT INTO "auth_identities" ("id","user_id","type","name","secret","secret2","expires","extra","force_reset","last_used_at","created_at","updated_at") VALUES (2,2,'email_password',NULL,'vardas@pavarde.lt','$2y$10$Mg4WeSXPH4yblZuJoATVg.PJQ/Lpikv0cKTnoel3ZyqusVf3cwa6C',NULL,NULL,0,NULL,'2022-10-12 11:54:37','2022-10-12 11:54:37');
INSERT INTO "auth_identities" ("id","user_id","type","name","secret","secret2","expires","extra","force_reset","last_used_at","created_at","updated_at") VALUES (3,3,'email_password',NULL,'vardas2@pavarde.lt','$2y$10$yVO.efsuW0HW..awx8u6jOQnXqhMqe1UQwb1KTTn7tZ015Xo5fuNG',NULL,NULL,0,NULL,'2022-10-12 11:56:23','2022-10-12 11:56:23');
INSERT INTO "auth_identities" ("id","user_id","type","name","secret","secret2","expires","extra","force_reset","last_used_at","created_at","updated_at") VALUES (4,4,'email_password',NULL,'vardas3@pavarde.lt','$2y$10$IZQwCIsjFxzL0w0Ls0utcuGnrtN0t2zJhPOSqfkCfAdR8aHPIhbV.',NULL,NULL,0,NULL,'2022-10-12 12:11:24','2022-10-12 12:11:24');

[dgvirtual]

I understand sqlite3 is not everyone's favourite db engine, but just in case anyone wants more data for debugging, – let me know.

I have just checked: after creating a user id=5 in db, running this insert statement in sql console is successfull:

INSERT INTO `auth_identities` (`user_id`, `type`, `secret`, `secret2`, `created_at`, `updated_at`) VALUES (5, 'email_password', 'vardas5@pavarde.lt', '$2y$10$kuRm8Cv/EZKMXAoxSKXWE.XyciS.LKOr987/0.qaX4G56piwG.HZu', '2022-10-12 12:11:24', '2022-10-12 12:11:24')

[dgvirtual]

So I think I have found the offending issue in the database. I exported the whole database as an sql file and found this at the end, where indexes are created:

CREATE UNIQUE INDEX IF NOT EXISTS "auth_identities_type_secret" ON "auth_identities" ( "type", "secret" );

as indexes are created only on unique fields, the index on "type" does not make sense...

After removing this index from the DB the error no longer happens. However, for each user I save there are two identities created in auth_identities DB table. Why? Only the first entry to be created is later modified if I modify the user, the other one remains untouched.

I presume the indexes are automatically created by Codeigniter migrations on all unique fields. And I see code

$this->forge->addUniqueKey(['type', 'secret']);

in the Shield Migrations file. How can that be so? That code must have worked for a lot of users for years now?

[dgvirtual]

Can no longer reproduce the issue.