Default DateTime or TimeStamp issue in mySql after upgrading to >= 5.6

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.
Problem :

If you have created a table in mySql 5.5 like

 

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 ****”.

Solution:

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

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

 

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

 

Please follow and like us:
20