Checking for AUTO_INCREMENT capacity with single query

April 5, 2011

Darn! This means oak-show-limits becomes redundant. Am I not supposed to speak about it on my coming presentation? Bad timing!

You have AUTO_INCREMENT columns. How far are you pushing the limits? Are you going to run out of AUTO_INCREMENT values soon? Perhaps you wonder whether you should ALTER from INT to BIGINT?

The answer is all there in INFORMATION_SCHEMA. The TABLES table shows the current AUTO_INCREMENT value per table, and the COLUMNS table tells us all about a column's data type.

It takes some ugly code to deduce the maximum value per column type, what with signed/unsigned and data type, but then its very simple. Here is the query:

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'
;

There's one row in the result set for each AUTO_INCREMENT column. since at most one AUTO_INCREMENT column can exist for any given table, each row also identifies a unique table. Resulting columns are mostly self-explanatory, but here's some details on some of the columns:

  • IS_UNSIGNED: 1 when the column is UNSIGNED, 0 otherwise.
  • MAX_VALUE: maximum value that can be contained within column.
  • AUTO_INCREMENT: current AUTO_INCREMENT value for table.
  • AUTO_INCREMENT_RATIO: value in the range [0..1], where 1 means "100% full".

A sample output:

+--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME  | DATA_TYPE | COLUMN_TYPE           | IS_UNSIGNED | MAX_VALUE  | AUTO_INCREMENT | AUTO_INCREMENT_RATIO |
+--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+
| sakila       | actor      | actor_id     | smallint  | smallint(5) unsigned  |           1 |      65535 |            201 |               0.0031 |
| sakila       | address    | address_id   | smallint  | smallint(5) unsigned  |           1 |      65535 |            606 |               0.0092 |
| sakila       | category   | category_id  | tinyint   | tinyint(3) unsigned   |           1 |        255 |             17 |               0.0667 |
| sakila       | city       | city_id      | smallint  | smallint(5) unsigned  |           1 |      65535 |            601 |               0.0092 |
| sakila       | country    | country_id   | smallint  | smallint(5) unsigned  |           1 |      65535 |            110 |               0.0017 |
| sakila       | customer   | customer_id  | smallint  | smallint(5) unsigned  |           1 |      65535 |            600 |               0.0092 |
| sakila       | film       | film_id      | smallint  | smallint(5) unsigned  |           1 |      65535 |           1001 |               0.0153 |
| sakila       | inventory  | inventory_id | mediumint | mediumint(8) unsigned |           1 |   16777215 |           4582 |               0.0003 |
| sakila       | language   | language_id  | tinyint   | tinyint(3) unsigned   |           1 |        255 |              7 |               0.0275 |
| sakila       | payment    | payment_id   | smallint  | smallint(5) unsigned  |           1 |      65535 |          16050 |               0.2449 |
| sakila       | rental     | rental_id    | int       | int(11)               |           0 | 2147483647 |          16050 |               0.0000 |
| sakila       | staff      | staff_id     | tinyint   | tinyint(3) unsigned   |           1 |        255 |              3 |               0.0118 |
| sakila       | store      | store_id     | tinyint   | tinyint(3) unsigned   |           1 |        255 |              3 |               0.0118 |
+--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+

Bonus: free advice on increasing your AUTO_INCREMENT capacity

Make it UNSIGNED. No, really. Check your definitions now.

tags: , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

  • Nice one!!

    Maybe it's a good idea to also exclude performance_schema?

  • @Daniel,
    thanks, updated.

  • Nice query, just add "ORDER BY 9 DESC" in my case.
    Thx !

  • ORDER BY is on you 🙂

  • Pingback: common_schema over traditional scripts | code.openark.org()

  • Martin

    Hi 10x for the great article.But I have a question I have a result like this from running this query against one of the DB-s:

    | ussd_ubrowser | ubrowser_phone_producers | id | int | int(11) | 0 | 2147483647 | 18446744073709551615 | 8589934596.0000 |

    How is that possible,the Signed int to have more than 2147483647 and the output in AUTO_INCREMENT_RATIO is as seen above 8589934596.The version of MySQL that I use is quite old:

    5.1.73 64Bit

    BR
    Martin

  • @martin, I guess the answer is "this is a bug" and that I should be casting along the way. But in order to fix it, I would like to see the data you were working on. Nothing special, just the table definition (only PK is of interest) and the current PK value, etc.

    Thanks!

  • Martin

    Hi Shlomi,

    10x for the fast response.Here is the requested information:

    mysql> show create table ussd_ubrowser.ubrowser_phone_producers \G;
    *************************** 1. row ***************************
    Table: ubrowser_phone_producers
    Create Table: CREATE TABLE `ubrowser_phone_producers` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(200) NOT NULL,
    `ordClmn` int(11) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

    mysql> show table status from ussd_ubrowser like 'ubrowser_phone_producers' \G;
    *************************** 1. row ***************************
    Name: ubrowser_phone_producers
    Engine: MyISAM
    Version: 10
    Row_format: Dynamic
    Rows: 6
    Avg_row_length: 21
    Data_length: 128
    Max_data_length: 281474976710655
    Index_length: 2048
    Data_free: 0
    Auto_increment: 18446744073709551615
    Create_time: 2013-02-22 20:42:54
    Update_time: 2013-02-22 20:42:54
    Check_time: 2015-11-27 14:57:07
    Collation: latin1_swedish_ci
    Checksum: NULL
    Create_options:
    Comment:
    1 row in set (0.01 sec)

  • @Martin,

    The answer lies in the strange output you got from your `SHOW CREATE TABLE`. How is it possible you have `AUTO_INCREMENT=18446744073709551615` for an INT column? There is nothing the query can do about this: I suggest reporting this as a bug to MySQL.

  • M

    Hi Shlomi,

    great 10x for looking into it.And thank you for the great utilities that you have made.

    BR
    Martin

 
Powered by Wordpress and MySQL. Theme by openark.org