Hi, Could you please describe you galera setup, MySQL and galera versions, and how many nodes and configuration of the nodes. I could not repeat your problem using 2-node galera cluster as seen below: jan@jan-GE70-0NC-0ND ~/mysql/galera-5.5 $ /usr/local/mysql/bin/mysql -u root -S /home/jan/mysql/galera-cluster-5.5/node0/mysql.4000.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.34-MariaDB-wsrep-debug-log Source distribution, wsrep_23.7.6.r3457 Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [test]> CREATE TABLE `user` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `userid` varchar(30) DEFAULT NULL, -> `passwd` varchar(30) DEFAULT NULL, -> `domain` varchar(30) DEFAULT NULL, -> `homeserver` varchar(30) DEFAULT NULL, -> `quotaInMiB` int(11) DEFAULT '100', -> `password_type` varchar(10) DEFAULT 'plain', -> `spamaction` enum('REJECT','ACCEPT','TAGGING') DEFAULT 'REJECT', -> `spampoint` decimal(5,1) DEFAULT '5.2', -> `virusaction` enum('REJECT','ACCEPT','REPLACE') DEFAULT 'REJECT', -> `keepFwdCopy` tinyint(1) DEFAULT '0', -> `quotaUsed` int(11) DEFAULT '0', -> `extRef` varchar(12) DEFAULT NULL, -> `mandate` int(11) DEFAULT '0', -> `FwdBounceCount` decimal(1,0) DEFAULT '0', -> `FwdActive` tinyint(1) DEFAULT '1', -> `lastActive` datetime DEFAULT '0000-00-00 00:00:00', -> `lastLogin` datetime DEFAULT '0000-00-00 00:00:00', -> `disabled` tinyint(1) DEFAULT '0', -> PRIMARY KEY (`id`), -> UNIQUE KEY `userid` (`userid`,`domain`) -> ) ENGINE=InnoDB AUTO_INCREMENT=26944 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.20 sec) MariaDB [test]> MariaDB [test]> insert into user(id, userid, domain) values (1, 'jan','test'); Query OK, 1 row affected (0.00 sec) MariaDB [test]> insert into user(id, userid, domain) values (5, 'benoit','ambonet.ch'); Query OK, 1 row affected (0.01 sec) MariaDB [test]> insert into user(id, userid, domain) values (7, 'kerr','test'); Query OK, 1 row affected (0.00 sec) MariaDB [test]> commit work; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> UPDATE user set lastLogin='2013-12-19 14:55:09' where -> concat(userid,'@',domain)='benoit@ambonet.ch'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test]> MariaDB [test]> UPDATE user set lastLogin='2013-12-19 14:55:16' where -> concat(userid,'@',domain)='benoit@ambonet.ch'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test]> MariaDB [test]> UPDATE user set lastLogin='2013-12-19 14:55:17' where -> concat(userid,'@',domain)='benoit@ambonet.ch'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test]> MariaDB [test]> UPDATE user set lastLogin='2013-12-19 14:55:17' where -> concat(userid,'@',domain)='benoit@ambonet.ch'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 MariaDB [test]> MariaDB [test]> UPDATE user set lastLogin='2013-12-19 14:55:17' where -> concat(userid,'@',domain)='benoit@ambonet.ch'; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0
Hello
For those with the same problem, I found a completely stupid solution, which works!
update user set lastlogin = IF(lastlogin != NOW(),NOW(),NOW()) where concat(userid,'@',domain)='$USER'
For those now knowing what IF does...
If lastlogin is not NOW() update it and set it to NOW() else set it to NOW()
Well actualy this does exactly the same as update user set lastlogin = NOW() but surprisingly does not lead to the deadlock error, if lastlogin is already NOW().
Mit freundlichen Grüssen
Benoit Panizzon
-- -- Jan Lindström Principal Engineer MariaDB | MaxScale | skype: jan_p_lindstrom www.skysql.com <http://www.skysql.com/> Twitter <http://twitter.com/skysql> Blog <http://www.skysql.com/blog/> Facebook <http://www.facebook.com/skysql> LinkedIn <http://www.linkedin.com/company/1214250> Google+ <https://plus.google.com/117544963211695643458/posts>
