CODEWITHSUNDEEP
mysqlmysql-5.7
Rampagre3e3121312324
Rampagre3e3121312324

Reputation: 23

Incorrect datetime value mysql57

After updating my mysql version from 5.6 to 5.7 I get an error : 

Incorect datetime value: 0000-00-00 00:00:00 for column 'create_time' at row 1

And this is the structure for create_time

`create_time` DATETIME DEFAULT '0000-00-00 00:00:00'

Upvotes: 0

Views: 395

Answers (1)

miken32
miken32

Reputation: 42715

Ensure that you are running a current MySQL 5.7 version newer than version 5.7.7, or disable strict mode. Versions 5.7.4 to 5.7.7 incorporated NO_ZERO_DATE SQL mode into strict mode.

As of MySQL 5.7.4, the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes are deprecated. From MySQL 5.7.4 though 5.7.7, these modes do nothing when named explicitly. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES). In other words, strict mode means the same thing in those versions as the pre-5.7.4 meaning of strict mode plus ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE.

The MySQL 5.7.4 change to make strict mode more strict by including ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE caused some problems. For example, in MySQL 5.6 with strict mode but not NO_ZERO_DATE enabled, TIMESTAMP columns can be defined with DEFAULT '0000-00-00 00:00:00'. In MySQL 5.7.4 with the same mode settings, strict mode includes the effect of NO_ZERO_DATE and TIMESTAMP columns cannot be defined with DEFAULT '0000-00-00 00:00:00'. This causes replication of CREATE TABLE statements from 5.6 to 5.7.4 to fail if they contain such TIMESTAMP columns.

Upvotes: 1

Related Questions