| Bug #75253 | ON DUPLICATE KEY UPDATE not working when auto_increment reaches MAX_INT | ||
|---|---|---|---|
| Submitted: | 18 Dec 2014 9:06 | Modified: | 22 Dec 2014 9:36 |
| Reporter: | Tong Zhou | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.5.32-log | OS: | Linux (CentOS) |
| Assigned to: | CPU Architecture: | Any | |
[18 Dec 2014 20:07]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php You have two unique keys in the same table, but MySQL does not guarantee how it will use these keys when resolves ON DUPLICATE ... UPDATE case. Therefore you either should accept unpredictable behavior or specify both unique keys, so in case when statement cannot be executed it rejected with error: mysql> insert into hotel_data (id, hotel, address) values (2,'beijing_2', 'addr2') on duplicate key update address = values(address); Query OK, 2 rows affected (0.00 sec) mysql> select * from hotel_data; +------------+-------------+---------+ | id | hotel | address | +------------+-------------+---------+ | 1 | beijing_1 | addr1 | | 2 | beijing_2 | addr2 | | 2147483647 | beijing_max | addr2 | +------------+-------------+---------+ 3 rows in set (0.00 sec) Regarding to Case B you tell MySQL to insert same value into hotel column which is defined unique. This is expected that the statement is rejected.
[22 Dec 2014 9:36]
Tong Zhou
Thank you for your help. Two questions remain unclear to me. 1. As you can see from the example above, when we wanna update address of "beijing_2", mysql updates address of "beijing_max" instead when AUTO_INCREMENT id reaches maximum value. And we know that every time we execute INSERT ... ON DUPLICATE KEY UPDATE will cause AUTO_INCREMENT id increments itself. Does this mean that AUTO_INCREMENT id will remain at the maximum value instead of going back to 1 if it has reached maximum value already? 2. "You have two unique keys in the same table, but MySQL does not guarantee how it will use these keys when resolves ON DUPLICATE ... UPDATE case", is there a specific technique to know which one to be used first when mysql encounters two or more unique keys? If the answer is yes, could you please introduce it briefly? Looking forward to your reply. Thanks a lot.
Description: We created a table with an auto_increment id based on int as primary key, and with another column as UNIQUE KEY. We found that INSERT ... ON DUPLICATE KEY UPDATE operation didn't update data on the duplicated UNIQUE KEY row when id reaches a maximum value. However, the row with a maximum value was updated seems like UNIQUE KEY was ignored. How to repeat: CREATE TABLE `hotel_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `hotel` varchar(30) NOT NULL DEFAULT '', `address` varchar(512) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `uniq_hotel` (`hotel`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; #insert some data mysql> insert into hotel_data (hotel, address) values ('beijing_1', 'addr1'); mysql> insert into hotel_data (hotel, address) values ('beijing_2', 'addr2'); mysql> select * from hotel_data; +----+-----------+---------+ | id | hotel | address | +----+-----------+---------+ | 1 | beijing_1 | addr1 | | 2 | beijing_2 | addr2 | +----+-----------+---------+ 2 rows in set (0.00 sec) mysql> insert into hotel_data (hotel, address) values ('beijing_2', 'addr10') on duplicate key update address = values(address); Query OK, 2 rows affected (0.00 sec) mysql> select * from hotel_data; +----+-----------+---------+ | id | hotel | address | +----+-----------+---------+ | 1 | beijing_1 | addr1 | | 2 | beijing_2 | addr10 | +----+-----------+---------+ 2 rows in set (0.01 sec) #Everything goes well by now. After We add a row with maximum value id, the behavior is kind of wild. mysql> insert into hotel_data (id, hotel, address) values (2147483647, 'beijing_max', 'addr_max'); mysql> select * from hotel_data; +------------+-------------+----------+ | id | hotel | address | +------------+-------------+----------+ | 1 | beijing_1 | addr1 | | 2 | beijing_2 | addr10 | | 2147483647 | beijing_max | addr_max | +------------+-------------+----------+ 3 rows in set (0.00 sec) #update 'addr10' back to 'addr2' mysql> insert into hotel_data (hotel, address) values ('beijing_2', 'addr2') on duplicate key update address = values(address); mysql> select * from hotel_data; +------------+-------------+---------+ | id | hotel | address | +------------+-------------+---------+ | 1 | beijing_1 | addr1 | | 2 | beijing_2 | addr10 | | 2147483647 | beijing_max | addr2 | +------------+-------------+---------+ #Note that things doesn't behavior like we expect, address of 'beijing_max' is updated to 'addr2'. #Meanwhile, the sql below returns an error which is supposed to be OK before 'beijing_max' row was inserted. We refer to this situation as CASE_B. mysql> insert into hotel_data (hotel, address) values ('beijing_2', 'addr2') on duplicate key update hotel = values(hotel), address = values(address); ERROR 1062 (23000): Duplicate entry 'beijing_2' for key 'uniq_hotel' Suggested fix: 1. An error should be returned when DUPLICATE KEY UPDATE is executed as the auto_increment id reaches a maximum value, 2. In CASE_B, the sql would like to be executed without replying error.