3

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'

Community
  • 1
  • 1
112233
  • 2,406
  • 3
  • 38
  • 88

1 Answers1

1

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.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Does not help is not an exact error description. 1) Did you successfully change the sql mode at all? 2) What exactly did you try to do after changing the sql mode? 3) What was the exact error message you received. – Shadow Jun 16 '16 at 09:31
  • I changed the strict mode to empty string mysql ini..and it works – 112233 Jun 16 '16 at 10:11