MySQL Bugs: #66566: Overflowing of auto_increment columns is not clearly indicated

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

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.

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'
...

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'

Bug #80373 marked as duplicate of this

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.