Что происходит, когда auto_increment для целочисленного столбца достигает max_value в базах данных?

Комментарий Джима Мартина из §3.6.9. "Использование AUTO_INCREMENT" документации MySQL:

На всякий случай, если возникнут вопросы, поле AUTO_INCREMENT /DOES NOT WRAP/. Как только вы достигнете предела размера поля, INSERT выдаст ошибку. (Со слов Джереми Коула)

Быстрый тест с MySQL 5.1.45 приводит к ошибке:

ОШИБКА 1467 (HY000): не удалось прочитать значение автоинкремента из механизма хранения.

Вы можете проверить эту ошибку при вставке и принять соответствующие меры.

Просто, чтобы успокоить нервы, подумайте об этом:

Предположим, у вас есть база данных, которая вставляет новое значение каждый раз, когда пользователь выполняет какую-либо транзакцию на вашем веб-сайте.

С 64-битным целым числом в качестве идентификатора это является условием для переполнения: при населении мира в 6 миллиардов, тогда, если каждый человек на земле выполняет транзакцию один раз в секунду каждый день и каждый год (без отдыха), это займет более 80 лет для вашего идентификатора, чтобы обернуть вокруг.

Т.е. только гуглю нужно изредка во время кофе-брейка невнятно рассматривать эту проблему.

Вы узнаете, когда он переполнится, посмотрев на самый большой идентификатор. Вы должны изменить его задолго до того, как какое-либо исключение даже приблизится к тому, чтобы быть брошенным.

На самом деле, для начала вы должны проектировать с достаточно большим типом данных. Производительность вашей базы данных не пострадает, даже если вы с самого начала используете 64-битный идентификатор.

В ответах здесь указано, что происходит, но только в одном ответе говорится, как обнаружить проблему (и то только после того, как ошибка произошла). Как правило, полезно иметь возможность обнаруживать эти вещи до того, как они станут проблемой в производственной среде, поэтому я написал запрос, чтобы определить, когда вот-вот произойдет переполнение:

SELECT
  c.TABLE_CATALOG,
  c.TABLE_SCHEMA,
  c.TABLE_NAME,
  c.COLUMN_NAME
FROM information_schema.COLUMNS AS c
JOIN information_schema.TABLES AS t USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
WHERE c.EXTRA LIKE '%auto_increment%'
  AND t.AUTO_INCREMENT / CASE c.DATA_TYPE
      WHEN 'TINYINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 255, 127)
      WHEN 'SMALLINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 65535, 32767)
      WHEN 'MEDIUMINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 16777215, 8388607)
      WHEN 'INT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 4294967295, 2147483647)
      WHEN 'BIGINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', '18446744073709551615', 9223372036854775807) # need to quote because column type defaults to unsigned.
      ELSE 0
    END > .9; # 10% buffer

Надеюсь, это поможет кому-то где-то.

Для MySQL 5.6 3.6.9 Использование AUTO_INCREMENT в говорит:

Используйте наименьший целочисленный тип данных для столбца AUTO_INCREMENT, который достаточно велик, чтобы вместить максимальное значение последовательности, которое вам нужно. Когда столбец достигает верхнего предела типа данных, следующая попытка создания порядкового номера завершается неудачно.

Я хотел бы поделиться личным опытом, который у меня только что был по этому поводу. Использование Nagios + Check_MK + NDOUtils. NDOUtils хранит все проверки в таблице с именем nagios_servicechecks. Первичный ключ — это auto_increment int со знаком. Что происходит с MySQL, когда этот предел находится в диапазоне? Ну, в моем случае MySQL удаляет все записи, кроме последней. Теперь стол почти пуст. Каждый раз, когда вставляется новая запись, старая удаляется. Не знаю, почему это произошло, но дело в том, что я потерял все свои записи. IDOUtils, используемый с Icinga (не Nagios), устранил эту проблему, изменив int на bigint. Это не выдавало ошибку.