Description:
When the table's self-increment exceeds its maximum, insert.. on duplicate key update can be executed normally, but the data is not written and updated. Normal should prompt the primary key conflict, and SQL execution fails.
How to repeat:
mysql> show create table at_test\G
*************************** 1. row ***************************
Table: at_test
Create Table: CREATE TABLE `at_test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`vid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'vid',
`tag` text COMMENT 'tag',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_vid` (`vid`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COMMENT='vid info表'
1 row in set (0.00 sec)
mysql> select * from at_test;
+------------+-----+------+
| id | vid | tag |
+------------+-----+------+
| 4294967295 | 1 | 1111 |
+------------+-----+------+
1 row in set (0.00 sec)
mysql> insert into at_test(vid,tag)values(1,'2222') on duplicate key update tag = values(tag);
Query OK, 2 rows affected (0.00 sec)
mysql> select * from at_test;
+------------+-----+------+
| id | vid | tag |
+------------+-----+------+
| 4294967295 | 1 | 2222 |
+------------+-----+------+
1 row in set (0.00 sec)
insert.. on duplicate key update:
mysql> insert into at_test(vid,tag)values(2,'2222') on duplicate key update tag = values(tag);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from at_test;
+------------+-----+------+
| id | vid | tag |
+------------+-----+------+
| 4294967295 | 1 | 2222 |
+------------+-----+------+
1 row in set (0.00 sec)
but insert :
mysql> insert into at_test(vid,tag)values(2,'2222');
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
Suggested fix:
"insert ..on duplicate key update " and "Insert into" should return the same value
(ERROR 1062 (23000): Duplicate entry)
Description: When the table's self-increment exceeds its maximum, insert.. on duplicate key update can be executed normally, but the data is not written and updated. Normal should prompt the primary key conflict, and SQL execution fails. How to repeat: mysql> show create table at_test\G *************************** 1. row *************************** Table: at_test Create Table: CREATE TABLE `at_test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id', `vid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'vid', `tag` text COMMENT 'tag', PRIMARY KEY (`id`), UNIQUE KEY `uniq_vid` (`vid`) ) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COMMENT='vid info表' 1 row in set (0.00 sec) mysql> select * from at_test; +------------+-----+------+ | id | vid | tag | +------------+-----+------+ | 4294967295 | 1 | 1111 | +------------+-----+------+ 1 row in set (0.00 sec) mysql> insert into at_test(vid,tag)values(1,'2222') on duplicate key update tag = values(tag); Query OK, 2 rows affected (0.00 sec) mysql> select * from at_test; +------------+-----+------+ | id | vid | tag | +------------+-----+------+ | 4294967295 | 1 | 2222 | +------------+-----+------+ 1 row in set (0.00 sec) insert.. on duplicate key update: mysql> insert into at_test(vid,tag)values(2,'2222') on duplicate key update tag = values(tag); Query OK, 0 rows affected (0.00 sec) mysql> select * from at_test; +------------+-----+------+ | id | vid | tag | +------------+-----+------+ | 4294967295 | 1 | 2222 | +------------+-----+------+ 1 row in set (0.00 sec) but insert : mysql> insert into at_test(vid,tag)values(2,'2222'); ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY' Suggested fix: "insert ..on duplicate key update " and "Insert into" should return the same value (ERROR 1062 (23000): Duplicate entry)