| Bug #70583 | INSERT ON DUPLICATE KEY UPDATE failing after MySQL 5.6 upgrade. | ||
|---|---|---|---|
| Submitted: | 10 Oct 2013 8:14 | Modified: | 2 Jul 2014 17:39 |
| Reporter: | Simon Mudd (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.6.14 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[10 Oct 2013 12:39]
MySQL Verification Team
Hello Simon, Thank you for the report. Verified as described. Thanks, Umesh
[10 Oct 2013 12:46]
MySQL Verification Team
How to repeat:
1. Setup simple replication (Master/Slave 5.5.34)
2. Upgrade slave to 5.6.14
3. This issue appears with concurrently executing INSERT ... ON
DUPLICATE KEY UPDATE.. like below mysqlslap(against master)
mysqlslap --user=root --password --delimiter=";" --create-schema=test1 --create="CREATE TABLE t(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,k INT,c CHAR(1),UNIQUE KEY(k)) ENGINE=InnoDB;INSERT INTO t(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();INSERT INTO t(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE id=10;" --concurrency=10 --iterations=100
Observe slave status:
Last_Error: Error 'Auto-increment value in UPDATE conflicts with internally generated values' on query. Default database: 'test1'. Query: 'INSERT INTO t(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE id=10'
Skip_Counter: 0
Exec_Master_Log_Pos: 1466382
Relay_Log_Space: 87655
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1869
Last_SQL_Error: Error 'Auto-increment value in UPDATE conflicts with internally generated values' on query. Default database: 'test1'. Query: 'INSERT INTO t(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE id=10'
Replicate_Ignore_Server_Ids:
// Excerpt from slave's error log
2013-10-11 16:10:11 19052 [ERROR] Slave SQL: Error 'Auto-increment value in UPDATE conflicts with internally generated values' on query. Default da
tabase: 'test1'. Query: 'INSERT INTO t(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE id=10', Error_code: 1869
2013-10-11 16:10:11 19052 [Warning] Slave: Auto-increment value in UPDATE conflicts with internally generated values Error_code: 1869
[2 Jul 2014 17:39]
David Moss
Thanks for your input. I have made the following changes with commits 3605 and 39213: * Added the error to the C API docs at - http://dev.mysql.com/doc/refman/5.6/en/mysql-insert-id.html This is the new content - Errors ER_AUTO_INCREMENT_CONFLICT A user specified AUTO_INCREMENT value in a multi INSERT statement falls within the range between the current AUTO_INCREMENT value and the sum of the current and number of rows affected values. * Added the information to the 5.6.20 / 5.7.5 release notes at - http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-5.html This is the new content - Replication: Beginning in MySQL 5.6.20, when a user specified AUTO_INCREMENT value falls outside of the range between the current AUTO_INCREMENT value and the sum of the current and number of rows affected values it is replicated correctly. In previous versions, an error was generated by the slave even if the user specified AUTO_INCREMENT value fell outside of the range. (Bug #17588419, Bug #70583)
[6 Aug 2014 17:08]
Laurynas Biveinis
$ bzr log -n0 -r 5918 ------------------------------------------------------------ revno: 5918 committer: Sujatha Sivakumar <sujatha.sivakumar@oracle.com> branch nick: Bug17588419_mysql-5.6 timestamp: Tue 2014-05-06 16:41:02 +0530 message: Bug#17588419: INSERT ON DUPLICATE KEY UPDATE FAILING AFTER MYSQL 5.6 UPGRADE. Problem: ======== After upgrading a MySQL 5.5.23 slave to 5.6.14 (the master still runs 5.5.23) on executing INSERT ON DUPLICATE KEY following error is reported in SBR. [ERROR] Slave SQL: Error 'Auto-increment value in UPDATE conflicts with internally generated values' on query. Analysis: ======== On master when user specifies an autoincrement value in multi insert statement the user given value is compared with current auto increment value and current + number of rows affected value. Whenever the user specified value falls within the given range ER_AUTO_INCREMENT_CONFLICT error is generated. On the slave the range is set to the value ULONGULONG_MAX by default. Whenever user specifies any value it will always fall within the above range and error gets generated. On slave each DML operation will result in n number of rows being manipulated. When number of manipulated rows is known the value range can be reset to currect value to current+n. Fix: === On slave identify the number of rows being manipulated. Reset the value ranage from currnet value to current value + number of maninupated rows. If the number is not known go ahead with default values.
Description: After upgrading a MySQL 5.5.23 slave to 5.6.14 I suddenly see this error (the master still runs 5.5.23). I'm also running SBR. 2013-10-09 02:42:58 30010 [ERROR] Slave SQL: Error 'Auto-increment value in UPDATE conflicts with internally generated values' on query. Default database: 'srv'. Query: 'INSERT INTO MyTable (name) VALUES ('XXXXXXXXXXXX') ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)', Error_code: 1869 2013-10-09 02:42:58 30010 [Warning] Slave: Auto-increment value in UPDATE conflicts with internally generated values Error_code: 1869 2013-10-09 02:42:58 30010 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.006276' position 828672993 I look on http://dev.mysql.com/doc/refman/5.6/en/mysql-insert-id.html and at the end it says: Errors None. This behaviour prevents upgrading servers to 5.6.14 from 5.5 without code workarounds which from a performance perspective would behave worse. How to repeat: See above. Suggested fix: The documentation suggests that an INSERT or UPDATE using IODKU as done above should work and provide the last insert id column with the id of the table matching name = 'XXXXX'. This worked in 5.5 but no longer works in 5.6.14. So please fix 5.6 to match the expected behaviour. If there are edge cases where this can't work document them in the page above. Note: the reason for using IODKU is to get a unique id out after inserting a value and for it to work in a single SQL statement. Previous code we'd written did an INSERT IGNORE followed by a SELECT but that is much slower.