3

Recently we had an overflow in an auto_increment column in a mysql table. Now I'd like to monitor our databases with nagios, so that we'll be alerted whenever an auto_increment column comes near to its max value.

Is there already a check for it? If yes, where can I find it? If not, any good hints on how to write one?

(I've found only a small handful of helpful Google results, and those used a Percona extension; sadly we're limited to "pure" mysql here).

2

SUGGESTION #1

I have a suggestion if you are willing to get your hands a little dirty and the tables are reasonably small enough.

You should convert all tables with auto_increment from INT to BIGINT. This should suppress autoincrement overflow issues for our lifetime. This will result in bigger tables (1 extra byte per row).

SUGGESTION #2

If you still prefer to monitor all tables with auto increment, here is a poor man's approach:

Start by running this query

SELECT table_schema,table_name,auto_increment FROM information_schema.tables
WHERE auto_increment IS NOT NULL;

This will give you every table that has auto_increment defined in the table and the next value that will used. If you want to see how many values are left, you can compute it.

For example, if all you auto_increments were defined as INT, the max signed value would be 2147483647. You could run this query

SELECT table_schema,table_name,auto_increment
(2147483647 - auto_increment) values_left
FROM information_schema.tables
WHERE auto_increment IS NOT NULL;

The values_left is what you can monitor. If you want to be warned of tables who have less than million values left, you can then run this:

SELECT * FROM
(SELECT table_schema,table_name,auto_increment
(2147483647 - auto_increment) values_left
FROM information_schema.tables
WHERE auto_increment IS NOT NULL) A
WHERE values_left < 1000000;

If you are able to make Nagios plugins or if you want to create a cronjob that calls for the tables you wish to monitor, you can use this last query as your base.

Give it a Try !!!

2
  • Thanks; it'll need to be a bit more involved, to incorporate the data type in the max value calculation, but it's a very good approach.
    – moritz
    Mar 14 '14 at 19:31
  • Actually 4 extra bytes, not 1 extra. Bigint is 8 bytes (64 bits), int is 4. But don't just blindly do this - if you have an app that records the ID values, if it can't handle 64 bit integers you will have a very bad time. Plus increasing storage space like this can slow down data read.
    – Ariel
    Jun 29 '14 at 8:30
1

You can give a look at https://vettabase.com/blog/monitor-auto-increment-usage

The given query report auto_increment columns status sorted by filling :

SELECT
    t.TABLE_SCHEMA AS `schema`,
    t.TABLE_NAME AS `table`,
    t.AUTO_INCREMENT AS `auto_increment`,
    c.DATA_TYPE AS `pk_type`,
    t.AUTO_INCREMENT / (
        CASE DATA_TYPE
          WHEN 'tinyint'   THEN IF(COLUMN_TYPE LIKE '%unsigned',                  255,                 127)
          WHEN 'smallint'  THEN IF(COLUMN_TYPE LIKE '%unsigned',                65535,               32767)
          WHEN 'mediumint' THEN IF(COLUMN_TYPE LIKE '%unsigned',             16777215,             8388607)
          WHEN 'int'       THEN IF(COLUMN_TYPE LIKE '%unsigned',           4294967295,          2147483647)
          WHEN 'bigint'    THEN IF(COLUMN_TYPE LIKE '%unsigned', 18446744073709551615, 9223372036854775807)
        END / 100) AS `max_value`
   FROM information_schema.TABLES t
  INNER JOIN information_schema.COLUMNS c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
                                         AND t.TABLE_NAME   = c.TABLE_NAME
  WHERE t.AUTO_INCREMENT IS NOT NULL
    AND c.COLUMN_KEY = 'PRI'
    AND c.DATA_TYPE LIKE '%int'
  ORDER BY `max_value` DESC

Cheers

0

Inspired by the answer from RonaldMySQLDBA I've now written https://github.com/moritz/mysql-check-autoincrement

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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