0

I'm new to cloudsql for mysql. I have a table with trigger. I need to do some ddl changes on that table. However, I can't take any downtime.

What I have tried so far:

I tried with pt-online-schema-change. Because, it work for our local databases. However, in case of managed cloudsql - it wasn't working. Opened an issue with Percona(PT-1964).

Next, I checked for gh-ost. However, as per their documentation(https://github.com/github/gh-ost/blob/master/doc/requirements-and-limitations.md) -it doesn't support any table with trigger.

Making changes with shared lock option isn't available for me - because we have a replica too.

Does anyone knows any other method/suggeation to make ddl changes online for cloudsql for mysql.

Thanks in advance!

2
  • What is the change? There could be a workaround.
    – Rick James
    May 22 at 22:35
  • Thanks @RickJames for response. There are two changes - index addition and column alteration(adding enum value) May 23 at 4:51
0
ALTER TABLE t MODIFY COLUMN c ENUM(...) ...

If that is only adding a new option on the end and not going past 255 options, that should be a "schema-only" change that happens instantly. (This was in 5.5, so has been around for 12 years.)

Any other change to the ENUM is likely to require a "copy" of the table (slow).

ALTER TABLE t ADD INDEX (...)

has been efficient (not instant, but did not require copying the table over) since 5.6 (2013).

For other column alterations -- It depends.

That probably does not apply to ADD UNIQUE, and certainly does not apply to any changes to the PRIMARY KEY.

So, based on that info, I would expect Cloud-SQL with 5.7 to handle things "fast". (Sorry, but I have no direct knowledge of that port of MySQL.) I suggest you take it up with the vendor.

3
  • Thanks for the solution @Rick . What I understood from your answer - that I can run enum modification without any of these kind of tool(since it's not going to lock the table) ..my table size is 150GB May 23 at 6:51
  • 1
    @ShiwanginiShishulkar - Correct; just do the ALTER. Read the 5.7 manual; it spells things out. ALTER has changed a lot, and frequently, since 5.6; I can't keep all the details in my head.
    – Rick James
    May 23 at 6:54
  • Thanks @Rick - I was able to add enum value without any locking directly. May 24 at 16:24

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.