We encounter error: Exiting due to errors while restoring triggers: DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction when using pt-online-schema-change.
We are using pt-online-schema-change to add NEW column to production DB table, name mydb.table1 (just masked the real name) which contain 60 million rows and contain a self defined trigger, after an hour the tool failed and finally the new table cannot be swapped, log and error is below:
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 `mydb`.`table1`...
No foreign keys reference `mydb`.`table1`; ignoring --alter-foreign-keys-method.
Creating new table...
CREATE TABLE `mydb`.`_table1_new` (
**** masked for security ****
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
Created new table mydb._table1_new OK.
Altering new table...
ALTER TABLE `mydb`.`_table1_new` ADD COLUMN testtest DATETIME(6)
Altered `mydb`.`_table1_new` OK.
2021-01-24T00:26:04 Creating triggers...
2021-01-24T00:26:04 Created triggers OK.
2021-01-24T00:26:04 Copying approximately 62489158 rows...
INSERT LOW_PRIORITY IGNORE INTO `mydb`.`_table1_new` **** masked for security **** /*pt-online-schema-change 31268 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `pk` FROM `mydb`.`table1` FORCE INDEX(`PRIMARY`) WHERE ((`pk` >= ?)) ORDER BY `pk` LIMIT ?, 2 /*next chunk boundary*/
Copying `mydb`.`table1`: 0% 02:57:05 remain
Copying `mydb`.`table1`: 0% 02:47:04 remain
*** skip long process ***
Copying `mydb`.`table1`: 0% 02:49:56 remain
Copying `mydb`.`table1`: 99% 00:00 remain
2021-01-24T04:40:43 Copied rows OK.
2021-01-24T04:40:43 Adding original triggers to new table.
Exiting due to errors while restoring triggers: DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement "LOCK TABLES `mydb`.`_table1_new` WRITE, `mydb`. `table1` WRITE;"] at /bin/pt-online-schema-change line 10109.
2021-01-24T04:41:45 Dropping triggers...
DROP TRIGGER IF EXISTS `mydb`.`pt_osc_mydb_table1_del`
DROP TRIGGER IF EXISTS `mydb`.`pt_osc_mydb_table1_upd`
DROP TRIGGER IF EXISTS `mydb`.`pt_osc_mydb_table1_ins`
2021-01-24T04:41:46 Dropped triggers OK.
Not dropping the new table `mydb`.`_table1_new` because --swap-tables failed. To drop the new table, execute:
DROP TABLE IF EXISTS `mydb`.`_table1_new`;
# Event Count
# ====== =====
# INSERT 20400
`mydb`.`table1` was not altered.
(in cleanup) DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement "LOCK TABLES `mydb`.`_table1_new` WRITE, `mydb`. `table1` WRITE;"] at /bin/pt-online-schema-change line 10109.
2021-01-24T04:41:46 Dropping triggers...
DROP TRIGGER IF EXISTS `mydb`.`pt_osc_mydb_table1_del`
DROP TRIGGER IF EXISTS `mydb`.`pt_osc_mydb_table1_upd`
DROP TRIGGER IF EXISTS `mydb`.`pt_osc_mydb_table1_ins`
2021-01-24T04:41:46 Dropped triggers OK.
Not dropping the new table `mydb`.`_table1_new` because --swap-tables failed. To drop the new table, execute:
DROP TABLE IF EXISTS `mydb`.`_table1_new`;
# Event Count
# ====== =====
# INSERT 20400
`mydb`.`table1` was not altered.
pt-online-schema-change version:
pt-online-schema-change 3.2.1
Our current setup:
2 node master master, DB version:
Server version: 5.7.30-33-57-log Percona XtraDB Cluster (GPL), Release rel33, Revision 5dd6d59, WSREP version 31.43, wsrep_31.43
pt-online-schema-change command (we have swap the command in bash script, below is osc part only):
pt-online-schema-change --preserve-triggers --defaults-file="pt-online-schema-change_my.cnf" --max-load=Threads_running:200 --critical-load=Threads_running:210 --nocheck-unique-key-change D=${DB},t=${TABLE} --socket=/home/mysql/mysql.sock --print --statistics --execute --alter-foreign-keys-method=auto --alter "${CMD}"" < /dev/null 2>&1 | tee -a /tmp/${date}.txt
original trigger in mydb.table1:
CREATE TRIGGER after_table1_delete AFTER DELETE ON table1 FOR EACH ROW INSERT INTO table1_delete_log
SET PK = OLD.PK, modifiedTS = NOW(), createdTS = NOW();
May i know anyone have encountered above error and advise how to avoid?
I checked source code of pt-online-schema-change 3.2.1, line 11552 that need issue LOCK TABLES command:
push @$sqls, "LOCK TABLES `$args{db}`.`$args{new_tbl}` WRITE, `$args{db}`. `$args{orig_tbl}` WRITE;";
Is that necessary and safe? As i do not expect any table lock using pt-online-schema-change.
question from:
https://stackoverflow.com/questions/65868921/pt-online-schema-change-exiting-due-to-errors-while-restoring-triggers-dbdm 
