Not the answer you need?
Register and ask your own question!

auto_increment counter not replicated

leeyc0leeyc0 EntrantInactive User Role Beginner
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?

Comments

  • rennschneckerennschnecke Contributor Current User Role Beginner
    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?
  • rennschneckerennschnecke Contributor Current User Role Beginner
    Please just test:
    What values do get if you insert directly data in each node?
    Will they grow as expected?
  • leeyc0leeyc0 Entrant Inactive User Role Beginner
    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.
  • leeyc0leeyc0 Entrant Inactive User Role Beginner
    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...
  • rennschneckerennschnecke Contributor Current User Role Beginner
    leeyc0 wrote: »

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

    why this should update the primary key?
  • rennschneckerennschnecke Contributor Current User Role Beginner
    leeyc0 wrote: »
    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!!! :-)
  • rennschneckerennschnecke Contributor Current User Role Beginner
    leeyc0 wrote: »

    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! ;)
  • leeyc0leeyc0 Entrant Inactive User Role Beginner
    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. :p
    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. :p
  • leeyc0leeyc0 Entrant Inactive User Role Beginner

    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...
  • leeyc0leeyc0 Entrant Inactive User Role Beginner
    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...
  • rennschneckerennschnecke Contributor Current User Role Beginner
    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"!
  • rennschneckerennschnecke Contributor Current User Role Beginner
    leeyc0 wrote: »

    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....
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.