MySQL8.0.16使用pt-online-schema-change在线添加索引报错处理

在线MySQL8.0结构变更问题排查

软件 版本
mysql-server 8.0.16
pt-tools 3.2.0

报错明细

[root@node2 install]# pt-online-schema-change --port=3306 --host=localhost --user=root --password=xxx --alter="add index TMS_LEG_IDS (ID,CHANGE_ORG_ID,FROM_RECEIVER_ID,TO_RECEIVER_ID,TO_REGIONZONE_ID)" D=wlyotwb,t=tms_leg_test --no-version-check --execute --charset=utf8
No slaves found.  See --recursion-method if host node2 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
There is an error in MySQL that makes the server to die when trying to rename a table with FKs. See https://bugs.mysql.com/bug.php?id=96145
Since pt-online-schema change needs to rename the old <-> new tables as the final step, and the requested table has FKs, it cannot be executed under the current MySQL version

https://jira.percona.com/browse/PT-1782

解决方法

  • pt-tools软件版本使用 3.0.13

或者

  • mysql-server版本升级到 8.0.20

测试环境

MySQL测试数据

alter user root@'localhost' identified WITH mysql_native_password by 'Zyadmin123';
flush privileges;

create database booboo;
create table booboo.t1 (id int primary key);
insert into booboo.t1 values (1);

pt-online-ddl

yum localinstall -y percona-toolkit-3.2.0-1.el7.x86_64.rpm
pt-online-schema-change --port=3306 --host=localhost --user=root --password=Zyadmin123 --alter="add column name varchar(22)" D=booboo,t=t1 --no-version-check --execute --charset=utf8

执行结果

[root@node2 install]# pt-online-schema-change --port=3306 --host=localhost --user=root --password=Zyadmin123 --alter="add column name varchar(22)" D=booboo,t=t1 --no-version-check --execute --charset=utf8
No slaves found.  See --recursion-method if host node2 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `booboo`.`t1`...
Creating new table...
Created new table booboo._t1_new OK.
Altering new table...
Altered `booboo`.`_t1_new` OK.
2020-05-12T21:26:08 Creating triggers...
2020-05-12T21:26:08 Created triggers OK.
2020-05-12T21:26:08 Copying approximately 1 rows...
2020-05-12T21:26:08 Copied rows OK.
2020-05-12T21:26:08 Analyzing new table...
2020-05-12T21:26:09 Swapping tables...
2020-05-12T21:26:09 Swapped original and new tables OK.
2020-05-12T21:26:09 Dropping old table...
2020-05-12T21:26:09 Dropped old table `booboo`.`_t1_old` OK.
2020-05-12T21:26:09 Dropping triggers...
2020-05-12T21:26:09 Dropped triggers OK.
Successfully altered `booboo`.`t1`.

总结

不同的数据库版本对应的pt工具版本会有区别，具体可以访问percona-toolkit

v3.2.0 released 2019-04-20

Improvements:

  • PT-1773: Don’t make the foreign key check in pt-online-schema-change if not needed.
  • PT-1757: pt-table-checksum can now handle small tables as a single chunk.
  • PT-1813: MariaDB 10.4 is now supported.

Bug fixes:

  • PT-1782: pt-online-schema-change declined to handle tables because of foreign keys even when there were no foreign keys with some MariaDB 10.2 and MySQL 8 versions.
  • PT-1759: pt-stalk with --mysql-only option didn’t collect MySQL Status variables.
  • PT-1802: pt-online-schema-change didn’t handle self-referencing foreign keys properly which caused an unnecessarily high resource consumption.
  • PT-1766: pt-table-checksum DIFF_ROWS was not computed correctly.
  • PT-1760: pt-online-schema-change regression caused it to hang for a stopped replica when using replication channels on the slave.
  • PT-1707: A number of the Percona Toolkit tools failed to operate in the IPv6 environment if the host address specified as a parameter was not enclosed in square brackets.
  • PT-1502: pt-online-schema-change was not recognizing the slave with multi-source replication active.
  • PT-1824: pt-online-schema-change allowed the name of a constraint to exceed 64 characters when --alter-foreign-keys-method=rebuild_constraints was used. (Thank you, Iwo Panowicz.)
  • PT-1765: Documentation for DIFF_ROWS doesn’t exist.
  • PT-297: pt-online-schema-change could break replication.
  • PT-1768: Source code for src/go/pt-mongodb-query-digest/pt-mongodb-query-digest was missing in the official source tar ball.
  • PT-1576: pt-stalk with `--mysql-only option was not adding MySQL processlist information to the output file.
  • PT-1793: pt-query-digest was unable to handle the year 2020 because of wrong tcpdump parsing. (Thank you, Kei Tsuchiya.)

v3.0.13 released 2019-01-03

Improvements

  • PT-1340: pt-stalk now doesn’t call mysqladmin debug command by default to avoid flooding in the error log when not needed. CMD_MYSQLADMIN="mysqladmin debug" environment variable reverts pt-stalk to the previous way of operation.
  • PT-1637: A new --fail-on-stopped-replication option allows pt-table-checksum to detect failing slave nodes.

Fixed bugs

  • PT-1673: pt-show-grants was incompatible with MariaDB 10+ (thanks Tim Birkett)
  • PT-1638: pt-online-schema-change was erroneously taking MariaDB 10.x for MySQL 8.0 and rejecting to work with it to avoid the upstream bug #89441 scope.
  • PT-1616: pt-table-checksum failed to resume on large tables with binary strings containing invalid UTF-8 characters.
  • PT-1573: pt-query-digest didn’t work in case of log_timestamps = SYSTEM my.cnf option.
  • PT-1114: pt-table-checksum failed when the table was empty.
  • PT-157: Specifying a non-primary key index with the i part of the --source argument made pt-archiver to ignore the --primary-key-only option presence.