Weird underscores on constraints when using pt-online-schema-change

Recently i was working on DDL request ALTER on a production table.Since it is production we usually prefer to work  
with percona toolkit tool pt-online-schema-change. This tool is great tool for live production servers to make changes in
your table without affecting the application access to that table. More info : pt-online-schema-change

I was doing a change column on that table which had foreign keys in it.

(I have recreated the same issue in my local which  happened on our production) .

I have used our sample database world

Below is the table structure :-

CREATE TABLE `countrylanguage` (

`CountryCode` char(3) NOT NULL DEFAULT '',

`Language` char(30) NOT NULL DEFAULT '',

`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',

`Percentage` float(4,1) NOT NULL DEFAULT '0.0',

PRIMARY KEY (`CountryCode`,`Language`),

KEY `CountryCode` (`CountryCode`),

CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

The change what i did was increasing the data type value  for column `Language`

From char(30) to char(35)

Below was my pt-osc command

pt-online-schema-change --recursion-method=none --alter-foreign-keys-method=drop_swap --port=3306 --alter "CHANGE COLUMN Language Language char(35) NOT NULL DEFAULT ''" D=world,t=countrylanguage,h=localhost --execute

After execution of above command successfully, i checked my table structure

CREATE TABLE `countrylanguage` (

`CountryCode` char(3) NOT NULL DEFAULT '',

`Language` char(35) NOT NULL DEFAULT '',

`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',

`Percentage` float(4,1) NOT NULL DEFAULT '0.0',

PRIMARY KEY (`CountryCode`,`Language`),

KEY `CountryCode` (`CountryCode`),

CONSTRAINT `_countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

If you see the above structure you can notice something different. Yes the foreign key name

CONSTRAINT `_countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`)

Most of the people may know the working procedure of pt-osc.  In simple words it will create a new table ,
do the changes , copy the records from present table to the new table in small chunks.
Once all the records are copied it will replace the new table with current one and drop that table.
In between any modifications are happening to the table will be taken care by the triggers which are created during the run.

Here the new table will be created in  the format _tablename_new  where tablename is the actual table name.

Similarly, MySQL  will not allow to store two CONSTRAINTS with same name ,
so in the new table it will create the CONSTRAINT name starting with underscore (_).
That’s what we have an underscore in the modified table.

But this scenario will not happen if you are doing a basic ALTER.

Actual problem didn’t came here , what happens if you want to do a change on the same table again.
Here comes the actual scenario.

I will now convert it from char(35) to char(30)

After execution of pt-osc , my table structure:-

CREATE TABLE `countrylanguage` (

`CountryCode` char(3) NOT NULL DEFAULT '',

`Language` char(30) NOT NULL DEFAULT '',

`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',

`Percentage` float(4,1) NOT NULL DEFAULT '0.0',

PRIMARY KEY (`CountryCode`,`Language`),

KEY `CountryCode` (`CountryCode`),

CONSTRAINT `__countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

Did you notice the CONSTRAINT name ? One more underscore is added ,

because logic NO two CONSTRAINTS can have same name.

What if if i run one more pt-osc from char(30) to char(35)

Table Structure:

CREATE TABLE `countrylanguage` (
 `CountryCode` char(3) NOT NULL DEFAULT '',
 `Language` char(35) NOT NULL DEFAULT '',
 `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
 `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
 PRIMARY KEY (`CountryCode`,`Language`),
 KEY `CountryCode` (`CountryCode`),
 CONSTRAINT `___countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Ohh it keeps growing, doing multiple pt-osc one after the other (after 3 tries)

CREATE TABLE `countrylanguage` (

`CountryCode` char(3) NOT NULL DEFAULT '',

`Language` char(35) NOT NULL DEFAULT '',

`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',

`Percentage` float(4,1) NOT NULL DEFAULT '0.0',

PRIMARY KEY (`CountryCode`,`Language`),

KEY `CountryCode` (`CountryCode`),

CONSTRAINT `______countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

So finally all my CONSTRAINTS were ,

CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

CONSTRAINT `_countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

CONSTRAINT `__countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

CONSTRAINT `___countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

Below is the screen shot for the same :-

It’s weird the CONSTRAINT names keeps adding one extra underscore  which is not a good behaviour.

What will happen if it is busy table where lots of alter keeps coming 😟😟😟

After this incident of underscores keep adding on every new ALTER i checked in my local virtual machine.

To my surprise max underscore created was 2 , when the CONSTRAINT reaches 2 underscores  
in next pt-osc it will remove all underscores for latest version.

Finally all my CONSTRAINTS were ,

CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

CONSTRAINT `_countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

CONSTRAINT `__countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

I was wondering what is it , then i figured it was due to version difference, Percona has fixed this bug in newer versions.

In production we were having version 2.2.11

[root@mydbtest ~]# pt-online-schema-change --version
pt-online-schema-change 2.2.11

My local was having 3.06

[root@mydbtest ~]# pt-online-schema-change --version
pt-online-schema-change 3.0.7

This bug is till in version 2.2.14, From 2.2.15-2  it was fixed.

To test the above scenario i have installed 2.2.11 in my local and recreated the issue.

So please upgrade you toolkit to latest versions.