[MySQL] ERROR 1292 (22007): Incorrect datetime value: ‚0000-00-00 00:00:00’

Bazy danych
[MySQL] ERROR 1292 (22007): Incorrect datetime value: ‚0000-00-00 00:00:00’

Po aktualizacji MySQL-a do wersji 5.7 możecie spotkać się z błędem „ERROR 1292 (22007): Incorrect datetime value: ‚0000-00-00 00:00:00′”. Bowiem od tej wersji MySQL już nie akceptuje wartości zerowej w polu daty (date) oraz daty i czasu (datetime).

Możemy podejrzeć te ustawienia wywołując zapytanie SQL:

SHOW VARIABLES LIKE 'sql_mode'

lub

SELECT @@sql_mode

co w rezultacie powinno wyświetlić nam

Ustawienie to jest o tyle problematyczna, że w przypadku wystąpienia rekordów z wartością „0000-00-00 00:00:00” w jakiejś tabeli, nie będziemy mogli zmodyfikować jej struktury oraz wykonać operacji na feralnych rekordach. Jednak MySQL nie zostawia nas na pastwę losu, możliwe jest obejście tego problemu na dwa sposoby.

Rozwiązanie 1: zmiana ustawień bazy danych

Jako że MySQL przestał akceptować wartości 0 w typach date oraz datetime co zostało zawarte w ustawieniach bazy danych to możemy dokonać modyfikacji owych ustawień. Nie polecał bym jednak takiego rozwiązania, po pierwsze twórcy MySQL-a nie po to dodają takie ustawienia jako domyślne, aby je ignorować bez namysłu. Po drugie i chyba ważniejsze taka data jest zwyczajnie błędna.

Zmiana ustawień jest bardzo prosta, przechodzimy do konsoli czy też phpMyAdmin-a i wykonujemy polecenia:

<br />
SET @@sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@@sql_mode,','),',NO_ZERO_DATE,'  ,','));<br />
SET @@sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@@sql_mode,','),',NO_ZERO_IN_DATE,',','));<br />

Od tego momentu możemy dokonywać modyfikacji nie martwiąc się o błędną datę. Jednak ustawienie to nie jest trwałe, po restarcie systemu czy też serwera bazy danych `@@sql_mode` wróci do ustawień zdefiniowanych w pliku konfiguracyjnym. Więc, aby dokonać stałej modyfikacji należy przejść do ustawień serwera zawartych w pliku my.ini lub my.cnf i odnaleźć linię sql_mode.

Jak widać linia ta może być za komentowana, wtedy usuwany znak # i wprowadzamy ustawienia które nas interesują. Najlepiej skopiować ustawienia wyświetlone przez SELECT @@sql_mode i usunąć NO_ZERO_DATE oraz NO_ZERO_IN_DATE. Po czym restartujemy serwer i cieszymy się nowymi ustawieniami 🙂

Rozwiązanie 2: zmiana daty feralnych rekordów

Drugie rozwiązanie jest zdecydowanie prostsze i nie wymaga dostępu do konfiguracji serwera MySQL, a także pozwala rozwiązać problem z błędem „Incorrect datetime value”.

Rozwiązanie to polega na poprawieniu błędnych rekordów. W moim przypadku niestety nigdy nie sprawdzało się polecenie, które mówiło o aktualizacji rekordów z wartością „0000-00-00 00:00:00”. Jednak może u was ono zadziała więc warto spróbować 😉

UPDATE `users` SET `created` = NULL WHERE `created` = '0000-00-00 00:00:00'

Polecenie odwołuje się do tabeli users oraz pola created, które ma zostać ustawione na NULL, gdy jego wartość równa się „0000-00-00 00:00:00”. Jednak gdyby powyższe polecenie nie zadziałało to nie martwcie się, gdyż u mnie także nigdy nie zadziałało 🙁 Jednak są osoby w internecie, które twierdzą że działa ono prawidłowo w ich przypadku. My zaś posłużymy się innym poleceniem, które działa u mnie zawsze i rozwiązuje problem:

UPDATE `users` SET `created` = NULL WHERE `created` < '0000-01-01 00:00:00'

W poleceniu tym zmienił się jedynie warunek, który chce wyszukać wszystkie rekordy z datą i czasem mniejszą od zdefiniowanego co spowoduje aktualizację błędnych rekordów 🙂