# auto\_increment counter not replicated
**URL:** https://forums.percona.com/t/auto-increment-counter-not-replicated/3606
**Category:** Percona XtraDB Cluster 5.x
**Created:** [July 9, 2014, 10:05am UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606 "2014-07-09T10:05:43Z")
**Posts on this page:** 13
**Page:** 1
### Author:  [@leeyc0](https://forums.percona.com/u/leeyc0)
#### Post date: [July 9, 2014, 10:05am UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/1 "2014-07-09T10:05:43Z")
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?
---
### Author:  [@rennschnecke](https://forums.percona.com/u/rennschnecke)
#### Post date: [July 9, 2014, 2:01pm UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/2 "2014-07-09T14:01:35Z")
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?
---
### Author:  [@rennschnecke](https://forums.percona.com/u/rennschnecke)
#### Post date: [July 9, 2014, 2:26pm UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/3 "2014-07-09T14:26:28Z")
Please just test:
What values do get if you insert directly data in each node?
Will they grow as expected?
---
### Author:  [@leeyc0](https://forums.percona.com/u/leeyc0)
#### Post date: [July 9, 2014, 8:56pm UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/4 "2014-07-09T20:56:00Z")
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.
---
### Author:  [@leeyc0](https://forums.percona.com/u/leeyc0)
#### Post date: [July 10, 2014, 1:42am UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/5 "2014-07-10T01:42:02Z")
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…
---
### Author:  [@rennschnecke](https://forums.percona.com/u/rennschnecke)
#### Post date: [July 10, 2014, 2:49pm UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/6 "2014-07-10T14:49:00Z")
> [@leeyc0;18583](#):
>
> REPLACE INTO `tbl_user_image` (`stub`) VALUES (‘a’);
why this should update the primary key?
---
### Author:  [@rennschnecke](https://forums.percona.com/u/rennschnecke)
#### Post date: [July 10, 2014, 2:55pm UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/7 "2014-07-10T14:55:19Z")
> [@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!!! 🙂
---
### Author:  [@rennschnecke](https://forums.percona.com/u/rennschnecke)
#### Post date: [July 10, 2014, 3:07pm UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/8 "2014-07-10T15:07:28Z")
> [@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! 😉
---
### Author:  [@leeyc0](https://forums.percona.com/u/leeyc0)
#### Post date: [July 10, 2014, 10:25pm UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/9 "2014-07-10T22:25:12Z")
> [@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. 😛
---
### Author:  [@leeyc0](https://forums.percona.com/u/leeyc0)
#### Post date: [July 10, 2014, 10:31pm UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/10 "2014-07-10T22:31:37Z")
> [@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…
---
### Author:  [@leeyc0](https://forums.percona.com/u/leeyc0)
#### Post date: [July 11, 2014, 2:11am UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/11 "2014-07-11T02:11:25Z")
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…
---
### Author:  [@rennschnecke](https://forums.percona.com/u/rennschnecke)
#### Post date: [July 12, 2014, 8:28am UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/12 "2014-07-12T08:28:50Z")
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”!
---
### Author:  [@rennschnecke](https://forums.percona.com/u/rennschnecke)
#### Post date: [August 4, 2014, 4:45am UTC](https://forums.percona.com/t/auto-increment-counter-not-replicated/3606/13 "2014-08-04T04:45:43Z")
> [@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…