Signature not getting saved

ninad · September 16, 2023, 10:45:21 AM

Dear All,

I am running 3 instances of Roundcube 1.5.2 on Centos 8.5 with MySQL Galera cluster and Memcache. The user base is quite high (in thousands).

Recently, I observed that my signature vanishes every time I log in again. Nothing in the logs, and even Galera synchronization is working properly. Also, any other user has not reported a similar issue.

While digging deeper, I found multiple entries in the identities table against my email address. Below are the details of the same.

I am not sure what this caused and how to debug this further. I request all to guide me with the same.

SQL Query: select identity_id, user_id, changed, del, standard, name, organization,email from identities where email="abc@example.com";

The result is:

+-------------+---------+---------------------+-----+----------+----------------+--------------+------------------+
| identity_id | user_id | changed | del | standard | name | organization | email |
+-------------+---------+---------------------+-----+----------+----------------+--------------+------------------+
| 61 | 64 | 2022-04-08 19:55:24 | 0 | 1 | Abc Def | | abc@example.com |
| 69864 | 70140 | 2022-04-09 19:03:45 | 0 | 1 | Abc Def | | abc@example.com |
| 69932 | 70208 | 2022-04-09 19:58:18 | 0 | 1 | Abc Def | | abc@example.com |
| 69963 | 70239 | 2022-04-09 20:16:03 | 0 | 1 | Abc Def | | abc@example.com |
| 70187 | 70463 | 2022-04-10 09:12:36 | 0 | 1 | Abc Def | | abc@example.com |
| 70512 | 70788 | 2022-04-11 08:56:05 | 0 | 1 | Abc Def | | abc@example.com |
| 70807 | 71086 | 2022-04-11 13:12:03 | 0 | 1 | Abc Def | | abc@example.com |
| 71438 | 71723 | 2022-04-13 12:59:23 | 0 | 1 | Abc Def | | abc@example.com |
| 71497 | 71782 | 2022-04-13 16:04:59 | 0 | 1 | | | abc@example.com |
| 72226 | 72511 | 2022-04-17 21:34:11 | 0 | 1 | Abc Def | | abc@example.com |
| 72271 | 72556 | 2022-04-18 09:28:05 | 0 | 1 | Abc Def | | abc@example.com |
| 72666 | 72951 | 2022-04-19 11:15:03 | 0 | 1 | Abc Def | | abc@example.com |
| 73085 | 73370 | 2022-04-21 11:04:11 | 0 | 1 | Abc Def | | abc@example.com |
| 73128 | 73413 | 2022-04-21 14:13:28 | 0 | 1 | Abc Def | | abc@example.com |
| 73397 | 73682 | 2022-04-22 16:34:09 | 0 | 1 | Abc Def | | abc@example.com |
| 73940 | 74225 | 2022-04-26 11:41:20 | 0 | 1 | Abc Def | | abc@example.com |
| 73996 | 74281 | 2022-04-26 15:03:42 | 0 | 1 | Abc Def | | abc@example.com |
| 74144 | 74429 | 2022-04-27 11:22:15 | 0 | 1 | Abc Def | | abc@example.com |
| 74253 | 74538 | 2022-04-27 19:03:50 | 0 | 1 | Abc Def | | abc@example.com |
| 74286 | 74571 | 2022-04-28 08:37:25 | 0 | 1 | Abc Def | | abc@example.com |
| 74762 | 75047 | 2022-04-30 23:39:51 | 0 | 1 | Abc Def | | abc@example.com |
| 74914 | 75199 | 2022-05-02 12:12:20 | 0 | 1 | Abc Def | | abc@example.com |
| 75160 | 75445 | 2022-05-04 11:56:46 | 0 | 1 | Abc Def | | abc@example.com |
| 76091 | 76376 | 2022-05-10 15:40:41 | 0 | 1 | Abc Def | | abc@example.com |
| 77208 | 77493 | 2022-05-17 14:42:07 | 0 | 1 | Abc Def | | abc@example.com |
| 77481 | 77766 | 2022-05-19 07:58:28 | 0 | 1 | Abc Def | | abc@example.com |
| 77633 | 77918 | 2022-05-19 20:38:10 | 0 | 1 | Abc Def | | abc@example.com |
| 77947 | 78232 | 2022-05-22 09:49:14 | 0 | 1 | Abc Def | | abc@example.com |
| 78246 | 78531 | 2022-05-24 08:54:21 | 0 | 1 | Abc Def | | abc@example.com |
| 78339 | 78624 | 2022-05-24 14:38:18 | 0 | 1 | Abc Def | | abc@example.com |
| 78663 | 78948 | 2022-05-26 13:57:02 | 0 | 1 | Abc Def | | abc@example.com |
| 79110 | 79395 | 2022-05-30 10:30:57 | 0 | 1 | Abc Def | | abc@example.com |
| 79791 | 80076 | 2022-06-02 11:16:47 | 0 | 1 | Abc Def | | abc@example.com |
| 80272 | 80557 | 2022-06-05 15:49:40 | 0 | 1 | Abc Def | | abc@example.com |
| 80722 | 81007 | 2022-06-07 19:49:39 | 0 | 1 | Abc Def | | abc@example.com |
| 81089 | 81374 | 2022-06-09 16:20:43 | 0 | 1 | Abc Def | | abc@example.com |
| 81164 | 81449 | 2022-06-10 08:34:36 | 0 | 1 | Abc Def | | abc@example.com |
| 81256 | 81541 | 2022-06-10 12:43:31 | 0 | 1 | Abc Def | | abc@example.com |
| 81526 | 81811 | 2022-06-13 10:24:41 | 0 | 1 | Abc Def | | abc@example.com |
| 81610 | 81895 | 2022-06-13 14:58:36 | 0 | 1 | Abc Def | | abc@example.com |
| 81772 | 82057 | 2022-06-14 12:10:57 | 0 | 1 | Abc Def | | abc@example.com |
| 82685 | 82970 | 2022-06-20 09:26:39 | 0 | 1 | Abc Def | | abc@example.com |
| 83102 | 83387 | 2022-06-22 09:00:35 | 0 | 1 | Abc Def | | abc@example.com |
| 83889 | 84174 | 2022-06-26 21:27:32 | 0 | 1 | Abc Def | | abc@example.com |
| 84807 | 85092 | 2022-06-30 18:52:23 | 0 | 1 | Abc Def | | abc@example.com |
| 84895 | 85180 | 2022-07-01 12:38:22 | 0 | 1 | Abc Def | | abc@example.com |
| 85197 | 85482 | 2022-07-04 09:13:22 | 0 | 1 | Abc Def | | abc@example.com |
| 86554 | 86839 | 2022-07-12 18:46:06 | 0 | 1 | Abc Def | | abc@example.com |
| 86996 | 87281 | 2022-07-14 21:07:05 | 0 | 1 | Abc Def | | abc@example.com |
| 87116 | 87401 | 2022-07-15 15:33:43 | 0 | 1 | Abc Def | | abc@example.com |
| 87192 | 87477 | 2022-07-16 12:31:46 | 0 | 1 | Abc Def | | abc@example.com |
| 87229 | 87514 | 2022-07-16 20:58:02 | 0 | 1 | Abc Def | | abc@example.com |
| 87759 | 88044 | 2022-07-20 09:49:41 | 0 | 1 | Abc Def | | abc@example.com |
| 87779 | 88064 | 2022-07-20 11:09:22 | 0 | 1 | Abc Def | | abc@example.com |
| 91552 | 91843 | 2022-07-25 18:52:33 | 0 | 1 | Abc Def | | abc@example.com |
| 91906 | 92197 | 2022-07-26 15:59:43 | 0 | 1 | Abc Def | | abc@example.com |
| 92309 | 92600 | 2022-07-27 18:10:41 | 0 | 1 | Abc Def | | abc@example.com |
| 92583 | 92874 | 2022-07-28 18:57:42 | 0 | 1 | Abc Def | | abc@example.com |
| 93172 | 93463 | 2022-08-01 15:32:20 | 0 | 1 | Abc Def | | abc@example.com |
| 93397 | 93688 | 2022-08-02 17:09:57 | 0 | 1 | Abc Def | | abc@example.com |
| 93584 | 93875 | 2022-08-03 14:37:06 | 0 | 1 | Abc Def | | abc@example.com |
| 93971 | 94262 | 2022-08-05 13:24:49 | 0 | 1 | Abc Def | | abc@example.com |
| 94019 | 94310 | 2022-08-05 19:49:00 | 0 | 1 | Abc Def | | abc@example.com |
| 94060 | 94351 | 2022-08-06 10:18:22 | 0 | 1 | Abc Def | | abc@example.com |
| 94102 | 94393 | 2022-08-06 19:05:40 | 0 | 1 | Abc Def | | abc@example.com |
| 94105 | 94396 | 2022-08-06 19:06:11 | 0 | 1 | Abc Def | | abc@example.com |
| 94501 | 94792 | 2022-08-09 19:48:34 | 0 | 1 | Abc Def | | abc@example.com |
| 94506 | 94797 | 2022-08-09 20:43:13 | 0 | 1 | Abc Def | | abc@example.com |
| 94680 | 94971 | 2022-08-10 17:21:23 | 0 | 1 | Abc Def | | abc@example.com |
| 94756 | 95047 | 2022-08-11 11:20:00 | 0 | 1 | Abc Def | | abc@example.com |
| 94854 | 95145 | 2022-08-11 16:19:27 | 0 | 1 | Abc Def | | abc@example.com |
| 95085 | 95376 | 2022-08-13 12:18:16 | 0 | 1 | Abc Def | | abc@example.com |
| 95302 | 95593 | 2022-08-15 21:52:55 | 0 | 1 | Abc Def | | abc@example.com |
| 95368 | 95659 | 2022-08-16 11:28:21 | 0 | 1 | Abc Def | | abc@example.com |
| 95545 | 95836 | 2022-08-17 11:29:18 | 0 | 1 | Abc Def | | abc@example.com |
| 95776 | 96067 | 2022-08-18 12:34:01 | 0 | 1 | Abc Def | | abc@example.com |
| 95843 | 96134 | 2022-08-18 18:53:27 | 0 | 1 | Abc Def | | abc@example.com |
| 96494 | 96785 | 2022-08-23 15:58:17 | 0 | 1 | Abc Def | | abc@example.com |
| 97043 | 97334 | 2022-08-26 20:23:05 | 0 | 1 | Abc Def | | abc@example.com |
.
.
| 155787 | 156195 | 2023-09-14 13:29:48 | 0 | 1 | Abc Def | | abc@example.com |
| 155954 | 156362 | 2023-09-15 16:03:14 | 0 | 1 | Abc Def | | abc@example.com |
| 155957 | 156365 | 2023-09-15 16:05:37 | 0 | 1 | Abc Def | | abc@example.com |
| 155960 | 156368 | 2023-09-15 16:06:24 | 0 | 1 | Abc Def | | abc@example.com |
| 155964 | 156372 | 2023-09-15 16:08:57 | 0 | 1 | Abc Def | | abc@example.com |
| 155967 | 156375 | 2023-09-15 16:09:52 | 0 | 1 | Abc Def | | abc@example.com |
| 155968 | 156376 | 2023-09-15 18:01:15 | 0 | 1 | Abc Def | | abc@example.com |
| 155973 | 156381 | 2023-09-15 16:16:59 | 0 | 1 | Abc Def | | abc@example.com |
+-------------+---------+---------------------+-----+----------+----------------+--------------+------------------+
342 rows in set (0.00 sec)

SKaero · September 16, 2023, 01:44:19 PM

They have different user_ids so look at the differences between the user accounts records.

ninad · September 16, 2023, 01:58:07 PM

Thank you so much for the reply.
I got similar output for different user ids.

mysql> select * from users where user_id="156374";
Empty set (0.00 sec)

mysql> select * from users where user_id="156375";
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
| user_id | username | mail_host | created | last_login | failed_login | failed_login_counter | language | preferences |
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
| 156375 | abc@example.com | imap.example.com | 2023-09-15 16:09:16 | 2023-09-15 16:09:16 | NULL | NULL | en_GB | a:1:{s:11:"client_hash";s:16:"fqBblnewnbZ2Aajs";} |
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from users where user_id="156365";
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
| user_id | username | mail_host | created | last_login | failed_login | failed_login_counter | language | preferences |
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
| 156365 | abc@example.com | imap.example.com | 2023-09-15 16:05:37 | 2023-09-15 16:05:37 | NULL | NULL | en_GB | a:1:{s:11:"client_hash";s:16:"ny0cKjYDs3UIAtwD";} |
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from users where user_id="156368";
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
| user_id | username | mail_host | created | last_login | failed_login | failed_login_counter | language | preferences |
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
| 156368 | abc@example.com | imap.example.com | 2023-09-15 16:06:24 | 2023-09-15 16:06:24 | NULL | NULL | en_GB | a:1:{s:11:"client_hash";s:16:"ika3aP46jakExsvC";} |
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from users where user_id="156362";
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
| user_id | username | mail_host | created | last_login | failed_login | failed_login_counter | language | preferences |
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
| 156362 | abc@example.com | imap.example.com | 2023-09-15 16:02:31 | 2023-09-15 16:02:31 | NULL | NULL | en_US | a:1:{s:11:"client_hash";s:16:"PLVg20afbr0e6k4U";} |
+---------+------------------+-----------------+---------------------+---------------------+--------------+----------------------+----------+---------------------------------------------------+
1 row in set (0.00 sec)

SKaero · September 17, 2023, 10:05:22 AM

That shouldn't be possible there should be a unique index on the users table that would prevent duplicate records with the same username and mail_host.

In the "system" table there should be a "roundcube-version" value, what is that set to?

ninad · September 18, 2023, 01:26:10 AM

ninad · September 18, 2023, 05:45:44 AM

I took a backup of the database and then deleted all the entries related to my email and username.

After this, I logged in and created a signature, and logged out (Firefox)
Then, I opened Google Chrome and logged in again, there was no signature.

While checking the identities table in the database, I found 2 entries, one with a signature and one without a signature.
the only difference was with the default language. It was en_US in one entry and en_GB in another.

Also, I observed multiple duplicate entries for other users as well.

SKaero · September 18, 2023, 10:55:27 AM

Even in Roundcube 1.4.3 there should be a unique index that would prevent that from happening. If you run the following sql command what is output?

Code Select

show create table users;

ninad · September 18, 2023, 12:45:30 PM

Below is the output of the sql query

show create table users;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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=156659 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SKaero · September 18, 2023, 01:23:51 PM

In the user table there is a unique index:

Code Select

UNIQUE KEY `username` (`username`,`mail_host`)

But there are multiple users that have the same username and mail_host. Something seems to be wrong with your mysql installation, it looks like its not enforcing unique index likely because of the Galera cluster.

ninad · September 18, 2023, 01:38:18 PM

Oh, What is the workaround for this? I can create another instance of MYSQL and debug the Galera separately. How could I export the correct or unique data from Galera?

There is nothing in the Galera cluster logs and Roundcube SQL debug logs.

SKaero · September 18, 2023, 04:34:56 PM

I'm not a Galera so I don't know why its happening or what the fix for it is but that is cause of the issue. You may want to reach out in a Galera community to see if anyone has run into the same issue.

As for correcting the data you would need to make sure that there is only one record for each username and mail_host combination, it would likely be a good idea to build a script to get all contacts and merge settings together so no data is lost.

ninad · September 19, 2023, 07:22:03 AM

@SKaero, You are right. Something is wrong with the Galera Mariadb.

In the user table, when I am searching with username="abc@example.com"

select * from users where username="abc@example.com";
Empty set (0.000 sec)

However, I get the details if I use like as follows.
select * from users where username like "%abc%";

isp-telecom · September 26, 2023, 04:52:10 AM

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?

ninad · September 26, 2023, 09:03:16 AM

I am using MariaDB-server-10.6.4-1. In one or 2 days I am planning to move everything to a standalone MariaDB from Galera

isp-telecom · September 26, 2023, 11:05:12 AM

I try only to alter the table

alter table users modify username varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL

it seems work