MySQL 5.7: Invalid default value for 'event_end_date'

Question

We are migrating our application from MySQL 5.5 to 5.7. As the default value 0000-00-00 is not allowed anymore for date fields in MySQL 5.7 in strict mode, I would like to change the default value to NULL.

The concerned fields are defined as follows:

+------------------+----------------------+------+-----+------------+----------------+
| Field            | Type                 | Null | Key | Default    | Extra          |
+------------------+----------------------+------+-----+------------+----------------+
| event_start_date | date                 | YES  |     | 0000-00-00 |                |
| event_end_date   | date                 | YES  |     | 0000-00-00 |                |
+------------------+----------------------+------+-----+------------+----------------+

When I try to execute the following ALTER query:

ALTER TABLE events CHANGE event_start_date event_start_date date DEFAULT NULL;    
ALTER TABLE events CHANGE event_end_date event_end_date date DEFAULT NULL;

I get the following error:

Invalid default value for 'event_end_date'

I know it would be possible to disable strict mode, but that is not the solution I am looking for. Strangely enough the same query worked for an other table.

Anyone has an idea what is going wrong?

Does this answer your question? [Error in MySQL when setting default value for DATE or DATETIME](https://stackoverflow.com/questions/36374335/error-in-mysql-when-setting-default-value-for-date-or-datetime) — Nico Haase, Apr 20 '21 at 08:25

score 3 · Accepted Answer · edited Jan 04 '19 at 16:22

The error happens already in your query on the first line. There you are trying to change the column event_start_date, the error message however is for column event_end_date. You need to change both columns with a single query in order to avoid this error:

ALTER TABLE events CHANGE event_start_date event_start_date date DEFAULT NULL, CHANGE event_end_date event_end_date date DEFAULT NULL;

It probably worked with your other table because you only had one column of type date.

score 0 · Answer 2 · answered Jan 04 '19 at 15:56

This is the new strict mode in MySQL 5.7. The default SQL_MODE in MySQL 5.7 is: 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

The best way is to change the schema as @cansik suggested. You can also remove NO_ZERO_IN_DATE,NO_ZERO_DATE from sql_mode (not recommended but can be temporary workaround)

set global sql_mode="... choose which modes you need ... "

MySQL 5.7: Invalid default value for 'event_end_date'

2 Answers2