| Bug #28781 | InnoDB increments auto-increment value incorrectly with ON DUPLICATE KEY UPDATE | ||
|---|---|---|---|
| Submitted: | 30 May 2007 19:24 | Modified: | 27 Feb 14:42 |
| Reporter: | Todd Farmer | ||
| Status: | Closed | ||
| Category: | Server: Docs | Severity: | S2 (Serious) |
| Version: | 5.0.42 | OS: | Any |
| Assigned to: | Paul DuBois | Target Version: | 5.0+ |
| Tags: | bfsm_2007_06_21 | ||
| Triage: | D2 (Serious) | ||
[1 Jun 2007 15:23]
Heikki Tuuri
Hmm... quite complex usage: auto-inc and ON DUPLICATE KEY UPDATE.
[7 Jun 2007 16:00]
Heikki Tuuri
Assigning this complex bug to Inaam. I do not even know what ON DUPLICATE KEY UPDATE does! --Heikki
[21 Jun 2007 13:34]
Heikki Tuuri
Sunny sent a patch for this on June 13.
[21 Jul 2007 8: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 7:08]
Alan Tam
Hello? Is there anyone who has got the patch?
[26 Jul 2007 7: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 7: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 7:26]
Inaam Rana
patch for 5.0
Attachment: 28781.patch (text/x-diff), 1.56 KiB.
[16 Aug 2007 2:54]
Tim Smith
Queued to 5.0-maint
[20 Aug 2007 12:04]
Bugs System
Pushed into 5.0.48
[20 Aug 2007 12:20]
Bugs System
Pushed into 5.1.22-beta
[24 Aug 2007 11: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 19:11]
Bugs System
Pushed into 5.1.23-beta
[7 Dec 2007 13:35]
Susanne Ebrecht
Bug #33025 is set as duplicate of this bug here.
[9 Jan 19:58]
Sveta Smirnova
Bug still exists in current 5.1.23 and 6.0 BK sources. See also bug #24432
[10 Jan 19: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 19: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 6: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 23: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 14: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 14: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)."
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;