MySQL: what is AUTO_INCREMENT “free space”? Quote from openark-kit. The software seems unmaintained (Google Code hosted and last commit from 2013).


  oak-show-limits: show AUTO_INCREMENT “free space”.


What is this? How-to see AUTO_INCREMENT “free space” without this tool?
It's just the max id the auto_increment can reach. 

You can make the same with the following SQL :

SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    `AUTO_INCREMENT` current_id, 
    if(m.max_id =-1,'18446744073709551615',m.max_id) max_id, 
    (m.max_id - `AUTO_INCREMENT`) free_id,
    (`AUTO_INCREMENT`/m.max_id*100) percent_used
FROM  
    INFORMATION_SCHEMA.TABLES t
    INNER JOIN 
    (
    SELECT 
        TABLE_NAME,
        TABLE_SCHEMA,
        CASE 
            WHEN COLUMN_TYPE LIKE "tinyint%unsigned"    THEN 255
            WHEN COLUMN_TYPE LIKE "tinyint%"    THEN 127
            WHEN COLUMN_TYPE LIKE "smallint%unsigned"   THEN 65535
            WHEN COLUMN_TYPE LIKE "smallint%"   THEN 32767
            WHEN COLUMN_TYPE LIKE "mediumint%unsigned"  THEN 16777215
            WHEN COLUMN_TYPE LIKE "mediumint%"  THEN 8388607
            WHEN COLUMN_TYPE LIKE "int%unsigned"    THEN 4294967295
            WHEN COLUMN_TYPE LIKE "int%"    THEN 2147483647
            WHEN COLUMN_TYPE LIKE "bigint%unsigned" THEN 18446744073709551615
            WHEN COLUMN_TYPE LIKE "bigint%" THEN 9223372036854775807
        END max_id
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE extra like "%auto_increment%" 
    ) m
    on 
        t.TABLE_NAME = m.TABLE_NAME
        AND t.TABLE_SCHEMA = m.TABLE_SCHEMA
-- WHERE TABLE_SCHEMA = 'DatabaseName'
-- AND   TABLE_NAME   = 'TableName'; 


oak-show-limits


  NAME
  
  oak-show-limits: show AUTO_INCREMENT "free space"
  
  SYNOPSIS
  
  Show all AUTO_INCREMENT columns, current AUTO_INCREMENT values and
  upper bounds, usage ratio, on all schemata and on all tables:
  
  DESCRIPTION
  
  oak-show-limits looks for AUTO_INCREMENT columns, and compares current
  values with the column type. It reports those columns where the
  AUTO_INCREMENT value is higher than a given threshold. This utility
  can be used for diagnosing underestimated and overestimated data type
  ranges. Underestimated data types are those in which the
  AUTO_INCRMENET current value is approaching the upper limit.
  Overestimated data types are those where the current value is
  extremely far from the upper limit. Output is in tab delimited, where
  each row specifies: