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  Edit
Everyone can see this information.

Other bug subscribers