Multi-row inserts into a table with auto_increment column when PXC node is in async replication and binlog_format is STATEMENT, causes data inconsistencies
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
5.5 |
Won't Fix
|
High
|
Unassigned | |||
5.6 |
Fix Released
|
High
|
Unassigned |
Bug Description
Suppose we have a PXC 3 node cluster which is replicating from an async master. Now if the binlog_format is STATEMENT and multi-row inserts are executed on a table with an auto_increment column such that values are automatically generated by MySQL, then the PXC node generates wrong auto_increment values which are different from what was generated on the async master.
The test case follows:
master> use test
Database changed
master> drop table t1;
Query OK, 0 rows affected (0.14 sec)
master> CREATE TABLE `t1` (
-> `i` int(11) NOT NULL AUTO_INCREMENT,
-> `c` char(32) DEFAULT 'dummy_text',
-> PRIMARY KEY (`i`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.37 sec)
master> select * from t1;
Empty set (0.00 sec)
master> insert into t1(i) values(null);
Query OK, 1 row affected (0.27 sec)
master> select * from t1;
+---+------------+
| i | c |
+---+------------+
| 1 | dummy_text |
+---+------------+
1 row in set (0.00 sec)
pxc_node1> select * from t1; -- all good here because only a single value was inserted
+---+------------+
| i | c |
+---+------------+
| 1 | dummy_text |
+---+------------+
1 row in set (0.00 sec)
master> insert into t1(i) values(null), (null), (null); -- OK we now insert 3 values such that all 3 have to be automatically generated
Query OK, 3 rows affected (0.51 sec)
Records: 3 Duplicates: 0 Warnings: 0
master> select * from t1;
+---+------------+
| i | c |
+---+------------+
| 1 | dummy_text |
| 2 | dummy_text |
| 3 | dummy_text |
| 4 | dummy_text |
+---+------------+
4 rows in set (0.00 sec)
pxc_node1> select * from t1; -- and there is an inconsistency for the auto_inc value generated for the fourth row
+---+------------+
| i | c |
+---+------------+
| 1 | dummy_text |
| 2 | dummy_text |
| 3 | dummy_text |
| 6 | dummy_text |
+---+------------+
4 rows in set (0.00 sec)
The variable wsrep_auto_
pxc_node1> show variables like '%increment%';
+------
| Variable_name | Value |
+------
| auto_increment_
| auto_increment_
| div_precision_
| innodb_
| wsrep_auto_
+------
The problem can be resolved by using binlog_format=ROW on the async master or by disabling wsrep_auto_
It looks to me as though PXC node does not completely obey all the contextual information that is logged in case of SBR. For example, when I look at the relay log on the PXC node it has the following relevant variables set:
SET @@session.
However, either that is not applied correctly to the slave_sql_thread or the variable 'wsrep_
Looks similar to lp:587170 in terms of workaround.
Also, there seem to be issues with STATEMENT binlog_format as
reported here:
lp:1161060
lp:1160854