Percona Toolkit moved to https://jira.percona.com/projects/PT

pt-online-schema change resulted in lost table

Bug #1597497 reported by Paul Carter-Brown on 2016-06-29

This bug affects 1 person

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

Tags:

Revision history for this message

Paul Carter-Brown (paulcb) wrote on 2016-06-29:

Aplogies,

here is the correct to-be version of table we were trying to change 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`),
  KEY `_product_instance_ibfk_5` (`CREATED_BY_ORGANISATION_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`),
  CONSTRAINT `product_instance_ibfk_5` FOREIGN KEY (`CREATED_BY_ORGANISATION_ID`) REFERENCES `organisation` (`ORGANISATION_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=689641 DEFAULT CHARSET=latin1;

Aplogies,

here is the correct to-be version of table we were trying to change 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`),
  KEY `_product_instance_ibfk_5` (`CREATED_BY_ORGANISATION_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`),
  CONSTRAINT `product_instance_ibfk_5` FOREIGN KEY (`CREATED_BY_ORGANISATION_ID`) REFERENCES `organisation` (`ORGANISATION_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=689641 DEFAULT CHARSET=latin1;

Jericho Rivera (jericho-rivera) on 2016-06-30

tags:	added: i70786
tags:	added: pt-online-schema-change

Revision history for this message

Przemek (pmalkowski) wrote on 2016-07-01:

Download full text (3.6 KiB)

I could not reproduce the problem of both new and old tables dropped, which pt-osc and MySQL version are you using?

~# pt-online-schema-change --version
pt-online-schema-change 2.2.16

~# pt-online-schema-change --user root --dry-run --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
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
No foreign keys reference `SmileDB`.`product_instance`; ignoring --alter-foreign-keys-method.
Starting a dry run. `SmileDB`.`product_instance` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table SmileDB._product_instance_new OK.
Altering new table...
Altered `SmileDB`.`_product_instance_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2016-07-01T15:18:52 Dropping new table...
2016-07-01T15:18:52 Dropped new table OK.
Dry run complete. `SmileDB`.`product_instance` was not altered.

~# 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" D=SmileDB,t=product_instance
No slaves found. See --recursion-method if host przemek-Latitude-E7450 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
No foreign keys reference `SmileDB`.`product_instance`; ignoring --alter-foreign-keys-method.
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-01T15:19:04 Creating triggers...
2016-07-01T15:19:04 Created triggers OK.
2016-07-01T15:19:04 Copying approximately 1 rows...
2016-07-01T15:19:04 Copied rows OK.
2016-07-01T15:19:04 Analyzing new table...
2016-07-01T15:19:04 Swapping tables...
2016-07-01T15:19:05 Dropping triggers...
2016-07-01T15:19:05 Dropped triggers OK.
Not dropping the new table `SmileDB`.`_product_instance_new` because --swap-tables failed. To drop the new table, execute:
DROP TABLE IF EXISTS `SmileDB`.`_product_instance_new`;
`SmileDB`.`product_instance` was not altered.
2016-07-01T15:19:05 Error swapping tables: 2016-07-01T15:19:05 Failed to find a unique old table name after serveral attem...

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

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.

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.

Revision history for this message

Paul Carter-Brown (paulcb) wrote on 2016-07-23: Re: [Bug 1597497] Re: pt-online-schema change resulted in lost table

Download full text (16.3 KiB)

Hi,

Is there any option I could have passed that would have prevented the error?

On 23 Jul 2016 00:45, "Sveta Smirnova" <email address hidden> wrote:

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
>

Revision history for this message

Shahriyar Rzayev (rzayev-sehriyar) wrote on 2018-01-24:

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1369

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Bug attachments

Dump I used to repeat this Edit

Add attachment

Remote bug watches

Bug watches keep track of this bug in other bug trackers.

Changed in percona-toolkit:
status:	New → Incomplete

Changed in percona-toolkit:
status:	Incomplete → New

Changed in percona-toolkit:
status:	New → Confirmed