ERROR 1067 (42000): Invalid default value for / ERROR 1292 (22007): Incorrect datetime value

Признаки

1. При импорте DDL в MySQL возникает следующая ошибка: "ERROR 1067 (42000): Invalid default value for 'column_name'".
2. При импорте данных в MySQL возникает следующая ошибка: "ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'column_name' at row 1".

Причины

Хотя по умолчанию нулевые даты в базе данных MySQL разрешены, это контролируется переменной sql_mode, которая может содержать или не содержать параметр NO_ZERO_DATE. Если параметр NO_ZERO_DATE добавлен, все значения TIMEASTAMP не допускают хранение нулевой даты. Проблема может возникнуть в двух случаях:

• При создании таблицы, когда нулевая дата, например '0000-00-00 00:00:00', указана в выражении по умолчанию для столбца TIMESTAMP. Например:

  mysql> select @@sql_mode;
  +-----------------------------------------------------------------------------+
  | @@sql_mode                                                                  |
  +-----------------------------------------------------------------------------+
  | STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
  +-----------------------------------------------------------------------------+
  1 row in set (0.00 sec)

  mysql> CREATE TABLE department
  -> (
  -> id INT,
  -> name VARCHAR(100),
  -> creation_dt TIMESTAMP NOT NULL default '0000-00-00 00:00:00'
  -> );
  ERROR 1067 (42000): Invalid default value for 'creation_dt'

• При загрузке данных, когда в столбец TIMESTAMP вставляется нулевая дата типа '0000-00-00 00:00:00' командой insert или load. Например:

  mysql> select @@sql_mode;
  +-----------------------------------------------------------------------------+
  | @@sql_mode                                                                  |
  +-----------------------------------------------------------------------------+
  | STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
  +-----------------------------------------------------------------------------+
  1 row in set (0.00 sec)

  mysql> CREATE TABLE department
  -> (
  -> id INT,
  -> name VARCHAR(100),
  -> creation_dt TIMESTAMP
  -> );
  Query OK, 0 rows affected (0.09 sec)

  mysql> INSERT INTO department VALUES (1, 'DBA', '0000-00-00 00:00:00');
  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'creation_dt' at row 1

Решения

Для решения этой проблемы вы можете рассмотреть возможность изменения переменной sql_mode в базе данных MySQL, исключив из неё параметр NO_ZERO_DATE. Чтобы проверить, содержится ли этот параметр в sql_mode, используйте следующий запрос:

 SELECT @@sql_mode

Если присутствует режим NO_ZERO_DATE, удалите его из списка режимов, выполнив команду set с указанием всех режимов, кроме NO_ZERO_DATE, как показано в примере ниже.

Если результат запроса 'SELECT @@sql_mode' выглядит следующим образом:

mysql> select @@sql_mode;
 +-----------------------------------------------------------------------------+
 | @@sql_mode                                                                  |
 +-----------------------------------------------------------------------------+
 | STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
 +-----------------------------------------------------------------------------+

скопируйте этот результат в команду SET sql_mode, исключив NO_ZERO_DATE:

 set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Теперь нулевые значения разрешены для столбцов TIMESTAMP в базе данных.