Announcement

Announcement Module
Collapse
No announcement yet.

auto_increment counter not replicated

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • auto_increment counter not replicated

    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?

  • #2
    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?

    Comment


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

      Comment


      • #4
        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.
        Last edited by leeyc0; 07-10-2014, 01:56 AM.

        Comment


        • #5
          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...
          Last edited by leeyc0; 07-11-2014, 03:33 AM.

          Comment


          • #6
            Originally posted by leeyc0 View Post

            REPLACE INTO `tbl_user_image` (`stub`) VALUES ('a');
            why this should update the primary key?

            Comment


            • #7
              Originally posted by leeyc0 View Post
              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!!! :-)

              Comment


              • #8
                Originally posted by leeyc0 View Post

                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!


                Comment


                • #9
                  Originally posted by rennschnecke View Post
                  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.

                  Originally posted by rennschnecke View Post
                  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.
                  Last edited by leeyc0; 07-11-2014, 03:34 AM.

                  Comment


                  • #10
                    Originally posted by rennschnecke View Post

                    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...
                    Last edited by leeyc0; 07-11-2014, 03:34 AM.

                    Comment


                    • #11
                      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...

                      Comment


                      • #12
                        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"!
                        Last edited by rennschnecke; 07-12-2014, 10:03 AM.

                        Comment


                        • #13
                          Originally posted by leeyc0 View Post

                          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....

                          Comment

                          Working...
                          X