If you are planning to upgrade your mySql version from 5.5 to 5.6 or 5.7 then you might face a big problem regarding default value of datetime or timestamp.
If you have created a table in mySql 5.5 like
CREATE TABLE `users` (
`user_id` int(4) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(250) NOT NULL,
`email` varchar(255) NOT NULL,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`updated_on` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
`created_on` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
PRIMARY KEY (`user_id`)
Here as you can see we have declared default value of updated_on column which is timestamp type as ‘0000-00-00 00:00:00’ and same for created_on which is datetime type.
In this case if you are not providing value of updated_on or created_on columns, then mySql automatically pass default value of datetime/timestamp which is ‘0000-00-00 00:00:00’. And it works fine.
Now the problem occurred when you update the version of mySql to 5.6 or 5.7. Actually in these versions mySql pass default value of datetime/timestamp with microsecond which looks like ‘0000-00-00 00:00:00.000000’.
Then you will get a mySql error like “invalid value passed for column ****”.
You can change default value of columns as ‘0000-00-00 00:00:00.000000’, but if you have lots of tables and columns in your database then it can be overwhelming for you.
There are 2 ways to solve this problem
- Changing sql_mode – By changing sql_mode to ‘STRICT_TRANS_TABLES’, you can solve this issue. But after doing so you may also not get some other mySql errors.
SET sql_mode = 'STRICT_TRANS_TABLES';
- By modifying my.cnf – You can also change modify mySql configuration file to solve this problem. My.cnf is configuration file for mySql and generally stored in /etc/mysql/ location if you are using linux based operating system. Just open this file and add this code at the end of the file
sql-mode = "NO_ENGINE_SUBSTITUTION"