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?

  • Could you post the create table for this table?
    – Tom V
    Oct 25, 2016 at 8:02

1 Answer 1


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;
  • 1
    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, 2016 at 8:44
  • (Note: Before 5.6.5, such defaults would not be allowed.)
    – Rick James
    Oct 25, 2016 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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