Easy way to compute how close an auto_increment is to its maximum value?

Question

So yesterday we had a table that has an auto_increment PK for a smallint that reached its maximum. We had to alter the table on an emergency basis, which is definitely not how we like to roll.

Is there an easy way to report on how close each auto_increment field that we use is to its maximum? The best way I can think of is to do a SHOW CREATE TABLE statement, parse out the size of the auto-incremented column, then compare that to the AUTO_INCREMENT value for the table.

On the other hand, given that the schema doesn't change very often, should I store information about the columns' maximum values and get the current AUTO_INCREMENT with SHOW TABLE STATUS?

Answer 1

Your question seems perfectly reasonable to me. You should be able to get the current auto-increment values for each table from information_schema. I don't think the max values for the various int types are available as constants in MySQL, but Roland Bouman demonstrated a simple way to generate them in MySQL:

In SQL how do I get the maximum value for an integer?

If you put that data into a table, then you can write a single SQL query to get the current auto-increment status of all of your tables so you can see how close you are to running out of values.

Here's a quick-and-dirty example to get you started:

create temporary table max_int_values
(
int_type varchar(10) not null,
extra varchar(8) not null default '',
max_value bigint unsigned not null,
primary key (int_type,max_value),
key int_type (int_type),
key max_value (max_value)
);

insert into max_int_values(int_type,extra,max_value) values ('tinyint','',~0 >> 57);
insert into max_int_values(int_type,extra,max_value) values ('tinyint','unsigned',~0 >> 56);
insert into max_int_values(int_type,extra,max_value) values ('smallint','',~0 >> 49);
insert into max_int_values(int_type,extra,max_value) values ('smallint','unsigned',~0 >> 48);
insert into max_int_values(int_type,extra,max_value) values ('mediumint','',~0 >> 41);
insert into max_int_values(int_type,extra,max_value) values ('mediumint','unsigned',~0 >> 40);
insert into max_int_values(int_type,extra,max_value) values ('int','',~0 >> 33);
insert into max_int_values(int_type,extra,max_value) values ('int','unsigned',~0 >> 32);
insert into max_int_values(int_type,extra,max_value) values ('bigint','',~0 >> 1);
insert into max_int_values(int_type,extra,max_value) values ('bigint','unsigned',~0);

select t.table_Schema,t.table_name,c.column_name,c.column_type,
  t.auto_increment,m.max_value,
  round((t.auto_increment/m.max_value)*100,2) as pct_of_values_used,
  m.max_value - t.auto_increment as values_left
from information_schema.tables t
  inner join information_schema.columns c 
    on c.table_Schema = t.table_Schema and c.table_name = t.table_name
  inner join max_int_values m 
    on m.int_type = substr(c.column_type,1,length(m.int_type)) 
    and ((m.extra like '%unsigned') = (c.column_type like '%unsigned'))
where c.extra = 'auto_increment'
order by pct_of_values_used;

Answer 2

From openark, here is a single query for checking auto-increment capacity:

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  COLUMN_TYPE,
  IF(
    LOCATE('unsigned', COLUMN_TYPE) > 0,
    1,
    0
  ) AS IS_UNSIGNED,
  (
    CASE DATA_TYPE
      WHEN 'tinyint' THEN 255
      WHEN 'smallint' THEN 65535
      WHEN 'mediumint' THEN 16777215
      WHEN 'int' THEN 4294967295
      WHEN 'bigint' THEN 18446744073709551615
    END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
  ) AS MAX_VALUE,
  AUTO_INCREMENT,
  AUTO_INCREMENT / (
    CASE DATA_TYPE
      WHEN 'tinyint' THEN 255
      WHEN 'smallint' THEN 65535
      WHEN 'mediumint' THEN 16777215
      WHEN 'int' THEN 4294967295
      WHEN 'bigint' THEN 18446744073709551615
    END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
  ) AS AUTO_INCREMENT_RATIO
FROM
  INFORMATION_SCHEMA.COLUMNS
  INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
  TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema')
  AND EXTRA='auto_increment'
;

And of course you can add an ORDER BY AUTO_INCREMENT_RATIO DESC with perhaps a LIMIT to easily pick out the ones closest to their limit.