CODEWITHSUNDEEP
mysql
maechler
maechler

Reputation: 1367

MySQL 5.7: Invalid default value for 'event_end_date'

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?

Upvotes: 3

Views: 724

Answers (2)

cansik
cansik

Reputation: 2004

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.

Upvotes: 3

Alexander Rubin
Alexander Rubin

Reputation: 421

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 ... "

Upvotes: 0

Related Questions