MySQL integer limits warning script

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!