Comment 3 for bug 1285070

Revision history for this message
Yiu-Chung Lee (lee-yiu-chung) wrote :

Ref: http://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/18544-auto_increment-counter-not-replicated

I met the same problem (but using different statement). Below is the table schema:

CREATE TABLE `tbl_user_image` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`stub` char(1) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB AUTO_INCREMENT=383345 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

And this is the sql statement used to update the table:

REPLACE INTO `tbl_user_image` (`stub`) VALUES ('a');

After some troubleshooting, I found out the problem is in mysql itself. I tested the above statement in a standard master-slave setup under mysql-server-5.5.35, and discovered the following:

1. No problem if binlog_format is set to statement (or mixed)
2. No problem if binlog_format is set to row and insert statement is used
3. No problem if binlog_format is set to row, replace statement is used and the table is empty (or no unique key conflict)
3. Problem if binlog_format is set to row and replace statement is used and there is a unique key conflict