pt-table-checksum --defaults-file isn't used for slaves

[Ryan Lowe]

The username and password specified /etc/percona.cnf are applied to only localhost, not any of the slaves.

In this case, I have a master (10.0.0.1) and one slave (10.0.0.2).

I am trying to run the checksum from the master.

%> cat /etc/*release
CentOS release 5.7 (Final)

%> pt-table-checksum --version
pt-table-checksum 2.1.2

%> pt-table-checksum --defaults-file /etc/percona.cnf --databases db1 localhost
Cannot connect to h=10.0.0.2

%> export PTDEBUG=1

%> pt-table-checksum --defaults-file /etc/percona.cnf --databases db1 localhost
...
# OptionParser:794 16427 Got option defaults-file = /etc/percona.cnf
...
# OptionParser:1014 16427 Nothing to validate for option defaults-file type s value /etc/percona.cnf
...
# DSNParser:140 16427 DSN string made from options: F=/etc/percona.cnf,h=localhost
# DSNParser:80 16427 Parsing F=/etc/percona.cnf,h=localhost
...
# DSNParser:192 16427 DBI:mysql:;mysql_read_default_file=/etc/percona.cnf;host=localhost;mysql_read_default_group=client
# DSNParser:238 16427 DBI:mysql:;mysql_read_default_file=/etc/percona.cnf;host=localhost;mysql_read_default_group=client undef undef mysql_enable_utf8=>0, ShowErrorStatement=>1, AutoCommit=>1, RaiseError=>1, PrintError=>0
...
# MasterSlave:2659 16427 Looking for slaves on F=/etc/percona.cnf,h=localhost using methods processlist hosts
...
# MasterSlave:2763 16427 DBI::db=HASH(0x2a301f0) SHOW PROCESSLIST
# DSNParser:80 16427 Parsing h=10.0.0.2
...
# MasterSlave:2637 16427 Recursing from F=/etc/percona.cnf,h=localhost to h=10.0.0.2
# DSNParser:192 16427 DBI:mysql:;host=10.0.0.2;mysql_read_default_group=client
# DSNParser:238 16427 DBI:mysql:;host=10.0.0.2;mysql_read_default_group=client undef undef mysql_enable_utf8=>0, ShowErrorStatement=>1, AutoCommit=>1, RaiseError=>1, PrintError=>0
# DSNParser:279 16427 DBI connect(';host=10.0.0.2;mysql_read_default_group=client','',...) failed: Access denied for user 'root'@'10.0.0.1' (using password: YES) at /usr/bin/pt-table-checksum line 241
#
# DSNParser:238 16427 DBI:mysql:;host=10.0.0.2;mysql_read_default_group=client undef undef mysql_enable_utf8=>0, ShowErrorStatement=>1, AutoCommit=>1, RaiseError=>1, PrintError=>0
# DSNParser:279 16427 DBI connect(';host=10.0.0.2;mysql_read_default_group=client','',...) failed: Access denied for user 'root'@'10.0.0.1' (using password: YES) at /usr/bin/pt-table-checksum line 241
#
Cannot connect to h=10.0.0.2

I think that the correct behavior would be for --defaults-file options to propagate to all slaves (otherwise, the option isn't really useful)

[Raghavendra D Prabhu (raghavendra-prabhu)]

Bug confirmed.

That is because DSN 'F' (defaults-file) is marked as not-copy in the options.

This is the patch/workaround (applies over bzr):

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

diff -u /home/raghavendra/.Work/code/percona-toolkit/bin/pt-table-checksum /dev/shm/
--- /home/raghavendra/.Work/code/percona-toolkit/bin/pt-table-checksum 2012-08-08 13:30:41.325087753 +0530
+++ /dev/shm/pt-table-checksum 2012-08-08 13:28:25.086938877 +0530
@@ -9611,7 +9611,7 @@

 =item * F

-dsn: mysql_read_default_file; copy: no
+dsn: mysql_read_default_file; copy: yes

 Only read default options from the given file

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

Confirmed that it works with the patch.

I wonder if the 'copy' ability of any of the DSNs can be made as option.

[Daniel Nichter (daniel-nichter)]

If I recall correctly, there's a reason we don't copy the F DSN part. Often times, a defaults file defines a socket, and DBD::mysql favors a socket, but you can't connect to a slave via a socket, so what happens is the tool creates a DSN for the slave, that slave DSN inherits the master's socket from the default file, then the master connects to the slave but DBD::mysql automagically uses the socket and so the master winds up connecting to itself.

Off the top of my head, I'm not sure if defaults files can be used to help connect to slaves. We'll have to look into it more closely.

[Ryan Lowe (ryan-a-lowe)]

Hi Daniel!

I just verified that DBD::mysql overrides the values in the defaults file if they're passed to it. Here's what I did on 10.0.0.1 (test1.lab).

%> cat /tmp/my.cnf
[client]

socket = /var/lib/mysql/mysql.sock
user = root
%> cat dbdtest.pl

#!/usr/bin/perl

use strict;
use warnings FATAL => 'all';
use DBI;

my $dbh = DBI->connect('DBI:mysql:test:10.0.0.2:3306;mysql_read_default_file=/tmp/my.cnf;mysql_read_default_group=client', 'testuser', 'testpass') || die "Could not connect to database: $DBI::errstr";
my $sth = $dbh->prepare('SELECT @@hostname AS val');
$sth->execute();
my $result = $sth->fetchrow_hashref();
$sth->finish;
print "Value returned: $result->{val}\n";
$dbh->disconnect()

%> perl dbdtest.pl
Value returned: test2.lab

So, if this is the only concern, we should be good to go?

[Daniel Nichter (daniel-nichter)]

Bug 1010031 "DSNs copy the socket file, causing connection failures" is related. Let me test this...

[Daniel Nichter (daniel-nichter)]

Ryan, you're correct. Explicit host=foo in the DSN override whatever is in the defaults file. I think my previous problem with this was related to the recursion method. If master host DSN is just F=/tmp/12345/my.sandbox.cnf, then MasterSlave doesn't see that it's a non-standard port, so it uses proclist rather than SHOW SLAVE HOSTS, and proclist shows the slave on localhost, so the tool tries to connect to it like

# MasterSlave:3186 33943 Recursing from F=/tmp/12345/my.sandbox.cnf,h=localhost,t=HASH(0x100ac0690) to F=/tmp/12345/my.sandbox.cnf,h=127.0.0.1

which doesn't work--it needs the slave's special port, 12346. When I add --recursion-method hosts, then it works:

# MasterSlave:3186 33946 Recursing from F=/tmp/12345/my.sandbox.cnf,h=localhost,t=HASH(0x100abfa90) to F=/tmp/12345/my.sandbox.cnf,P=12346,h=127.0.0.1

So, I'll set copy: yes on F (like Raghu said) and test and make sure it doesn't blow up. If it does, you know who gets all the blame. :-D

[Daniel Nichter (daniel-nichter)]

The change didn't blow up the tests, so I think it's good.

[Ryan Lowe (ryan-a-lowe)]

That only proves you need better tests

[Daniel Nichter (daniel-nichter)]

Sounds like you just offered to buy me http://www.amazon.com/The-Software-Testing-Glenford-Myers/dp/1118031962/

[Shahriyar Rzayev (rzayev-sehriyar)]

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