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.

socat for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

When i tried to install percona xtradb cluster 5.6 on my centos environment i got multiple errors as below.

[root@dbnode2 ~]# yum install Percona-XtraDB-Cluster-56

Failed to set locale, defaulting to C

Loaded plugins: fastestmirror

Setting up Install Process

Determining fastest mirrors

 * base: centos.myfahim.com

 * extras: centos.myfahim.com

 * updates: mirrors.viethosting.com

percona-release-noarch                                                                                                                                     | 2.5 kB     00:00     

percona-release-noarch/primary_db                                                                                                                          |  20 kB     00:00     

percona-release-x86_64                                                                                                                                     | 2.9 kB     00:00     

percona-release-x86_64/primary_db                                                                                                                          | 895 kB     00:00     

Resolving Dependencies

--> Running transaction check

---> Package Percona-XtraDB-Cluster-56.x86_64 1:5.6.38-26.23.1.el6 will be installed

--> Processing Dependency: Percona-XtraDB-Cluster-client-56 = 1:5.6.38-26.23.1.el6 for package: 1:Percona-XtraDB-Cluster-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: Percona-XtraDB-Cluster-server-56 = 1:5.6.38-26.23.1.el6 for package: 1:Percona-XtraDB-Cluster-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: Percona-XtraDB-Cluster-galera-3 = 3.23-1.el6 for package: 1:Percona-XtraDB-Cluster-56-5.6.38-26.23.1.el6.x86_64

--> Running transaction check

---> Package Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.38-26.23.1.el6 will be installed

--> Processing Dependency: perl-DBI for package: 1:Percona-XtraDB-Cluster-client-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: /usr/bin/perl for package: 1:Percona-XtraDB-Cluster-client-56-5.6.38-26.23.1.el6.x86_64

---> Package Percona-XtraDB-Cluster-galera-3.x86_64 0:3.23-1.el6 will be installed

---> Package Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.38-26.23.1.el6 will be installed

--> Processing Dependency: Percona-XtraDB-Cluster-shared-56 = 1:5.6.38-26.23.1.el6 for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: percona-xtrabackup >= 2.2.5 for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: libnuma.so.1(libnuma_1.2)(64bit) for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: socat for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: lsof for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: perl-DBD-MySQL for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: libnuma.so.1(libnuma_1.1)(64bit) for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: rsync for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: libnuma.so.1()(64bit) for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Running transaction check

---> Package Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.38-26.23.1.el6 will be installed

--> Processing Dependency: socat for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

---> Package Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.38-26.23.1.el6 will be installed

---> Package lsof.x86_64 0:4.82-5.el6 will be installed

---> Package numactl.x86_64 0:2.0.9-2.el6 will be installed

---> Package percona-xtrabackup.x86_64 0:2.3.10-1.el6 will be installed

--> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-2.3.10-1.el6.x86_64

---> Package perl.x86_64 4:5.10.1-144.el6 will be installed

--> Processing Dependency: perl-libs = 4:5.10.1-144.el6 for package: 4:perl-5.10.1-144.el6.x86_64

--> Processing Dependency: perl-libs for package: 4:perl-5.10.1-144.el6.x86_64

--> Processing Dependency: perl(version) for package: 4:perl-5.10.1-144.el6.x86_64

--> Processing Dependency: perl(Pod::Simple) for package: 4:perl-5.10.1-144.el6.x86_64

--> Processing Dependency: perl(Module::Pluggable) for package: 4:perl-5.10.1-144.el6.x86_64

--> Processing Dependency: libperl.so()(64bit) for package: 4:perl-5.10.1-144.el6.x86_64

---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed

---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed

---> Package rsync.x86_64 0:3.0.6-12.el6 will be installed

--> Running transaction check

---> Package Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.38-26.23.1.el6 will be installed

--> Processing Dependency: socat for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

---> Package percona-xtrabackup.x86_64 0:2.3.10-1.el6 will be installed

--> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-2.3.10-1.el6.x86_64

---> Package perl-Module-Pluggable.x86_64 1:3.90-144.el6 will be installed

---> Package perl-Pod-Simple.x86_64 1:3.13-144.el6 will be installed

--> Processing Dependency: perl(Pod::Escapes) >= 1.04 for package: 1:perl-Pod-Simple-3.13-144.el6.x86_64

---> Package perl-libs.x86_64 4:5.10.1-144.el6 will be installed

---> Package perl-version.x86_64 3:0.77-144.el6 will be installed

--> Running transaction check

---> Package Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.38-26.23.1.el6 will be installed

--> Processing Dependency: socat for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

---> Package percona-xtrabackup.x86_64 0:2.3.10-1.el6 will be installed

--> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-2.3.10-1.el6.x86_64

---> Package perl-Pod-Escapes.x86_64 1:1.04-144.el6 will be installed

--> Finished Dependency Resolution

Error: Package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64 (percona-release-x86_64)

           Requires: socat

Error: Package: percona-xtrabackup-2.3.10-1.el6.x86_64 (percona-release-x86_64)

           Requires: libev.so.4()(64bit)

 You could try using --skip-broken to work around the problem

 You could try running: rpm -Va --nofiles --nodigest

I was trying to install from the repo, to fix above all errors we need to install socat .

To do so we can install it from epel.

You can download the Epel rpm file from here,  

[root@dbnode2 ~]# wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

--2018-02-07 12:15:35--  http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

Resolving dl.fedoraproject.org... 209.132.181.25, 209.132.181.23, 209.132.181.24

Connecting to dl.fedoraproject.org|209.132.181.25|:80... connected.

HTTP request sent, awaiting response... 200 OK

Length: 14540 (14K) [application/x-rpm]

Saving to: `epel-release-6-8.noarch.rpm'

100%[========================================================================================================================================>] 14,540      --.-K/s   in 0.007s  

2018-02-07 12:15:37 (2.11 MB/s) - `epel-release-6-8.noarch.rpm' saved [14540/14540]

[root@dbnode2 ~]# yum install epel-release-6-8.noarch.rpm 

Failed to set locale, defaulting to C

Loaded plugins: fastestmirror

Setting up Install Process

Examining epel-release-6-8.noarch.rpm: epel-release-6-8.noarch

Marking epel-release-6-8.noarch.rpm to be installed

Loading mirror speeds from cached hostfile

 * base: centos.myfahim.com

 * extras: centos.myfahim.com

 * updates: mirrors.viethosting.com

Resolving Dependencies

--> Running transaction check

---> Package epel-release.noarch 0:6-8 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================================================================================================

 Package                                    Arch                                 Version                             Repository                                              Size

==================================================================================================================================================================================

Installing:

 epel-release                               noarch                               6-8                                 /epel-release-6-8.noarch                                22 k

Transaction Summary

==================================================================================================================================================================================

Install       1 Package(s)

Total size: 22 k

Installed size: 22 k

Is this ok [y/N]: y

Downloading Packages:

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

  Installing : epel-release-6-8.noarch                                                                                                                                        1/1 

  Verifying  : epel-release-6-8.noarch                                                                                                                                        1/1 

Installed:

  epel-release.noarch 0:6-8                                                                                                                                                       

Complete!

[root@dbnode2 ~]# yum install socat

Failed to set locale, defaulting to C

Loaded plugins: fastestmirror

Setting up Install Process

Loading mirror speeds from cached hostfile

epel/metalink                                                                                                                                              | 7.5 kB     00:00     

 * base: centos.myfahim.com

 * epel: mirror.lzu.edu.cn

 * extras: centos.myfahim.com

 * updates: mirrors.viethosting.com

epel                                                                                                                                                       | 4.7 kB     00:00     

epel/primary_db                                                                                                                                            | 6.0 MB     00:37     

Resolving Dependencies

--> Running transaction check

---> Package socat.x86_64 0:1.7.2.3-1.el6 will be installed

--> Processing Dependency: libreadline.so.5()(64bit) for package: socat-1.7.2.3-1.el6.x86_64

--> Running transaction check

---> Package compat-readline5.x86_64 0:5.2-17.1.el6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================================================================================================

 Package                                          Arch                                   Version                                       Repository                            Size

==================================================================================================================================================================================

Installing:

 socat                                            x86_64                                 1.7.2.3-1.el6                                 epel                                 246 k

Installing for dependencies:

 compat-readline5                                 x86_64                                 5.2-17.1.el6                                  base                                 130 k

Transaction Summary

==================================================================================================================================================================================

Install       2 Package(s)

Total download size: 375 k

Installed size: 1.2 M

Is this ok [y/N]: y

Downloading Packages:

(1/2): compat-readline5-5.2-17.1.el6.x86_64.rpm                                                                                                            | 130 kB     00:00     

(2/2): socat-1.7.2.3-1.el6.x86_64.rpm                                                                                                                      | 246 kB     00:01     

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total                                                                                                                                             114 kB/s | 375 kB     00:03     

warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

Importing GPG key 0x0608B895:

 Userid : EPEL (6) <epel@fedoraproject.org>

 Package: epel-release-6-8.noarch (@/epel-release-6-8.noarch)

 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

Is this ok [y/N]: y

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

  Installing : compat-readline5-5.2-17.1.el6.x86_64                                                                                                                           1/2 

  Installing : socat-1.7.2.3-1.el6.x86_64                                                                                                                                     2/2 

  Verifying  : socat-1.7.2.3-1.el6.x86_64                                                                                                                                     1/2 

  Verifying  : compat-readline5-5.2-17.1.el6.x86_64                                                                                                                           2/2 

Installed:

  socat.x86_64 0:1.7.2.3-1.el6                                                                                                                                                    

Dependency Installed:

  compat-readline5.x86_64 0:5.2-17.1.el6                                                                                                                                          

Complete!

[root@dbnode2 ~]# 

On successful installation of socat  i was able to install xtradb cluster without any issues.

[root@dbnode2 ~]# yum install Percona-XtraDB-Cluster-56

Failed to set locale, defaulting to C

Loaded plugins: fastestmirror

Setting up Install Process

Loading mirror speeds from cached hostfile

epel/metalink                                                                                                                                              | 7.5 kB     00:00     

 * base: centos.myfahim.com

 * epel: mirror.lzu.edu.cn

 * extras: centos.myfahim.com

 * updates: mirror.ehost.vn

base                                                                                                                                                       | 3.7 kB     00:00     

extras                                                                                                                                                     | 3.4 kB     00:00     

percona-release-noarch                                                                                                                                     | 2.5 kB     00:00     

percona-release-x86_64                                                                                                                                     | 2.9 kB     00:00     

updates                                                                                                                                                    | 3.4 kB     00:00     

Resolving Dependencies

--> Running transaction check

---> Package Percona-XtraDB-Cluster-56.x86_64 1:5.6.38-26.23.1.el6 will be installed

--> Processing Dependency: Percona-XtraDB-Cluster-client-56 = 1:5.6.38-26.23.1.el6 for package: 1:Percona-XtraDB-Cluster-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: Percona-XtraDB-Cluster-server-56 = 1:5.6.38-26.23.1.el6 for package: 1:Percona-XtraDB-Cluster-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: Percona-XtraDB-Cluster-galera-3 = 3.23-1.el6 for package: 1:Percona-XtraDB-Cluster-56-5.6.38-26.23.1.el6.x86_64

--> Running transaction check

---> Package Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.38-26.23.1.el6 will be installed

--> Processing Dependency: perl-DBI for package: 1:Percona-XtraDB-Cluster-client-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: /usr/bin/perl for package: 1:Percona-XtraDB-Cluster-client-56-5.6.38-26.23.1.el6.x86_64

---> Package Percona-XtraDB-Cluster-galera-3.x86_64 0:3.23-1.el6 will be installed

---> Package Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.38-26.23.1.el6 will be installed

--> Processing Dependency: Percona-XtraDB-Cluster-shared-56 = 1:5.6.38-26.23.1.el6 for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: percona-xtrabackup >= 2.2.5 for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: libnuma.so.1(libnuma_1.2)(64bit) for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: lsof for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: perl-DBD-MySQL for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: libnuma.so.1(libnuma_1.1)(64bit) for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: rsync for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Processing Dependency: libnuma.so.1()(64bit) for package: 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64

--> Running transaction check

---> Package Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.38-26.23.1.el6 will be installed

---> Package lsof.x86_64 0:4.82-5.el6 will be installed

---> Package numactl.x86_64 0:2.0.9-2.el6 will be installed

---> Package percona-xtrabackup.x86_64 0:2.3.10-1.el6 will be installed

--> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-2.3.10-1.el6.x86_64

---> Package perl.x86_64 4:5.10.1-144.el6 will be installed

--> Processing Dependency: perl-libs = 4:5.10.1-144.el6 for package: 4:perl-5.10.1-144.el6.x86_64

--> Processing Dependency: perl-libs for package: 4:perl-5.10.1-144.el6.x86_64

--> Processing Dependency: perl(version) for package: 4:perl-5.10.1-144.el6.x86_64

--> Processing Dependency: perl(Pod::Simple) for package: 4:perl-5.10.1-144.el6.x86_64

--> Processing Dependency: perl(Module::Pluggable) for package: 4:perl-5.10.1-144.el6.x86_64

--> Processing Dependency: libperl.so()(64bit) for package: 4:perl-5.10.1-144.el6.x86_64

---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed

---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed

---> Package rsync.x86_64 0:3.0.6-12.el6 will be installed

--> Running transaction check

---> Package libev.x86_64 0:4.03-3.el6 will be installed

---> Package perl-Module-Pluggable.x86_64 1:3.90-144.el6 will be installed

---> Package perl-Pod-Simple.x86_64 1:3.13-144.el6 will be installed

--> Processing Dependency: perl(Pod::Escapes) >= 1.04 for package: 1:perl-Pod-Simple-3.13-144.el6.x86_64

---> Package perl-libs.x86_64 4:5.10.1-144.el6 will be installed

---> Package perl-version.x86_64 3:0.77-144.el6 will be installed

--> Running transaction check

---> Package perl-Pod-Escapes.x86_64 1:1.04-144.el6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================================================================================================

 Package                                               Arch                        Version                                      Repository                                   Size

==================================================================================================================================================================================

Installing:

 Percona-XtraDB-Cluster-56                             x86_64                      1:5.6.38-26.23.1.el6                         percona-release-x86_64                       26 k

Installing for dependencies:

 Percona-XtraDB-Cluster-client-56                      x86_64                      1:5.6.38-26.23.1.el6                         percona-release-x86_64                      6.4 M

 Percona-XtraDB-Cluster-galera-3                       x86_64                      3.23-1.el6                                   percona-release-x86_64                      834 k

 Percona-XtraDB-Cluster-server-56                      x86_64                      1:5.6.38-26.23.1.el6                         percona-release-x86_64                       20 M

 Percona-XtraDB-Cluster-shared-56                      x86_64                      1:5.6.38-26.23.1.el6                         percona-release-x86_64                      687 k

 libev                                                 x86_64                      4.03-3.el6                                   epel                                        113 k

 lsof                                                  x86_64                      4.82-5.el6                                   base                                        324 k

 numactl                                               x86_64                      2.0.9-2.el6                                  base                                         74 k

 percona-xtrabackup                                    x86_64                      2.3.10-1.el6                                 percona-release-x86_64                      5.3 M

 perl                                                  x86_64                      4:5.10.1-144.el6                             base                                         10 M

 perl-DBD-MySQL                                        x86_64                      4.013-3.el6                                  base                                        134 k

 perl-DBI                                              x86_64                      1.609-4.el6                                  base                                        705 k

 perl-Module-Pluggable                                 x86_64                      1:3.90-144.el6                               base                                         41 k

 perl-Pod-Escapes                                      x86_64                      1:1.04-144.el6                               base                                         33 k

 perl-Pod-Simple                                       x86_64                      1:3.13-144.el6                               base                                        213 k

 perl-libs                                             x86_64                      4:5.10.1-144.el6                             base                                        579 k

 perl-version                                          x86_64                      3:0.77-144.el6                               base                                         52 k

 rsync                                                 x86_64                      3.0.6-12.el6                                 base                                        335 k

Transaction Summary

==================================================================================================================================================================================

Install      18 Package(s)

Total download size: 46 M

Installed size: 72 M

Is this ok [y/N]: y

Downloading Packages:

(1/18): Percona-XtraDB-Cluster-56-5.6.38-26.23.1.el6.x86_64.rpm                                                                                                                      |  26 kB     00:00     

(2/18): Percona-XtraDB-Cluster-client-56-5.6.38-26.23.1.el6.x86_64.rpm                                                                                                               | 6.4 MB     00:00     

(3/18): Percona-XtraDB-Cluster-galera-3-3.23-1.el6.x86_64.rpm                                                                                                                        | 834 kB     00:00     

(4/18): Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64.rpm                                                                                                               |  20 MB     01:32     

(5/18): Percona-XtraDB-Cluster-shared-56-5.6.38-26.23.1.el6.x86_64.rpm                                                                                                               | 687 kB     00:00     

(6/18): libev-4.03-3.el6.x86_64.rpm                                                                                                                                                  | 113 kB     00:00     

(7/18): lsof-4.82-5.el6.x86_64.rpm                                                                                                                                                   | 324 kB     00:00     

(8/18): numactl-2.0.9-2.el6.x86_64.rpm                                                                                                                                               |  74 kB     00:00     

(9/18): percona-xtrabackup-2.3.10-1.el6.x86_64.rpm                                                                                                                                   | 5.3 MB     00:00     

(10/18): perl-5.10.1-144.el6.x86_64.rpm                                                                                                                                              |  10 MB     00:10     

(11/18): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm                                                                                                                                       | 134 kB     00:00     

(12/18): perl-DBI-1.609-4.el6.x86_64.rpm                                                                                                                                             | 705 kB     00:00     

(13/18): perl-Module-Pluggable-3.90-144.el6.x86_64.rpm                                                                                                                               |  41 kB     00:00     

(14/18): perl-Pod-Escapes-1.04-144.el6.x86_64.rpm                                                                                                                                    |  33 kB     00:00     

(15/18): perl-Pod-Simple-3.13-144.el6.x86_64.rpm                                                                                                                                     | 213 kB     00:00     

(16/18): perl-libs-5.10.1-144.el6.x86_64.rpm                                                                                                                                         | 579 kB     00:00     

(17/18): perl-version-0.77-144.el6.x86_64.rpm                                                                                                                                        |  52 kB     00:00     

(18/18): rsync-3.0.6-12.el6.x86_64.rpm                                                                                                                                               | 335 kB     00:00     

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total                                                                                                                                                                       270 kB/s |  46 MB     02:53        

warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Percona

Importing GPG key 0xCD2EFD2A:

 Userid : Percona MySQL Development Team <mysql-dev@percona.com>

 Package: percona-release-0.1-4.noarch (@/percona-release-0.1-4.noarch)

 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-Percona

Is this ok [y/N]: y

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

  Installing : rsync-3.0.6-12.el6.x86_64                                                                                                                                     1/18 

  Installing : Percona-XtraDB-Cluster-galera-3-3.23-1.el6.x86_64                                                                                                             2/18 

  Installing : 1:perl-Pod-Escapes-1.04-144.el6.x86_64                                                                                                                        3/18 

  Installing : 4:perl-libs-5.10.1-144.el6.x86_64                                                                                                                             4/18 

  Installing : 1:perl-Module-Pluggable-3.90-144.el6.x86_64                                                                                                                   5/18 

  Installing : 1:perl-Pod-Simple-3.13-144.el6.x86_64                                                                                                                         6/18 

  Installing : 3:perl-version-0.77-144.el6.x86_64                                                                                                                            7/18 

  Installing : 4:perl-5.10.1-144.el6.x86_64                                                                                                                                  8/18 

  Installing : perl-DBI-1.609-4.el6.x86_64                                                                                                                                   9/18 

  Installing : 1:Percona-XtraDB-Cluster-client-56-5.6.38-26.23.1.el6.x86_64                                                                                                 10/18 

  Installing : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                                                                            11/18 

  Installing : libev-4.03-3.el6.x86_64                                                                                                                                      12/18 

  Installing : percona-xtrabackup-2.3.10-1.el6.x86_64                                                                                                                       13/18 

  Installing : 1:Percona-XtraDB-Cluster-shared-56-5.6.38-26.23.1.el6.x86_64                                                                                                 14/18 

  Installing : lsof-4.82-5.el6.x86_64                                                                                                                                       15/18 

  Installing : numactl-2.0.9-2.el6.x86_64                                                                                                                                   16/18 

  Installing : 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64                                                                                                 17/18 

warning: user mysql does not exist - using root

warning: group mysql does not exist - using root

2018-02-07 12:32:42 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-02-07 12:32:42 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2018-02-07 12:32:42 0 [Note] /usr/sbin/mysqld (mysqld 5.6.38-83.0-56) starting as process 1441 ...

2018-02-07 12:32:42 1441 [Note] WSREP: Setting wsrep_ready to false

2018-02-07 12:32:42 1441 [Note] WSREP: Read nil XID from storage engines, skipping position init

2018-02-07 12:32:42 1441 [Note] WSREP: wsrep_load(): loading provider library 'none'

2018-02-07 12:32:42 1441 [Note] WSREP: Setting wsrep_ready to true

2018-02-07 12:32:42 1441 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

2018-02-07 12:32:42 1441 [Note] InnoDB: Using atomics to ref count buffer pool pages

2018-02-07 12:32:42 1441 [Note] InnoDB: The InnoDB memory heap is disabled

2018-02-07 12:32:42 1441 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-02-07 12:32:42 1441 [Note] InnoDB: Memory barrier is not used

2018-02-07 12:32:42 1441 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-02-07 12:32:42 1441 [Note] InnoDB: Using Linux native AIO

2018-02-07 12:32:42 1441 [Note] InnoDB: Using CPU crc32 instructions

2018-02-07 12:32:42 1441 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2018-02-07 12:32:42 1441 [Note] InnoDB: Completed initialization of buffer pool

2018-02-07 12:32:42 1441 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!

2018-02-07 12:32:42 1441 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB

2018-02-07 12:32:42 1441 [Note] InnoDB: Database physically writes the file full: wait...

2018-02-07 12:32:42 1441 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB

2018-02-07 12:32:42 1441 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB

2018-02-07 12:32:42 1441 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

2018-02-07 12:32:42 1441 [Warning] InnoDB: New log files created, LSN=45781

2018-02-07 12:32:42 1441 [Note] InnoDB: Doublewrite buffer not found: creating new

2018-02-07 12:32:42 1441 [Note] InnoDB: Doublewrite buffer created

2018-02-07 12:32:42 1441 [Note] InnoDB: 128 rollback segment(s) are active.

2018-02-07 12:32:42 1441 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-02-07 12:32:42 1441 [Note] InnoDB: Foreign key constraint system tables created

2018-02-07 12:32:42 1441 [Note] InnoDB: Creating tablespace and datafile system tables.

2018-02-07 12:32:42 1441 [Note] InnoDB: Tablespace and datafile system tables created.

2018-02-07 12:32:42 1441 [Note] InnoDB: Creating zip_dict and zip_dict_cols system tables.

2018-02-07 12:32:42 1441 [Note] InnoDB: zip_dict and zip_dict_cols system tables created.

2018-02-07 12:32:42 1441 [Note] InnoDB: Waiting for purge to start

2018-02-07 12:32:42 1441 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.38-83.0 started; log sequence number 0

2018-02-07 12:32:42 1441 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.

2018-02-07 12:32:42 1441 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.

2018-02-07 12:32:43 1441 [Note] WSREP: Service disconnected.

2018-02-07 12:32:43 1441 [Note] WSREP: Waiting to close threads......

2018-02-07 12:32:48 1441 [Note] WSREP: Some threads may fail to exit.

2018-02-07 12:32:48 1441 [Note] Binlog end

2018-02-07 12:32:48 1441 [Note] InnoDB: FTS optimize thread exiting.

2018-02-07 12:32:48 1441 [Note] InnoDB: Starting shutdown...

2018-02-07 12:32:48 1441 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool

2018-02-07 12:32:50 1441 [Note] InnoDB: Shutdown completed; log sequence number 1631996

2018-02-07 12:32:50 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-02-07 12:32:50 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2018-02-07 12:32:50 0 [Note] /usr/sbin/mysqld (mysqld 5.6.38-83.0-56) starting as process 1465 ...

2018-02-07 12:32:50 1465 [Note] WSREP: Setting wsrep_ready to false

2018-02-07 12:32:50 1465 [Note] WSREP: Read nil XID from storage engines, skipping position init

2018-02-07 12:32:50 1465 [Note] WSREP: wsrep_load(): loading provider library 'none'

2018-02-07 12:32:50 1465 [Note] WSREP: Setting wsrep_ready to true

2018-02-07 12:32:50 1465 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

2018-02-07 12:32:50 1465 [Note] InnoDB: Using atomics to ref count buffer pool pages

2018-02-07 12:32:50 1465 [Note] InnoDB: The InnoDB memory heap is disabled

2018-02-07 12:32:50 1465 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-02-07 12:32:50 1465 [Note] InnoDB: Memory barrier is not used

2018-02-07 12:32:50 1465 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-02-07 12:32:50 1465 [Note] InnoDB: Using Linux native AIO

2018-02-07 12:32:50 1465 [Note] InnoDB: Using CPU crc32 instructions

2018-02-07 12:32:50 1465 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2018-02-07 12:32:50 1465 [Note] InnoDB: Completed initialization of buffer pool

2018-02-07 12:32:50 1465 [Note] InnoDB: Highest supported file format is Barracuda.

2018-02-07 12:32:50 1465 [Note] InnoDB: 128 rollback segment(s) are active.

2018-02-07 12:32:50 1465 [Note] InnoDB: Waiting for purge to start

2018-02-07 12:32:50 1465 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.38-83.0 started; log sequence number 1631996

2018-02-07 12:32:50 1465 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.

2018-02-07 12:32:50 1465 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.

2018-02-07 12:32:50 1465 [Note] WSREP: Service disconnected.

2018-02-07 12:32:50 1465 [Note] WSREP: Waiting to close threads......

2018-02-07 12:32:55 1465 [Note] WSREP: Some threads may fail to exit.

2018-02-07 12:32:55 1465 [Note] Binlog end

2018-02-07 12:32:55 1465 [Note] InnoDB: FTS optimize thread exiting.

2018-02-07 12:32:55 1465 [Note] InnoDB: Starting shutdown...

2018-02-07 12:32:56 1465 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool

2018-02-07 12:32:58 1465 [Note] InnoDB: Shutdown completed; log sequence number 1632006

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

  /usr/bin/mysqladmin -u root password 'new-password'

  /usr/bin/mysqladmin -u root -h dbnode2 password 'new-password'

Alternatively you can run:

  /usr/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with Percona XtraDB Cluster 5.6 at https://bugs.launchpad.net/percona-xtradb-cluster/+filebug

The latest information about Percona XtraDB Cluster 5.6 is available on the web at

  http://www.percona.com/doc/percona-xtradb-cluster/5.6/

Support Percona XtraDB Cluster 5.6 by buying support at http://www.percona.com/products/mysql-support

WARNING: Default config file /etc/my.cnf exists on the system

This file will be read by default by the MySQL server

If you do not want to use this, either remove it, or use the

--defaults-file argument to mysqld_safe when starting the server

Percona XtraDB Cluster is distributed with several useful UDFs from Percona Toolkit.

Run the following commands to create these functions:

mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"

mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"

mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

See  http://www.percona.com/doc/percona-server/5.5/management/udf_percona_toolkit.html for more details

  Installing : 1:Percona-XtraDB-Cluster-56-5.6.38-26.23.1.el6.x86_64                                                                                                        18/18 

  Verifying  : 3:perl-version-0.77-144.el6.x86_64                                                                                                                            1/18 

  Verifying  : 1:Percona-XtraDB-Cluster-56-5.6.38-26.23.1.el6.x86_64                                                                                                         2/18 

  Verifying  : 1:perl-Pod-Simple-3.13-144.el6.x86_64                                                                                                                         3/18 

  Verifying  : percona-xtrabackup-2.3.10-1.el6.x86_64                                                                                                                        4/18 

  Verifying  : numactl-2.0.9-2.el6.x86_64                                                                                                                                    5/18 

  Verifying  : 4:perl-5.10.1-144.el6.x86_64                                                                                                                                  6/18 

  Verifying  : lsof-4.82-5.el6.x86_64                                                                                                                                        7/18 

  Verifying  : 1:Percona-XtraDB-Cluster-client-56-5.6.38-26.23.1.el6.x86_64                                                                                                  8/18 

  Verifying  : 1:Percona-XtraDB-Cluster-shared-56-5.6.38-26.23.1.el6.x86_64                                                                                                  9/18 

  Verifying  : perl-DBI-1.609-4.el6.x86_64                                                                                                                                  10/18 

  Verifying  : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                                                                            11/18 

  Verifying  : libev-4.03-3.el6.x86_64                                                                                                                                      12/18 

  Verifying  : Percona-XtraDB-Cluster-galera-3-3.23-1.el6.x86_64                                                                                                            13/18 

  Verifying  : 4:perl-libs-5.10.1-144.el6.x86_64                                                                                                                            14/18 

  Verifying  : 1:perl-Module-Pluggable-3.90-144.el6.x86_64                                                                                                                  15/18 

  Verifying  : rsync-3.0.6-12.el6.x86_64                                                                                                                                    16/18 

  Verifying  : 1:perl-Pod-Escapes-1.04-144.el6.x86_64                                                                                                                       17/18 

  Verifying  : 1:Percona-XtraDB-Cluster-server-56-5.6.38-26.23.1.el6.x86_64                                                                                                 18/18 

Installed:

  Percona-XtraDB-Cluster-56.x86_64 1:5.6.38-26.23.1.el6                                                                                                                           

Dependency Installed:

  Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.38-26.23.1.el6  Percona-XtraDB-Cluster-galera-3.x86_64 0:3.23-1.el6  Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.38-26.23.1.el6 

  Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.38-26.23.1.el6  libev.x86_64 0:4.03-3.el6                            lsof.x86_64 0:4.82-5.el6                                     

  numactl.x86_64 0:2.0.9-2.el6                                  percona-xtrabackup.x86_64 0:2.3.10-1.el6             perl.x86_64 4:5.10.1-144.el6                                 

  perl-DBD-MySQL.x86_64 0:4.013-3.el6                           perl-DBI.x86_64 0:1.609-4.el6                        perl-Module-Pluggable.x86_64 1:3.90-144.el6                  

  perl-Pod-Escapes.x86_64 1:1.04-144.el6                        perl-Pod-Simple.x86_64 1:3.13-144.el6                perl-libs.x86_64 4:5.10.1-144.el6                            

  perl-version.x86_64 3:0.77-144.el6                            rsync.x86_64 0:3.0.6-12.el6                         

Complete!

[root@dbnode2 ~]# 

While installing if you face any error like “(28, 'Operation too slow. Less than 1 bytes/sec transfered the last 30 seconds')” , please refer my past blog on how to fix this here.