Clever Geek Handbook

Alter table error because the row has invalid data. Invalid datetime value: '0000-00-00 00:00:00'

I am updating the current user table with the following expression:

`ALTER TABLE  `users` ADD  `title` VARCHAR( 5 ) NULL DEFAULT NULL AFTER  `surname` ;`

Returns the following error:

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at' at row 1

Column structure

Created_at:

created_at datetime NOT NULL

Not sure why the data is not valid - could it be related to MySQL versions (5.5.43 to 5.1.73)?

The fix I have at the moment is to update the datetime value before the ALTER TABLE statement:

UPDATE `users` SET `created_at`='2014-01-01 00:00:00' WHERE `created_at`='0000-00-00 00:00:00';

Is this a smart decision?

+3


source to share


3 answers


MySQL docs say

The server requires the month and day values ​​to be valid, not just in the range 1 to 12 and 1 to 31, respectively.

also:



When strict mode is enabled, invalid dates will generate an error. To allow such dates, enable ALLOW_INVALID_DATES.

So Michael - sqlbot asked you the right question.

+3


source


Created column structure: created_at datetime NOT NULL



It would seem that "0000-00-00 00:00:00" is considered NULL in your new MySQL version, but not your old one, and you have specified that the DB should not be NULL ("NOT ZERO").

+2


source


you should disable STRICT_TRANS_TABLES mode

SET sql_mode = '';

or go to /etc/mysql/my.cnf

and comment outSTRICT_TRANS_TABLES

useful note SQL STRICT MODE

0


source






More articles:


All Articles