pt-online-schema-change(Error dropping trigger: DBD::mysql::db do failed: Lock wait timeout exceeded)

MySQL & MariaDB
1

Hi All,

I am using pt-online-schema-change for adding column in table and getting below error .

MYSQL Version : mysql Ver 14.14 Distrib 5.6.34-79.1, for debian-linux-gnu (x86_64) using readline 6.3
pt-online-schema-change --version (2.2.20)

Error:

Copying SmileDB.charging_detail: 99% 00:38 remain
Pausing because Threads_running=68.
Pausing because Threads_running=30.
Pausing because Threads_running=27.
Pausing because Threads_running=29.
Pausing because Threads_running=27.
Pausing because Threads_running=39.
2022-06-28T23:18:37 Copied rows OK.
2022-06-28T23:18:37 Analyzing new table…
2022-06-28T23:18:37 Swapping tables…
2022-06-28T23:38:55 Error dropping trigger: 2022-06-28T23:38:55 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement “DROP TRIGGER IF EXISTS SmileDB.pt_osc_SmileDB_charging_detail_del;”] at /usr/bin/pt-online-schema-change line 10632.

2022-06-28T23:49:04 Error dropping trigger: 2022-06-28T23:49:04 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement “DROP TRIGGER IF EXISTS SmileDB.pt_osc_SmileDB_charging_detail_upd;”] at /usr/bin/pt-online-schema-change line 10632.

2022-06-28T23:59:15 Error dropping trigger: 2022-06-28T23:59:15 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement “DROP TRIGGER IF EXISTS SmileDB.pt_osc_SmileDB_charging_detail_ins;”] at /usr/bin/pt-online-schema-change line 10632.

2022-06-28T23:59:15 To try dropping the triggers again, execute:
DROP TRIGGER IF EXISTS SmileDB.pt_osc_SmileDB_charging_detail_del;
DROP TRIGGER IF EXISTS SmileDB.pt_osc_SmileDB_charging_detail_upd;
DROP TRIGGER IF EXISTS SmileDB.pt_osc_SmileDB_charging_detail_ins;
2022-06-28T23:28:46 Dropping triggers…
Not dropping the new table SmileDB._charging_detail_new because --swap-tables failed. To drop the new table, execute:
DROP TABLE IF EXISTS SmileDB._charging_detail_new;
SmileDB.charging_detail was not altered.
2022-06-28T23:28:46 Error swapping tables: 2022-06-28T23:28:46 2022-06-28T23:28:46 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement “RENAME TABLE SmileDB.charging_detail TO SmileDB._charging_detail_old, SmileDB._charging_detail_new TO SmileDB.charging_detail”] at /usr/bin/pt-online-schema-change line 10134.

To clean up, first verify that the original table SmileDB.charging_detail has not been modified or renamed, then drop the new table SmileDB._charging_detail_new if it exists.
# Exiting on SIGPIPE.

Could you please suggest what I can do to fix this error or is there any way to add column in table online.

Regards,
Daljit Singh

2

Hi, it appears your schema migration completed successfully, however during the cleanup phase is when timeouts were reached and exceeded. You can try to run the RENAME TABLE and then the three DROP TRIGGER statements again.
This is telling you that pt-osc waited but couldn’t remove the triggers within the timeout value

1 Like
3

Hi Micheal,

Thanks for your support.

1 Like