Deadlock while running ineffective UPDATE query on 5.5.33 with STATEMENT-based binlog format

[Marko Skender]

After upgrading cluster nodes to 5.33 we are faced with following issue.

Running this query:

update users set user_agent='Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.66 Safari/537.36', ref_id='45586829' where id=878424

Yields:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The error is produced if fields are the same as already committed to database (i.e. running query several times in a row).
If you run the query with different values than those already in the table, the query is successful.
If you run the query again with same values again, the above error is produced every time on every node.

Note that we have single node for read/write over HAProxy, and others are for backup/standby purposes.
We had no issues with this on 5.30. or any other previos versions of MySQL/Percona server (from Mysql 4.x, 5.x, Percona Server 5.5 to XtraDB cluster 5.30).

For testing purposes, I have lowered wsrep_slave_threads to 1, but it didn't help.

I have also tried testing other tables and other databases and updating other kinds of fields (int, varchar, etc) but the problem is persists.

All afflicted databases are INNODB.

[Alex Yurchenko (ayurchen)]

And let me guess, your effective binlog_format is STATEMENT?

[Marko Skender (marko-r)]

I feel pretty stupid right now!

Of course, you are correct, I was missing binlog_format in my.cnf from the very day we installed the cluster. :(

Sorry to bother you, I was 100% sure the directive was there from setup (because I know it's a requirement) and didn't bother to check twice.

[Raghavendra D Prabhu (raghavendra-prabhu)]

In addition to being documented to use ROW binlog_format, I think the binlog_format set to STATEMENT (and/or MIXED) should produce warnings in the error log.

[Alex Yurchenko (ayurchen)]

So this is a bug with wsrep STATEMENT replication. I'd suggest that reference to STATEMENT be put into title.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1451