Automatyczne przyrosty pól MySQL resetują się same

Mamy tabelę MySQL, która ma pole automatycznego przyrostu ustawione jako INT (11). Ta tabela zawiera prawie listę zadań uruchomionych w aplikacji. W dowolnym momencie podczas użytkowania aplikacji tabela może zawierać tysiące wpisów lub być całkowicie pusta (tzn. Wszystko się skończyło).

Pole nie jest przypisane do żadnego innego klucza.

Wydaje się, że auto-przyrost losowo sam się resetuje do zera, chociaż tak naprawdę nigdy nie byliśmy w stanie zablokować resetowania.

Problem staje się oczywisty, ponieważ widzimy, że pole automatycznego przyrostu dochodzi do, powiedzmy, około 600 000 rekordów, a chwilę później pole automatycznego przyrostu wydaje się działać w niskich 1000.

To prawie tak, jakby auto-przyrost resetuje się sam, jeśli tabela jest pusta.

Czy jest to możliwe, a jeśli tak, to jak je wyłączyć lub zmienić sposób resetowania?

Jeśli tak nie jest, czy ktoś ma wyjaśnienie, dlaczego to robi?

Dzięki!

Licznik automatycznego przyrostu jest przechowywany tylko w pamięci głównej, a nie na dysku.

http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html

Z tego powodu po ponownym uruchomieniu usługi (lub serwera) nastąpi:

Po uruchomieniu serwera, dla pierwszego wstawienia do tabeli t, InnoDB wykonuje odpowiednik tej instrukcji: SELECT MAX (ai_col) FROM t FOR UPDATE;

InnoDB zwiększa o jeden wartość pobraną przez instrukcję i przypisuje ją do kolumny oraz licznika automatycznego przyrostu dla tabeli. Jeśli tabela jest pusta, InnoDB używa wartości 1.

Mówiąc wprost, po uruchomieniu usługi MySQL nie ma pojęcia, jaka powinna być wartość automatycznego przyrostu tabeli. Kiedy więc wstawisz wiersz po raz pierwszy, znajdzie maksymalną wartość pola, które korzysta z automatycznego przyrostu, dodaje 1 do tej wartości i używa wartości wynikowej. Jeśli nie ma żadnych wierszy, rozpocznie się od 1.

Był to dla nas problem, ponieważ używaliśmy funkcji automatycznego zwiększania tabeli i mysql do porządnego zarządzania identyfikatorami w wielowątkowym środowisku, w którym użytkownicy przekierowywani byli na stronę płatniczą innej firmy. Musieliśmy więc upewnić się, że identyfikator, który osoba trzecia otrzymała i odesłał do nas, był unikalny i pozostanie w ten sposób (i oczywiście istnieje możliwość anulowania transakcji przez użytkownika po przekierowaniu).

Tworzyliśmy więc wiersz, uzyskiwaliśmy wygenerowaną wartość automatycznego przyrostu, usuwaliśmy wiersz, aby utrzymać tabelę w czystości, i przesyłaliśmy wartość na stronę płatności. Ostatecznie zrobiliśmy to, aby naprawić problem, w jaki InnoDB obsługuje wartości 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); 

To zawsze utrzymuje najnowszą transakcję wygenerowaną jako wiersz w tabeli, bez niepotrzebnego wysadzania tabeli.

Mam nadzieję, że pomoże to każdemu, kto na to wpadnie.

Edytuj (2018-04-18) :

Jak wspomniano poniżej Finesse, wygląda na to, że zostało to zmodyfikowane w MySQL 8.0+.

https://dev.mysql.com/worklog/task/?id=6204

Sformułowanie w tym dzienniku pracy jest co najwyżej błędne, jednak wydaje się, że InnoDB w tych nowszych wersjach obsługuje teraz trwałe wartości autoinc podczas ponownego uruchamiania.

-Gremio

Wystąpił ten problem i odkryliśmy, że gdy optymalizowana tabela została uruchomiona na pustej tabeli, wartość automatycznego przyrostu również została zresetowana. Zobacz ten raport o błędach MySQL .

Aby obejść ten problem, możesz:

ALTER TABLE a AUTO_INCREMENT=3 ENGINE=innoDB;

Zamiast OPTIMIZE TABLE.

Wygląda na to, że MySQL robi to wewnętrznie (oczywiście bez ustawiania wartości Auto increment oczywiście)

Tylko strzał w ciemność - jeśli aplikacja używa a TRUNCATE TABLEdo opróżnienia tabeli po zakończeniu przetwarzania, spowoduje to zresetowanie pola automatycznego przyrostu. Oto krótka dyskusja na ten temat. Chociaż ten link wspomina, że ​​InnoDB nie resetuje auto_increments na skracaniu, który został zgłoszony jako błąd i naprawiony kilka lat temu.

Zakładając, że moje przypuszczenie jest słuszne, możesz zmienić z przycinania na usuwanie, aby rozwiązać problem.

Tylko jawne zresetowanie tej wartości, upuszczenie / odtworzenie tego pola lub inna podobna gwałtowna operacja powinna kiedykolwiek zresetować licznik auto_increment. (TRUNCATE była naprawdę dobrą teorią). Wydaje się niemożliwe, że nagle pakujesz 32-bitową INT, gdy ostatnia obserwowana wartość wynosi zaledwie 600 tys. Zdecydowanie nie powinno się resetować tylko dlatego, że stół się opróżnia. Masz błąd mysql lub coś w kodzie PHP. Albo facet w sąsiedniej kabinie robi z tobą podstęp.

Możesz debugować, włączając dziennik binarny , ponieważ będzie on zawierał następujące instrukcje w całym:

SET INSERT_ID=3747670/*!*/;

Wtedy przynajmniej zobaczysz każdy szczegół tego, co dzieje się z tym stołem, w tym tuż przed zresetowaniem licznika.

ALTER TABLE nazwa_tabeli ENGINE = MyISAM

Pracuje dla mnie. Nasz stół jest zawsze bardzo mały, więc nie potrzebujesz InnoDB.

InnoDB nie przechowuje wartości automatycznego przyrostu na dysku, dlatego zapomina o tym, gdy serwer MySQL jest zamknięty. Gdy MySQL jest uruchomiony ponownie, silnik InnoDB przywraca wartość auto przyrost ten sposób: SELECT (MAX(id) + 1) AS auto_increment FROM table. Jest to błąd , który jest ustalony w MySQL w wersji 8.0.

Zmień silnik tabeli, aby rozwiązać problem:

ALTER TABLE table ENGINE = MyISAM

Lub zaktualizuj serwer MySQL do wersji 8.0, gdy zostanie wydany.