pt-online-schema-change doesn't work with PXC and tables with foreign keys

Bug #1079867 reported by Daniel Nichter on 2012-11-16
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Invalid
Critical
Daniel Nichter

Bug Description

It seems like there's an issue with PXC and tables with foreign keys because tests fail like:

not ok 65 - Child table exit 0
# Failed test 'Child table exit 0'
# at basics.t line 152.
# got: '255'
# expected: '0'
ok 66 - Child table tables
ok 67 - Child table rows
ok 68 - Child table MAX(pk_col)
ok 69 - Child table AUTO_INCREMENT=16
ok 70 - Child table ALTER ENGINE=innodb
# Output from failed test:
# Altering `pt_osc`.`address`...
# Creating new table...
# CREATE TABLE `pt_osc`.`_address_new` (
# `address_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
# `address` varchar(50) NOT NULL,
# `city_id` smallint(5) unsigned NOT NULL,
# `postal_code` varchar(10) DEFAULT NULL,
# `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
# PRIMARY KEY (`address_id`),
# KEY `idx_fk_city_id` (`city_id`),
# CONSTRAINT `_fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
# ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
# Created new table pt_osc._address_new OK.
# Altering new table...
# ALTER TABLE `pt_osc`.`_address_new` ENGINE=INNODB
# Altered `pt_osc`.`_address_new` OK.
# Creating triggers...
# CREATE TRIGGER `pt_osc_pt_osc_address_del` AFTER DELETE ON `pt_osc`.`address` FOR EACH ROW DELETE IGNORE FROM `pt_osc`.`_address_new` WHERE `pt_osc`.`_address_new`.`address_id` <=> OLD.`address_id`
# CREATE TRIGGER `pt_osc_pt_osc_address_upd` AFTER UPDATE ON `pt_osc`.`address` FOR EACH ROW REPLACE INTO `pt_osc`.`_address_new` (`address_id`, `address`, `city_id`, `postal_code`, `last_update`) VALUES (NEW.`address_id`, NEW.`address`, NEW.`city_id`, NEW.`postal_code`, NEW.`last_update`)
# CREATE TRIGGER `pt_osc_pt_osc_address_ins` AFTER INSERT ON `pt_osc`.`address` FOR EACH ROW REPLACE INTO `pt_osc`.`_address_new` (`address_id`, `address`, `city_id`, `postal_code`, `last_update`) VALUES (NEW.`address_id`, NEW.`address`, NEW.`city_id`, NEW.`postal_code`, NEW.`last_update`)
# Created triggers OK.
# Copying approximately 5 rows...
# INSERT LOW_PRIORITY IGNORE INTO `pt_osc`.`_address_new` (`address_id`, `address`, `city_id`, `postal_code`, `last_update`) SELECT `address_id`, `address`, `city_id`, `postal_code`, `last_update` FROM `pt_osc`.`address` /*pt-online-schema-change 4192 copy table*/
# Dropping triggers...
# DROP TRIGGER IF EXISTS `pt_osc`.`pt_osc_pt_osc_address_del`;
# DROP TRIGGER IF EXISTS `pt_osc`.`pt_osc_pt_osc_address_upd`;
# DROP TRIGGER IF EXISTS `pt_osc`.`pt_osc_pt_osc_address_ins`;
# Dropped triggers OK.
# Dropping new table...
# DROP TABLE IF EXISTS `pt_osc`.`_address_new`;
# Dropped new table OK.
# `pt_osc`.`address` was not altered.
# No foreign keys reference `pt_osc`.`address`; ignoring --alter-foreign-keys-method.
# Error copying rows from `pt_osc`.`address` to `pt_osc`.`_address_new`: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`pt_osc`.`_address_new`, CONSTRAINT `_fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE) [for Statement "INSERT LOW_PRIORITY IGNORE INTO `pt_osc`.`_address_new` (`address_id`, `address`, `city_id`, `postal_code`, `last_update`) SELECT `address_id`, `address`, `city_id`, `postal_code`, `last_update` FROM `pt_osc`.`address` /*pt-online-schema-change 4192 copy table*/"] at /home/vagrant/percona-toolkit/trunk/bin/pt-online-schema-change line 9531.
#
not ok 71 - Drop-swap child exit 0
# Failed test 'Drop-swap child exit 0'
# at basics.t line 152.
# got: '255'
# expected: '0'
ok 72 - Drop-swap child tables
not ok 73 - Drop-swap child rows
# Failed test 'Drop-swap child rows'
# at basics.t line 168.
# Structures begin differing at:
# $got->[0][4] = '2012-11-16 11:26:54'
# $expected->[0][4] = Does not exist
ok 74 - Drop-swap child MAX(pk_col)
ok 75 - Drop-swap child AUTO_INCREMENT=16
not ok 76 - Drop-swap child ALTER DROP COLUMN=last_update
# Failed test 'Drop-swap child ALTER DROP COLUMN=last_update'
# at basics.t line 211.
# 'CREATE TABLE `address` (
# `address_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
# `address` varchar(50) NOT NULL,
# `city_id` smallint(5) unsigned NOT NULL,
# `postal_code` varchar(10) DEFAULT NULL,
# `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
# PRIMARY KEY (`address_id`),
# KEY `idx_fk_city_id` (`city_id`),
# CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
# ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1'
# matches '(?m-xis:^\s+`last_update`\s+)'
# Output from failed test:
# Altering `pt_osc`.`address`...
# Creating new table...
# CREATE TABLE `pt_osc`.`_address_new` (
# `address_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
# `address` varchar(50) NOT NULL,
# `city_id` smallint(5) unsigned NOT NULL,
# `postal_code` varchar(10) DEFAULT NULL,
# `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
# PRIMARY KEY (`address_id`),
# KEY `idx_fk_city_id` (`city_id`),
# CONSTRAINT `_fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
# ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
# Created new table pt_osc._address_new OK.
# Altering new table...
# ALTER TABLE `pt_osc`.`_address_new` DROP COLUMN last_update
# Altered `pt_osc`.`_address_new` OK.
# Creating triggers...
# CREATE TRIGGER `pt_osc_pt_osc_address_del` AFTER DELETE ON `pt_osc`.`address` FOR EACH ROW DELETE IGNORE FROM `pt_osc`.`_address_new` WHERE `pt_osc`.`_address_new`.`address_id` <=> OLD.`address_id`
# CREATE TRIGGER `pt_osc_pt_osc_address_upd` AFTER UPDATE ON `pt_osc`.`address` FOR EACH ROW REPLACE INTO `pt_osc`.`_address_new` (`address_id`, `address`, `city_id`, `postal_code`) VALUES (NEW.`address_id`, NEW.`address`, NEW.`city_id`, NEW.`postal_code`)
# CREATE TRIGGER `pt_osc_pt_osc_address_ins` AFTER INSERT ON `pt_osc`.`address` FOR EACH ROW REPLACE INTO `pt_osc`.`_address_new` (`address_id`, `address`, `city_id`, `postal_code`) VALUES (NEW.`address_id`, NEW.`address`, NEW.`city_id`, NEW.`postal_code`)
# Created triggers OK.
# Copying approximately 5 rows...
# INSERT LOW_PRIORITY IGNORE INTO `pt_osc`.`_address_new` (`address_id`, `address`, `city_id`, `postal_code`) SELECT `address_id`, `address`, `city_id`, `postal_code` FROM `pt_osc`.`address` /*pt-online-schema-change 4211 copy table*/
# Dropping triggers...
# DROP TRIGGER IF EXISTS `pt_osc`.`pt_osc_pt_osc_address_del`;
# DROP TRIGGER IF EXISTS `pt_osc`.`pt_osc_pt_osc_address_upd`;
# DROP TRIGGER IF EXISTS `pt_osc`.`pt_osc_pt_osc_address_ins`;
# Dropped triggers OK.
# Dropping new table...
# DROP TABLE IF EXISTS `pt_osc`.`_address_new`;
# Dropped new table OK.
# `pt_osc`.`address` was not altered.
# No foreign keys reference `pt_osc`.`address`; ignoring --alter-foreign-keys-method.
# Error copying rows from `pt_osc`.`address` to `pt_osc`.`_address_new`: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`pt_osc`.`_address_new`, CONSTRAINT `_fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE) [for Statement "INSERT LOW_PRIORITY IGNORE INTO `pt_osc`.`_address_new` (`address_id`, `address`, `city_id`, `postal_code`) SELECT `address_id`, `address`, `city_id`, `postal_code` FROM `pt_osc`.`address` /*pt-online-schema-change 4211 copy table*/"] at /home/vagrant/percona-toolkit/trunk/bin/pt-online-schema-change line 9531.
#
not ok 77 - Preserve all fks exit 0
# Failed test 'Preserve all fks exit 0'
# at basics.t line 152.
# got: '255'
# expected: '0'
not ok 78 - Preserve all fks tables
# Failed test 'Preserve all fks tables'
# at basics.t line 160.
# Structures begin differing at:
# $got->[0][0] = '_city_old'
# $expected->[0][0] = 'address'

tags: added: pxc
removed: percona-xtradb-cluster
Daniel Nichter (daniel-nichter) wrote :

This was a MyISAM-related issue:

# ALTER TABLE `pt_osc`.`_address_new` ENGINE=INNODB

The table being altered was MyISAM which has a few bugs that caused this. As of 2.1.8, pt-osc is beta PXC-certified, and limitations are noted in its docs, i.e. that MyISAM doesn't work.

Changed in percona-toolkit:
status: In Progress → Invalid

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers