Alter Table fails because a row has incorrect data - Incorrect datetime value: '0000-00-00 00:00:00'

Question

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

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

Which returns the following error:

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

Created_at column structure is:

created_at datetime NOT NULL

Not sure why the data is invalid - could it be related to MySQL versions (from 5.5.43 into 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 reasonable workaround?

Answer 1

you should disable STRICT_TRANS_TABLES mode

SET sql_mode = '';

or go to /etc/mysql/my.cnf & comment out STRICT_TRANS_TABLES

useful note SQL STRICT MODE