Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1369

LP #1597497: pt-online-schema change resulted in lost table

    XMLWordPrintable

    Details

      Description

      **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)

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Smart Checklist