MySQL Niepoprawna wartość daty i godziny: „0000-00-00 00:00:00”

Niedawno przejąłem stary projekt, który powstał 10 lat temu. Używa MySQL 5.1.

Między innymi muszę zmienić domyślny zestaw znaków z latin1 na utf8.

Jako przykład mam tabele takie jak ta:

  CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `first_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `last_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `username` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `email` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `pass` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `active` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'Y',
    `created` datetime NOT NULL,
    `last_login` datetime DEFAULT NULL,
    `author` varchar(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'N',
    `locked_at` datetime DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `ripple_token` varchar(36) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `ripple_token_expires` datetime DEFAULT '2014-10-31 08:03:55',
    `authentication_token` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `index_users_on_reset_password_token` (`reset_password_token`),
    UNIQUE KEY `index_users_on_confirmation_token` (`confirmation_token`),
    UNIQUE KEY `index_users_on_unlock_token` (`unlock_token`),
    KEY `users_active` (`active`),
    KEY `users_username` (`username`),
    KEY `index_users_on_email` (`email`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1677 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

Skonfigurowałem własnego Maca do pracy nad tym. Bez zastanawiania się nad tym uruchomiłem "brew install mysql", który zainstalował MySQL 5.7. Więc mam pewne konflikty wersji.

Pobrałem kopię tej bazy danych i zaimportowałem ją.

Jeśli spróbuję uruchomić takie zapytanie:

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL  

Otrzymuję ten błąd:

  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

Pomyślałem, że mogę to naprawić za pomocą:

  ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-01 00:00:00';
  Query OK, 0 rows affected (0.06 sec)
  Records: 0  Duplicates: 0  Warnings: 0

ale dostaję:

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL ;
  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

Czy muszę aktualizować każdą wartość?

Moja sugestia, jeśli jest tak, że tabela jest pusta lub niezbyt duża, to wyeksportowanie instrukcji create jako pliku .sql i przepisanie ich według własnego uznania. Zrób to również, jeśli masz jakieś istniejące dane, np. Wyeksportuj instrukcje wstawiania (polecam zrobić to w osobnym pliku jako instrukcje create). Na koniec usuń tabelę i wykonaj najpierw instrukcję create, a następnie inserts.

Możesz użyć do tego dowolnego mysqldumppolecenia zawartego w instalacji MySQL lub możesz również zainstalować MySQL Workbench, które jest bezpłatnym narzędziem graficznym, które zawiera również tę opcję w bardzo konfigurowalny sposób, bez konieczności szukania konkretnych opcji poleceń.

Nie mogłem tego zrobić:

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

(w MySQL 5.7.13).

Ciągle otrzymywałem Incorrect datetime value: '0000-00-00 00:00:00' błąd.

O dziwo, to działało: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. Nie mam pojęcia, dlaczego pierwszy zawodzi, a drugi działa ... może błąd MySQL?

W każdym razie to zapytanie UPDATE zadziałało:

UPDATE users SET created = NULL WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00'

Zmiana wartości domyślnej dla kolumny z ALTER TABLEinstrukcją, np

 ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-02'

... nie zmienia żadnych wartości, które są już zapisane. Wartość „domyślna” dotyczy wstawianych wierszy, dla których nie podano wartości dla kolumny.

Jeśli chodzi o przyczynę napotkania tego błędu, prawdopodobnie sql_modeustawienie dla Twojej sesji obejmuje NO_ZERO_DATE.

Źródła: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

Po wykonaniu „importu” instrukcje SQL, które wykonały operację INSERT w tej tabeli, zostały uruchomione w sesji, która zezwalała na zerowe daty.

Aby zobaczyć ustawienie sql_mode:

SHOW VARIABLES LIKE 'sql_mode' ;

-lub-

SELECT @@sql_mode ;

Jeśli chodzi o to, jak „naprawić” bieżący problem, aby błąd nie został wyrzucony po uruchomieniu ALTER TABLE instrukcji.

Kilka opcji:

1) zmień, sql_modeaby zezwolić na zerowe daty, usuwając NO_ZERO_DATEi NO_ZERO_IN_DATE. Zmianę można zastosować w pliku my.cnf, więc po restarcie serwera MySQL,sql_mode zmienna zostanie zainicjowana zgodnie z ustawieniem w my.cnf.

W przypadku tymczasowej zmiany możemy zmodyfikować ustawienie podczas jednej sesji, bez konieczności zmiany globalnej.

-- save current setting of sql_mode
SET @old_sql_mode := @@sql_mode ;

-- derive a new value by removing NO_ZERO_DATE and NO_ZERO_IN_DATE
SET @new_sql_mode := @old_sql_mode ;
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_DATE,'  ,','));
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_IN_DATE,',','));
SET @@sql_mode := @new_sql_mode ;

-- perform the operation that errors due to "zero dates"

-- when we are done with required operations, we can revert back
-- to the original sql_mode setting, from the value we saved
SET @@sql_mode := @old_sql_mode ;

2) Zmień createdkolumnę, aby zezwolić na wartości NULL, i zaktualizuj istniejące wiersze, aby zmienić daty zerowe na wartości null

3) zaktualizuj istniejące wiersze, aby zmienić daty zerowe na prawidłową datę

Nie musimy uruchamiać oddzielnych instrukcji, aby zaktualizować każdy wiersz. Możemy zaktualizować wszystkie wiersze za jednym zamachem (zakładając, że jest to tabela o rozsądnych rozmiarach. W przypadku większej tabeli, aby uniknąć ogromnego generowania wycofywania / cofania, możemy wykonać operację w rozsądnych fragmentach).

W pytaniu AUTO_INCREMENT wartość pokazana dla definicji tabeli zapewnia, że ​​liczba wierszy nie jest nadmierna.

Jeśli już zmieniliśmy createdkolumnę, aby zezwalać na NULLwartości, możemy zrobić coś takiego:

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

Lub możemy ustawić je na ważną datę, np. 2 stycznia 1970

UPDATE  `users` SET `created` = '1970-01-02' WHERE `created` = '0000-00-00 00:00:00'

(Zwróć uwagę, że wartość daty i godziny o północy 1 stycznia 1970 r. ( '1970-01-01 00:00:00') Jest „datą zerową”. Zostanie ona oceniona jako'0000-00-00 00:00:00'

Naprawiłem to, robiąc to przed zapytaniem

SET SQL_MODE='ALLOW_INVALID_DATES';

Zgodnie z podręcznikiem MySQL 5.7 :

Domyślny tryb SQL w MySQL 5.7 obejmuje następujące tryby: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER i NO_ENGINE_SUBSTITIT.

Ponieważ 0000-00-00 00:00:00nie jest to prawidłowa DATETIMEwartość, twoja baza danych jest uszkodzona. Dlatego MySQL 5.7 - który jest NO_ZERO_DATEdomyślnie włączony w tryb - wyświetla błąd podczas próby wykonania operacji zapisu.

Możesz naprawić swoją tabelę, aktualizując wszystkie nieprawidłowe wartości na inne prawidłowe, na przykład NULL:

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

Ponadto, aby uniknąć tego problemu, radzę zawsze ustawiać bieżący czas jako wartość domyślną dla createdpól-like, aby były one automatycznie wypełniane INSERT. Po prostu zrób:

ALTER TABLE users
ALTER created SET DEFAULT CURRENT_TIMESTAMP

SET sql_mode = 'NO_ZERO_DATE';
UPDATE `news` SET `d_stop`='2038-01-01 00:00:00' WHERE `d_stop`='0000-00-00 00:00:00'

Oto moje rozwiązanie PhpMyAdmin / Fedora 29 / MySQL 8.0 (na przykład):

set sql_mode='SOMETHING'; nie działa , wywołanie polecenia powiodło się, ale nic się nie zmieniło.

set GLOBAL sql_mode='SOMETHING'; zmiana konfiguracji globalnej trwała zmiana.

set SESSION sql_mode='SOMETHING'; zmiana konfiguracji sesji Zmienna SESSION dotyczy tylko bieżącego klienta.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

Więc robię to:

• Pobierz SQL_MODE: SHOW VARIABLES LIKE 'sql_mode';
• Wynik: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
• Usuń wynik: NO_ZERO_IN_DATE,NO_ZERO_DATE
• Ustaw nową konfigurację: set GLOBAL SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

W ten sam sposób możesz usunąć lub dodać inny tryb.

Jest to pomocne przy zmianie globalnej na używanie i testowanie frameworków lub sql_mode muszą być określone w każdym pliku lub grupie zapytań.

Na podstawie pytania zadanego tutaj: how-can-i-disable-mysql -rict-mode

Przykład: zainstaluj najnowszą zawartość Joomla 4.0-alpha.

Edycja: W PhpMyadmin, jeśli masz kontrolę nad serwerem, możesz zmienić sql_mode(i wszystkie inne parametry) bezpośrednio wPlus > Variables > sql_mode

Możesz zmienić typ tworzonego pola z datetimena varchar(255), następnie możesz ustawić (zaktualizować) wszystkie rekordy, które mają wartość "0000-00-00 00:00:00"na NULL.

Teraz możesz wykonywać zapytania bez błędów. Po zakończeniu można zmienić typ pola utworzonego na datetime.

Ten błąd też mam po aktualizacji MySQL z 5.6 do 5.7

Doszedłem do wniosku, że najlepszym rozwiązaniem dla mnie jest połączenie niektórych rozwiązań tutaj i zrobienie czegoś, co działa przy minimalnym wkładzie.

Używam MyPHPAdmin ze względu na prostotę wysyłania zapytań przez interfejs, ponieważ wtedy mogę łatwo sprawdzić strukturę i wszystko to w prosty sposób. Możesz użyć ssh bezpośrednio lub innego interfejsu. Metoda i tak powinna być podobna lub taka sama.

...

1.

Najpierw sprawdź rzeczywisty błąd podczas próby naprawy db:

joomla.jos_menu Uwaga: Kolumny TIME / TIMESTAMP / DATETIME w starym formacie zostały zaktualizowane do nowego formatu.

Ostrzeżenie: niepoprawna wartość daty i godziny: „0000-00-00 00:00:00” dla kolumny „check_out_time” w wierszu 1

Błąd: nieprawidłowa wartość domyślna dla „check_out_time”

status: operacja nie powiodła się

To mówi mi, że kolumna check_out_time w tabeli jos_menu musi mieć naprawione wszystkie złe daty, a także zmienioną „domyślną”.

...

2.

Uruchamiam zapytanie SQL na podstawie informacji w komunikacie o błędzie:

UPDATE jos_menu SET checked_out_time = '1970-01-01 08:00:00' WHERE checked_out_time = 0

Jeśli pojawi się błąd, możesz zamiast tego użyć poniższego zapytania, które wydaje się zawsze działać:

UPDATE jos_menu SET checked_out_time = '1970-01-01 08:00:00' WHERE CAST(checked_out_time AS CHAR(20)) = '0000-00-00 00:00:00'

...

3.

Następnie, gdy to się skończy, uruchamiam drugie zapytanie SQL:

ALTER TABLE `jos_menu` CHANGE `checked_out_time` `checked_out_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP;

Lub w przypadku, gdy jest to data, która musi być NULL

ALTER TABLE `jos_menu` CHANGE `checked_out_time` `checked_out_time` DATETIME NULL DEFAULT NULL;

...

Jeśli teraz uruchomię bazę danych napraw, otrzymam:

joomla.jos_menu OK

...

Działa dobrze :)

Czek

SELECT @@sql_mode;

jeśli zobaczysz tam dane „ZERO_DATE”, spróbuj

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_DATE',''));   
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_IN_DATE',''));   

Wyloguj się i zaloguj ponownie do swojego klienta (to dziwne) i spróbuj ponownie

Spraw, aby tryb sql nie był rygorystyczny

jeśli używasz laravel, przejdź do config-> database, przejdź do ustawień mysql i ustaw tryb ścisły na false

Miałem podobny problem, ale w moim przypadku jakaś linia miała wartość NULL.

więc najpierw aktualizuję tabelę:

update `my_table`set modified = '1000-01-01 00:00:00' WHERE modified is null

problem rozwiązany, przynajmniej w moim przypadku.

Ja też dostałem

SQLSTATE [22007]: Nieprawidłowy format daty i godziny: 1292 Niepoprawna wartość daty i godziny: „0000-00-00 00:00:00” dla kolumny

informacje o błędzie

Napraw to, zmieniając 0000-00-00 00:00:00 na 1970-01-01 08:00:00

1970-01-01 08:00:00 unix timestamp to 0

Rozwiązanie znalazłem na https://support.plesk.com/hc/en-us/articles/115000666509-How-to-change-the-SQL-mode-in-MySQL . Miałem to:

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

Zwróć uwagę NO_ZERO_IN_DATE,NO_ZERO_DATEna powyższe wyniki. Usunąłem to, robiąc to:

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Wtedy miałem to:

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                        |
+---------------+--------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

Po wykonaniu tej czynności mogłem z ALTER TABLEpowodzeniem używać i modyfikować moje tabele.

Oto, co zrobiłem, aby rozwiązać mój problem. Testowałem w lokalnym MySQL 5.7 ubuntu 18.04.

set global sql_mode="NO_ENGINE_SUBSTITUTION";

Przed uruchomieniem tego zapytania globalnie dodałem plik cnf w katalogu /etc/mysql/conf.d . Nazwa pliku cnf to mysql.cnf i kody

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Następnie ponownie uruchamiam mysql

sudo service mysql restart

Mam nadzieję, że to może komuś pomóc.

Jest to niesamowicie brzydkie, ale szybko rozwiązało problem. Twoja tabela wymaga unikalnego klucza, którego użyjesz do naprawy skażonych kolumn. W tym przykładzie klucz podstawowy nosi nazwę „id”, a uszkodzona kolumna z sygnaturą czasową nosi nazwę „BadColumn”.

1. Wybierz identyfikatory skażonych kolumn.

   select id from table where BadColumn='0000-00-00 00:00:00'

2. Zbierz identyfikatory w ciąg rozdzielany przecinkami. Przykład: 1, 22, 33. Użyłem do tego zewnętrznego opakowania (skryptu Perla), aby szybko je wszystkie wypluć.

3. Skorzystaj z listy identyfikatorów, aby zaktualizować stare kolumny o prawidłową datę (od 1971 do 2038).

   update table set BadColumn='2000-01-01 00:00:00' where id in (1, 22, 33)

Moje rozwiązanie

SET sql_mode='';
UPDATE tnx_k2_items
SET created_by = 790
, modified = '0000-00-00 00:00:00'
, modified_by = 0

Zamiast

UPDATE your_table SET your_column = new_valid_value where your_column = '0000-00-00 00:00:00';

Posługiwać się

UPDATE your_table SET your_column = new_valid_value where your_column = 0;

Jeśli wprowadzasz dane ręcznie, możesz rozważyć usunięcie wartości i zer z TIMESTAMP (6) .000000, tak aby stał się TIMESTAMP. U mnie to działało dobrze.