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

[Frederic Descamps]

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

[Steven Barre (slashterix)]

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 (slashterix)]

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

[Sveta Smirnova (svetasmirnova)]

Not repeatable with versions 2.2.20 and 3.0.2

[Shahriyar Rzayev (rzayev-sehriyar)]

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