Keren Keren -5 years ago 192
MySQL Question

Unable to add new column due to invalid datetime default value

In my table, I have two columns along with others. These two columns are of

type, and their value is set to
0000-00-00 00:00:00
by the previous programmer. Now I just want to add columns with int datatype. However everytime I click save in phpMyAdmin, it says the previous two table are of invalid default value.

So I click on one of them to set the default to none namely
columns. Whenever I click save on
, it says
has invalid default value, and whenever I click save on
, it says
has invalid default value. Basically I'mm just unable to change the default value in these two columns, also unable to add any other column.

I did refer to these, but they somehow don't help to solve my issue:

MySQL default datetime through phpmyadmin

MySQL - Cannot insert NULL value in column, but I have a default value specified?

Invalid default value for 'dateAdded'

Answer Source

Probably a combination of no_zero_date and strict sql modes are enabled (depending on your exact MySQL version) preventing MySQL to use '0000-00-00' as a default value. I would disable the strict sql mode using SET SESSION sql_mode = '...'; command (but make note of the exact sql mode setting), then I would change the table not to use zero date as default, then you can restore the sql mode setting.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download