LP #1542291: pt-osc rebuild constraint can be faster since 5.6

**Reported in Launchpad by DavidDucos last update 17-08-2016 17:54:24

Since 5.6, it is possible to use inplace algorithm for alter tables and this can be combined with FOREIGN_KEY_CHECKS=OFF.

This means that could be possible to rebuild the constraint in child tables pretty fast because it does not rebuild the table when you create the constraint.

I attached a patch for version 2.2.16 just for testing but I think that we need to change the logic because we need to determine mysql version.

This is my test case:

CREATE TABLE `parent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fk_child` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `child_ibfk_1` (`fk_child`),
CONSTRAINT `parent_ibfk_1` FOREIGN KEY (`fk_child`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into parent values (); – Execute it several times

insert into child (fk_child) select * from parent;

insert into child (fk_child) select fk_child from child; – Execute it several times

• This is for test that it takes times:
  alter table child add CONSTRAINT `parent_ibfk_2` FOREIGN KEY (`fk_child`) REFERENCES `parent` (`id`);

alter table child drop FOREIGN KEY `parent_ibfk_2`;

• This is for test that it is really fast:
  set foreign_key_checks=off;

alter table child add CONSTRAINT `parent_ibfk_2` FOREIGN KEY (`fk_child`) REFERENCES `parent` (`id`);

alter table child drop FOREIGN KEY `parent_ibfk_2`;

set foreign_key_checks=on;

• This is the error when you try to do it inplace with foreign_key_checks=ON :
  alter table child add CONSTRAINT `parent_ibfk_2` FOREIGN KEY (`fk_child`) REFERENCES `parent` (`id`), algorithm= inplace;
  ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY.

I used this command to see if the inodes changed at os level:

! ls -li /var/lib/mysql/test/