Sql ошибка 1067 invalid default value for

При редактировании БД столкнулся с ошибкой:

#1067 — Некорректное значение по умолчанию для ‘post_date’
#1067 — Invalid default value

дефолтное значение стояло

0000-00-00 00:00:00

база выдает ошибку т.к. данная дата не валидна потому, что она не находится в допустимом диапазоне
1970-01-01 00:00:01 — 2038-01-19 03:14:07

Но даже при запросе смены дефолтного значения на NULL:

ALTER TABLE `wp_comments` CHANGE `comment_date` `comment_date` DATETIME NULL DEFAULT NULL;

база выдавала ошибку т.к. были другие столбцы в таблице с неверным значением.

Решение

Пришлось править конфиг mySQL, чтобы разрешить работу с некорректным значением по-умолчанию:

[mysqld]
sql-mode=NO_ENGINE_SUBSTITUTION

перезапустить БД, после этого поменять значения на NULL или валидные, затем вернуть конфиг обратно.

The ERROR 1067 (42000): Invalid default value for ‘created_at’ is a common error encountered in MySQL. This error occurs when the user tries to create a table with a default value for the column that is not acceptable to the database system. The default value for the column can either be a constant, a function or an expression, but if it is not in the correct format, MySQL will return this error. This error can be resolved by understanding the root cause and applying the appropriate solution.

Method 1: Correct the Default Value

To fix the ERROR 1067 (42000): Invalid default value for 'created_at' error in MySQL, you can correct the default value of the created_at field.

First, you need to check the data type of the created_at field. It should be set to TIMESTAMP or DATETIME.

Then, you can set the default value of the created_at field to CURRENT_TIMESTAMP using the DEFAULT keyword.

Here is an example SQL query to correct the default value of the created_at field:

ALTER TABLE `table_name` 
CHANGE COLUMN `created_at` `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

This query will change the created_at field to a TIMESTAMP data type, set it to NOT NULL, and set the default value to CURRENT_TIMESTAMP.

Note that this query assumes that the table_name and created_at field names are correct. You should replace them with the actual names used in your database.

Method 2: Disable Strict Mode

To fix the «Mysql: how to fix ERROR 1067 (42000): Invalid default value for ‘created_at’?» error with «Disable Strict Mode», follow these steps:

1. Open the my.cnf file using your favorite editor. For example:

   sudo nano /etc/mysql/my.cnf

2. Add the following line under the [mysqld] section:

   sql_mode=NO_ENGINE_SUBSTITUTION

3. Save and close the file.

4. Restart MySQL server.

   sudo service mysql restart

5. Connect to MySQL server and run the following command to disable strict mode:

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

6. Now, you can create a table with a default value for ‘created_at’ column without any error. For example:

   CREATE TABLE example (
       id INT(11) NOT NULL AUTO_INCREMENT,
       name VARCHAR(255) NOT NULL,
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (id)
   );

   This will create a table with three columns: id, name, and created_at. The created_at column will have a default value of the current timestamp.

7. Insert a row into the table without specifying a value for the created_at column:

   INSERT INTO example (name) VALUES ('John');

   This will insert a row into the table with the name ‘John’ and the current timestamp as the value for the created_at column.

That’s it! You have successfully fixed the «Mysql: how to fix ERROR 1067 (42000): Invalid default value for ‘created_at’?» error with «Disable Strict Mode».

Method 3: Update MySQL Version

To fix the «Invalid default value for ‘created_at'» error in MySQL, you can update the MySQL version. Here are the steps to follow:

1. Check the current version of MySQL by running the following command:

2. Download the latest version of MySQL from the official website.

3. Stop the MySQL server by running the following command:

4. Uninstall the current version of MySQL by running the following command:

   sudo apt-get remove mysql-server

5. Install the new version of MySQL by running the following command:

   sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb
   sudo apt-get update
   sudo apt-get install mysql-server

6. Start the MySQL server by running the following command:

7. Open the MySQL command line by running the following command:

8. Select the database that you want to fix by running the following command:

9. Alter the table that is causing the error by running the following command:

   ALTER TABLE your_table_name
   MODIFY COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

10. Exit the MySQL command line by running the following command:

That’s it! You have successfully fixed the «Invalid default value for ‘created_at'» error in MySQL by updating the MySQL version.