Bug #66566 Overflowing of auto_increment columns is not clearly indicated
Submitted: 28 Aug 2012 7:19 Modified: 28 Aug 2012 8:43
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: windmill
Triage: Needs Triage: D4 (Minor)

[28 Aug 2012 7:19] Simon Mudd
Description:
A comment from a colleague says:

"last Sunday, we had an outage because we overflowed a signed int AUTO_INCREMENT PK.

The application errors we saw at the application level were: ... DBD::mysql::st execute failed: Duplicate entry '2147483647' for key 'PRIMARY' at /path/to/some/code line 3682.

So, it simply stays at MAX_INT and stops auto-incrementing. While yes, all good devs should immediately catch on to the 2^31, but at 5am on a Sunday, it took us a while."

I'd like to request that MySQL emits a useful error message that points at the underlying problem? It must already have a check for "can I increment this further?", so adding a clearer error message to describe the underlying problem should not be that expensive in development time and then makes the underlying issue much clearer.

How to repeat:

Set an AUTO_INCREMENT column's primary key value close to the maximum allowed setting.
The insert more rows, and wait for the limit to be reached and get the error message reported above.

Suggested fix:
Provide a more concise error message like:

Failed to insert row: Reached maximum value '2147483647' permitted for AUTO_INCREMENT column table_name.column_name.
[28 Aug 2012 8:43] Valeriy Kravchuk
This is at the border of a feature request, but yes, it would be nice to get a descriptive and specific error message instead of:

mysql> insert into tiny values (0);
ERROR 1062 (23000): Duplicate entry '127' for key 'PRIMARY'
mysql> insert into tiny values (0);
ERROR 1062 (23000): Duplicate entry '127' for key 'PRIMARY'
...
[10 Oct 2014 11:50] Daniƫl van Eeden
In 5.7.5 with innodb_strict_mode and STRICT_ALL_TABLES:

When inserting multiple rows into an empty table when AUTO_INCREMENT=127 (tinyint):
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

When inserting just 1 row twice times I get the expected:
ERROR 1062 (23000): Duplicate entry '127' for key 'PRIMARY'
[17 Feb 2016 8:52] Umesh Shastry
Bug #80373 marked as duplicate of this
[10 Dec 2018 18:50] Hein Htat
Is there any updates here? We had a major outage due to this. AUTO_INCREMENT reaching the max value paired with ON DUPLICATE KEY UPDATE led to data lost do to rows being overwritten. Instead of an error'ing out the INSERT due to AUTO_INCREMENT reaching max, the db went an served the ON DUPLICATE KEY UPDATE.