Pt-online-schema-change Missing "Create Table" Retry Option

Polyglot Projects Percona Toolkit
1

When running pt-osc on version 3.5.1 on MySQL 8.0.28, I usually encounter a metadata lock like this:

CREATE TABLE `test`.`_test_new` ( ...

And I get an error like this:

	(in cleanup) DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement "CREATE TABLE `test`.`_test_new` (

This is the pt-osc that I run:

pt-online-schema-change \
       --user=root \
       --password=XXX \
       --alter "..." \
       --alter-foreign-keys-method=auto \
       --max-load Threads_running=50 \
       --critical-load Threads_running=200 \
       --nocheck-unique-key-change \
       --nocheck-foreign-keys \
       --print \
       --progress=time,10 \
       --set-vars lock_wait_timeout=5 \
       --tries=create_triggers:200:1,drop_triggers:200:1,swap_tables:200:1,update_foreign_keys:200:1,analyze_table:200:1 \
       D=test,t=test,h=127.0.0.1 \
       --execute

Note this table has a foreign key.

In pt-online-schema-change — Percona Toolkit Documentation version 3.5.1, the --tries option only allows the following operations:

OPERATION            TRIES   WAIT
===================  =====   ====
create_triggers         10      1
drop_triggers           10      1
copy_rows               10   0.25
swap_tables             10      1
update_foreign_keys     10      1
analyze_table           10      1

I use the --tries option to get around the metadata lock but I noticed that it does not have an option to try if it fails to create the new table. Am I missing something?

If there is no option to retry the creation of the new table, what do you recommend so that pt-osc can just wait if the transaction that causes the metadata lock is done?

2

You can increase the lock_wait_timeout, but that might also affect other transactions.

3

So I am actually lowering this value so that I can use this in conjunction with --tries option. What I want is if the create table statement fails, just retry it…