2

Some how I have 2 columns with incorrect default values. Not sure how it got created in the first place as I imported the db from another source.

Problem now is, I cannot make any changes to the table cause MYSQL will complain:

MySQL said: Invalid default value for 'campaign_start_date'

if I tried adding a column.

But when I try changing the DEFAULT VALUE to CURRENT_TIMESTAMP for campaign_start_date, it complains campaign_end_date has an invalid default value.

So I'm stuck in a loop that doesn't allow me to make any changes to the table.

Is there anyway to fix this?

7

Try changing both columns defaults at the same time:

ALTER TABLE yourtable 
    CHANGE campaign_start_date campaign_start_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHANGE campaign_end_date campaign_end_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
  • Yeah that worked. Thanks! I found another solution is to change the sql_mode. Just remove the NO_ZERO_DATE field in my.cnf, so MYSQL will stop complaining. – resting Oct 25 '16 at 8:44
  • (Note: Before 5.6.5, such defaults would not be allowed.) – Rick James Oct 25 '16 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.