MySQL Forums
Forum List  »  General

Online DDL and dropping "on update current_timestamp"
Posted by: Mike Rudolph
Date: April 30, 2014 12:04AM

I have a database table with multiple timestamp columns.

This database has over 100 million rows in it, so I'm looking to take advantage of the online DDL support if possible to make this change.

All I want to do is remove the "on update current_timestamp" from the column.

1) Using this syntax:
alter table MYTABLE modify MYCOLUMN timestamp default current_timestamp;

Attempt to copy the table and does not use the online DDL support (but would affect the change I want).

2) Using this syntax:
alter table MYTABLE alter MYCOLUMN drop default;

Will remove the default of "current_timestamp" but doesn't remove the "on update current_timestamp"

3) Using this syntax:
alter table MYTABLE alter MYCOLUMN set default 0;

Also will remove any defaults, but still keeps the "on update current_timestamp" intact.

4) Using this syntax:
alter table MYTABLE modify MYCOLUMN
timestamp default CURRENT_TIMESTAMP not null, ALGORITHM=INPLACE, LOCK=NONE;

Reports this error:
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Even though the column type is not actually changing (it's staying timestamp).



I'd like to think the change I'm interested in making doesn't affect the data at all and just requires a fast update to the .FRM and not hours of copying to a TMP table.

Any insight? Is this a limitation in the current online DDL support?

Options: ReplyQuote


Subject
Written By
Posted
Online DDL and dropping "on update current_timestamp"
April 30, 2014 12:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.