pt-online-schema-change causes "ERROR 1146 (42S02): Table 'db._t_new' doesn't exist"

[Jenni Snyder]

In this failure case, pt-online-schema-change stopped running because a load criteria was met.

During the cleanup phase, one of the triggers could not be dropped. pt-online-schema-change then dropped the __mytable_new table, causing all updates on the original table to fail because the destination table for the trigger was not present!

jsnyder@test:~$ pt-online-schema-change --alter=" DROP col1 " D=mydb,t=mytable,h=127.0.0.1,P=3306 --max-lag=4 --recursion-method=dsn=D=test,t=dsns --no-check-replication-filters --no-drop-old-table --statistics --chunk-time=0.25 --retries=5 --set-vars 'lock_wait_timeout=30' --execute
Altering `mydb`.`mytable`...
Creating new table...
Created new table mydb.__mytable_new OK.
Altering new table...
Altered `mydb`.`__mytable_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 10000000 rows...
Copying `mydb`.`mytable`: 0% 01:59:54 remain
Copying `mydb`.`mytable`: 0% 01:50:40 remain
Dropping triggers...
Error dropping trigger: DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/bin/pt-online-schema-change line 9655.

To try dropping the triggers again, execute:
DROP TRIGGER IF EXISTS `mydb`.`pt_osc_mydb_mytable_upd`;
Dropping new table...
Dropped new table OK.
`mydb`.`mytable` was not altered.
Error copying rows from `mydb`.`mytable` to `mydb`.`__mytable_new`: Threads_running=51 exceeds its critical threshold 50

Until the trigger was dropped, attempts to write to the original table resulted in the error:
Table 'mydb.__mytable_new' doesn't exist

The simplest solution to me seems to be that if pt-online-schema-change cannot drop one or more of the triggers, it should not drop the __mytable__new table.

[Daniel Nichter (daniel-nichter)]

I confirm this too:

"""
mysql> insert into t values (null, 'x', now());
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> drop table _t_new;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (null, 'y', now());
ERROR 1146 (42S02): Table 'pt_osc._t_new' doesn't exist
"""

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-358