| Bug #71188 | Strange beheavior ON DUPLICATE KEY UPDATE when auto_increment reaches MAXINT | ||
|---|---|---|---|
| Submitted: | 20 Dec 2013 13:05 | Modified: | 20 Dec 2013 23:06 |
| Reporter: | Paul Namuag | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.5 - 5.6.15 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[20 Dec 2013 13:07]
Paul Namuag
Attached create-tables for testing the usigned integer types
Attachment: create-tables.sql (application/octet-stream, text), 6.50 KiB.
[20 Dec 2013 13:22]
Peter Laursen
I did not try wiht MyISAM, but this could very well be an InnoDB specific problem - and maybe related to setting of 'innodb_autoinc_lock_mode' variable (http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html) Peter (not a MySQL/Oracle person)
[20 Dec 2013 23:06]
Sveta Smirnova
Thank you for the report. You use two unique keys with insert ... on duplicate key update statement which is not recommended to use (see " In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes. " at http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html). Also same page says how INSERT gets converted to UPDATE for such statements: UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1; So for case like: mysql> INSERT INTO `testautoinc` VALUES (NULL,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 2 rows affected (0.00 sec) /* Notice the wrong row was updated! */ mysql> SELECT * FROM `testautoinc`; +------------+------------+--------+ | id | date | clicks | +------------+------------+--------+ | 4294967293 | 2013-12-16 | 1 | | 4294967294 | 2013-12-17 | 2 | | 4294967295 | 2013-12-18 | 3 | +------------+------------+--------+ INSERT compares value in the PRIMARY KEY first, then in the `date` column.
[20 Dec 2013 23:24]
Przemyslaw Malkowski
How about a simpler test case, with just PK. There are three problems as I see here: 1 - there is inconsistent behavior between a column with bigint unsigned comparing to any other int type, 2 - for bigint unsigned, you are unable to reach the actual maximum value by using INSERT ... ON DUPLICATE KEY UPDATE, but that's actually hardly important issue, 3 - there is a question whether we would like to see the row with last possible auto_increment value updated forever with INSERT ... ON DUPLICATE KEY UPDATE with no error nor warning returned to client, or we would like to see that error? I personally prefer to have my application fail rather then silently loose data. Examples below were made on MySQL 5.6.15, InnoDB, innodb_autoinc_lock_mode 0/1 (no influence on results). 56> CREATE TABLE t_big_s (id bigint signed auto_increment primary key, c int unsigned); Query OK, 0 rows affected (0.30 sec) 56> insert into t_big_s values (9223372036854775805,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 1 row affected (0.22 sec) 56> insert into t_big_s values (null,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 1 row affected (0.04 sec) 56> insert into t_big_s values (null,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 1 row affected (0.04 sec) 56> insert into t_big_s values (null,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 2 rows affected (0.04 sec) 56> select * from t_big_s; +---------------------+------+ | id | c | +---------------------+------+ | 9223372036854775805 | 1 | | 9223372036854775806 | 1 | | 9223372036854775807 | 2 | +---------------------+------+ 3 rows in set (0.00 sec) ----------------------------------------------------------------------------------------- 56> CREATE TABLE t_big_u (id bigint unsigned auto_increment primary key, c int unsigned); Query OK, 0 rows affected (0.30 sec) 56> insert into t_big_u values (18446744073709551613,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 1 row affected (0.22 sec) 56> insert into t_big_u values (null,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 1 row affected (0.05 sec) 56> insert into t_big_u values (null,1) ON DUPLICATE KEY UPDATE c=c+1; ERROR 1467 (HY000): Failed to read auto-increment value from storage engine 56> select * from t_big_u; +----------------------+------+ | id | c | +----------------------+------+ | 18446744073709551613 | 1 | | 18446744073709551614 | 1 | +----------------------+------+ 2 rows in set (0.00 sec) 56> insert into t_big_u values (18446744073709551615,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 1 row affected (0.22 sec) 56> select * from t_big_u; +----------------------+------+ | id | c | +----------------------+------+ | 18446744073709551613 | 1 | | 18446744073709551614 | 1 | | 18446744073709551615 | 1 | +----------------------+------+ 3 rows in set (0.00 sec) 56> insert into t_big_u values (null,1) ON DUPLICATE KEY UPDATE c=c+1; ERROR 1467 (HY000): Failed to read auto-increment value from storage engine ----------------------------------------------------------------------------------------- 56> CREATE TABLE t_tiny_u (id tinyint unsigned auto_increment primary key, c int unsigned); Query OK, 0 rows affected (0.34 sec) 56> insert into t_tiny_u values (253,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 1 row affected (0.04 sec) 56> insert into t_tiny_u values (null,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 1 row affected (0.04 sec) 56> insert into t_tiny_u values (null,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 1 row affected (0.00 sec) 56> insert into t_tiny_u values (null,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 2 rows affected (0.01 sec) 56> select * from t_tiny_u; +-----+------+ | id | c | +-----+------+ | 253 | 1 | | 254 | 1 | | 255 | 2 | +-----+------+ 3 rows in set (0.00 sec)
[21 Dec 2013 1:49]
Kevin Kwast
When the auto-increment doesn't happen because of a boundary issue, the INSERT should error 1467 (or similar). Trying to proceed using the MAX(id) of an existing row leads to broken behavior.
[24 Dec 2013 14:22]
Sveta Smirnova
Thank you for the feedback. In my opinion behavior of all integer values except BIGIN UNSIGNED is predictable and accurate here. Regarding to BIGINT UNSIGNED error happens, because storage engine internally meets overflow when tries to create new value for auto-increment field. I think returning error is still OK for this case. And error is correct, because it reports issue happened when server tried to get value from the storage engine.
[26 Dec 2013 16:01]
Valeriy Kravchuk
See bug #71232 for this specific problem: "2 - for bigint unsigned, you are unable to reach the actual maximum value by using INSERT ... ON DUPLICATE KEY UPDATE, but that's actually hardly important issue" mentioned in the great comment dated "[20 Dec 23:24] Przemyslaw Malkowski" Important or not, but I'd say it's a bug in InnoDB's implementation of auto_increment.
Description: We noticed this strange behaviour from INSERT ON DUPLICATE KEY UPDATE when the auto_increment id reaches a maximum value or its boundary basing on INTEGER types. So far the tests are based on unsigned integer types. The only base unsigned integer data type that is able to produced an error when a boundary is reached, is found when type is of BIGINT UNSIGNED. It produces, ERROR 1467 (HY000) at line 146: Failed to read auto-increment value from storage engine. I've tested this from 5.5.29-30, 5.6.12 and 5.6.15 as well as tested on 5.1 How to repeat: CREATE TABLE `testautoinc` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `clicks` int unsigned DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY (`date`) ) ENGINE=InnoDB ; mysql> INSERT INTO `testautoinc` VALUES (4294967293,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM `testautoinc`; +------------+------------+--------+ | id | date | clicks | +------------+------------+--------+ | 4294967293 | 2013-12-16 | 1 | +------------+------------+--------+ 1 row in set (0.00 sec) mysql> INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM `testautoinc`; +------------+------------+--------+ | id | date | clicks | +------------+------------+--------+ | 4294967293 | 2013-12-16 | 1 | | 4294967294 | 2013-12-17 | 1 | +------------+------------+--------+ 2 rows in set (0.00 sec) mysql> INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 2 rows affected (0.00 sec) mysql> SELECT * FROM `testautoinc`; +------------+------------+--------+ | id | date | clicks | +------------+------------+--------+ | 4294967293 | 2013-12-16 | 1 | | 4294967294 | 2013-12-17 | 2 | +------------+------------+--------+ 2 rows in set (0.00 sec) mysql> INSERT INTO `testautoinc` VALUES (NULL,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM `testautoinc`; +------------+------------+--------+ | id | date | clicks | +------------+------------+--------+ | 4294967293 | 2013-12-16 | 1 | | 4294967294 | 2013-12-17 | 2 | | 4294967295 | 2013-12-18 | 1 | +------------+------------+--------+ 3 rows in set (0.00 sec) /* id is at the maximum value, what happens to the next insert? /* mysql> INSERT INTO `testautoinc` VALUES (NULL,'2013-12-19',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 2 rows affected (0.00 sec) mysql> SELECT * FROM `testautoinc`; +------------+------------+--------+ | id | date | clicks | +------------+------------+--------+ | 4294967293 | 2013-12-16 | 1 | | 4294967294 | 2013-12-17 | 2 | | 4294967295 | 2013-12-18 | 2 | +------------+------------+--------+ 3 rows in set (0.00 sec) /* Without the ON DUPLICATE KEY UPDATE clause, an error code is produced */ mysql> INSERT INTO `testautoinc` VALUES (NULL,'2013-12-19',1); ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY' /* Test an insert that should duplicate the existing 2013-12-16 row - it points to the boundary number (4294967295) as it seemed that PK is first check, without considering the date */ mysql> INSERT INTO `testautoinc` VALUES (NULL,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 2 rows affected (0.00 sec) /* Notice the wrong row was updated! */ mysql> SELECT * FROM `testautoinc`; +------------+------------+--------+ | id | date | clicks | +------------+------------+--------+ | 4294967293 | 2013-12-16 | 1 | | 4294967294 | 2013-12-17 | 2 | | 4294967295 | 2013-12-18 | 3 | +------------+------------+--------+ 3 rows in set (0.00 sec) If we changed the type to BIGINT UNSIGNED, mysql> CREATE TABLE `testautoinc` ( -> `id` bigint unsigned NOT NULL AUTO_INCREMENT, -> `date` date NOT NULL, -> `clicks` int unsigned DEFAULT '0', -> PRIMARY KEY (`id`), -> UNIQUE KEY (`date`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO `testautoinc` VALUES (18446744073709551613,'2013-12-16',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 1 row affected (0.03 sec) mysql> SELECT * FROM `testautoinc`; +----------------------+------------+--------+ | id | date | clicks | +----------------------+------------+--------+ | 18446744073709551613 | 2013-12-16 | 1 | +----------------------+------------+--------+ 1 row in set (0.01 sec) mysql> INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM `testautoinc`; +----------------------+------------+--------+ | id | date | clicks | +----------------------+------------+--------+ | 18446744073709551613 | 2013-12-16 | 1 | | 18446744073709551614 | 2013-12-17 | 1 | +----------------------+------------+--------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM `testautoinc`; +----------------------+------------+--------+ | id | date | clicks | +----------------------+------------+--------+ | 18446744073709551613 | 2013-12-16 | 1 | | 18446744073709551614 | 2013-12-17 | 1 | +----------------------+------------+--------+ 2 rows in set (0.00 sec) /* At this time around, it produces an error code 1467, but it didn't got onto it's boundary yet, i.e. there's value 18446744073709551615 still not used */ mysql> INSERT INTO `testautoinc` VALUES (NULL,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; ERROR 1467 (HY000): Failed to read auto-increment value from storage engine mysql> INSERT INTO `testautoinc` VALUES (NULL,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; ERROR 1467 (HY000): Failed to read auto-increment value from storage engine /* Let's insert with ID being specified */ mysql> INSERT INTO `testautoinc` VALUES (18446744073709551614,'2013-12-17',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 2 rows affected (0.03 sec) mysql> INSERT INTO `testautoinc` VALUES (18446744073709551614,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 2 rows affected (0.02 sec) /* Now, it is now inserted with the boundary value of BIGINT UNSIGNED type which it is inserted */ mysql> INSERT INTO `testautoinc` VALUES (18446744073709551615,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; Query OK, 1 row affected (0.00 sec) mysql> select * from testautoinc; +----------------------+------------+--------+ | id | date | clicks | +----------------------+------------+--------+ | 18446744073709551613 | 2013-12-16 | 1 | | 18446744073709551614 | 2013-12-17 | 3 | | 18446744073709551615 | 2013-12-18 | 1 | +----------------------+------------+--------+ 3 rows in set (0.00 sec) /* and inserting back again but with id value is null */ mysql> INSERT INTO `testautoinc` VALUES (NULL,'2013-12-18',1) ON DUPLICATE KEY UPDATE clicks=clicks+1; ERROR 1467 (HY000): Failed to read auto-increment value from storage engine Suggested fix: - When integers reached its boundary or an overflow of it's data type, it should produce an error as what BIGINT UNSIGNED is doing - Must provide an explanation in the manual regarding how it handles its data type when it reaches its boundary, the algorithm in BIGINT UNSIGNED vs other types is vague. - For BIGINT UNSIGNED, inserting a new with unique value base on the example, ...VALUES (NULL,'2013-12-18',1) ON DUPLICATE KEY ... should be inserted with the record "id" value of 18446744073709551615. As what is happening, its lookup mechanism goes advance while record primary key id is yet 18446744073709551614 and haven't yet gotten to it's boundary value of 18446744073709551615, then it produces an error. However, specifying the value of 18446744073709551615 literally as the "id" value is allowed and thus can be inserted.