Error 1067 (42000): Invalid Default Value for 'Created_At'

ERROR 1067 (42000): Invalid default value for 'created_at'

The problem is because of sql_modes.
Please check your current sql_modes by command:

show variables like 'sql_mode' ; 

And remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" to make it work.
This is the default sql_mode in mysql new versions.

You can set sql_mode globally as root by command:

set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

ERROR: 1067 (42000): Invalid default value for 'created_at'

Looks like you are using some MySQL version less than 5.6.5. For MySQL version 5.5, if you want to set default value, you would have to use "TIMESTAMP" type, instead of "DATETIME" type. Hope TIMESTAMP type is not a problem for you.

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ERROR 1067 (42000): Invalid default value for 'end_time'

MySQL treats timestamp in a special way, that is a little hard to find in the documentation when you don't know what you are looking for:

In MySQL, the TIMESTAMP data type differs in nonstandard ways from other data types:

• TIMESTAMP columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types, if not explicitly declared as NOT NULL, permit NULL values.) Setting such a column to NULL sets it to the current timestamp.

• The first TIMESTAMP column in a table, if not declared with the NULL attribute or an explicit DEFAULT or ON UPDATE clause, is automatically assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

• TIMESTAMP columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

Those nonstandard behaviors remain the default for TIMESTAMP but as of MySQL 5.6.6 are deprecated and this warning appears at startup:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option 
(see documentation for more details).

That means your second timestamp not null column will get an implicit default value of '0000-00-00 00:00:00', which is not allowed in combination with the NO ZERO DATE and strict sql mode (which is by default enabled in MySQL 5.7) and results in your error.

To solve your problem, enable the option --explicit_defaults_for_timestamp. It treats the timestamp columns as you expected (and will be the default behaviour in some future MySQL release anyway), or allow them to be null.

Error 1067: Invalid default value for created_at

Colums with the CURRENT_TIMESTAMP attribute must have the data type TIMESTAMP, not DATETIME, in MySQL versions prior to 5.6.

So you need

`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP

or a later version of MySQL.

ERROR 1067 (42000): Invalid default value for 'date_time' in MySQL

You cannot use CURRENT_TIMESTAMP on update. Instead, change it to a TIMESTAMP.

ERROR 1067 (42000) Invalid default value '0000-00-00 00:00:00'

I guess you're using MySQL 5.7 (or higher). There the default value for SQL mode is among others NO_ZERO_IN_DATE,NO_ZERO_DATE.

You can check this with

SHOW GLOBAL VARIABLES LIKE 'sql_mode';
/* or...*/
SHOW SESSION VARIABLES LIKE 'sql_mode';

Either you remove above mentioned values from the sql mode, or you just make your column nullable and store NULL instead of 0000-00-00 00:00:00. I'd prefer the latter. It doesn't waste storage and is clear in its meaning.

---

Related Topics