需求是对一张近 20亿的超大型表做字段类型的修改，我们这里是需要将自增字段从 int => bigint, 目前自增 id 已接近 20亿所以不得不修改了。我们都知道一般在对表 DDL 的时候我们尽量会使用 onlineDDL 并且使用 inplace 算法来实现这一点以实现不阻塞表的插入。但是某些修改却无法使用 inplace. 而修改表字段就是无法使用 onlineDDL 的情况。
具体是否可以支持我们可以在这里查询到
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-primary-key-operations
所以我们需要借助一些别的工具。 还是老两样一个是 github 提供的 gh-ost, 还有就是最新版的 pt-osc(v3.3.1 released 2021-04-28)。
gh-ost
首先我想绕过之前使用触发器原理的 pt-osc 试试 gh-ost 来修改。因为不需要去处理触发器权限等问题(我在 gco cloudSQL 上改不方便)使用 binlog，所以打算使用它。
原理在这儿不再过多赘述。
https://www.modb.pro/db/22996
唯一需要注意的是打开操作库的 binlog 日志。使用如下命令开始操作
wget https://github.com/github/gh-ost/releases/download/v1.1.4/gh-ost_1.1.4_amd64.deb
sudo dpkg -i gh-ost_1.1.4_amd64.deb
gh-ost -allow-on-master -host xxxx \ -database xxx \ -table xxx \ -user xxx \ -verbose \ -ask-pass \ -gcp \ -alter "ALTER TABLE xxx MODIFY bigint(20) unsigned NOT NULL AUTO_INCREMENT"
这里注意因为我是在 gcp 上操作，工具需要我带上 gcp 的 flag。如果你使用了 aliyun，需要将这个 flag 修改为 aliyun 的 flag。
正常来讲就会开始拷贝表了，但是我的问题更加复杂，该表是有外键依赖的子表导致无法更改。因为在当前版本 gh-ost 是不支持带外键表字段修改的不管你是母表还是子表。
2022-06-10 07:54:04 INFO starting gh-ost 1.1.4 2022-06-10 07:54:04 INFO Migrating `xxx`.`xxx` 2022-06-10 07:54:05 INFO inspector connection validated on xxx:3306 2022-06-10 07:54:05 INFO User has REPLICATION CLIENT, REPLICATION SLAVE privileges, and has ALL privileges on `delmondo`.* 2022-06-10 07:54:05 INFO binary logs validated on xxx:3306 2022-06-10 07:54:05 INFO Restarting replication on xxx:3306 to make sure binlog settings apply to replication thread 2022-06-10 07:54:07 INFO Inspector initiated on xxx:3306, version 5.7.37-google-log 2022-06-10 07:54:07 INFO Table found. Engine=InnoDB 2022-06-10 07:54:07 ERROR Found 1 child-side foreign keys on `delmondo`.`xxx`. Child-side foreign keys are not supported. Bailing out 2022-06-10 07:54:07 INFO Tearing down inspector 2022-06-10 07:54:07 FATAL 2022-06-10 07:54:07 ERROR Found 1 child-side foreign keys on `xxx`.`xxx `. Child-side foreign keys are not supported. Bailing out
所以我们只能尝试另外一个工具 pt-osc。
pt-osc
pt-osc 其实也是有限度的支持了带外键表的 DDL。这里我觉得需要介绍一下 pt-osc 修改有外键的表他是怎么处理的。为什么他可以支持，支持到什么地步我们是需要比较清楚了解了才敢下手去改。
首先不带外键修改表的操作流程是
1. Create a similar table _T1_new
2. Modify the column c1 to BIGINT in the table _T1_new
3. Define triggers on table T1 so that changes to data on the original table will be applied to _T1_new as well.
4. Copy the data from table T1 to _T1_new.
5. Swap the tables
6. Drop triggers.
带上外键后 pt-osc 提供两个操作方法
1. alter-foreign-keys-method=drop_swap 关闭外键检查直接 rename 新表 to 老表
2. alter-foreign-keys-method=rebuild_constraints 完整重键所有子表约束
第一种操作的流程是
1. Disable foreign key checks for the session (FOREIGN_KEY_CHECKS=0)
2. Drop the table T1_old
3. Rename the new table _T1_new –> T1
可以看到，这种方法不能再 reanme 的时候出问题，如果 rename 发生什么意外。那么子表上所有约束就都丢失了，因为老的跟他们相关的约束表已经被删除了，这个方法不是非常稳健。
第二种操作流程是
1. Rename T1 –> T1_old
2. Rename _T1_new –> T1
3. ALTER on child table to adjust the foreign key so that it points to T1 rather T1_old.
ALTER TABLE child_table DROP FOREIGN KEY `fk_name`, ADD CONSTRAINT `_fk_name` FOREIGN KEY (`child_table_column`) REFERENCES _T1_new (`parent_table_column`)
ALTER TABLE child_table DROP FOREIGN KEY `fk_name`, ADD CONSTRAINT `_fk_name` FOREIGN KEY (`child_table_column`) REFERENCES _T1_new (`parent_table_column`)
3. Drop the table T1_old
4. Drop triggers from the new T1 table.
可以看到这种可以看到这种方法是拷贝完数据互换数据之后再来重建子表上的约束。让他们从老的表一个一个指向新的表。
看上去很优雅但是这种方法有他自己的问题。比如如果需要重建索引的子表很大，改动母表上的字段将会引起一系列连锁反应。
在我们不设置 FOREIGN_KEY_CHECKS=0 的情况下，MySQL OnlineDDL 和 pt-osc 都不支持 inplace 重建索引。copy 算法将会长时间阻止我们写入数据。
并且很遗憾，目前 CloudSQL 是不支持 FOREIGN_KEY_CHECKS=0 的设置的。所以我们必须要权限表修改的代价。如果我们修改一个
带外键的母表，母表并不大的情况下，我们需要考虑是否直接再母表上直接修改。例如这次我发现子表里面需要重建索引的表竟高达8亿条数据，
重建子表的代价超过了直接修改母表。。
最理想的情况还是数据库允许我们修改 FOREIGN_KEY_CHECKS=0 参数。这样我们在子表巨大的情况下可以考虑方法1. 考虑方法二也可以手动 inplace 重建索引。
pt-online-schema-change --host xxx \ --alter="CHANGE COLUMN _id _id bigint(20) unsigned NOT NULL AUTO_INCREMENT" D=xxx,t=xxx \ --alter-foreign-keys-method rebuild_constraints \ --user delmondo \ --ask-pass \ --critical-load Threads_running=2000 \ --print \ --execute
实际操作的时候需要注意的一些问题
因为我使用的 CloudSQL 我还有一些额外的事情需要做。如果用 gh-ost 我需要打开 binlog，如果需要使用 pt-osc 我需要打开允许触发器的权限。
将 log_bin_trust_function_creators=on, 这是因为我们无法获取 CloudSQL 的 super privileges 权限，我们需要允许其他用户能够创建触发器。
在执行 pt-osc 还需要注意挑选一些有用的参数设置。最新版本不仅支持修改过程中多维度实时检测数据库负载暂停拷贝。还支持检测 slave 的 lag 情况。如果 lag 太大可以暂停等待，这些都可以配置。具体可以查询这里
https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html#cmdoption-pt-online-schema-change-max-load
实际操作的时候遇到的问题
我在对超大型表修改的时候实际操作的时候还遇到一些问题在这里分享一下如何处理
Q: 2022-06-10T06:37:06 Error copying rows from `delmondo`.`FacebookPostHistories` to `delmondo`.`_FacebookPostHistories_new`: Threads_running=53 exceeds its critical thresho ld 50
A: 修改的时候修改到一半退出了报出这个问题。这就是参数 critical-load 默认值太小(当然其实 50已经不小了)但是我们的数据库单机比较大比较繁忙。
所以要避免出现这个报错导致退出回滚需要把这个值设置得足够大，比如我就设置的 2000.
Q: 由于改动的表过于大，又没有进度显示我该去哪里知道表的修改进度？
A: 如果是重建索引的过程中，我们可以在表 information_schema.INNODB_TRX 中找到正在重建表的语句。并且字段 trx_rows_modified 可以让你知道目前的进度。一条记录对应这里的一条。显示数值和表大小有关系。
Q: 我正常使用 OnlineDDL 也想知道字段修改进度我应该去哪儿看？
A: 可以在 performance_schema.events_stages_current 中找到对应的修改记录。可以比较 WORK_COMPLETED 和 WORK_ESTIMATED 值来评估目前的改表进度。
Q: 修改的时候偶尔会出现 Lock wait timeout exceeded; try restarting transaction 是什么问题？
A: 这个我觉得可以好好说一说这个问题。这个报错通常是因为后提交的事务等待前面处理的事务释放锁，但是在等待的时候超过了mysql的锁等待时间。日常中 MySQL 影响我们日常有3个超时时间，名字非常相似但是表达的意思完全不同顺带提一下。
1. innodb_lock_wait_timeout: 行锁等待超时实践，默认 50s。一般事务等锁超时报 Lock wait timeout exceeded; try restarting transaction 就是死锁或者别的长事物阻塞了表超过这个时间引起的。
2. lock_wait_timeout: 等待 ddl 的 metalock 超时时间。这个时间默认值非常长，默认改表其他操作是要等的，但是我们可以修改这个值变小。那么等 metalock 的 thread 就会超时释放了。
3. wait_timeout: 如果连接长时间空闲超过了这个时间就会释放了，默认是 8小时。著名 python peewee mysql gone away 问题由这个参数引起。因为在以前的版本中默认不会重试连接。
如果在改表的时候遇到这个问题，重试一下可能就会好。
Reference:
https://github.com/github/gh-ost/blob/master/doc/requirements-and-limitations.md Requirements and limitations
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-primary-key-operations innodb-online-ddl-operations
https://www.modb.pro/db/22996 MYSQL 修改表结构 gh-ost 到底强哪里 作者自己来talk
https://www.percona.com/blog/2019/06/07/how-pt-online-schema-change-handles-foreign-keys/ how-pt-online-schema-change-handles-foreign-keys
https://medium.com/nerd-for-tech/online-schema-migration-with-mysql-on-gcp-cloud-sql-70c02195e2d2 Online Schema Migration with MySQL on GCP (Cloud SQL)
https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/ Online DDL: why FOREIGN KEYs are not supported
https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html#cmdoption-pt-online-schema-change-max-load pt-osc configuration intruduce
https://cloud.tencent.com/developer/article/1670819 如何评估 alter 语句的进度