Ostatnio miałem okazję natknąć się na bardzo specyficzny “feature” InnoDB dotyczący autoinkrementacji. Jako że InnoDB będzie coraz częstszym gościem na serwerach MySQL, dobrze wiedzieć jakie ma specyficzne zwyczaje. Szczególnie że ten konkretny nie jest ani logiczny, ani intuicyjny, ani nie zawiera nawet ślad sensu. Trzeba jednak jakoś sobie radzić.

Dwie tabele, powiązane ze sobą przy pomocy autoinkrementowanej kolumny. Nałożone klucze główne, bądź unikalne indeksy. W pewnym momencie okazało się, że INSERTy zaczęły zwracać błąd o próbach duplikowania wpisów. Szybkie zerknięcie wykazało, że zmieniły się wartości AUTO_INCREMENT dla obu tabel. Konkretnie, zmniejszyły się. To poskutkowało tym, że do drugiej tabeli zaczęły trafiać wpisy z wartościami, które już się z w niej znajdowały, co oczywiście skutkowało błędem. Dalsza analiza wykazała, że serwer MySQL zresetował się chwilę przed wystąpieniem tego błędu. O co chodzi?

MySQL, na szczęście, ma bardzo dobrą dokumentację – można w niej znaleźć takżę i odpowiedź na tą zagadkę. Konkretnie tu: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Przyczyną wystąpienia tego problemu jest fakt, że InnoDB nie przechowuje nigdzie wartości AUTO_INCREMENT. Znaczy się, przechowuje ale tylko w pamięci. Na dysku – nie, bo i po co. Skutek jest taki, że po resecie serwera dane te są tracone. Oczywiście, trzeba je jakoś wygenerować, więc InnoDB sprawdza sobie maksymalną wartość w autoinkrementowanej kolumnie:

SELECT MAX(ai_col) FROM t FOR UPDATE;

Oczywiście, to że w praktyce wartość AUTO_INCREMENT mogła być kompletnie inna (wyższa), nie jest już istotne. W każdym razie, InnoDB ma coś, co uznaje za wartość optymalną, a że wartość ta nie pokrywa się z tym, czego spodziewa się użytkownik? Nie ważne. W tym konkretnym przypadku zmiana była dosyć znacząca – z ok. 1000 do ok. 700.

Rozwiązanie takiej sytuacji leży w gestii użytkownika. Możemy samodzielnie utworzyć sobie tabelę, w której będziemy przechowywać dane o AUTO_INCREMENT a potem przy pomocy np. triggera, będziemy sobie to sprawdzać, ustawiać, modyfikować. Możemy też przy pomocy kluczy obcych upewnić się, że zachowane będą więzy integralności – w ten sposób będziemy mieli gwarancję, że to co kasujemy w jednej tabeli będzie także kasowane w drugiej i na pewno nie pojawią się problemy z duplikowaniem wpisów.

Problem ten jest dosyć szeroko opisany. Trafiłem nawet na buga z roku 2003, dotyczącego MySQL 4.0. Niestety, nie zapowiada się aby sytuacja miała się zmienić. Tym bardziej trzeba być świadomym że taka sytuacja może zajść i nie pokładać zbyt wielkiego zaufania w wartości AUTO_INCREMENT. Chwalebne to to dla MySQL nie jest, ale co zrobić?