pt-online-schema-change doesn't work with PXC and tables with foreign keys
| 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`
# Creating new table...
# CREATE TABLE `pt_osc`
# `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`
# Altered `pt_osc`
# Creating triggers...
# CREATE TRIGGER `pt_osc_
# CREATE TRIGGER `pt_osc_
# CREATE TRIGGER `pt_osc_
# Created triggers OK.
# Copying approximately 5 rows...
# INSERT LOW_PRIORITY IGNORE INTO `pt_osc`
# Dropping triggers...
# DROP TRIGGER IF EXISTS `pt_osc`
# DROP TRIGGER IF EXISTS `pt_osc`
# DROP TRIGGER IF EXISTS `pt_osc`
# Dropped triggers OK.
# Dropping new table...
# DROP TABLE IF EXISTS `pt_osc`
# Dropped new table OK.
# `pt_osc`.`address` was not altered.
# No foreign keys reference `pt_osc`.`address`; ignoring --alter-
# Error copying rows from `pt_osc`.`address` to `pt_osc`
#
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:
# Output from failed test:
# Altering `pt_osc`
# Creating new table...
# CREATE TABLE `pt_osc`
# `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`
# Altered `pt_osc`
# Creating triggers...
# CREATE TRIGGER `pt_osc_
# CREATE TRIGGER `pt_osc_
# CREATE TRIGGER `pt_osc_
# Created triggers OK.
# Copying approximately 5 rows...
# INSERT LOW_PRIORITY IGNORE INTO `pt_osc`
# Dropping triggers...
# DROP TRIGGER IF EXISTS `pt_osc`
# DROP TRIGGER IF EXISTS `pt_osc`
# DROP TRIGGER IF EXISTS `pt_osc`
# Dropped triggers OK.
# Dropping new table...
# DROP TABLE IF EXISTS `pt_osc`
# Dropped new table OK.
# `pt_osc`.`address` was not altered.
# No foreign keys reference `pt_osc`.`address`; ignoring --alter-
# Error copying rows from `pt_osc`.`address` to `pt_osc`
#
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 | #1 |
| Changed in percona-toolkit: | |
| status: | In Progress → Invalid |
| Shahriyar Rzayev (rzayev-sehriyar) wrote | #2 |
This was a MyISAM-related issue:
# ALTER TABLE `pt_osc`
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.