pt-online-schema-change is not able to modify more than one time a table with constraint

Reported by Frederic Descamps on 2013-08-01
32
This bug affects 6 people
Affects Status Importance Assigned to Milestone
Percona Toolkit
Undecided
Unassigned

Bug Description

versions tested: 2.2.1 and 2.2.4

When a table with constraint is modified via pt-osc, it cannot be modified again with the tool.

The error message is :

Error swapping tables: Failed to find a unique old table name after serveral attempts. (<-- typo in the message)

How to reproduce:

mysql world < world_structure.sql

the table to modify is City:

CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

Let's add some fields:

pt-online-schema-change h=127.0.0.1,P=3306,u=root,p=,D="world",t=City --alter="add PolutionLevel int" --executeOperation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `world`.`City`...
Creating new table...
Created new table world._City_new OK.
Altering new table...
Altered `world`.`_City_new` OK.
2013-08-01T08:26:11 Creating triggers...
2013-08-01T08:26:11 Created triggers OK.
2013-08-01T08:26:11 Copying approximately 1 rows...
2013-08-01T08:26:11 Copied rows OK.
2013-08-01T08:26:11 Swapping tables...
2013-08-01T08:26:11 Swapped original and new tables OK.
2013-08-01T08:26:11 Dropping old table...
2013-08-01T08:26:11 Dropped old table `world`.`_City_old` OK.
2013-08-01T08:26:11 Dropping triggers...
2013-08-01T08:26:11 Dropped triggers OK.
Successfully altered `world`.`City`.

Now let's add a new one:

pt-online-schema-change h=127.0.0.1,P=3306,u=root,p=,D="world",t=City --alter="add PolutionIndice int" --execute
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `world`.`City`...
Creating new table...
Created new table world._City_new OK.
Altering new table...
Altered `world`.`_City_new` OK.
2013-08-01T08:28:01 Creating triggers...
2013-08-01T08:28:01 Created triggers OK.
2013-08-01T08:28:01 Copying approximately 1 rows...
2013-08-01T08:28:01 Copied rows OK.
2013-08-01T08:28:01 Swapping tables...
2013-08-01T08:28:02 Dropping triggers...
2013-08-01T08:28:02 Dropped triggers OK.
Not dropping the new table `world`.`_City_new` because --swap-tables failed. To drop the new table, execute:
DROP TABLE IF EXISTS `world`.`_City_new`;
`world`.`City` was not altered.
2013-08-01T08:28:02 Error swapping tables: 2013-08-01T08:28:02 Failed to find a unique old table name after serveral attempts.

To clean up, first verify that the original table `world`.`City` has not been modified or renamed, then drop the new table `world`.`_City_new` if it exists.

now an extra table is present:
+-----------------+
| Tables_in_world |
+-----------------+
| City |
| Country |
| CountryLanguage |
| _City_new |
+-----------------+

Expected result: _City_new should replace City

Frederic Descamps (lefred) wrote :
Changed in percona-toolkit:
status: New → Confirmed
importance: Undecided → Medium
milestone: none → 2.2.5
tags: added: pt-online-schema-change
Changed in percona-toolkit:
milestone: 2.2.5 → none
Changed in percona-toolkit:
milestone: none → 2.2.6
Changed in percona-toolkit:
milestone: 2.2.6 → none
importance: Medium → Undecided
Steven Barre (steven-8) wrote :

Here is an error I got via PTDEBUG

2014-03-31T18:30:19 Swapping tables...
# Retry:3655 12170 Try 1 of 10
# pt_online_schema_change:9637 12170 RENAME TABLE `bgelfer_cms`.`history_data` TO `bgelfer_cms`.`_history_data_old`, `bgelfer_cms`.`_history_data_new` TO `bgelfer_cms`.`history_data`
# Retry:3661 12170 Try code failed: DBD::mysql::db do failed: Table './bgelfer_cms/history_data' already exists [for Statement "RENAME TABLE `bgelfer_cms`.`history_data` TO `bgelfer_cms`.`_history_data_old`, `bgelfer_cms`.`_history_data_new` TO `bgelfer_cms`.`history_data`"] at /usr/bin/pt-online-schema-change line 9638.
#

Steven Barre (steven-8) wrote :

Oh, and I'm on CentOS 6.5 with MariaDB 5.5.32-MariaDB-log

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

Other bug subscribers

Bug attachments