MySQL Bugs: #71159: INSERT OR UPDATE updates last row when max autoinc value is reached

Bug #71159	INSERT OR UPDATE updates last row when max autoinc value is reached
Submitted:	17 Dec 2013 10:00	Modified:	17 Dec 2013 17:06
Reporter:	Cyril Scetbon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	autoinc, insert or update

Description:
When max auto increment value has been reached, following INSERT OR UPDATE DML always update the last value and not the row concerned by the unique key

How to repeat:
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t(id TINYINT AUTO_INCREMENT PRIMARY KEY, c CHAR(10), v INT, UNIQUE KEY(c));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t VALUES(127,'A',1);
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `c` char(10) DEFAULT NULL,
  `v` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> INSERT INTO t(c,v) VALUES('A',1) ON DUPLICATE KEY UPDATE v = v+1;
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM t\G
*************************** 1. row ***************************
id: 127
 c: A
 v: 2
1 row in set (0.00 sec)

mysql> INSERT INTO T(c,v) VALUES('B',1) ON DUPLICATE KEY UPDATE v = v+1;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM T\G
*************************** 1. row ***************************
id: 127
 c: A
 v: 3
1 row in set (0.00 sec)

mysql> INSERT INTO t VALUES(125,'B',1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t\G
*************************** 1. row ***************************
id: 125
 c: B
 v: 1
*************************** 2. row ***************************
id: 127
 c: A
 v: 3
2 rows in set (0.00 sec)

mysql> INSERT INTO t(c,v) values('B',1) ON DUPLICATE KEY UPDATE v = v+1;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM t\G
*************************** 1. row ***************************
id: 125
 c: B
 v: 1
*************************** 2. row ***************************
id: 127
 c: A
 v: 4
2 rows in set (0.00 sec)

Suggested fix:
If row exists update it and not the last one if we reached the max rows or max auto increment value

Thank you for the bug report. Please check the Manual:

http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html

"The behavior of the auto-increment mechanism is not defined if you assign a negative value to the column, or if the value becomes bigger than the maximum integer that can be stored in the specified integer type."

The matter is that the row exists and must be updated in the last case for example. I'm not providing a bigger or negative value for the auto_increment value as said in the documentation. Moreover, I really think the case where we attempt to insert a new row and the auto_increment has reached its max value should be handled.

thanks

Hello Mr. Scetbon,

Thank you for report, however this is not a bug. I will explain why ....

As far as I understand, your only quibble is with the last INSERT, which inserts 'B' in the character column, which also happens to have a UNIQUE index on it. Here goes my explanation. 

The row that you are inserting first gets the same value for the column that is auto-incremented. However, as that column has reached it's maximum value, the value to be inserted is converted to the maximum value, which for signed TINYINT is 127.

Now, when MySQL server checks for duplicates, it first checks for the duplicates in the premier key and only then to other unique keys. Hence, a tuple with value of 127 for PK and  'A' for the character column is considered a duplicate, due to the value of 127 for the PK column, which already exists.

That is a reason why that tuple gets updated and not the one which has a value of 'B' in it's character column. 

I hope that this clears the issue.

thanks for your explanation. I understand why it happens but not that you think it's not a bug :(
If it's the way it works cause of auto_increments I understand, but that behaviour is REALLY annoying