MySQL integer overflow 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 http://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');

  $ok=true;

  while($Table=mysqli_fetch_array($Tables))
    {
      $Columns=mysqli_query($Link,'show columns from '.$Table[0]);
      while($Column=mysqli_fetch_array($Columns))
        {
          if($Column['Extra']=='auto_increment')
            {
// 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)
                {
                  echo '** INTEGER LIMIT BREACHED **',"\n";
                  echo 'Table    ',$Table[0],"\n";
                  echo 'Field    ',$Column['Field'],"\n";
                  echo 'Datatype ',$Column['Type'],"\n";
                  echo 'Current  ',$Data['Max'],"\n";
                  echo 'Limit    ',$Limits[$DataType],"\n";
                  echo '%        ',$Percent,"\n\n";

                  $ok=false;
                }
            } // Field
        } // Column loop
    } // Table loop

  if($ok)
    echo 'Datatype limits ok';
?>

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!