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
Triage: D2 (Serious)

[30 May 2007 17:24] Todd Farmer
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;
[1 Jun 2007 13:23] Heikki Tuuri
Hmm... quite complex usage: auto-inc and ON DUPLICATE KEY UPDATE.
[7 Jun 2007 14:00] Heikki Tuuri
Assigning this complex bug to Inaam. I do not even know what ON DUPLICATE KEY UPDATE does!

--Heikki
[21 Jun 2007 11:34] Heikki Tuuri
Sunny sent a patch for this on June 13.
[21 Jul 2007 6:02] Alan Tam
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!
[26 Jul 2007 5:08] Alan Tam
Hello? Is there anyone who has got the patch?
[26 Jul 2007 5:13] Todd Farmer
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.
[8 Aug 2007 5:17] Alan Tam
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.
[8 Aug 2007 5:26] Inaam Rana
patch for 5.0

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

[16 Aug 2007 0:54] Timothy Smith
Queued to 5.0-maint
[20 Aug 2007 10:04] Bugs System
Pushed into 5.0.48
[20 Aug 2007 10:20] Bugs System
Pushed into 5.1.22-beta
[24 Aug 2007 9:36] Jon Stephens
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.
[4 Sep 2007 17:11] Bugs System
Pushed into 5.1.23-beta
[7 Dec 2007 12:35] Susanne Ebrecht
Bug #33025 is set as duplicate of this bug here.
[9 Jan 2008 18:58] Sveta Smirnova
Bug still exists in current 5.1.23 and 6.0 BK sources. See also bug #24432
[10 Jan 2008 18:31] Inaam Rana
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
[10 Jan 2008 18:32] Inaam Rana
autoinc behaviour with different values of innodb_autoinc_lock_mode

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

[15 Jan 2008 5:19] Inaam Rana
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
[15 Jan 2008 22:46] Todd Farmer
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.
[27 Feb 2008 13:31] Paul Dubois
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.
[27 Feb 2008 13:42] Paul Dubois
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)."
[9 Mar 2009 4:48] Roel Van de Paar
Notice bug #41263 which looks very similar.
[23 Mar 2009 15:23] Susanne Ebrecht
Bug #41263 was set as duplicate of this bug here
[5 May 2010 15:13] Bugs System
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)
[6 May 2010 2:58] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:10] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:38] Bugs System
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)
[28 May 2010 7:06] Bugs System
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)
[29 May 2010 2:30] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:15] Bugs System
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)
[17 Jun 2010 13:02] Bugs System
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)
[17 Jun 2010 13:43] Bugs System
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)
[6 Sep 2010 15:45] Mark Callaghan
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.
[10 Sep 2010 8:15] Andrei Elkin
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.
[10 Sep 2010 16:40] Mark Callaghan
From my understanding of the code I also think it is safe. The comment in the docs scared me though.
[25 Nov 2012 19:56] Mikhail Gavrilov
Why this bug still not fixed?
[26 Nov 2012 21:22] James Day
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
[27 Nov 2012 2:22] Mikhail Gavrilov
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;
[27 Nov 2012 9:02] James Day
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.
[29 Nov 2012 20:58] Mikhail Gavrilov
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?