pt-table-sync port problem

[Giancarlo Nebiolo Navidad]

I have two machines with MySQL running on the default port 3306, however for security reasons that port is blocked from outside address so in order to mount a master-master replication environment I am using a redirection using the following rule in iptables:

For server A:

iptables -t nat --verbose -A PREROUTING --protocol tcp --destination-port 39966 --jump REDIRECT --to-ports 3306

In "CHANGE MASTER TO" the parameter "MASTER_PORT" is equal to 39965

For server B:

iptables -t nat --verbose -A PREROUTING --protocol tcp --destination-port 39965 --jump REDIRECT --to-ports 3306

In "CHANGE MASTER TO" the parameter "MASTER_PORT" is equal to 39966

 When testing the replication with the tool "pt-table-checksum" called using the next parameters:

server A > pt-table-checksum --replicate=test.checksum --create-replicate-table --databases=mydatabase --host=serverA --password=mypass --port=3306 --user=test --chunk-size-limit=0 --chunk-time=0 --tables=mytable

  the tool run perfectly and report the results, however when I run "pt-table-sync" using the next parameters:

server A > pt-table-sync --sync-to-master --execute h=ServerA,P=3306,u=test,p=mypass --databases=mydatabase --wait=0 --noforeign-key-checks --nounique-checks --tables=mytable --verbose --transaction --lock=1 --recursion-method=hosts

The next error message is printed

The slave is connected to 39966 but the master's port is 3306 at /usr/bin/pt-table-sync line 6840.

using export PTDEBUG=1 printed these lines (I am omiting part of the output for briefness)

...
# $DBD::mysql::VERSION: 4.020 $DBI::VERSION: 1.616
# pt_table_sync:10813 21624 DBI::db=HASH(0x29f71a8) /*!40014 SET UNIQUE_CHECKS=0 */
# pt_table_sync:10818 21624 DBI::db=HASH(0x29f71a8) /*!40014 SET FOREIGN_KEY_CHECKS=0 */
# pt_table_sync:10825 21624 DBI::db=HASH(0x29f71a8) /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
# VersionParser:2525 21624 VersionParser got a dbh, trying to get the version
# VersionParser:2573 21624 InnoDB support: DEFAULT
# VersionParser:2585 21624 InnoDB version: 5.5.34
# pt_table_sync:10837 21624 DBI::db=HASH(0x29f71a8) SELECT @@binlog_format
# pt_table_sync:10839 21624 Original binlog_format: STATEMENT
# pt_table_sync:10864 21624 DBI::db=HASH(0x29f71a8) SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
# pt_table_sync:10871 21624 Opened dbh DBI::db=HASH(0x29f71a8)
# MasterSlave:6879 21624 DBI::db=HASH(0x29f71a8) SHOW SLAVE STATUS
# DSNParser:2056 21624 Parsing h=192.168.1.251,P=39966
# DSNParser:2075 21624 Finding value for S
# DSNParser:2085 21624 Copying value for S from defaults
# DSNParser:2075 21624 Finding value for F
# DSNParser:2085 21624 Copying value for F from defaults
# DSNParser:2075 21624 Finding value for A
# DSNParser:2085 21624 Copying value for A from defaults
# DSNParser:2075 21624 Finding value for P
# DSNParser:2075 21624 Finding value for u
# DSNParser:2081 21624 Copying value for u from previous DSN
# DSNParser:2075 21624 Finding value for p
# DSNParser:2081 21624 Copying value for p from previous DSN
# DSNParser:2075 21624 Finding value for h
# DSNParser:2075 21624 Finding value for D
# DSNParser:2085 21624 Copying value for D from defaults
# DSNParser:2075 21624 Finding value for t
# DSNParser:2085 21624 Copying value for t from defaults
# DSNParser:2169 21624 DBI:mysql:;host=192.168.1.251;port=39966;mysql_read_default_group=client
# DSNParser:2218 21624 DBI:mysql:;host=192.168.1.251;port=39966;mysql_read_default_group=client test mypass mysql_enable_utf8=>0, ShowErrorStatement=>1, AutoCommit=>0, RaiseError=>1, PrintError=>0
# DSNParser:2246 21624 DBI::db=HASH(0x2a92000) SELECT @@SQL_MODE
# DSNParser:2377 21624 DBI::db=HASH(0x2a92000) SET SESSION wait_timeout=10000
# DSNParser:2277 21624 DBI::db=HASH(0x2a92000) SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/
# DSNParser:2286 21624 DBH info: DBI::db=HASH(0x2a92000) $VAR1 = {
# '@@hostname' => 'soportecentos',
# 'CONNECTION_ID()' => '9',
# 'DATABASE()' => undef,
# 'VERSION()' => '5.6.10-log'
# };
# Connection info: 192.168.1.251 via TCP/IP Character set info: $VAR1 = [
# {
# Value => 'latin1',
# Variable_name => 'character_set_client'
# },
# {
# Value => 'latin1',
# Variable_name => 'character_set_connection'
# },
# {
# Value => 'latin1',
# Variable_name => 'character_set_database'
# },
# {
# Value => 'binary',
# Variable_name => 'character_set_filesystem'
# },
# {
# Value => 'latin1',
# Variable_name => 'character_set_results'
# },
# {
# Value => 'latin1',
# Variable_name => 'character_set_server'
# },
# {
# Value => 'utf8',
# Variable_name => 'character_set_system'
# },
# {
# Value => '/usr/share/mysql/charsets/',
# Variable_name => 'character_sets_dir'
# }
# ];
# $DBD::mysql::VERSION: 4.020 $DBI::VERSION: 1.616
# pt_table_sync:10813 21624 DBI::db=HASH(0x2a92000) /*!40014 SET UNIQUE_CHECKS=0 */
# pt_table_sync:10818 21624 DBI::db=HASH(0x2a92000) /*!40014 SET FOREIGN_KEY_CHECKS=0 */
# pt_table_sync:10825 21624 DBI::db=HASH(0x2a92000) /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
# VersionParser:2525 21624 VersionParser got a dbh, trying to get the version
# VersionParser:2573 21624 InnoDB support: DEFAULT
# VersionParser:2585 21624 InnoDB version: 1.2.10
# pt_table_sync:10837 21624 DBI::db=HASH(0x2a92000) SELECT @@binlog_format
# pt_table_sync:10839 21624 Original binlog_format: STATEMENT
# pt_table_sync:10864 21624 DBI::db=HASH(0x2a92000) SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
# pt_table_sync:10871 21624 Opened dbh DBI::db=HASH(0x2a92000)
# MasterSlave:6903 21624 DBI::db=HASH(0x2a92000) SHOW MASTER STATUS
# MasterSlave:6907 21624 Position=120 Binlog_Do_DB= Binlog_Ignore_DB= File=mysql-bin.000014 Executed_Gtid_Set=
# MasterSlave:6879 21624 DBI::db=HASH(0x29f71a8) SHOW SLAVE STATUS
# MasterSlave:6789 21624 DBI::db=HASH(0x2a92000) SHOW GRANTS FOR CURRENT_USER()
# MasterSlave:6819 21624 DBI::db=HASH(0x2a92000) SHOW PROCESSLIST
The slave is connected to 39966 but the master's port is 3306 at /usr/bin/pt-table-sync line 6840.

P.D: I am not very good at english and this is my first bug report so excuse me if i am wrong or there are errors in the grammar. Thank for your atention.

[Muhammad Irfan (muhammad-irfan)]

I am able to replicate this problem as described. Relevant output with PTDEBUG=1

# /usr/bin/perl 5.010001
# Linux centos2 3.8.0-44-generic #66~precise1-Ubuntu SMP Tue Jul 15 04:01:04 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
# Arguments: _[--sync-to-master]_ _[--execute]_ _[h=10.0.3.239,P=3306,u=checksum,p=xxxxxx]_ _[--databases=world]_ _[--wait=0]_ _[--noforeign-key-checks]_ _[--nounique-checks]_ _[--tables=test]_ _[--verbose]_ _[--transaction]_ _[--lock=1]_ _[--recursion-method=hosts]_
# OptionParser:290 1678 Option rule: Specify at least one of --print, --execute, or --dry-run.
# OptionParser:290 1678 Option rule: --where and --replicate are mutually exclusive.
# OptionParser:290 1678 Option rule: This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.
.
.
# DSNParser:2028 1678 DSN option: copy=1, dsn=charset, desc=Default character set, key=A
# DSNParser:2028 1678 DSN option: copy=1, dsn=database, desc=Database containing the table to be synced, key=D
# DSNParser:2028 1678 DSN option: copy=1, dsn=mysql_read_default_file, desc=Only read default options from the given file, key=F
# DSNParser:2028 1678 DSN option: copy=1, dsn=host, desc=Connect to host, key=h
# DSNParser:2028 1678 DSN option: copy=1, dsn=password, desc=Password to use when connecting, key=p
# DSNParser:2028 1678 DSN option: copy=1, dsn=port, desc=Port number to use for connection, key=P
# DSNParser:2028 1678 DSN option: copy=1, dsn=mysql_socket, desc=Socket file to use for connection, key=S
# DSNParser:2028 1678 DSN option: copy=1, dsn=undef, desc=Table to be synced, key=t
# DSNParser:2028 1678 DSN option: copy=1, dsn=user, desc=User for login if not current user, key=u
# OptionParser:253 1678 pt-table-sync 2.2.10
# OptionParser:575 1678 Cannot open /etc/percona-toolkit/percona-toolkit.conf: No such file or directory
#
# OptionParser:575 1678 Cannot open /etc/percona-toolkit/pt-table-sync.conf: No such file or directory
#
# OptionParser:575 1678 Cannot open /root/.percona-toolkit.conf: No such file or directory
#
# OptionParser:575 1678 Cannot open /root/.pt-table-sync.conf: No such file or directory
#
# OptionParser:543 1678 Got option sync-to-master = 1
# OptionParser:543 1678 Got option execute = 1
# OptionParser:543 1678 Got option databases = world
# OptionParser:543 1678 Got option wait = 0
# OptionParser:543 1678 Got option foreign-key-checks = 0
# OptionParser:543 1678 Got option unique-checks = 0
# OptionParser:543 1678 Got option tables = test
# OptionParser:543 1678 Got option verbose = 1
# OptionParser:543 1678 Got option transaction = 1
# OptionParser:543 1678 Got option lock = 1
# OptionParser:543 1678 Got option recursion-method = hosts
.
.
# $DBD::mysql::VERSION: 4.013 $DBI::VERSION: 1.609
# pt_table_sync:10795 1678 DBI::db=HASH(0x145fda8) /*!40014 SET UNIQUE_CHECKS=0 */
# pt_table_sync:10800 1678 DBI::db=HASH(0x145fda8) /*!40014 SET FOREIGN_KEY_CHECKS=0 */
# pt_table_sync:10807 1678 DBI::db=HASH(0x145fda8) /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
# VersionParser:2525 1678 VersionParser got a dbh, trying to get the version
# VersionParser:2573 1678 InnoDB support: DEFAULT
#...

[Matthew B (utdrmac)]

percona@wh1:~$ pt-table-sync --replicate percona.checksums --sync-to-master --print --verbose h=localhost
The slave is connected to 3307 but the master's port is 3306 at /usr/bin/pt-table-sync line 6840.

Not sure why this is a rule/check within the perl code. Just experienced this with a client who is running an SSH tunnel to his master for security. The slave runs on 3306 and connects to the master on 127.0.0.1:3307 which is forwarded to master:3306. So my client is getting this error as well.

In the sub is_master_of() I simply commented out these lines:

# if ( $port != $slave_status->{master_port} ) {
# die "The slave is connected to $slave_status->{master_port} "
# . "but the master's port is $port";
# }

And the script, as executed above, works as expected. So, not sure what the original intent of protection was.

[Frank Cizmich (frank-cizmich)]

This check can be circumvented with the option: "--no-check-master"

http://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html#cmdoption-pt-table-sync--[no]check-master

[Shahriyar Rzayev (rzayev-sehriyar)]

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