MySQL Неверное значение даты и времени: '0000-00-00 00:00:00'

Недавно я взял старый проект, который был создан 10 лет назад. Он использует MySQL 5.1.

Кроме всего прочего, мне нужно изменить набор символов по умолчанию с latin1 на utf8.

В качестве примера у меня есть такие таблицы, как:

  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

Я создал свой собственный Mac для работы над этим. Недолго думая об этом, я запустил "brew install mysql", который установил MySQL 5.7. Поэтому у меня есть некоторые конфликты версий.

Я загрузил копию этой базы данных и импортировал ее.

Если я попытаюсь запустить такой запрос:

  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

Я подумал, что могу исправить это с помощью:

  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

но я получаю:

  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

Нужно ли обновлять каждое значение?

Ответ 1

Мое предложение, если это так, что таблица пуста или не очень большая, нужно экспортировать операторы create в виде .sql файла, переписывайте их по своему усмотрению. Также делайте то же самое, если у вас есть какие-либо существующие данные, т.е. Экспортные вставки (я рекомендую сделать это в отдельном файле в качестве операторов создания). Наконец, отбросьте таблицу и выполните первый оператор create, а затем вставьте.

Вы можете использовать для этой команды mysqldump, включенную в вашу установку MySQL, или вы также можете установить MySQL Workbench, который является бесплатным графическим инструментом, который также включает эту опцию очень настраиваемым образом, без необходимости искать определенную команду варианты.

Ответ 2

Я не мог этого сделать:

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

(на MySQL 5.7.13).

Я продолжал получать ошибку Incorrect datetime value: '0000-00-00 00:00:00'.

Странно, это сработало: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. Я понятия не имею, почему первое не удается, и последнее работает... может быть, ошибка MySQL?

В любом случае этот запрос UPDATE работал:

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

Ответ 3

Изменение значения по умолчанию для столбца с помощью оператора ALTER TABLE, например

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

... не изменяет значения, которые уже сохранены. Значение "по умолчанию" применяется к вставленным строкам и для которых значение не указывается для столбца.

В связи с тем, что вы сталкиваетесь с ошибкой, вероятно, что параметр sql_mode для вашего сеанса включает NO_ZERO_DATE.

Ссылка: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

Когда вы выполнили "импорт", операторы SQL, которые использовали INSERT в этой таблице, выполнялись в сеансе, который допускал нулевые даты.

Чтобы увидеть настройку sql_mode:

SHOW VARIABLES LIKE 'sql_mode' ;

-или -

SELECT @@sql_mode ;

Насколько "исправить" текущую проблему, чтобы при запуске оператора ALTER TABLE ошибка не была выбрана.

Несколько параметров:

1) измените sql_mode, чтобы разрешить нулевые даты, удалив NO_ZERO_DATE и NO_ZERO_IN_DATE. Это изменение может быть применено в файле my.cnf, поэтому после перезагрузки MySQL Server переменная sql_mode будет инициализирована в параметре my.cnf.

Для временного изменения мы можем изменить настройку за один сеанс, не требуя глобального изменения.

-- 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) измените столбец created, чтобы разрешить значения NULL, и обновите существующие строки, чтобы изменить нулевые даты на нулевые значения

3) обновить существующие строки, чтобы изменить нулевые даты на допустимую дату.

Нам не нужно запускать отдельные инструкции для обновления каждой строки. Мы можем обновить все строки одним махом (при условии, что это таблица с разумным размером. Для большей таблицы, чтобы избежать гигантского откат/отмена генерации, мы можем выполнить операцию в кусках разумного размера.)

В вопросе значение AUTO_INCREMENT, показанное для определения таблицы, гарантирует, что количество строк не является чрезмерным.

Если мы уже изменили столбец created, чтобы разрешить значения NULL, мы можем сделать что-то вроде этого:

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

Или мы можем установить их на действительную дату, например. 2 января 1970 г.

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

(Обратите внимание, что значение даты и времени полуночи 1 января 1970 г. ('1970-01-01 00:00:00') является "нулевой датой". Это будет оценено как '0000-00-00 00:00:00'

Ответ 4

Согласно Справочное руководство MySQL 5.7:

Режим SQL по умолчанию в MySQL 5.7 включает следующие режимы: 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.

Так как 0000-00-00 00:00:00 не является допустимым значением DATETIME, ваша база данных не работает. Вот почему MySQL 5.7 - который поставляется с включенным по умолчанию режимом NO_ZERO_DATE - выдает ошибку при попытке выполнить операцию записи.

Вы можете исправить свою таблицу, обновляя все недопустимые значения для любого другого действительного, например NULL:

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

Кроме того, чтобы избежать этой проблемы, я рекомендую вам всегда устанавливать текущее время как значение по умолчанию для ваших полей created, так что они автоматически заполняются на INSERT. Просто выполните:

ALTER TABLE users
ALTER created SET DEFAULT CURRENT_TIMESTAMP

Ответ 5

Я получил это исправить, выполнив это до запроса

SET SQL_MODE='ALLOW_INVALID_DATES';

Ответ 6

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'

Ответ 7

Вы можете изменить тип созданного поля с datetime на varchar(255), затем вы можете установить (обновить) все записи со значением "0000-00-00 00:00:00" на NULL.

Теперь вы можете делать ваши запросы без ошибок. После того, как вы закончите, вы можете изменить тип поля, созданного для datetime.

Ответ 8

Сделайте режим sql не строгим

при использовании laravel перейдите в базу данных config->, перейдите в настройки mysql и установите строгий режим в значение false

Ответ 9

Я также получил

SQLSTATE [22007]: неверный формат даты и времени: 1292 Неверное время datetime значение: '0000-00-00 00:00:00' для столбца

информация об ошибке

Исправьте это, изменив 0000-00-00 00:00:00 на 1970-01-01 08:00:00

1970-01-01 08:00:00 timestamp unix равен 0

Ответ 10

Вот какое у меня решение PhpMyAdmin/Fedora 29/MySQL 8.0 (например):

set sql_mode='SOMETHING'; не работает, вызов команды выполнен успешно, но ничего не изменилось.

set GLOBAL sql_mode='SOMETHING'; изменить глобальную конфигурацию постоянное изменение.

set SESSION sql_mode='SOMETHING'; Изменение конфигурации сеанса Переменная SESSION влияет только на текущего клиента.

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

Итак, я делаю это:

Получить SQL_MODE: SHOW VARIABLES LIKE 'sql_mode';
Результат: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Удалить по результату: NO_ZERO_IN_DATE,NO_ZERO_DATE
Установите новую конфигурацию: set GLOBAL SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

Вы можете удалить или добавить другой режим таким же образом.

Это полезно для изменения глобальных параметров для использования и тестирования сред, или в каждом файле или группе запросов должен быть указан sql_mode.

Адаптировано из вопроса, задаваемого здесь: how-can-i-disable-mysql-strict-mode

Пример: установить последний контент Joomla 4.0-alpha.

Ответ 11

У меня эта ошибка также после обновления MySQL с 5.6 до 5.7

Я понял, что лучшим решением для меня было объединить некоторые решения здесь и сделать что-то такое, что работало бы с минимальными затратами.

Я использую MyPHPAdmin для простоты отправки запросов через интерфейс, потому что тогда я могу легко проверить структуру и все такое. Вы можете использовать SSH напрямую или другой интерфейс. Метод должен быть похожим или одинаковым в любом случае.

...

Сначала проверьте фактическую ошибку при попытке восстановить базу данных:

joomla.jos_menu Примечание: столбцы TIME/TIMESTAMP/DATETIME старого формата были обновлены до нового формата.

Предупреждение: неверное значение даты и времени: "0000-00-00 00:00:00" для столбца "check_out_time" в строке 1

Ошибка: недопустимое значение по умолчанию для 'checked_out_time'

статус: операция не удалась

Это говорит мне о том, что столбцу check_out_time в таблице jos_menu необходимо исправить все неверные даты, а также изменить "по умолчанию".

...

Я запускаю SQL-запрос на основе информации в сообщении об ошибке:

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

Если вы получили ошибку, вы можете использовать приведенный ниже запрос, который, кажется, всегда работает:

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'

...

Затем, как только это будет сделано, я запускаю второй запрос SQL:

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

Или в случае, если это дата, которая должна быть NULL

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

...

Если я запускаю базу данных восстановления, я получаю:

joomla.jos_menu ОК

...

Работает просто отлично :)

Ответ 12

У меня была похожая проблема, но в моем случае в какой-то строке было значение NULL.

Итак, сначала я обновляю таблицу:

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

проблема решена, по крайней мере, в моем случае.

Ответ 13

Это то, что я сделал, чтобы решить мою проблему. Я тестировал в локальном MySQL 5.7 Ubuntu 18.04.

set global sql_mode="NO_ENGINE_SUBSTITUTION";

Перед глобальным запуском этого запроса я добавил файл cnf в каталог /etc/mysql/conf.d. Имя файла cnf: mysql.cnf и коды

[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

Затем я перезапустить MySQL

sudo service mysql restart

Надеюсь, это может кому-то помочь.

Ответ 14

Используйте IGNORE, поскольку он игнорирует все ошибки и предупреждения, заставляющие базу данных выполнять задачу

Ответ 15

Просто измените запрос с "0000-00-00 00:00:00" на одиночный 0