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

Reported by Daniel Nichter on 2012-11-16
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
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
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers