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

Bug #1188002 reported by Jenni Snyder
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
High
Daniel Nichter

Bug Description

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.

Related branches

tags: added: i32429
Changed in percona-toolkit:
status: New → Confirmed
importance: Undecided → High
assignee: nobody → Daniel Nichter (daniel-nichter)
tags: added: percona-32429 pt-online-schema-change
removed: i32429
Changed in percona-toolkit:
status: Confirmed → In Progress
tags: added: risk
summary: - pt-online-schema-change should not drop __mytable_new table if it was
- unable to drop one or more triggers
+ pt-online-schema-change drops table __tbl_new even if DROP TRIGGER fails
+ causing "Table '__tbl_new' doesn't exist" errors
summary: - pt-online-schema-change drops table __tbl_new even if DROP TRIGGER fails
- causing "Table '__tbl_new' doesn't exist" errors
+ pt-online-schema-change drops table __tbl_new even if a DROP TRIGGER
+ fails causing "Table '__tbl_new' doesn't exist" errors
Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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
"""

summary: - pt-online-schema-change drops table __tbl_new even if a DROP TRIGGER
- fails causing "Table '__tbl_new' doesn't exist" errors
+ pt-online-schema-change causes "drops table __tbl_new even if a DROP
+ TRIGGER fails causing "Table '__tbl_new' doesn't exist" errors
summary: - pt-online-schema-change causes "drops table __tbl_new even if a DROP
- TRIGGER fails causing "Table '__tbl_new' doesn't exist" errors
+ pt-online-schema-change causes "ERROR 1146 (42S02): Table 'db._t_new'
+ doesn't exist"
Changed in percona-toolkit:
milestone: none → 2.2.3
status: In Progress → Fix Committed
Changed in percona-toolkit:
milestone: 2.2.4 → 2.2.3
Changed in percona-toolkit:
status: Fix Committed → Fix Released
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.