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:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.6.14 OS:Any
Assigned to: CPU Architecture:Any

[10 Oct 2013 8:14] Simon Mudd
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.
[10 Oct 2013 12:39] Umesh Shastry
Hello Simon,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[10 Oct 2013 12:46] Umesh Shastry
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.