Mysql – Online schema changes for cloudsql DB

ddlgoogle-cloud-sqlMySQLmysql-5.7online-operations

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!

Best Answer

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.

Related Solutions

MySQL – LOCK=NONE Error Adding NOT NULL Constraint

This seems to be a bug that was introduced in MySQL 5.6.10 and has since been fixed (although there is no bug report on http://bugs.mysql.com that I can find). The behaviour described in the question does not occur in the latest version of MySQL.

Observed behaviour in MySQL 5.7.5-m15:

mysql> CREATE TABLE `Test` (
    ->   `id` int(10) auto_increment PRIMARY KEY,
    ->   `stuff` varchar(100) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.38 sec)

mysql> ALTER TABLE Test MODIFY stuff varchar(100) NOT NULL, LOCK=none;
Query OK, 0 rows affected (0.97 sec)
Records: 0  Duplicates: 0  Warnings: 0

Mysql – pt-online-schema-change MySQL Error: This version of MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’

pt-online-schema-change requires setting triggers on a table. MySQL (prior to version 5.7) only allows one trigger per event (before insert, after insert etc.) This error is informing you know that a trigger is already defined, and pt-online-schema-change is prevented from running.

tl;dr: You need to remove your triggers so the tool can run. With MySQL 5.7+ this will not be required.

Best Answer

Related Solutions

MySQL – LOCK=NONE Error Adding NOT NULL Constraint

Mysql – pt-online-schema-change MySQL Error: This version of MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’

Related Question