MySQL Bugs: #28781: InnoDB increments auto-increment value incorrectly with ON DUPLICATE KEY UPDATE

Bug #28781	InnoDB increments auto-increment value incorrectly with ON DUPLICATE KEY UPDATE
Submitted:	30 May 2007 17:24	Modified:	18 Jun 2010 1:32
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.42	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	autoincrement replication, bfsm_2007_06_21

Description:
When INSERT ... ON DUPLICATE KEY statements on InnoDB tables increment the auto-increment counter when a unique key value is matched and the statement falls to the ON DUPLICATE KEY UPDATE.  This does not happen when the INSERT ... ON DUPLICATE KEY statement is preceded by SET INSERT_ID=[next insert id] - making this particularly problematic for replication, where this is used.  This causes replication to rows with different auto-increment values on the slave than on the master, completely breaking data integrity.

Below are three examples, the first without SET INSERT_ID (showing the gap at id=4).  The second uses SET INSERT_ID=4, which is how the slave processes this statement (no gap in id values).  The last example uses SET INSERT_ID=0, which produces the same result (with gap at id=4) as not using SET INSERT_ID at all:

mysql> DROP TABLE IF EXISTS rep_test;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> CREATE TABLE rep_test (
    ->  id INT AUTO_INCREMENT PRIMARY KEY,
    ->  a VARCHAR(10),
    ->  b VARCHAR(10),
    ->  UNIQUE KEY (a)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> INSERT INTO rep_test (a, b)
    ->  VALUES
    ->  ('one', 'one'),
    ->  ('two', 'two'),
    ->  ('three', 'three');
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO rep_test (id, a, b)
    ->  VALUES
    ->  (null, 'one', 'first'),
    ->  (null, 'four', 'four'),
    ->  (null, 'five', 'five')
    ->  ON DUPLICATE KEY UPDATE b = VALUES(b);
Query OK, 4 rows affected (0.11 sec)
Records: 3  Duplicates: 1  Warnings: 0

mysql>
mysql> SELECT * FROM rep_test;
+----+-------+-------+
| id | a     | b     |
+----+-------+-------+
|  1 | one   | first |
|  2 | two   | two   |
|  3 | three | three |
|  5 | four  | four  |
|  6 | five  | five  |
+----+-------+-------+
5 rows in set (0.00 sec)

mysql>
mysql> TRUNCATE rep_test;
Query OK, 5 rows affected (0.05 sec)

mysql>
mysql> INSERT INTO rep_test (a, b)
    ->  VALUES
    ->  ('one', 'one'),
    ->  ('two', 'two'),
    ->  ('three', 'three');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> SET INSERT_ID=4;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO rep_test (id, a, b)
    ->  VALUES
    ->  (null, 'one', 'first'),
    ->  (null, 'four', 'four'),
    ->  (null, 'five', 'five')
    ->  ON DUPLICATE KEY UPDATE b = VALUES(b);
Query OK, 4 rows affected (0.17 sec)
Records: 3  Duplicates: 1  Warnings: 0

mysql>
mysql> SELECT * FROM rep_test;
+----+-------+-------+
| id | a     | b     |
+----+-------+-------+
|  1 | one   | first |
|  2 | two   | two   |
|  3 | three | three |
|  4 | four  | four  |
|  5 | five  | five  |
+----+-------+-------+
5 rows in set (0.00 sec)

mysql>
mysql> TRUNCATE rep_test;
Query OK, 5 rows affected (0.06 sec)

mysql>
mysql> INSERT INTO rep_test (a, b)
    ->  VALUES
    ->  ('one', 'one'),
    ->  ('two', 'two'),
    ->  ('three', 'three');
Query OK, 3 rows affected (0.17 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> SET INSERT_ID=0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO rep_test (id, a, b)
    ->  VALUES
    ->  (null, 'one', 'first'),
    ->  (null, 'four', 'four'),
    ->  (null, 'five', 'five')
    ->  ON DUPLICATE KEY UPDATE b = VALUES(b);
Query OK, 4 rows affected (0.14 sec)
Records: 3  Duplicates: 1  Warnings: 0

mysql> SELECT * FROM rep_test;
+----+-------+-------+
| id | a     | b     |
+----+-------+-------+
|  1 | one   | first |
|  2 | two   | two   |
|  3 | three | three |
|  5 | four  | four  |
|  6 | five  | five  |
+----+-------+-------+
5 rows in set (0.00 sec)

The following show the manifestation of the problem in a replicated environment:

[below from master]

mysql> SELECT * FROM rep_test;
+----+-------+-------+
| id | a     | b     |
+----+-------+-------+
|  1 | one   | first |
|  2 | two   | two   |
|  3 | three | three |
|  5 | four  | four  |
|  6 | five  | five  |
+----+-------+-------+
5 rows in set (0.00 sec)

mysql>
mysql> show master status\G
*************************** 1. row ***************************
            File: knopfler-bin.000002
        Position: 1555
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

[below from slave]

mysql> show create table rep_test;
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----+
| Table    | Create Table

     |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----+
| rep_test | CREATE TABLE `rep_test` (
  `id` int(11) NOT NULL auto_increment,
  `a` varchar(10) default NULL,
  `b` varchar(10) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----+
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 127.0.0.1
                Master_User: repl
                Master_Port: 3307
              Connect_Retry: 60
            Master_Log_File: knopfler-bin.000002
        Read_Master_Log_Pos: 1555
             Relay_Log_File: knopfler-relay-bin.000004
              Relay_Log_Pos: 1695
      Relay_Master_Log_File: knopfler-bin.000002
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 1555
            Relay_Log_Space: 1695
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

mysql> select * from rep_test;
+----+-------+-------+
| id | a     | b     |
+----+-------+-------+
|  1 | one   | first |
|  2 | two   | two   |
|  3 | three | three |
|  4 | four  | four  |
|  5 | five  | five  |
+----+-------+-------+
5 rows in set (0.00 sec)

mysql>

How to repeat:
Execute the following on master:

DROP TABLE IF EXISTS rep_test;

CREATE TABLE rep_test (
 id INT AUTO_INCREMENT PRIMARY KEY, 
 a VARCHAR(10), 
 b VARCHAR(10), 
 UNIQUE KEY (a)
) ENGINE = InnoDB;

INSERT INTO rep_test (a, b) 
 VALUES 
 ('one', 'one'), 
 ('two', 'two'), 
 ('three', 'three');
 
INSERT INTO rep_test (id, a, b) 
 VALUES 
 (null, 'one', 'first'), 
 (null, 'four', 'four'), 
 (null, 'five', 'five') 
 ON DUPLICATE KEY UPDATE b = VALUES(b);
 
SELECT * FROM rep_test;

# Compare to slave:

SELECT * FROM rep_test;

Hmm... quite complex usage: auto-inc and ON DUPLICATE KEY UPDATE.

Assigning this complex bug to Inaam. I do not even know what ON DUPLICATE KEY UPDATE does!

--Heikki

Sunny sent a patch for this on June 13.

Where can I access the patch?
I've been facing this problem since upgrading to 5.0.42, while 5.0.40 has even more "ON DUPLICATE KEY UPDATE" bugs!

Hello? Is there anyone who has got the patch?

The patch is being merged to the current 5.0 tree and should be available shortly.  When the patch is ready, it will be noted in this bug report.

From my experience dealing with MySQL patches, one developer will first check-in the patch into his/her own repository and then push it to the central bk tree.

If this is the case, at least I can look at the patch and patch my own system at my own will. But somehow now I have to left my production data without backup (since all slaves are down) for over a month, because I don't have access to the patch.

patch for 5.0

Attachment: 28781.patch (text/x-diff), 1.56 KiB.

Queued to 5.0-maint

Pushed into 5.0.48

Pushed into 5.1.22-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.48 and 5.1.22 changelogs.

Pushed into 5.1.23-beta

Bug #33025 is set as duplicate of this bug here.

Bug still exists in current 5.1.23 and 6.0 BK sources. See also bug #24432

This is working as designed. The autoinc increment behaviour depends on the value of innodb_autoinc_lock_mode which is documented here http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

User can fall back to old behaviour (i.e.: no gaps) by setting the value of innodb_autoinc_lock_mode = 0. But this means old locking mechanism. For any non zero value of the parameter the performance and scalability is much better but gaps can potentially occur.

Perhaps we need to update the documentation to state this fact more explicitly.
I have attached results with both values of innodb_autoinc_lock_mode to explain the difference in behaviour.

regards,
inaam

autoinc behaviour with different values of innodb_autoinc_lock_mode

Attachment: autoinc.out (application/octet-stream, text), 4.42 KiB.

I am closing this down as 'not a bug'. The behaviour of auto_increment can be controlled through the config parameter (as mentioned earlier) and the gaps in the autoinc values are expected.

I'll make sure that we add more to the documentation to clarify this point.

regards,
inaam

I'm changing this status to "Documenting" as this is most assuredly a bug (which is fixed) in 5.0.  The reports of it not being fixed in 5.1 may be related to the need provide further documentation of the new innodb_autoinc_lock_mode parameter introduced in 5.1.

This turns out to be a documentation problem. INSERT ... ON DUPLICATE KEY UPDATE is a mixed-mode insert, not a simple insert, and thus is subject to the exception described in the docs for "consecutive" lock mode that render it unsafe for statement-based replication. Oracle is providing the corrections to the docs.

Reclassifying this report as a documentation bug and assigning to myself.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Changes to documentation:

- INSERT ... ON DUPLICATE KEY UPDATE is a mixed-mode insert, not a simple insert.

- Modified the description for innodb_autoinc_lock_mode = 1 ("consecutive" mode) as follows (note especially the last para, which is new):

"Simply put, the important impact of this lock mode is significantly better scalability. This mode is safe for use with statement-based replication. Further, as with “traditional” lock mode, auto-increment numbers assigned by any given statement are consecutive. In this mode, there is no change in semantics compared to “traditional” mode for any statement that uses auto-increment, with one important exception. [add "important", remove the word "minor"]

The exception is for “mixed-mode inserts”, where the user provides explicit values for an AUTO_INCREMENT column for some, but not all, rows in a multiple-row “simple insert.” For such inserts, InnoDB will allocate more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.

A similar situation exists if you use ON DUPLICATE KEY UPDATE ... in the INSERT statement which is also classified as a "mixed-mode insert". Because InnoDB allocates the autoincrement value before the insert is actually attempted, it cannot know whether an inserted value will be a duplicate of an existing value and thus cannot know if the autoincrement value it generates will be used for a new row or not. Therefore, if you are using statement-based replication, you must avoid ON DUPLICATE KEY UPDATE or use innodb_autoinc_lock_mode = 0 (“traditional” lock mode)."

Notice bug #41263 which looks very similar.

Bug #41263 was set as duplicate of this bug here

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

The default values for MySQL 5.1 reproduce this bug:
binlog_format --> STATEMENT
innodb_autoinc_lock_mode --> 1

As this bug indicates, many users encounter this problem. 

Why was this done?
Are we expected to read every page of the manual to determine whether it is safe to upgrade to the next MySQL release?

Change the InnoDB plugin to use innodb_autoinc_lock_mode=0 as the default value.
Log an error message somewhere when the server is run in an unsafe mode.

The error log has been spammed for other statements that are deemed unsafe for SBR yet nothing is done in this case.

Mark,

per our analysis it's actually replication-safe to mix the two
binlog_format --> STATEMENT
innodb_autoinc_lock_mode --> 1

The gap due to mode=1 should not make SBR failing anymore after the current bug fixes.

From my understanding of the code I also think it is safe. The comment in the docs scared me though.

Why this bug still not fixed?

Mikhail, it's not a bug. If you don't want gaps in the autoincrement sequence just set innodb_autoinc_lock_mode = 0. Then you'll get the old way with more contiguous values but more locking that will slow things down, as it did before.

We added the different lock modes because autoincrement locking was a common performance problem. But we knew that some applications would want the contiguous values, so we provided the option for those who want that more than they want more speed.

Just pick which way you want and set the variable.

James Day, MySQL Senior Principal Support Engineer, Oracle

Thanks, James.

Yes, I am know about "innodb_autoinc_lock_mode = 0" parameter, but I am hear this is bad for database performance.

I am don't understand why so heavy to makes unique index checking before applying "insert ... on duplicate key ..." SQL statement. And not increment auto increment fields if index check failed.

Example:

create table t1 (a int primary key auto_increment, b int unique, c varchar(20)) engine=innodb;
insert t1 (b) values (10),(20),(30);
insert t1 (b) values (20) on duplicate key update c='!';
insert t1 (b) values (40);
select * from t1;

Yes, 0 is bad for performance for workloads that do a lot of inserting. It is why we introduced the other values and don't have 0 as the default. But just try it. If you see lots of transactions waiting for autoincrement locks, it's a problem for you. If you don't then it may just be a small speed penalty.

Why not wait to allocate the value after checking all of the unique indexes? The server could do that, but would it be too pessimistic? If you think that there will be a duplicate, you should update instead of inserting. Then do an insert if the update fails. So INSERT ... ON DUPLICATE KEY UPDATE is for the case where it is expected that there will not be a duplicate and it is optimistic about that, assuming that will be true.

I assume that changing the internal order of index checking was too complicated.

Another example:

CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT, b INT UNIQUE, c VARCHAR(20)) ENGINE=INNODB;
INSERT t1 (b) VALUES (10),(20),(30);
INSERT t1 (b) VALUES (20) ON DUPLICATE KEY UPDATE c='!';
INSERT t1 (b) VALUES (40);
SELECT * FROM t1;

INSERT t1 (a) VALUES (5) ON DUPLICATE KEY UPDATE c='!';
INSERT t1 (b) VALUES (50);
SELECT * FROM t1;

Describe that PRIMARY KEY not have this issue.

This means the check PRIMARY KEY before AUTO INCREMENT worked. Why can't do the same thing for UNIQUE KEY's?