I have a very similar problem.
I migrated Roundcube to version 1.6 and also migrated MariaDB to version 10.5.16 which works on a Galera Cluster.
As with the previous problem, some times (it is not always!) the user_id is not found and another one is recreated.
So I find myself having some "usernames" with multiple user_ids, so the user_id is not unique.
I also do not "simply" see the duplicate.
For examples
MariaDB [roundcube]> select user_id from users where username = "example@example.com";
+---------+
| user_id |
+---------+
| 32411 |
+---------+
1 row in set (0.000 sec)
But if I query with HEX I see both user_id
MariaDB [roundcube]> select user_id, hex(username) from users where hex(username) = "682E6A2E64616E7A65724061646F6E2E6C69";
+---------+--------------------------------------+
| user_id | hex(username) |
+---------+--------------------------------------+
| 32411 | 682E6A2E64616E7A65724061646F6E2E6C69 |
| 4381 | 682E6A2E64616E7A65724061646F6E2E6C69 |
+---------+--------------------------------------+
2 rows in set (0.003 sec)
And in /var/log/mariadb/mariadb.log I can see also di Error
mariadb.log-20230926:2023-09-25 13:02:27 127762101 [ERROR] InnoDB: duplicate key in `username` of table `roundcube`.`users`: TUPLE (info_bits=0, 3 fields): {[25]example@example.com(0x77696C6C692E72656E6E657240706F776572737572662E6C69),[11]imap.fl1.li(0x696D61702E666C312E6C69),[4] { (0x00007B1A)}, COMPACT RECORD(info_bits=0, 3 fields): {[25]example@example.com(0x77696C6C692E72656E6E657240706F776572737572662E6C69),[11]imap.example.com(0x696D61702E666C312E6C69),[4] {G(0x00007B47)}
mariadb.log-20230926:2023-09-25 13:02:27 127762101 [ERROR] InnoDB: index records in a wrong order in `username` of table `roundcube`.`users`: TUPLE (info_bits=0, 3 fields): {[18]example@example.com(0x77696C6C6940706F776572737572662E6C69),[11]imap.example.com(0x696D61702E666C312E6C69),[4] - (0x00002DC4)}, COMPACT RECORD(info_bits=0, 3 fields): {[14]example@example.com(0x77696C6C69314061646F6E2E6C69),[11]imap.example.com(0x696D61702E666C312E6C69),[4] - (0x00002DC1)}
So we have seen that, for some reason unknown to us, the users table in the new Galera Cluster (MariaDB 10.5.16) has different values than before the migration (MariaDB 10.1).
Old
MariaDB [roundcube]> show create table users;
| users | CREATE TABLE `users` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
`mail_host` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`last_login` datetime DEFAULT NULL,
`failed_login` datetime DEFAULT NULL,
`failed_login_counter` int(10) unsigned DEFAULT NULL,
`language` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`preferences` longtext COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`user_id`),
UNIQUE KEY `username` (`username`,`mail_host`)
) ENGINE=InnoDB AUTO_INCREMENT=31200 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC |
New
| users | CREATE TABLE `users` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`mail_host` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`last_login` datetime DEFAULT NULL,
`failed_login` datetime DEFAULT NULL,
`failed_login_counter` int(10) unsigned DEFAULT NULL,
`language` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`preferences` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `username` (`username`,`mail_host`)
) ENGINE=InnoDB AUTO_INCREMENT=33695 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC |
So we verified that the username value changed
from
`username` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
to
`username` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
Can this be just the problem?
Is it enough again to change the username value or is it a bug in the Roundcube version?
