Duplicate entry 'mail@domain.tld-mail.mailserver.tld' for key 'username'

[Roi]

I'm getting these error messages for one of our users. All other users can log into Roundcube just fine. Just this single mailuser cannot login.

I checked the database - there is no duplicate entry.

[28-Jul-2011 21:27:05] MDB2 Error: constraint violation (-3): _doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_mysql_61ca925c69c44d33e7038b33216e5d9c8aec039f8 USING @0, @1, @2, @3]
[Native code: 1062]
[Native message: Duplicate entry 'mail@domain.tld-mail.mailserver.tld' for key 'username']

[03-Aug-2011 03:05:00] PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 80 bytes) in /var/www/mail.mailserver.tld_https/web/program/lib/PEAR.php on line 884
[03-Aug-2011 03:05:00] PHP Fatal error:  Call to a member function get() on a non-object in Unknown on line 0
[03-Aug-2011 06:03:52] MDB2 Error: constraint violation (-3): _doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_mysql_6efa6c5e02877b33a4c80d8ed2657d032316680d3 USING @0, @1, @2, @3]
[Native code: 1062]
[Native message: Duplicate entry 'mail@domain.tld-mail.mailserver.tld' for key 'username']

[alec]

Check users table for this login? It might be some case-sensitivity related issue.

[Roi]

Replying to alec:

Check users table for this login? It might be some case-sensitivity related issue.

I checked the database. The username is written in lower case characters completly.
When the user (or for testing me) tried to logon to RC we also used lower case characters only.

[alec]

Enable sql_debug and check the log. You will see all executed SQL queries, maybe this will help to find what's wrong.

[Roi]

Replying to alec:

Enable sql_debug and check the log. You will see all executed SQL queries, maybe this will help to find what's wrong.

Here you are:

[09-Aug-2011 14:25:30 +0200]: query(1): SELECT vars, ip, UNIX_TIMESTAMP(changed) AS changed FROM session WHERE sess_id = 'gc2d3815aoch90ljsd0odnr921';
[09-Aug-2011 14:25:30 +0200]: query(1): INSERT INTO logs(log, userip, hostip, line, user_id, username) VALUES ('access', '217.84.42.221', '46.4.23.118', 'POST: RoundCube Root', 0, 'login');
[09-Aug-2011 14:25:30 +0200]: query(1): DELETE FROM session WHERE sess_id = 'gc2d3815aoch90ljsd0odnr921';
[09-Aug-2011 14:25:30 +0200]: query(1): SELECT * FROM users WHERE mail_host = 'mail.mailserver.tld' AND username = BINARY 'mail@domain.tld';
[09-Aug-2011 14:25:30 +0200]: query(1): SELECT * FROM users WHERE mail_host = 'mail.mailserver.tld' AND alias = BINARY 'mail@domain.tld';
[09-Aug-2011 14:25:30 +0200]: query(1): INSERT INTO users (created, last_login, username, mail_host, alias, language) VALUES (now(), now(), 'mail@domain.tld', 'mail.mailserver.tld', '', 'de_DE');
[09-Aug-2011 14:25:30 +0200]: query(1): INSERT INTO logs(log, userip, hostip, line, user_id, username) VALUES ('errors', '217.84.42.221', '46.4.23.118', 'DB Error: MDB2 Error: constraint violation Query: _doQuery: [Error message: Could not execute statement] [Last executed query: INSERT INTO users (created, last_login, username, mail_host, alias, language) VALUES (now(), now(), \'mail@domain.tld\', \'mail.mailserver.tld\', \'\', \'de_DE\')] [Native code: 1062] [Native message: Duplicate entry \'mail@domain.tld-mail.mailserver.tld\' for key \'username\']  in /var/www/mail.mailserver.tld_https/web/program/include/rcube_mdb2.php on line 642 (POST /?_task=login&_action=login)', 0, 'login');
[09-Aug-2011 14:25:30 +0200]: query(1): SELECT COUNT(id) FROM logs;
[09-Aug-2011 14:25:30 +0200]: query(1): SELECT LAST_INSERT_ID();
[09-Aug-2011 14:25:30 +0200]: query(1): SELECT * FROM users WHERE user_id = 5849;
[09-Aug-2011 14:25:30 +0200]: query(1): DELETE FROM session WHERE sess_id = 'gc2d3815aoch90ljsd0odnr921';
[09-Aug-2011 14:25:30 +0200]: query(1): INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('gc2d3815aoch90ljsd0odnr921', 'bGFuZ3VhZ2V8TjthdXRoX3RpbWV8aToxMzEyODkyNzMwO3RlbXB8YjoxOw==', '217.84.42.221', '2011-08-09 14:25:30', '2011-08-09 14:25:30');

[alec]

Did you resolve the issue? This looks to me as some stupid mysql bug.

[Roi]

Replying to alec:

Did you resolve the issue? This looks to me as some stupid mysql bug.

No, I did not resolve the problem. I did not do anything after I posted the debug log. I'm using the standard tables of ISPConfig for IMAP mail user identification via , which is used by a lot of people. The table looks very simple. I also switch (for another reason) from Courier to Dovecot just lately which had no effect on the problem here.

[alec]

The solution would be to change column type to "VARCHAR(128) BINARY".

[alec]

Fixed in [d5ae9772].

[arekm]

That will break setups where username is case insensitive (as it is in my setup).

Now if user logs in as: "fred" it will be different from "Fred" while in my mail system fred == Fred (and any other form of fred) and there should be only one entry in mysql for all such variants.

[arekm]

Hm, or won't break anything thanks to
$rcmail_configlogin_lc? = true;