Description:
When inserting into a table which has reached its max auto-increment value the error returned is:
[Err] 1062 - Duplicate entry '2147483647' for key 'PRIMARY'
This prevents on duplicate key statements from operating correctly, possibly resulting in data loss.
Instead it would make more sense for an auto-increment failure to be specific e.g.
[Err] 1467 - Failed to read auto-increment value from storage engine
How to repeat:
Create a table with the max auto-increment value set to its max e.g.
CREATE TABLE `xxx_test` (
`test` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(255) DEFAULT NULL,
`updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`test`),
UNIQUE KEY `unq_val` (`val`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483646 DEFAULT CHARSET=latin1;
Try to insert into said table two rows:
INSERT INTO `xxx_test` (`val`, `updated`)
VALUES ('value1', NOW())
ON DUPLICATE KEY UPDATE `updated` = VALUES(`updated`);
# id = 2147483646
INSERT INTO `xxx_test` (`val`, `updated`)
VALUES ('value2', NOW())
ON DUPLICATE KEY UPDATE `updated` = VALUES(`updated`);
# id = 2147483647
Now a row which should trigger an update of the id = 2147483646, val = value1 row:
INSERT INTO `xxx_test` (`val`, `updated`)
VALUES ('value1', NOW())
ON DUPLICATE KEY UPDATE `updated` = VALUES(`updated`);
Instead of updating the row with id = 2147483646, val = 'value1' it tries to update the row with id = 2147483647, val = 'value2' due to the auto-increment failure, resulting in the confusing error:
[Err] 1062 - Duplicate entry 'value1' for key 'unq_val'
If the following is run:
INSERT INTO `xxx_test` (`val`, `updated`)
VALUES ('value3', NOW())
ON DUPLICATE KEY UPDATE `updated` = VALUES(`updated`);
we get:
# Affected rows: 2
# Time: 0.012s
This again is incorrect as it should have inserted a new row but didn't due to the auto-increment failure and hence it updated the row with id = 2147483647 losing that data.
Suggested fix:
Failure to increment the auto-increment value should always return an error specific to that case e.g.
[Err] 1467 - Failed to read auto-increment value from storage engine
Preventing it from being treated as a duplicate key error, which can cause data loss.
Description: When inserting into a table which has reached its max auto-increment value the error returned is: [Err] 1062 - Duplicate entry '2147483647' for key 'PRIMARY' This prevents on duplicate key statements from operating correctly, possibly resulting in data loss. Instead it would make more sense for an auto-increment failure to be specific e.g. [Err] 1467 - Failed to read auto-increment value from storage engine How to repeat: Create a table with the max auto-increment value set to its max e.g. CREATE TABLE `xxx_test` ( `test` int(11) NOT NULL AUTO_INCREMENT, `val` varchar(255) DEFAULT NULL, `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`test`), UNIQUE KEY `unq_val` (`val`) ) ENGINE=InnoDB AUTO_INCREMENT=2147483646 DEFAULT CHARSET=latin1; Try to insert into said table two rows: INSERT INTO `xxx_test` (`val`, `updated`) VALUES ('value1', NOW()) ON DUPLICATE KEY UPDATE `updated` = VALUES(`updated`); # id = 2147483646 INSERT INTO `xxx_test` (`val`, `updated`) VALUES ('value2', NOW()) ON DUPLICATE KEY UPDATE `updated` = VALUES(`updated`); # id = 2147483647 Now a row which should trigger an update of the id = 2147483646, val = value1 row: INSERT INTO `xxx_test` (`val`, `updated`) VALUES ('value1', NOW()) ON DUPLICATE KEY UPDATE `updated` = VALUES(`updated`); Instead of updating the row with id = 2147483646, val = 'value1' it tries to update the row with id = 2147483647, val = 'value2' due to the auto-increment failure, resulting in the confusing error: [Err] 1062 - Duplicate entry 'value1' for key 'unq_val' If the following is run: INSERT INTO `xxx_test` (`val`, `updated`) VALUES ('value3', NOW()) ON DUPLICATE KEY UPDATE `updated` = VALUES(`updated`); we get: # Affected rows: 2 # Time: 0.012s This again is incorrect as it should have inserted a new row but didn't due to the auto-increment failure and hence it updated the row with id = 2147483647 losing that data. Suggested fix: Failure to increment the auto-increment value should always return an error specific to that case e.g. [Err] 1467 - Failed to read auto-increment value from storage engine Preventing it from being treated as a duplicate key error, which can cause data loss.