Mysql – pt-online-schema-change error creating triggers

indexMySQL

I am trying to add an index with pt-online-schema-change. The dry-run succeeds, but I am stuck at creating triggers. Any help ?

pt-online-schema-change --alter "ADD INDEX domain_index (domain) USING BTREE" D=master,t=website,u=root --execute --ask-pass
Enter MySQL password: 
No slaves found.  See --recursion-method if host inoopa-storage 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 `master`.`website`...
Creating new table...
Created new table master._website_new OK.
Altering new table...
Altered `master`.`_website_new` OK.
2020-06-05T16:02:10 Creating triggers...
2020-06-05T16:12:19 Dropping triggers...
2020-06-05T16:12:19 Dropped triggers OK.
2020-06-05T16:12:19 Dropping new table...
2020-06-05T16:12:19 Dropped new table OK.
`master`.`website` was not altered.
Error creating triggers: 2020-06-05T16:12:19 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement "CREATE TRIGGER `pt_osc_master_website_del` AFTER DELETE ON `master`.`website` FOR EACH ROW DELETE IGNORE FROM `master`.`_website_new` WHERE `master`.`_website_new`.`id` <=> OLD.`id`"] at /usr/bin/pt-online-schema-change line 11074, <STDIN> line 1.

Best Answer

The error message shows a lock wait timeout.

This means you cannot lock the table.

There is always three triggers needed for pt-osc on the source table:

  • INSERT trigger
  • DELETE trigger
  • UPDATE trigger

The triggers are not create atomically. They are created one at a time.

If root@localhost has GRANT ALL PRIVILEGES on *.*, you should be able to create the trigger. It looks like the table must have very high read/write traffic.

You will have to clean up as follows:

Kill the pt-osc

Login to MySQL and run

USE master
DROP TABLE `_website_new`;
DROP TRIGGER pt_osc_master_website_ins;
DROP TRIGGER pt_osc_master_website_upd;
DROP TRIGGER pt_osc_master_website_del;

The, start pt-osc over again

Related Question