CODEWITHSUNDEEP
mysqldatetimephpmyadmin
112233
112233

Reputation: 2466

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 DATETIME 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 registerDate and LastLoginDate columns. Whenever I click save on registerDate, it says LastLoginDate has invalid default value, and whenever I click save on LastLoginDate, it says registerDate 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'

Upvotes: 3

Views: 1534

Answers (1)

Shadow
Shadow

Reputation: 34232

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.

Upvotes: 1

Related Questions