1

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?

1 Answer 1

1

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.