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

10 Comments to "Checking for AUTO_INCREMENT capacity with single query"

  1. Daniël van Eeden wrote:

    Nice one!!

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

  2. shlomi wrote:

    @Daniel,
    thanks, updated.

  3. Cédric wrote:

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

  4. shlomi wrote:

    ORDER BY is on you 🙂

  5. common_schema over traditional scripts | code.openark.org wrote:

    [...] It turns out it takes very little effort to build a query which does the same. In which case it is also easy to build a view which provides the [...]

  6. Martin wrote:

    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

  7. shlomi wrote:

    @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!

  8. Martin wrote:

    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)

  9. shlomi wrote:

    @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.

  10. M wrote:

    Hi Shlomi,

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

    BR
    Martin

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org