Cannot alter MYSQL Table (stuck in a invalid default value loop)

Question

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
– Tom V

2016-10-25 08:02:30 +00:00
Commented Oct 25, 2016 at 8:02 — Tom V
– Tom V, Commented Oct 25, 2016 at 8:02

Philᵀᴹ · Accepted Answer · 2016-10-25 07:38:04Z

14

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;

answered Oct 25, 2016 at 7:38

Philᵀᴹ

32k10 gold badges86 silver badges108 bronze badges

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
– resting

2016-10-25 08:44:42 +00:00
Commented Oct 25, 2016 at 8:44
(Note: Before 5.6.5, such defaults would not be allowed.)

Rick James
– Rick James

2016-10-25 18:54:51 +00:00
Commented Oct 25, 2016 at 18:54

Add a comment |

Stack Exchange Network

Cannot alter MYSQL Table (stuck in a invalid default value loop)

1 Answer 1

Your Answer

Hot Network Questions

Cannot alter MYSQL Table (stuck in a invalid default value loop)

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions