Tag Archives: MYSQL Error 1067

When mysql creates a table, set timestamp DEFAULT NULL error 1067-Invalid default value for’updated_at’

Problem background:

The structure of the exported database in the mysql server on the online linux server. Want to create a test version locally

After exporting and running on local mysql, it reports error 1067-Invalid default value for

 

The timestamp column needs to be used in the mysql database to store the creation time and update time of the data

For example, to create a background management menu table, only look at the two fields created_at and updated_at

DROP TABLE IF EXISTS `admin_menu`;
CREATE TABLE `admin_menu`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL DEFAULT 0,
  `order` int(11) NOT NULL DEFAULT 0,
  `title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `icon` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `uri` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permission` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` timestamp(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

An error was found after execution

1067-Invalid default value for’updated_at’. The default value of the updated_at field is invalid. The created_ad field type is the datetime date and time type. The default value is NULL and no error occurs.

 At first I thought it was the sql_mode mode problem, but when the sql_mode mode is strict or loose mode, an error will be reported.
The same mode of local and online servers will not work either
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

Next, check the comparison between the online server version and the local version,
Use select VERSION()
Online mysql version: 5.7.22-log
Local mysql version: 5.7.23-log
There is not much difference between the versions, so version problems are excluded. That can only find information.
Solution:
method 1
Since timestamp will report an error, it can be solved by directly using datetime to save the time.
Change the updated_at type timestamp to datetime, the process of creating a table can run normally
Method 2
Since it cannot be NULL, can I change it to a fixed value?
`updated_at` timestamp(0) DEFAULT '1970-01-01 08:00:00',
Set the time to the date corresponding to the unix timestamp of 0 (the default is 1970-01-01 00:00:00, and the Chinese time must be increased by 8).

 

Method 3

Search online, add configuration parameters in the mysql configuration file

[mysqld] Add under node

explicit_defaults_for_timestamp = ON

Restart the mysql database for trial configuration to take effect.

After that, the above data can be executed normally

 

The following is a specific explanation of method three, it is only for record, and it is not necessary to know

Search information online,
mysql5.6.6Previously, the timestamp time type had a default behavior.
TIMESTAMP columns that did not explicitly declare the NULL attribute defaulted to NOT NULL. (And other data types that do not show a declaration of NOT NULL allow NULL values.)
insert inserts a piece of data with a TIMESTAMP column value of NULL, it will automatically store the current timestamp into this timestamp column when it is stored.
In other words, the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes will be automatically assigned.
Each time the record is updated, the timestamp column will be updated to the time value corresponding to the current timestamp
Now after mysql5.6 version, the default behavior of the timestamp field has changed, and there are some more restrictions.
If the timestamp column is set to the default value of NULL,
Default NULL This will give error 1067-Invalid default value for
If you need to make the timestamp column a NULL value when creating a table, you need to set explicit_defaults_for_timestamp to ON
explicit_defaults_for_timestamp is OFF by default, and it can prevent the default behavior of timestamp when it is turned on.