LP #1597497: pt-online-schema change resulted in lost table
Description
Environment
Smart Checklist
Activity
Oliver Dala February 18, 2020 at 5:09 PM
I found the cause for this after running into it today:
When you used pt-online-schema-change on the table before, all the foreign keys will be prefixed with underscores.
If you didn't specify constraint names before, and add a new foreign key after that, without specifying a constraint name again, or using the same names innodb generates, you might get a constraint name collision because innodb will just begin counting at 1 again for the names.
To reproduce use this table, mind the 2 leading underscores in the constraint name:
create table muh (
id INT,
fk_field int,
PRIMARY KEY (id),
CONSTRAINT __muh_ibfk_1 FOREIGN KEY (fk_field) references muh (id)
) ENGINE=InnoDB;then run:
pt-online-schema-change D=test,t=muh --execute --alter-foreign-keys-method drop_swap --alter 'ADD COLUMN new_fk_field INT, ADD FOREIGN KEY (new_fk_field) REFERENCES muh (id)'
if you specify a constraint name, it works:
pt-online-schema-change D=test,t=muh --execute --alter-foreign-keys-method drop_swap --alter 'ADD COLUMN new_fk_field INT, ADD CONSTRAINT fk_muh_muh FOREIGN KEY (new_fk_field) REFERENCES muh (id)'
I think this should be mentioned in the manual and also hinted at by the --check-alter switch.
lpjirasync January 24, 2018 at 9:14 PM
**Comment from Launchpad by: Paul Carter-Brown on: 23-07-2016 05:36:24
Hi,
Is there any option I could have passed that would have prevented the error?
On 23 Jul 2016 00:45, "Sveta Smirnova" <sveta@js-client.com> wrote:
> Bug is repeatable if have a table, which references original one, and
> use option --alter-foreign-keys-method=drop_swap:
>
> sveta@Thinkie:~/issues/100474$
> ~/src/percona-toolkit/bin/pt-online-schema-change --user root --execute
> --no-check-replication-filters --alter-foreign-keys-method=auto
> --critical-load Threads_running=400 --alter "ADD CREATED_BY_ORGANISATION_ID
> INT AFTER CREATED_BY_CUSTOMER_PROFILE_ID, ADD FOREIGN KEY
> (CREATED_BY_ORGANISATION_ID) REFERENCES SmileDB.organisation
> (ORGANISATION_ID) ON UPDATE RESTRICT ON DELETE RESTRICT"
> --alter-foreign-keys-method=drop_swap
> D=SmileDB,t=product_instance,h=127.0.0.1,P=13001
> No slaves found. See --recursion-method if host Thinkie has slaves.
> Not checking slave lag because no slaves were found and --check-slave-lag
> was not specified.
> Operation, tries, wait:
> analyze_table, 10, 1
> copy_rows, 10, 0.25
> create_triggers, 10, 1
> drop_triggers, 10, 1
> swap_tables, 10, 1
> update_foreign_keys, 10, 1
> Child tables:
> `SmileDB`.`service_instance` (approx. 1 rows)
> Will use the drop_swap method to update foreign keys.
> Altering `SmileDB`.`product_instance`...
> Creating new table...
> Created new table SmileDB._product_instance_new OK.
> Altering new table...
> Altered `SmileDB`.`_product_instance_new` OK.
> 2016-07-23T00:55:20 Creating triggers...
> 2016-07-23T00:55:20 Created triggers OK.
> 2016-07-23T00:55:20 Copying approximately 1 rows...
> 2016-07-23T00:55:20 Copied rows OK.
> 2016-07-23T00:55:20 Drop-swapping tables...
> 2016-07-23T00:55:20 Analyzing new table...
> 2016-07-23T00:55:21 Dropping triggers...
> 2016-07-23T00:55:21 Dropped triggers OK.
> 2016-07-23T00:55:21 Dropping new table...
> 2016-07-23T00:55:21 Dropped new table OK.
> Altered `SmileDB`.`product_instance` but there were errors or warnings.
> Error updating foreign key constraints: 2016-07-23T00:55:21 DBD::mysql::db
> do failed: Table './SmileDB/product_instance' already exists [for Statement
> "RENAME TABLE `SmileDB`.`_product_instance_new` TO
> `SmileDB`.`product_instance`"] at
> /home/sveta/src/percona-toolkit/bin/pt-online-schema-change line 10507.
>
> veta@Thinkie:~/mysql_packages/Percona-Server-5.6.21-rel70.1-698.Linux.x86_64/mysql-test$
> mysqlmtr -P13001 SmileDB
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 18
> Server version: 5.6.21-70.1-log Percona Server (GPL), Release 70.1,
> Revision 698
>
> Copyright (c) 2009-2014 Percona LLC and/or its affiliates
> Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights
> reserved.
>
> Oracle is a registered trademark of Oracle Corporation and/or its
> affiliates. Other names may be trademarks of their respective
> owners.
>
> Type 'help;' or '\h' for help. Type '\c' to clear the current input
> statement.
>
> mysql> show tables;
> -----------------------
> | Tables_in_SmileDB |
> -----------------------
> | customer_profile |
> | organisation |
> | product_specification |
> | service_instance |
> -----------------------
> 4 rows in set (0,00 sec)
>
> Manually adding option --alter-foreign-keys-method=drop_swap is valid
> test method, because in the tool's output in the initial description we
> see:
>
> Determining the method to update foreign keys...
> 2016-06-29T18:33:59 `SmileDB`.`service_instance`: too many rows: 74456;
> must use drop_swap
> 2016-06-29T18:33:59 Drop-swapping tables...
>
> I set status of this report to "Confirmed", but this is actually feature
> request, because such behavior of drop_swap described at
> https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-
> change.html#cmdoption-pt-online-schema-change--alter-foreign-keys-
> method:
>
> ----<q>----
> drop_swap
>
> Disable foreign key checks (FOREIGN_KEY_CHECKS=0), then drop the
> original table before renaming the new table into its place. This is
> different from the normal method of swapping the old and new table,
> which uses an atomic RENAME that is undetectable to client applications.
>
> This method is faster and does not block, but it is riskier for two
> reasons. First, for a short time between dropping the original table and
> renaming the temporary table, the table to be altered simply does not
> exist, and queries against it will result in an error. Secondly, if
> there is an error and the new table cannot be renamed into the place of
> the old one, then it is too late to abort, because the old table is gone
> permanently.
>
> This method forces --no-swap-tables and --no-drop-old-table.
> ----</q>----
>
> Feature request: specify option (--auto=rebuild-constraints,none or
> --auto=none) which fails back from rebuild_constraints to none for those
> who wants reasonably fast swap, but without risk of loosing data. Or
> allow drop_swap to work with option --no-drop-new-table: do not ignore
> all warnings and if warnings exist rename new table back.
>
> ** Attachment added: "Dump I used to repeat this"
>
> https://bugs.launchpad.net/percona-toolkit/+bug/1597497/+attachment/4705637/+files/orig_data.sql
>
> –
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1597497
>
> Title:
> pt-online-schema change resulted in lost table
>
> Status in Percona Toolkit:
> Confirmed
>
> Bug description:
> We lost a production table using pt-online schema change. The tool got
> an error and dropped both the old and new tables - i.e. we completely
> lost the table we were changing. The ibd and frm files were gone off
> the file system and our slave also lost the table. We've subsequently
> rebuilt the table but this is a very worrying thing to happen. Here is
> the log:
>
> root@TZRE-MySQLM:~ # pt-online-schema-change --user root --password XXX
> --execute --no-check-replication-filters --alter-foreign-keys-method=auto
> --critical-load Threads_running=400 --alter "ADD CREATED_BY_ORGANISATION_ID
> INT AFTER CREATED_BY_CUSTOMER_PROFILE_ID, ADD FOREIGN KEY
> (CREATED_BY_ORGANISATION_ID) REFERENCES SmileDB.organisation
> (ORGANISATION_ID) ON UPDATE RESTRICT ON DELETE RESTRICT"
> D=SmileDB,t=product_instance
> Found 1 slaves:
> TZRE-MySQLS
> Will check slave lag on:
> TZRE-MySQLS
> Operation, tries, wait:
> analyze_table, 10, 1
> copy_rows, 10, 0.25
> create_triggers, 10, 1
> drop_triggers, 10, 1
> swap_tables, 10, 1
> update_foreign_keys, 10, 1
> Child tables:
> `SmileDB`.`service_instance` (approx. 74456 rows)
> Will automatically choose the method to update foreign keys.
> Altering `SmileDB`.`product_instance`...
> Creating new table...
> Created new table SmileDB._product_instance_new OK.
> Waiting forever for new table `SmileDB`.`_product_instance_new` to
> replicate to TZRE-MySQLS...
> Altering new table...
> Altered `SmileDB`.`_product_instance_new` OK.
> 2016-06-29T18:33:56 Creating triggers...
> 2016-06-29T18:33:58 Created triggers OK.
> 2016-06-29T18:33:58 Copying approximately 29974 rows...
> 2016-06-29T18:33:59 Copied rows OK.
> 2016-06-29T18:33:59 Max rows for the rebuild_constraints method: 51530
> Determining the method to update foreign keys...
> 2016-06-29T18:33:59 `SmileDB`.`service_instance`: too many rows:
> 74456; must use drop_swap
> 2016-06-29T18:33:59 Drop-swapping tables...
> 2016-06-29T18:33:59 Analyzing new table...
> 2016-06-29T18:34:00 Dropping triggers...
> 2016-06-29T18:34:00 Dropped triggers OK.
> 2016-06-29T18:34:00 Dropping new table...
> 2016-06-29T18:34:00 Dropped new table OK.
> Altered `SmileDB`.`product_instance` but there were errors or warnings.
> Error updating foreign key constraints: 2016-06-29T18:34:00
> DBD::mysql::db do failed: Table './SmileDB/product_instance' already exists
> [for Statement "RENAME TABLE `SmileDB`.`_product_instance_new` TO
> `SmileDB`.`product_instance`"] at /usr/bin/pt-online-schema-change line
> 10487.
>
>
>
> Here is the schema before the change:
> CREATE TABLE `product_instance` (
> `PRODUCT_INSTANCE_ID` int(11) NOT NULL AUTO_INCREMENT,
> `PRODUCT_SPECIFICATION_ID` int(11) NOT NULL,
> `CUSTOMER_PROFILE_ID` int(11) NOT NULL,
> `ORGANISATION_ID` int(11) NOT NULL,
> `STATUS` char(2) NOT NULL,
> `SEGMENT` varchar(20) NOT NULL,
> `CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
> `CREATED_DATETIME` datetime NOT NULL,
> `PROMOTION_CODE` varchar(100) NOT NULL,
> `LAST_MODIFIED` datetime NOT NULL,
> `FRIENDLY_NAME` varchar(200) NOT NULL,
> `LOGICAL_ID` int(11) DEFAULT NULL,
> `PHYSICAL_ID` varchar(20) DEFAULT NULL,
> `LAST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
> `FIRST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
> `LAST_RECONNECTION_DATETIME` datetime DEFAULT NULL,
> `LAST_IMEI` varchar(50) DEFAULT NULL,
> PRIMARY KEY (`PRODUCT_INSTANCE_ID`),
> KEY `CUSTOMER_PROFILE_ID` (`CUSTOMER_PROFILE_ID`),
> KEY `FK_PRODUCT_INSTANCE_SPECIFICATION` (`PRODUCT_SPECIFICATION_ID`),
> KEY `product_instance_ibfk_3` (`ORGANISATION_ID`),
> KEY `product_instance_ibfk_4` (`CREATED_BY_CUSTOMER_PROFILE_ID`),
> KEY `LOGICAL_ID` (`LOGICAL_ID`),
> CONSTRAINT `_product_instance_ibfk_1` FOREIGN KEY
> (`PRODUCT_SPECIFICATION_ID`) REFERENCES `product_specification`
> (`PRODUCT_SPECIFICATION_ID`),
> CONSTRAINT `_product_instance_ibfk_2` FOREIGN KEY
> (`CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile`
> (`CUSTOMER_PROFILE_ID`),
> CONSTRAINT `_product_instance_ibfk_3` FOREIGN KEY (`ORGANISATION_ID`)
> REFERENCES `organisation` (`ORGANISATION_ID`),
> CONSTRAINT `_product_instance_ibfk_4` FOREIGN KEY
> (`CREATED_BY_CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile`
> (`CUSTOMER_PROFILE_ID`)
> ) ENGINE=InnoDB AUTO_INCREMENT=308821 DEFAULT CHARSET=latin1;
>
>
> And what we were changing it to:
>
> CREATE TABLE `product_instance` (
> `PRODUCT_INSTANCE_ID` int(11) NOT NULL AUTO_INCREMENT,
> `PRODUCT_SPECIFICATION_ID` int(11) NOT NULL,
> `CUSTOMER_PROFILE_ID` int(11) NOT NULL,
> `ORGANISATION_ID` int(11) NOT NULL,
> `STATUS` char(2) NOT NULL,
> `SEGMENT` varchar(20) NOT NULL,
> `CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
> `CREATED_BY_ORGANISATION_ID` int(11) DEFAULT NULL,
> `CREATED_DATETIME` datetime NOT NULL,
> `PROMOTION_CODE` varchar(100) NOT NULL,
> `LAST_MODIFIED` datetime NOT NULL,
> `FRIENDLY_NAME` varchar(200) NOT NULL,
> `LOGICAL_ID` int(11) DEFAULT NULL,
> `PHYSICAL_ID` varchar(20) DEFAULT NULL,
> `LAST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
> `FIRST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
> `LAST_RECONNECTION_DATETIME` datetime DEFAULT NULL,
> `LAST_IMEI` varchar(50) DEFAULT NULL,
> PRIMARY KEY (`PRODUCT_INSTANCE_ID`),
> KEY `CUSTOMER_PROFILE_ID` (`CUSTOMER_PROFILE_ID`),
> KEY `FK_PRODUCT_INSTANCE_SPECIFICATION` (`PRODUCT_SPECIFICATION_ID`),
> KEY `product_instance_ibfk_3` (`ORGANISATION_ID`),
> KEY `product_instance_ibfk_4` (`CREATED_BY_CUSTOMER_PROFILE_ID`),
> KEY `LOGICAL_ID` (`LOGICAL_ID`),
> CONSTRAINT `product_instance_ibfk_1` FOREIGN KEY
> (`PRODUCT_SPECIFICATION_ID`) REFERENCES `product_specification`
> (`PRODUCT_SPECIFICATION_ID`),
> CONSTRAINT `product_instance_ibfk_2` FOREIGN KEY
> (`CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile`
> (`CUSTOMER_PROFILE_ID`),
> CONSTRAINT `product_instance_ibfk_3` FOREIGN KEY (`ORGANISATION_ID`)
> REFERENCES `organisation` (`ORGANISATION_ID`),
> CONSTRAINT `product_instance_ibfk_4` FOREIGN KEY
> (`CREATED_BY_CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile`
> (`CUSTOMER_PROFILE_ID`)
> ) ENGINE=InnoDB AUTO_INCREMENT=308827 DEFAULT CHARSET=latin1;
>
>
> As per additional info requested from Percona support:
>
> there was no remaining `SmileDB`.`product_instance` or
> `SmileDB`.`_product_instance_new` table left ?
>
> Correct - I double/triple checked. Same also on your slave database -
> i.e. the drop table was valid SQL and replicated over to the slave.
>
> To rebuild the table we got the binlog section where pt-online-schema-
> change copied all the data to the _product_instance_new table and then
> did some magic with awk etc to change the binary log to valid SQL and
> ran it to populate a new table with the schema of
> _product_instance_new.
>
>
> Here is the rest of the schema.
>
> (root@localhost:pm)[SmileDB]>SHOW CREATE TABLE
> SmileDB.product_specification\G
> *************************** 1. row ***************************
> Table: product_specification
> Create Table: CREATE TABLE `product_specification` (
> `PRODUCT_SPECIFICATION_ID` int(11) NOT NULL,
> `PRODUCT_NAME` varchar(1000) NOT NULL,
> `PRODUCT_DESCRIPTION` varchar(2000) NOT NULL,
> `AVAILABLE_FROM` datetime NOT NULL,
> `AVAILABLE_TO` datetime NOT NULL,
> `PROVISION_ROLES` varchar(2000) NOT NULL,
> `SEGMENTS` varchar(2000) NOT NULL,
> `REPORTING_TYPE` varchar(20) NOT NULL,
> PRIMARY KEY (`PRODUCT_SPECIFICATION_ID`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> (root@localhost:pm)[SmileDB]>SHOW CREATE TABLE
> SmileDB.customer_profile\G
> *************************** 1. row ***************************
> Table: customer_profile
> Create Table: CREATE TABLE `customer_profile` (
> `CUSTOMER_PROFILE_ID` int(11) NOT NULL AUTO_INCREMENT,
> `TITLE` varchar(20) DEFAULT NULL,
> `FIRST_NAME` varchar(100) NOT NULL,
> `MIDDLE_NAME` varchar(100) NOT NULL,
> `LAST_NAME` varchar(100) NOT NULL,
> `ID_NUMBER` varchar(50) NOT NULL,
> `CREATED_DATETIME` datetime NOT NULL,
> `DATE_OF_BIRTH` varchar(8) NOT NULL,
> `GENDER` varchar(1) NOT NULL,
> `LANGUAGE` varchar(50) NOT NULL,
> `EMAIL_ADDRESS` varchar(200) NOT NULL,
> `ALTERNATIVE_CONTACT_1` varchar(100) NOT NULL,
> `ALTERNATIVE_CONTACT_2` varchar(100) NOT NULL,
> `CLASSIFICATION` varchar(50) NOT NULL,
> `VERSION` int(11) NOT NULL,
> `STATUS` varchar(2) NOT NULL,
> `SSO_IDENTITY` varchar(100) NOT NULL,
> `SSO_DIGEST` varchar(100) NOT NULL,
> `SSO_AUTH_ATTEMPTS` tinyint(4) NOT NULL,
> `SSO_LOCK_EXPIRY` datetime DEFAULT NULL,
> `ID_NUMBER_TYPE` varchar(50) NOT NULL,
> `OPT_IN_LEVEL` int(11) NOT NULL,
> `CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
> `ACCOUNT_MANAGER_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
> `MOTHERS_MAIDEN_NAME` varchar(100) NOT NULL,
> `NATIONALITY` varchar(100) NOT NULL,
> `PASSPORT_EXPIRY_DATE` varchar(8) NOT NULL,
> `WAREHOUSE_ID` varchar(50) NOT NULL,
> `UPDATED_DATETIME` datetime DEFAULT NULL,
> `KYC_STATUS` varchar(1) DEFAULT NULL,
> `REFERRAL_CODE` varchar(200) DEFAULT NULL,
> PRIMARY KEY (`CUSTOMER_PROFILE_ID`),
> UNIQUE KEY `SSO_IDENTITY` (`SSO_IDENTITY`),
> KEY `FIRST_NAME` (`FIRST_NAME`),
> KEY `LAST_NAME` (`LAST_NAME`),
> KEY `ID_NUMBER` (`ID_NUMBER`),
> KEY `EMAIL_ADDRESS` (`EMAIL_ADDRESS`)
> ) ENGINE=InnoDB AUTO_INCREMENT=41655 DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> (root@localhost:pm)[SmileDB]>SHOW CREATE TABLE SmileDB.organisation\G
> *************************** 1. row ***************************
> Table: organisation
> Create Table: CREATE TABLE `organisation` (
> `ORGANISATION_ID` int(11) NOT NULL AUTO_INCREMENT,
> `ORGANISATION_NAME` varchar(200) NOT NULL,
> `ALTERNATIVE_CONTACT_1` varchar(100) NOT NULL,
> `ALTERNATIVE_CONTACT_2` varchar(100) NOT NULL,
> `EMAIL_ADDRESS` varchar(200) NOT NULL,
> `ORGANISATION_TYPE` varchar(50) NOT NULL,
> `TAX_NUMBER` varchar(50) NOT NULL,
> `COMPANY_NUMBER` varchar(50) NOT NULL,
> `SIZE` varchar(20) NOT NULL,
> `INDUSTRY` varchar(50) NOT NULL,
> `ACCOUNT_MANAGER_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
> `CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
> `STATUS` varchar(2) NOT NULL,
> `VERSION` int(11) NOT NULL,
> `CREATED_DATETIME` datetime NOT NULL,
> `CREDIT_ACCOUNT_NUMBER` varchar(20) NOT NULL,
> `MODIFICATION_ROLES` varchar(2000) NOT NULL,
> `CHANNEL_CODE` varchar(20) NOT NULL,
> PRIMARY KEY (`ORGANISATION_ID`),
> UNIQUE KEY `ORGANISATION_NAME` (`ORGANISATION_NAME`)
> ) ENGINE=InnoDB AUTO_INCREMENT=7259 DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-toolkit/+bug/1597497/+subscriptions
>
lpjirasync January 24, 2018 at 9:14 PM
**Comment from Launchpad by: Sveta Smirnova on: 22-07-2016 22:40:18
Bug is repeatable if have a table, which references original one, and use option --alter-foreign-keys-method=drop_swap:
sveta@Thinkie:~/issues/100474$ ~/src/percona-toolkit/bin/pt-online-schema-change --user root --execute --no-check-replication-filters --alter-foreign-keys-method=auto --critical-load Threads_running=400 --alter "ADD CREATED_BY_ORGANISATION_ID INT AFTER CREATED_BY_CUSTOMER_PROFILE_ID, ADD FOREIGN KEY (CREATED_BY_ORGANISATION_ID) REFERENCES SmileDB.organisation (ORGANISATION_ID) ON UPDATE RESTRICT ON DELETE RESTRICT" --alter-foreign-keys-method=drop_swap D=SmileDB,t=product_instance,h=127.0.0.1,P=13001
No slaves found. See --recursion-method if host Thinkie has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Child tables:
`SmileDB`.`service_instance` (approx. 1 rows)
Will use the drop_swap method to update foreign keys.
Altering `SmileDB`.`product_instance`...
Creating new table...
Created new table SmileDB._product_instance_new OK.
Altering new table...
Altered `SmileDB`.`_product_instance_new` OK.
2016-07-23T00:55:20 Creating triggers...
2016-07-23T00:55:20 Created triggers OK.
2016-07-23T00:55:20 Copying approximately 1 rows...
2016-07-23T00:55:20 Copied rows OK.
2016-07-23T00:55:20 Drop-swapping tables...
2016-07-23T00:55:20 Analyzing new table...
2016-07-23T00:55:21 Dropping triggers...
2016-07-23T00:55:21 Dropped triggers OK.
2016-07-23T00:55:21 Dropping new table...
2016-07-23T00:55:21 Dropped new table OK.
Altered `SmileDB`.`product_instance` but there were errors or warnings.
Error updating foreign key constraints: 2016-07-23T00:55:21 DBD::mysql::db do failed: Table './SmileDB/product_instance' already exists [for Statement "RENAME TABLE `SmileDB`.`_product_instance_new` TO `SmileDB`.`product_instance`"] at /home/sveta/src/percona-toolkit/bin/pt-online-schema-change line 10507.
veta@Thinkie:~/mysql_packages/Percona-Server-5.6.21-rel70.1-698.Linux.x86_64/mysql-test$ mysqlmtr -P13001 SmileDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.6.21-70.1-log Percona Server (GPL), Release 70.1, Revision 698
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
-----------------------
Tables_in_SmileDB |
-----------------------
customer_profile |
organisation |
product_specification |
service_instance |
-----------------------
4 rows in set (0,00 sec)
Manually adding option --alter-foreign-keys-method=drop_swap is valid test method, because in the tool's output in the initial description we see:
Determining the method to update foreign keys...
2016-06-29T18:33:59 `SmileDB`.`service_instance`: too many rows: 74456; must use drop_swap
2016-06-29T18:33:59 Drop-swapping tables...
I set status of this report to "Confirmed", but this is actually feature request, because such behavior of drop_swap described at https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change--alter-foreign-keys-method:
----<q>---- drop_swap
Disable foreign key checks (FOREIGN_KEY_CHECKS=0), then drop the original table before renaming the new table into its place. This is different from the normal method of swapping the old and new table, which uses an atomic RENAME that is undetectable to client applications.
This method is faster and does not block, but it is riskier for two reasons. First, for a short time between dropping the original table and renaming the temporary table, the table to be altered simply does not exist, and queries against it will result in an error. Secondly, if there is an error and the new table cannot be renamed into the place of the old one, then it is too late to abort, because the old table is gone permanently.
This method forces --no-swap-tables and --no-drop-old-table.
----</q>----
Feature request: specify option (--auto=rebuild-constraints,none or --auto=none) which fails back from rebuild_constraints to none for those who wants reasonably fast swap, but without risk of loosing data. Or allow drop_swap to work with option --no-drop-new-table: do not ignore all warnings and if warnings exist rename new table back.
lpjirasync January 24, 2018 at 9:14 PM
**Comment from Launchpad by: Paul Carter-Brown on: 05-07-2016 12:11:01
root@TZRE-MySQLM:~ # mysql --version
mysql Ver 14.14 Distrib 5.6.28-76.1, for debian-linux-gnu (x86_64) using readline 6.3
lpjirasync January 24, 2018 at 9:14 PM
**Comment from Launchpad by: Paul Carter-Brown on: 05-07-2016 08:07:45
Sorry for the delay:
root@TZRE-MySQLM:~ # pt-online-schema-change --version
pt-online-schema-change 2.2.16
Details
Details
Assignee
Reporter
lpjirasyncPriority
LowSmart Checklist
Open Smart Checklist
Smart Checklist
**Reported in Launchpad by Paul Carter-Brown last update 23-07-2016 05:36:32
We lost a production table using pt-online schema change. The tool got an error and dropped both the old and new tables - i.e. we completely lost the table we were changing. The ibd and frm files were gone off the file system and our slave also lost the table. We've subsequently rebuilt the table but this is a very worrying thing to happen. Here is the log:
root@TZRE-MySQLM:~ # pt-online-schema-change --user root --password XXX --execute --no-check-replication-filters --alter-foreign-keys-method=auto --critical-load Threads_running=400 --alter "ADD CREATED_BY_ORGANISATION_ID INT AFTER CREATED_BY_CUSTOMER_PROFILE_ID, ADD FOREIGN KEY (CREATED_BY_ORGANISATION_ID) REFERENCES SmileDB.organisation (ORGANISATION_ID) ON UPDATE RESTRICT ON DELETE RESTRICT" D=SmileDB,t=product_instance
Found 1 slaves:
TZRE-MySQLS
Will check slave lag on:
TZRE-MySQLS
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Child tables:
`SmileDB`.`service_instance` (approx. 74456 rows)
Will automatically choose the method to update foreign keys.
Altering `SmileDB`.`product_instance`...
Creating new table...
Created new table SmileDB._product_instance_new OK.
Waiting forever for new table `SmileDB`.`_product_instance_new` to replicate to TZRE-MySQLS...
Altering new table...
Altered `SmileDB`.`_product_instance_new` OK.
2016-06-29T18:33:56 Creating triggers...
2016-06-29T18:33:58 Created triggers OK.
2016-06-29T18:33:58 Copying approximately 29974 rows...
2016-06-29T18:33:59 Copied rows OK.
2016-06-29T18:33:59 Max rows for the rebuild_constraints method: 51530
Determining the method to update foreign keys...
2016-06-29T18:33:59 `SmileDB`.`service_instance`: too many rows: 74456; must use drop_swap
2016-06-29T18:33:59 Drop-swapping tables...
2016-06-29T18:33:59 Analyzing new table...
2016-06-29T18:34:00 Dropping triggers...
2016-06-29T18:34:00 Dropped triggers OK.
2016-06-29T18:34:00 Dropping new table...
2016-06-29T18:34:00 Dropped new table OK.
Altered `SmileDB`.`product_instance` but there were errors or warnings.
Error updating foreign key constraints: 2016-06-29T18:34:00 DBD::mysql::db do failed: Table './SmileDB/product_instance' already exists [for Statement "RENAME TABLE `SmileDB`.`_product_instance_new` TO `SmileDB`.`product_instance`"] at /usr/bin/pt-online-schema-change line 10487.
Here is the schema before the change:
CREATE TABLE `product_instance` (
`PRODUCT_INSTANCE_ID` int(11) NOT NULL AUTO_INCREMENT,
`PRODUCT_SPECIFICATION_ID` int(11) NOT NULL,
`CUSTOMER_PROFILE_ID` int(11) NOT NULL,
`ORGANISATION_ID` int(11) NOT NULL,
`STATUS` char(2) NOT NULL,
`SEGMENT` varchar(20) NOT NULL,
`CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
`CREATED_DATETIME` datetime NOT NULL,
`PROMOTION_CODE` varchar(100) NOT NULL,
`LAST_MODIFIED` datetime NOT NULL,
`FRIENDLY_NAME` varchar(200) NOT NULL,
`LOGICAL_ID` int(11) DEFAULT NULL,
`PHYSICAL_ID` varchar(20) DEFAULT NULL,
`LAST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
`FIRST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
`LAST_RECONNECTION_DATETIME` datetime DEFAULT NULL,
`LAST_IMEI` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PRODUCT_INSTANCE_ID`),
KEY `CUSTOMER_PROFILE_ID` (`CUSTOMER_PROFILE_ID`),
KEY `FK_PRODUCT_INSTANCE_SPECIFICATION` (`PRODUCT_SPECIFICATION_ID`),
KEY `product_instance_ibfk_3` (`ORGANISATION_ID`),
KEY `product_instance_ibfk_4` (`CREATED_BY_CUSTOMER_PROFILE_ID`),
KEY `LOGICAL_ID` (`LOGICAL_ID`),
CONSTRAINT `_product_instance_ibfk_1` FOREIGN KEY (`PRODUCT_SPECIFICATION_ID`) REFERENCES `product_specification` (`PRODUCT_SPECIFICATION_ID`),
CONSTRAINT `_product_instance_ibfk_2` FOREIGN KEY (`CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile` (`CUSTOMER_PROFILE_ID`),
CONSTRAINT `_product_instance_ibfk_3` FOREIGN KEY (`ORGANISATION_ID`) REFERENCES `organisation` (`ORGANISATION_ID`),
CONSTRAINT `_product_instance_ibfk_4` FOREIGN KEY (`CREATED_BY_CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile` (`CUSTOMER_PROFILE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=308821 DEFAULT CHARSET=latin1;
And what we were changing it to:
CREATE TABLE `product_instance` (
`PRODUCT_INSTANCE_ID` int(11) NOT NULL AUTO_INCREMENT,
`PRODUCT_SPECIFICATION_ID` int(11) NOT NULL,
`CUSTOMER_PROFILE_ID` int(11) NOT NULL,
`ORGANISATION_ID` int(11) NOT NULL,
`STATUS` char(2) NOT NULL,
`SEGMENT` varchar(20) NOT NULL,
`CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
`CREATED_BY_ORGANISATION_ID` int(11) DEFAULT NULL,
`CREATED_DATETIME` datetime NOT NULL,
`PROMOTION_CODE` varchar(100) NOT NULL,
`LAST_MODIFIED` datetime NOT NULL,
`FRIENDLY_NAME` varchar(200) NOT NULL,
`LOGICAL_ID` int(11) DEFAULT NULL,
`PHYSICAL_ID` varchar(20) DEFAULT NULL,
`LAST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
`FIRST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
`LAST_RECONNECTION_DATETIME` datetime DEFAULT NULL,
`LAST_IMEI` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PRODUCT_INSTANCE_ID`),
KEY `CUSTOMER_PROFILE_ID` (`CUSTOMER_PROFILE_ID`),
KEY `FK_PRODUCT_INSTANCE_SPECIFICATION` (`PRODUCT_SPECIFICATION_ID`),
KEY `product_instance_ibfk_3` (`ORGANISATION_ID`),
KEY `product_instance_ibfk_4` (`CREATED_BY_CUSTOMER_PROFILE_ID`),
KEY `LOGICAL_ID` (`LOGICAL_ID`),
CONSTRAINT `product_instance_ibfk_1` FOREIGN KEY (`PRODUCT_SPECIFICATION_ID`) REFERENCES `product_specification` (`PRODUCT_SPECIFICATION_ID`),
CONSTRAINT `product_instance_ibfk_2` FOREIGN KEY (`CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile` (`CUSTOMER_PROFILE_ID`),
CONSTRAINT `product_instance_ibfk_3` FOREIGN KEY (`ORGANISATION_ID`) REFERENCES `organisation` (`ORGANISATION_ID`),
CONSTRAINT `product_instance_ibfk_4` FOREIGN KEY (`CREATED_BY_CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile` (`CUSTOMER_PROFILE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=308827 DEFAULT CHARSET=latin1;
As per additional info requested from Percona support:
there was no remaining `SmileDB`.`product_instance` or `SmileDB`.`_product_instance_new` table left ?
Correct - I double/triple checked. Same also on your slave database - i.e. the drop table was valid SQL and replicated over to the slave.
To rebuild the table we got the binlog section where pt-online-schema-change copied all the data to the _product_instance_new table and then did some magic with awk etc to change the binary log to valid SQL and ran it to populate a new table with the schema of _product_instance_new.
Here is the rest of the schema.
(root@localhost:pm)[SmileDB]>SHOW CREATE TABLE SmileDB.product_specification\G
*************************** 1. row ***************************
Table: product_specification
Create Table: CREATE TABLE `product_specification` (
`PRODUCT_SPECIFICATION_ID` int(11) NOT NULL,
`PRODUCT_NAME` varchar(1000) NOT NULL,
`PRODUCT_DESCRIPTION` varchar(2000) NOT NULL,
`AVAILABLE_FROM` datetime NOT NULL,
`AVAILABLE_TO` datetime NOT NULL,
`PROVISION_ROLES` varchar(2000) NOT NULL,
`SEGMENTS` varchar(2000) NOT NULL,
`REPORTING_TYPE` varchar(20) NOT NULL,
PRIMARY KEY (`PRODUCT_SPECIFICATION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
(root@localhost:pm)[SmileDB]>SHOW CREATE TABLE SmileDB.customer_profile\G
*************************** 1. row ***************************
Table: customer_profile
Create Table: CREATE TABLE `customer_profile` (
`CUSTOMER_PROFILE_ID` int(11) NOT NULL AUTO_INCREMENT,
`TITLE` varchar(20) DEFAULT NULL,
`FIRST_NAME` varchar(100) NOT NULL,
`MIDDLE_NAME` varchar(100) NOT NULL,
`LAST_NAME` varchar(100) NOT NULL,
`ID_NUMBER` varchar(50) NOT NULL,
`CREATED_DATETIME` datetime NOT NULL,
`DATE_OF_BIRTH` varchar(8) NOT NULL,
`GENDER` varchar(1) NOT NULL,
`LANGUAGE` varchar(50) NOT NULL,
`EMAIL_ADDRESS` varchar(200) NOT NULL,
`ALTERNATIVE_CONTACT_1` varchar(100) NOT NULL,
`ALTERNATIVE_CONTACT_2` varchar(100) NOT NULL,
`CLASSIFICATION` varchar(50) NOT NULL,
`VERSION` int(11) NOT NULL,
`STATUS` varchar(2) NOT NULL,
`SSO_IDENTITY` varchar(100) NOT NULL,
`SSO_DIGEST` varchar(100) NOT NULL,
`SSO_AUTH_ATTEMPTS` tinyint(4) NOT NULL,
`SSO_LOCK_EXPIRY` datetime DEFAULT NULL,
`ID_NUMBER_TYPE` varchar(50) NOT NULL,
`OPT_IN_LEVEL` int(11) NOT NULL,
`CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
`ACCOUNT_MANAGER_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
`MOTHERS_MAIDEN_NAME` varchar(100) NOT NULL,
`NATIONALITY` varchar(100) NOT NULL,
`PASSPORT_EXPIRY_DATE` varchar(8) NOT NULL,
`WAREHOUSE_ID` varchar(50) NOT NULL,
`UPDATED_DATETIME` datetime DEFAULT NULL,
`KYC_STATUS` varchar(1) DEFAULT NULL,
`REFERRAL_CODE` varchar(200) DEFAULT NULL,
PRIMARY KEY (`CUSTOMER_PROFILE_ID`),
UNIQUE KEY `SSO_IDENTITY` (`SSO_IDENTITY`),
KEY `FIRST_NAME` (`FIRST_NAME`),
KEY `LAST_NAME` (`LAST_NAME`),
KEY `ID_NUMBER` (`ID_NUMBER`),
KEY `EMAIL_ADDRESS` (`EMAIL_ADDRESS`)
) ENGINE=InnoDB AUTO_INCREMENT=41655 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
(root@localhost:pm)[SmileDB]>SHOW CREATE TABLE SmileDB.organisation\G
*************************** 1. row ***************************
Table: organisation
Create Table: CREATE TABLE `organisation` (
`ORGANISATION_ID` int(11) NOT NULL AUTO_INCREMENT,
`ORGANISATION_NAME` varchar(200) NOT NULL,
`ALTERNATIVE_CONTACT_1` varchar(100) NOT NULL,
`ALTERNATIVE_CONTACT_2` varchar(100) NOT NULL,
`EMAIL_ADDRESS` varchar(200) NOT NULL,
`ORGANISATION_TYPE` varchar(50) NOT NULL,
`TAX_NUMBER` varchar(50) NOT NULL,
`COMPANY_NUMBER` varchar(50) NOT NULL,
`SIZE` varchar(20) NOT NULL,
`INDUSTRY` varchar(50) NOT NULL,
`ACCOUNT_MANAGER_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
`CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
`STATUS` varchar(2) NOT NULL,
`VERSION` int(11) NOT NULL,
`CREATED_DATETIME` datetime NOT NULL,
`CREDIT_ACCOUNT_NUMBER` varchar(20) NOT NULL,
`MODIFICATION_ROLES` varchar(2000) NOT NULL,
`CHANNEL_CODE` varchar(20) NOT NULL,
PRIMARY KEY (`ORGANISATION_ID`),
UNIQUE KEY `ORGANISATION_NAME` (`ORGANISATION_NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=7259 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)