This PHP script will check all MySQL tables and warn you if any integer fields with an AUTO_INCREMENT setting are approaching the limit of that field's datatype.
When the limit for a field size is reached future INSERT operations will fail (generating an ERROR 1467: Failed to read auto-increment value from storage engine) resulting in an annoyed user. Set up a cronjob to run this script regularly and email the results to receive a warning in time to increase the limit or take other action before any damage is done.
<?php
// Connect to server and select database Update with your credentials
$Link=mysqli_connect('host','username','password','db');
// Warn me if this % of the datatype's limit is breached
$WarningThreshold=66;
// Limits as specified https://dev.mysql.com/doc/refman/5.1/en/integer-types.html
$Limits=['tinyint' => 127,
'tinyint unsigned' => 255,
'smallint' => 32767,
'smallint unsigned' => 65535,
'mediumint' => 8388607,
'mediumint unsigned' => 16777215,
'int' => 2147483647,
'int unsigned' => 4294967295,
'bigint' => 9223372036854775807,
'bigint unsigned' => 18446744073709551615];
$Tables=mysqli_query($Link,'show tables') or exit('could not list tables');
$Text='';
while($Table=mysqli_fetch_array($Tables))
{
$Columns=mysqli_query($Link,'show columns from '.$Table[0].' where Extra="auto_increment"');
if($Column=mysqli_fetch_array($Columns))
{
// Integer types will be in the format /(tiny|small|medium|big)?int\(\d+\) (unsigned)?/
$DataType=strtolower(preg_replace('/\(\d*\)/','',$Column['Type']));
$Max=mysqli_query($Link,'select Max('.$Column['Field'].') as Max from '.$Table[0]);
$Data=mysqli_fetch_array($Max);
$Percent=$Data['Max']/$Limits[$DataType]*100;
if($Percent>$WarningThreshold)
{
$Text.='** INTEGER LIMIT APPROACHING! **'."\n";
$Text.='Table '.$Table[0]."\n";
$Text.='Field '.$Column['Field']."\n";
$Text.='Datatype '.$Column['Type']."\n";
$Text.='Current '.$Data['Max']."\n";
$Text.='Limit '.$Limits[$DataType]."\n";
$Text.='% '.$Percent."\n\n";
}
} // Column block
} // Table loop
if($Text==='')
{
$Text='Datatype limits ok';
}
echo $Text;
?>
Yes, a good coder should be able to choose realistic datatypes for the data they will be storing allowing for room to grow, but I don't like to leave things to chance!