Percona Toolkit moved to https://jira.percona.com/projects/PT

pt-table-sync port problem

Bug #1266896 reported by Giancarlo Nebiolo Navidad
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Confirmed
Undecided
Unassigned

Bug Description

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)
Changed in percona-toolkit:
assignee: nobody → Muhammad Irfan (muhammad-irfan)
Revision history for this message
Muhammad Irfan (muhammad-irfan) wrote :
Download full text (3.9 KiB)

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
#...

Read more...

Changed in percona-toolkit:
assignee: Muhammad Irfan (muhammad-irfan) → nobody
status: New → Confirmed
Revision history for this message
Matthew B (utdrmac) wrote :

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.

Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

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

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.