auto_increment counter not replicated

[leeyc0]

We found a problem in auto_increment counter replication. Below is the details

Server 1:
mysql> show create table tbl_user_image\G
*************************** 1. row ***************************
Table: tbl_user_image
Create Table: 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
1 row in set (0.00 sec)

Server 2:
mysql> show create table tbl_user_image\G
*************************** 1. row ***************************
Table: tbl_user_image
Create Table: 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=383188 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

Note the AUTO_INCREMENT in the create table statement. When a new record is inserted into Server 1, the AUTO_INCREMENT counter in Server 1 increases by 2 (it is a two-node set-up), but not replicated to Server 2. Is there any way to fix this problem?

[rennschnecke]

just guessing!!!:

I would think this is no error!

Why?
Node 1 has an autoincrement offset of 1…
Node 2 has an autoincrement offset of 2…

I’m not sure as I said but it’s seems all ok and I would think node 1 had some more initial generated inserts as node 2 had!
So its counter is a bit higher and odd while nodes 2 count is a bit lower because of less inserts (haproxy?) and its count value is even!
What did you expect to see?

[rennschnecke]

Please just test:
What values do get if you insert directly data in each node?
Will they grow as expected?

[leeyc0]

Actually I don’t think it is a problem either, but our customer had concerns. They expect that insert in Server 1 will also increase the AUTO_INCREMENT in Server 2.

I had created another database instance pair but unfortunately we cannot repeat their problem. I saw the AUTO_INCREMENT increases in both sides.

I started to suspect the problem is caused by some strange sql statement the customer used to update the table. I am asking them for further information. I am also going to create another table in their database pair and use simple insert statements to see if auto increment counter increases.

[leeyc0]

After some testing, found out it’s the REPLACE statement causing the problem:

REPLACE INTO tbl_user_image (stub) VALUES (‘a’);

If this statement is issued, the id will not be updated.

I have further confirmed the problem using a standard mysql with master-slave replication, and found out the problem only occurs when binlog_format is set to ROW. Setting to STATEMENT (or MIXED) doesn’t have this problem.

Since PXC requires row-based binlog format, this problem have no solution…

[rennschnecke]

@leeyc0;18583

REPLACE INTO tbl_user_image (stub) VALUES (‘a’);

why this should update the primary key?

[rennschnecke]

@leeyc0;18580

Actually I don’t think it is a problem either, but our customer had concerns. They expect that […]

May I print this on my new t-shirt? Great words!!!

[rennschnecke]

@leeyc0;18580

I had created another database instance pair but unfortunately we cannot repeat their problem. I saw the AUTO_INCREMENT increases in both sides.

Please test this like I mentioned! This seems very easy to find out!!!

I started to suspect the problem is caused by some strange sql statement the customer used to update the table. I am asking them for further information. I am also going to create another table in their database pair and use simple insert statements to see if auto increment counter increases.

Indeed! This must be the problem. Most people think databases are just a great big holes with infinite speed…
If you could abstract the problem a bit please post the query to analyze!

But:
Out of some experiences with “cool queries…” I would think none really want this to see in expectation of some nightmares!

[leeyc0]

@rennschnecke;18657

Please test this like I mentioned! This seems very easy to find out!!!

I cannot touch their production database at the time I posted #3, so I have to rebuild a test enviroment (but cannot repeat their symptom using insert statement). After I am able to ask for the exact statement, the toubleshooting progress went as fast as a rocket.

@rennschnecke;18657

Indeed! This must be the problem. Most people think databases are just a great big holes with infinite speed…
If you could abstract the problem a bit please post the query to analyze!

But:
Out of some experiences with “cool queries…” I would think none really want this to see in expectation of some nightmares!

Actually I already found the source of the problem as stated in #5, turns out to be the problem of a particular statement under row-based replication.

[leeyc0]

@rennschnecke;18655

why this should update the primary key?

This is the statement provided the customer, and we are not responsible for the program. So I am unsure what exactly they want to so in this statement. I can only say that from the documentation:

“REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.”

Since there is a unique key (stub) conflict, the old record is deleted and a new record is inserted, therefore a new ID is assigned…

[leeyc0]

In summary: the database schema is:

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;

The database already contain this record:
id=383344, stub=‘a’

Note that there is a unique key in the columb ‘stub’.

Now, the customer executes the statement:

REPLACE INTO tbl_user_image (stub) VALUES (‘a’);

Since there is a unique key conflict, the record with (id=383344, stub=‘a’) is deleted and a new record with (id=383345, stub=‘a’) is inserted, and AUTO_INCREMENT in show create table tbl_user_image is set to 383346, and the new AUTO_INCREMENT value not replicated to another server.

I tested it in another mysql master-slave replication pair (using standard mysql), and found out that when the binlog format is set to ‘statement’ or ‘mixed’, the AUTO_INCREMENT value can be replicated to slave, but when binlog format is set to ‘row’, the AUTO_INCREMENT value cannot be replicated…

[rennschnecke]

Ah I got you wrong!
I was confused about the offsets and thought that would be a problem but these are internals which should not be important for your query…
So you finally got a conflict with 2 different values on master and slave, right?
If so, do you ignore replication errors in your my.cnf ??

-> trapped again, it’s a cluster, I guess.
Hmm, I just found one way to get the nodes out of sync! And this is when you take one node as a slave from another master to replicate data into a cluster. Then you have to set “log-slave-updates”!

[rennschnecke]

@leeyc0;18681

This is the statement provided the customer, and we are not responsible for the program. So I am unsure what exactly they want to so in this statement. I can only say that from the documentation:

“REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.”

Since there is a unique key (stub) conflict, the old record is deleted and a new record is inserted, therefore a new ID is assigned…

Seems like the customer knows what to do…