pt-online-schema change resulted in lost table
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Confirmed
|
Undecided
|
Unassigned |
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-
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_
Child tables:
`SmileDB`
Will automatically choose the method to update foreign keys.
Altering `SmileDB`
Creating new table...
Created new table SmileDB.
Waiting forever for new table `SmileDB`
Altering new table...
Altered `SmileDB`
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`
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`
Error updating foreign key constraints: 2016-06-29T18:34:00 DBD::mysql::db do failed: Table './SmileDB/
Here is the schema before the change:
CREATE TABLE `product_instance` (
`PRODUCT_
`PRODUCT_
`CUSTOMER_
`ORGANISATION_ID` int(11) NOT NULL,
`STATUS` char(2) NOT NULL,
`SEGMENT` varchar(20) NOT NULL,
`CREATED_
`CREATED_
`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_
`FIRST_
`LAST_
`LAST_IMEI` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PRODUCT_
KEY `CUSTOMER_
KEY `FK_PRODUCT_
KEY `product_
KEY `product_
KEY `LOGICAL_ID` (`LOGICAL_ID`),
CONSTRAINT `_product_
CONSTRAINT `_product_
CONSTRAINT `_product_
CONSTRAINT `_product_
) ENGINE=InnoDB AUTO_INCREMENT=
And what we were changing it to:
CREATE TABLE `product_instance` (
`PRODUCT_
`PRODUCT_
`CUSTOMER_
`ORGANISATION_ID` int(11) NOT NULL,
`STATUS` char(2) NOT NULL,
`SEGMENT` varchar(20) NOT NULL,
`CREATED_
`CREATED_
`CREATED_
`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_
`FIRST_
`LAST_
`LAST_IMEI` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PRODUCT_
KEY `CUSTOMER_
KEY `FK_PRODUCT_
KEY `product_
KEY `product_
KEY `LOGICAL_ID` (`LOGICAL_ID`),
CONSTRAINT `product_
CONSTRAINT `product_
CONSTRAINT `product_
CONSTRAINT `product_
) ENGINE=InnoDB AUTO_INCREMENT=
As per additional info requested from Percona support:
there was no remaining `SmileDB`
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-
Here is the rest of the schema.
(root@localhost
*******
Table: product_
Create Table: CREATE TABLE `product_
`PRODUCT_
`PRODUCT_NAME` varchar(1000) NOT NULL,
`PRODUCT_
`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_
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
(root@localhost
*******
Table: customer_profile
Create Table: CREATE TABLE `customer_profile` (
`CUSTOMER_
`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_
`ALTERNATIVE_
`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_
`ACCOUNT_
`MOTHERS_
`NATIONALITY` varchar(100) NOT NULL,
`PASSPORT_
`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_
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=
1 row in set (0.00 sec)
(root@localhost
*******
Table: organisation
Create Table: CREATE TABLE `organisation` (
`ORGANISATION_ID` int(11) NOT NULL AUTO_INCREMENT,
`ORGANISATION_NAME` varchar(200) NOT NULL,
`ALTERNATIVE_
`ALTERNATIVE_
`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_
`CREATED_
`STATUS` varchar(2) NOT NULL,
`VERSION` int(11) NOT NULL,
`CREATED_DATETIME` datetime NOT NULL,
`CREDIT_
`MODIFICATION_
`CHANNEL_CODE` varchar(20) NOT NULL,
PRIMARY KEY (`ORGANISATION_
UNIQUE KEY `ORGANISATION_NAME` (`ORGANISATION_
) ENGINE=InnoDB AUTO_INCREMENT=7259 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
tags: | added: i70786 |
tags: | added: pt-online-schema-change |
Changed in percona-toolkit: | |
status: | New → Incomplete |
Changed in percona-toolkit: | |
status: | Incomplete → New |
Changed in percona-toolkit: | |
status: | New → Confirmed |
Aplogies,
here is the correct to-be version of table we were trying to change to:
CREATE TABLE `product_instance` ( INSTANCE_ ID` int(11) NOT NULL AUTO_INCREMENT, SPECIFICATION_ ID` int(11) NOT NULL, PROFILE_ ID` int(11) NOT NULL, BY_CUSTOMER_ PROFILE_ ID` int(11) NOT NULL, BY_ORGANISATION _ID` int(11) DEFAULT NULL, DATETIME` datetime NOT NULL, ACTIVITY_ DATETIME` datetime DEFAULT NULL, ACTIVITY_ DATETIME` datetime DEFAULT NULL, RECONNECTION_ DATETIME` datetime DEFAULT NULL, INSTANCE_ ID`), PROFILE_ ID` (`CUSTOMER_ PROFILE_ ID`), INSTANCE_ SPECIFICATION` (`PRODUCT_ SPECIFICATION_ ID`), instance_ ibfk_3` (`ORGANISATION_ ID`), instance_ ibfk_4` (`CREATED_ BY_CUSTOMER_ PROFILE_ ID`), instance_ ibfk_5` (`CREATED_ BY_ORGANISATION _ID`), instance_ ibfk_1` FOREIGN KEY (`PRODUCT_ SPECIFICATION_ ID`) REFERENCES `product_ specification` (`PRODUCT_ SPECIFICATION_ ID`), instance_ ibfk_2` FOREIGN KEY (`CUSTOMER_ PROFILE_ ID`) REFERENCES `customer_profile` (`CUSTOMER_ PROFILE_ ID`), instance_ ibfk_3` FOREIGN KEY (`ORGANISATION_ID`) REFERENCES `organisation` (`ORGANISATION_ ID`), instance_ ibfk_4` FOREIGN KEY (`CREATED_ BY_CUSTOMER_ PROFILE_ ID`) REFERENCES `customer_profile` (`CUSTOMER_ PROFILE_ ID`), instance_ ibfk_5` FOREIGN KEY (`CREATED_ BY_ORGANISATION _ID`) REFERENCES `organisation` (`ORGANISATION_ID`) 689641 DEFAULT CHARSET=latin1;
`PRODUCT_
`PRODUCT_
`CUSTOMER_
`ORGANISATION_ID` int(11) NOT NULL,
`STATUS` char(2) NOT NULL,
`SEGMENT` varchar(20) NOT NULL,
`CREATED_
`CREATED_
`CREATED_
`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_
`FIRST_
`LAST_
`LAST_IMEI` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PRODUCT_
KEY `CUSTOMER_
KEY `FK_PRODUCT_
KEY `product_
KEY `product_
KEY `LOGICAL_ID` (`LOGICAL_ID`),
KEY `_product_
CONSTRAINT `product_
CONSTRAINT `product_
CONSTRAINT `product_
CONSTRAINT `product_
CONSTRAINT `product_
) ENGINE=InnoDB AUTO_INCREMENT=