pt-online-schema-change doesn't ignore signals during drop-swap
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
High
|
Frank Cizmich |
Bug Description
Hi,
I tried to use pt-online-
Here is the command line and the output from the failed execution:
root@DB:~# pt-online-
Cannot connect to D=ims,h=
Cannot connect to D=ims,h=
Cannot connect to D=ims,h=
Child tables:
`ims`
Will automatically choose the method to update foreign keys.
Altering `ims`.`
Creating new table...
Created new table ims._feed_items_new OK.
Altering new table...
Altered `ims`.`
Creating triggers...
Created triggers OK.
Copying approximately 489575 rows...
Copying `ims`.`feed_items`: 13% 03:10 remain
Copying `ims`.`feed_items`: 20% 03:54 remain
Copying `ims`.`feed_items`: 26% 04:06 remain
Copying `ims`.`feed_items`: 32% 04:07 remain
Copying `ims`.`feed_items`: 38% 04:01 remain
Copying `ims`.`feed_items`: 43% 03:57 remain
Copying `ims`.`feed_items`: 48% 03:46 remain
Copying `ims`.`feed_items`: 53% 03:30 remain
Copying `ims`.`feed_items`: 58% 03:13 remain
Copying `ims`.`feed_items`: 62% 03:00 remain
Copying `ims`.`feed_items`: 66% 02:44 remain
Copying `ims`.`feed_items`: 70% 02:31 remain
Copying `ims`.`feed_items`: 74% 02:14 remain
Copying `ims`.`feed_items`: 77% 01:58 remain
Copying `ims`.`feed_items`: 81% 01:39 remain
Copying `ims`.`feed_items`: 85% 01:19 remain
Copying `ims`.`feed_items`: 89% 00:57 remain
Copied rows OK.
Max rows for the rebuild_constraints method: 1722
Determining the method to update foreign keys...
`ims`
Drop-swapping tables...
Dropping triggers...
Dropped triggers OK.
Dropping new table...
Dropped new table OK.
Altered `ims`.`feed_items` but there were errors or warnings.
(in cleanup) Error updating foreign key constraints: DBD::mysql::db do failed: Table './ims/feed_items' already exists [for Statement "RENAME TABLE `ims`.`
Dropping triggers...
Dropped triggers OK.
Altered `ims`.`feed_items` but there were errors or warnings.
- We use one master with 3 slave replicas. All MySQL servers are version 5.5.33
- pt-online-
Related branches
- Daniel Nichter: Approve
-
Diff: 52 lines (+14/-0)1 file modifiedbin/pt-online-schema-change (+14/-0)
summary: |
- Table deleted during alter with pt-online-schema-change + pt-online-schema-change --alter-foreign-keys-method=drop-swap is not + atomic |
tags: | added: i50649 |
Changed in percona-toolkit: | |
status: | Expired → New |
Changed in percona-toolkit: | |
milestone: | none → 2.2.14 |
assignee: | nobody → Frank Cizmich (frank-cizmich) |
importance: | Undecided → Medium |
Changed in percona-toolkit: | |
status: | Confirmed → Fix Committed |
Changed in percona-toolkit: | |
importance: | Medium → High |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
summary: |
- pt-online-schema-change --alter-foreign-keys-method=drop-swap is not - atomic + pt-online-schema-change doesn't ignore signals during drop-swap |
Hi, Let me describe here, how exactly pt-online-schema works.
"pt-online- schema- change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table."
In your case, you get error while rename new table to original table, _feed_items_ new` TO `ims`.` feed_items` "]
Error updating foreign key constraints: DBD::mysql::db do failed: Table './ims/feed_items' already exists [for Statement "RENAME TABLE `ims`.`
So you must have table feed_items in the database. It should not dropped. Even you should also have table like _feed_items_new. I have noticed one more thing that even if you have given option --alter- foreign- keys-method auto it uses drop-swap.
Determining the method to update foreign keys... .`video_ thumbs` : too many rows: 2236639; must use drop_swap
`ims`
Drop-swapping tables...
Can you please check again and provide the exact test case that how exactly we can reproduce it?
MySQL error log and table structure will be helpful.