MySQL – How to Fix Invalid Default Value Loop When Altering Table

alter-tabledefault valueMySQL

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?

Best Answer

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;

Related Solutions

Sql-server – An alternative for sp_unbinddefault

As per BOL rather than KB, use ALTER TABLE

ALTER TABLE Mytable DROP <defaultname>;

The name of the default could be user supplied or system generated.
To find it and generate the SQL needed:

SELECT
    OBJECT_NAME(c.object_id), dc.name,
    'ALTER TABLE ' + QUOTENAME(OBJECT_NAME(c.object_id)) + ' DROP ' + QUOTENAME(dc.name);
FROM
    sys.columns c
    JOIN
    sys.default_constrainst dc ON c.columnid = dc.parent_column_id AND c.object_id = dc.parent_object_id
WHERE
    c.name = 'UW_ZeroDefault';

Mysql – alter table (thesql) adding column with default value of space (‘ ‘)

Either the rules have changed, or somebody missed something in compiler builds.

According to the MySQL Documentation on ENUM

For example, a column specified as ENUM('one', 'two', 'three') can have any of the values shown here. The index of each value is also shown.

Value Index

NULL NULL

'' 0

'one' 1

'two' 2

'three' 3

An enumeration can have a maximum of 65,535 elements.

Trailing spaces are automatically deleted from ENUM member values in the table definition when a table is created.

You are using MySQL 5.1.49. That build probably errors out on seeing a one-character string with a blank.

MySQL 5.5 probably removes trailing spaces leaving an empty string.

Best Answer

Related Solutions

Sql-server – An alternative for sp_unbinddefault

Mysql – alter table (thesql) adding column with default value of space (‘ ‘)

Related Question