Multi-Valued INSERTs, AUTO_INCREMENT & Percona XtraDB Cluster - Peter Zaitsev

http://ift.tt/1pLrvqj

A common migration path from standalone MySQL/Percona Server to a Percona XtraDB Cluster (PXC) environment involves some measure of time where one node in the new cluster has been configured as a slave of the production master that the cluster is slated to replace. In this way, the new cluster acts as a slave of the production environment – traditional replication takes care of getting the data into the cluster, and then Galera replication handles the intra-cluster traffic. This often works without issue, although there is one case that I’ve encountered recently where special care must be taken to properly configure the stream to ensure that replication does not break. If you use multi-valued inserts with auto-increment columns, then this post is for you.

For purposes of our discussion, assume that we have a basic 3-node PXC cluster that we’ve set up using the PXC Reference Architecture document, and that we’re replicating from an asynchronous master (call it “server A”) into one of the PXC nodes. Without loss of generality, we’ll pick PXC03. Also, for purposes of our discussion, we’ll be working with the following table definition:

  serverA> show create table auto_inc_test;  CREATE TABLE `auto_inc_test` (    `i` int(11) NOT NULL AUTO_INCREMENT,    `stuff` varchar(20) DEFAULT NULL,    PRIMARY KEY (`i`)  ) ENGINE=InnoDB DEFAULT CHARSET=latin1  1 row in set (0.00 sec)  serverA> SELECT * FROM auto_inc_test;  Empty set (0.00 sec)  

If we insert rows into this table one at a time, we have no issues.

  serverA> INSERT INTO auto_inc_test(stuff) VALUES ('first row');  serverA> INSERT INTO auto_inc_test(stuff) VALUES ('second row');  serverA> INSERT INTO auto_inc_test(stuff) VALUES ('third row');  serverA> SELECT * FROM auto_inc_test;  +---+------------+  | i | stuff      |  +---+------------+  | 1 | first row  |  | 2 | second row |  | 3 | third row  |  +---+------------+  PXC03> SELECT * FROM auto_inc_test;  +---+------------+  | i | stuff      |  +---+------------+  | 1 | first row  |  | 2 | second row |  | 3 | third row  |  +---+------------+  

But if we start doing multi-valued inserts, we can run into a problem.

  serverA> INSERT INTO auto_inc_test(stuff) VALUES('first row'),('second row'),('third row');  serverA> INSERT INTO auto_inc_test(stuff) VALUES('fourth row'),('fifth row');  serverA> SELECT * FROM auto_inc_test;  +---+------------+  | i | stuff      |  +---+------------+  | 1 | first row  |  | 2 | second row |  | 3 | third row  |  | 4 | fourth row |  | 5 | fifth row  |  +---+------------+  PXC03> SELECT * FROM auto_inc_test;  +---+------------+  | i | stuff      |  +---+------------+  | 1 | first row  |  | 2 | second row |  | 5 | third row  |  +---+------------+  PXC03> SHOW SLAVE STATUS;  ... output elided ...  Last_SQL_Errno: 1062  Last_SQL_Error: Error 'Duplicate entry '5' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO auto_inc_test (stuff) VALUES ('fourth row'),('fifth row')'  ... output elided ...  

Uh oh. Replication is broken and our data is now inconsistent. So why does this happen and how can we prevent it?

binlog_format

The astute observer will note that I have not yet said anything about the binary log format on the master. If the binary log format on the master is already set to ROW, then the above error will not occur. RBR will properly replicate multi-valued INSERTs to the PXC cluster without issue, and the data will be consistent. Problem solved. However, there may be reasons that the master is not using or cannot use RBR, such as disk space or IOPS limitations, and thus it’s running in MIXED or STATEMENT mode. In that case, we need to look elsewhere….

wsrep_auto_increment_control

When set to ON (the default), this variable has the effect of automatically specifying values for auto_increment_increment and auto_increment_offset based on the cluster size. The idea behind it is to help prevent auto-increment value conflicts when writing to multiple nodes. However, what it also means is that in a multi-node cluster, the auto-increment values generated by any given node will never be consecutive and the “next” auto-increment value on the slave cluster node will always be higher than what the master believes it should be. For example:

  serverA> INSERT INTO auto_inc_test (stuff) VALUES ('first row'),('second row'),('third row');  serverA> SHOW CREATE TABLE auto_inc_test;  CREATE TABLE `auto_inc_test` (    `i` int(11) NOT NULL AUTO_INCREMENT,    `stuff` varchar(20) DEFAULT NULL,    PRIMARY KEY (`i`)  ) ENGINE=InnoDB AU  

Truncated by Planet PHP, read more at the original (another 3010 bytes)

from Planet MariaDB http://ift.tt/1lvLCrP