pt-online-schema-change foreign key

[Kenny Gryp]

When you run pt-osc and it fails, in some cases the _new_table file will still exist (mind the underscore file).
The pt-osc knows that and actually creates __new_table the next run. (That's why we love percona toolkit!)

A similar mechanism happens when you have foreign keys, as they are unique in name as well.
An underscore is added to the foreign key name when you create such table.

HOWEVER, when that _new_table is still there, the foreign key name with the underscore still exists. pt-osc does not try to find a unique foreign key name by adding for example another underscore.

In the end you get a very weird error 121 which actually means that there's a duplicate FK name.

Please talk to me if you need a test. As it's quite late, I have no energy left to perform tests, apologies! :)

[Nilnandan Joshi (nilnandan-joshi)]

Verified.

mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table test_child \G
*************************** 1. row ***************************
       Table: test_child
Create Table: CREATE TABLE `test_child` (
  `id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  KEY `id` (`id`),
  CONSTRAINT `test_child_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>
[root@localhost ~]# pt-online-schema-change --no-drop-new-table --alter "ADD COLUMN c2 INT" D=nil,t=test_child --execute
No slaves found. See --recursion-method if host localhost.localdomain has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 `nil`.`test_child`...
Creating new table...
Created new table nil._test_child_new OK.
Altering new table...
Altered `nil`.`_test_child_new` OK.
Not dropping the new table `nil`.`_test_child_new` because --no-drop-new-table was specified. To drop the new table, execute:
DROP TABLE IF EXISTS `nil`.`_test_child_new`;
`nil`.`test_child` was not altered.
The new table `nil`.`_test_child_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
[root@localhost ~]#

[root@localhost ~]# ls -al /var/lib/mysql/nil/
total 508
drwx------. 2 mysql mysql 4096 Mar 10 11:28 .
drwxrwxrwx. 6 mysql mysql 4096 Mar 10 11:00 ..
-rw-rw----. 1 mysql mysql 65 Mar 10 11:00 db.opt
-rw-rw----. 1 mysql mysql 8586 Mar 10 11:25 nil_tab.frm
-rw-rw----. 1 mysql mysql 98304 Mar 10 11:25 nil_tab.ibd
-rw-rw----. 1 mysql mysql 8596 Mar 10 11:02 test_child.frm
-rw-rw----. 1 mysql mysql 131072 Mar 10 11:12 test_child.ibd
-rw-rw----. 1 mysql mysql 8622 Mar 10 11:28 _test_child_new.frm
-rw-rw----. 1 mysql mysql 131072 Mar 10 11:28 _test_child_new.ibd
-rw-rw----. 1 mysql mysql 8586 Mar 10 11:01 test.frm
-rw-rw----. 1 mysql mysql 98304 Mar 10 11:11 test.ibd
[root@localhost ~]#
[root@localhost ~]# pt-online-schema-change --no-drop-new-table --alter "ADD COLUMN c2 INT" D=nil,t=test_child --execute
No slaves found. See --recursion-method if host localhost.localdomain has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 `nil`.`test_child`...
Creating new table...
`nil`.`test_child` was not altered.
Error creating new table: DBD::mysql::db do failed: Can't write; duplicate key in table '__test_child_new' [for Statement "CREATE TABLE `nil`.`__test_child_new` (
  `id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAUL...

[Laurent Minost (lolomin)]

Hi,

I can also confirm this bug !
Verified and confirmed with latest Percona Toolkit version at the time of writing : v3.0.3.

This bug is very blocking for us as we now have some tables with a lot of FK named __<name>_ibfk_XX where <name> is the table name and XX is the auto incremented number of the FK.

Problem is that we also have some tables on which we can see a FK 1 named __table_ibfk_1 (two leading underscore) and another FK 2 named _table_ibfk_1 (only one leading underscore), so when we need to use pt-online-schema-change on this table, the tool failed with a "can't create table (errno 121)" error as it cannot create the new table because of the new name for FK2 in the destination table that is already existing for FK1 in the source table ...
If we try to create the new schema for the temporary table and issue a SHOW ENGINE INNODB STATUS, then we can see :

170531 11:34:09 Error in foreign key constraint creation for table `db`.`_table_new`.
A foreign key constraint of name `db`.`__table_ibfk_1`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)

How can we get out of this problem please as we need to use the tool to ALTER the table, and renaming the FK is needing and ALTER to be done on the table :p

Regards,

Laurent

[Jervin R (revin)]

Laurent,

Are the existing FK in the tables not from pt-osc but from your own processes? Because of this bug, this would definitely be a blocker for you but there is a workaround. You can use hooks to create the right table and triggers with the correct FK name close to what is mentioned in this blog post.

https://www.percona.com/blog/2017/03/21/dropping-foreign-key-constraint-using-pt-online-schema-change-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-1271