У нас есть таблица MySQL с автоматически увеличивающимся полем, установленным как INT (11). Эта таблица в значительной степени хранит список заданий, выполняемых в приложении. В любой момент времени существования приложения таблица может содержать тысячи записей или быть полностью пустой (т. Е. Все закончено).
Поле не привязано ни к чему другому по внешнему ключу.
Автоматическое приращение, кажется, случайным образом сбрасывается до нуля, хотя нам никогда не удавалось зафиксировать сброс.
Проблема становится очевидной, потому что мы видим, что поле автоинкремента достигает, скажем, 600 000 записей или около того, а затем, спустя некоторое время, поле автоинкремента, кажется, работает на низком уровне в 1000.
Это почти как если бы автоматическое приращение сбрасывалось само, если таблица пуста.
Возможно ли это, и если да, то как мне его выключить или изменить способ сброса?
Если это не так, есть ли у кого-нибудь объяснение, почему он может это делать?
Спасибо!
Счетчик автоинкремента хранится только в основной памяти, а не на диске.
http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html
Из-за этого при перезапуске службы (или сервера) произойдет следующее:
После запуска сервера для первой вставки в таблицу t InnoDB выполняет эквивалент этого оператора: SELECT MAX (ai_col) FROM t FOR UPDATE;
InnoDB увеличивает на единицу значение, полученное оператором, и назначает его столбцу и счетчику автоинкремента для таблицы. Если таблица пуста, InnoDB использует значение 1.
Говоря простым языком, после запуска службы MySQL она понятия не имеет, каким должно быть значение автоинкремента для вашей таблицы. Поэтому, когда вы впервые вставляете строку, она находит максимальное значение поля, которое использует автоинкремент, добавляет 1 к этому значению и использует полученное значение. Если строк нет, он начнется с 1.
Это было проблемой для нас, поскольку мы использовали таблицу и функцию автоинкремента mysql для аккуратного управления идентификаторами в многопоточной среде, когда пользователи перенаправлялись на сторонний платежный сайт. Поэтому нам нужно было убедиться, что идентификатор, который третья сторона получила и отправила нам, был уникальным и оставался таким (и, конечно, есть вероятность, что пользователь отменит транзакцию после того, как они были перенаправлены).
Итак, мы создавали строку, получали сгенерированное значение автоинкремента, удаляли строку, чтобы таблица оставалась чистой, и перенаправляли значение на сайт оплаты. Что мы сделали, чтобы исправить проблему того, как InnoDB обрабатывает значения AI, было следующее:
$query = "INSERT INTO transactions_counter () VALUES ();";
mysql_query($query);
$transactionId = mysql_insert_id();
$previousId = $transactionId - 1;
$query = "DELETE FROM transactions_counter WHERE transactionId='$previousId';";
mysql_query($query);
Это всегда сохраняет последний идентификатор транзакции, созданный в виде строки в таблице, без ненужного взрыва таблицы.
Надеюсь, что это поможет любому, кто может столкнуться с этим.
Изменить (2018-04-18):
Как указано ниже, похоже, что его поведение было изменено в MySQL 8.0+.
https://dev.mysql.com/worklog/task/?id=6204
Формулировка в этом рабочем журнале в лучшем случае ошибочна, однако кажется, что InnoDB в этих более новых версиях теперь поддерживает постоянные значения autoinc при перезагрузках.
-Гремио
Мы столкнулись с этой проблемой и обнаружили, что при запуске таблицы оптимизации для пустой таблицы значение автоматического увеличения также сбрасывалось. Видеть этот отчет об ошибке MySQL.
В качестве обходного пути вы можете сделать:
ALTER TABLE a AUTO_INCREMENT=3 ENGINE=innoDB;
Вместо того OPTIMIZE TABLE.
Кажется, это то, что MySQL делает внутри (очевидно, без установки значения автоматического увеличения)
Просто выстрел в темноте - если приложение использует TRUNCATE TABLE чтобы очистить таблицу после завершения обработки, это приведет к сбросу поля автоинкремента. Вот краткое обсуждение по вопросу. Хотя в этой ссылке упоминается, что InnoDB не сбрасывает auto_increments для усечения, это было зарегистрировано как ошибка и исправлено несколько лет назад.
Предполагая, что моя догадка верна, вы можете перейти от усечения к удалению, чтобы решить проблему.
Только явный сброс этого значения или удаление / воссоздание этого поля или другая подобная насильственная операция должны когда-либо сбрасывать счетчик auto_increment. (TRUNCATE был действительно хорошей теорией.) Кажется невозможным, что вы внезапно оборачиваете 32-битный INT, когда последнее значение, которое вы видите, составляет всего 600k. Он определенно не должен сбрасываться только потому, что таблица пустеет. У вас либо ошибка mysql, либо что-то в вашем PHP-коде. Или парень из соседней кабинки подшучивает над вами.
Вы можете отладить, включив двоичный журнал, поскольку он будет содержать такие утверждения повсюду:
SET INSERT_ID=3747670/*!*/;
Тогда, по крайней мере, вы сможете увидеть каждую деталь того, что происходит с этой таблицей, в том числе прямо перед сбросом счетчика.
ALTER TABLE имя_таблицы ENGINE = MyISAM
У меня работает. Наша таблица всегда очень маленькая, поэтому InnoDB не нужен.
InnoDB не сохраняет значение автоматического увеличения на диске, поэтому забывает его, когда сервер MySQL завершает работу. Когда MySQL снова запускается, движок InnoDB восстанавливает значение автоматического увеличения следующим образом: SELECT (MAX(id) + 1) AS auto_increment FROM table. Это Жук то есть фиксированный в MySQL версии 8.0.
Измените движок таблицы, чтобы решить проблему:
ALTER TABLE table ENGINE = MyISAM
Или обновите сервер MySQL до версии 8.0, когда она будет выпущена.