unable to run pt-table-sync

[Aaron Lee]

Hi,

I am trying to fix my master-master replication which is currently out of sync. Unfortunately I keep getting Access denied error

root@A: pt-table-sync --dry-run --sync-to-master h=B,u=root,p=XXX

failed with 

DBI connect(';host=B;mysql_read_default_group=client','root',...) failed: Access denied for user 'root'@'B' (using password: YES) at /usr/bin/pt-table-sync line 1382

However doing the following on A

mysql -h B -u root -pXXX

works perfectly fine

Any ideas?

-Aaron

[Aaron Lee]

Sorry the error message was

DBI connect(';host=B;mysql_read_default_group=client','root',...) failed: Access denied for user 'root'@'A' (using password: YES) at /usr/bin/pt-table-sync line 1382

Even though on B we have 'root'@'A' granted in mysql.user and it works on cmdline

[Bill Karwin]

Have you tried passing user and password with discrete options instead of through the DSN?

pt-table-sync --user $USER --password $PASS ....etc...

I think I have found that the DSN doesn't work as expected in some PT tools.

Regards,
Bill Karwin

[Aaron Lee]

I did but it still doesn't work

I also tried --ask-pass and no luck neither

I also tried export PTDEBUG=1 and see if there are any clues but no luck

[Jervin R]

Do you have any ~/.my.cnf or /etc/.../my.cnf which stores the root password by chance? Is it correct?

[Kaiwang Chen]

2012/4/10 Aaron Lee <waif...@gmail.com>:

> I did but it still doesn't work
>
> I also tried --ask-pass and no luck neither
>
> I also tried export PTDEBUG=1 and see if there are any clues but no luck

PTDEBUG=1 is overwhelming. I suggest invoking the debugger shell (-d)
to print variables on the error line, like this session

$ perl -d pt-table-sync --dry-run --sync-to-master
h=127.0.0.1,u=msandbox,p=msandbo,P=25362

Loading DB routines from perl5db.pl version 1.28
Editor support available.

Enter h or `h h' for help, or `man perldebug' for more help.

main::(pt-table-sync:20): package OptionParser;
DB<1> b 1382
DB<2> c
DSNParser::get_dbh(pt-table-sync:1382):
1382: $dbh = DBI->connect($cxn_string, $user, $pass, $defaults);
DB<2> p $cxn_string
DBI:mysql:;host=127.0.0.1;port=25362;mysql_read_default_group=client
DB<3> p $user
msandbox
DB<4> p $pass
msandbo
DB<5> p Dumper($defaults)
$VAR1 = {
AutoCommit => 0,
PrintError => 0,
RaiseError => 1,
ShowErrorStatement => 1,
mysql_enable_utf8 => 0
};

It is DBI->connect() anyway, should be clear if anything went wrong.
Then you can use a simple script
#!/usr/bin/perl
use DBI;
DBI->connect('DBI:mysql:;host=127.0.0.1;port=25362;mysql_read_default_group=client',
'msandbox', 'msandbo',{
AutoCommit => 0,
PrintError => 0,
RaiseError => 1,
ShowErrorStatement => 1,
mysql_enable_utf8 => 0
});

to verify the problem without pt-table-sync.

Thanks,
Kaiwang

>
>
> On Monday, April 9, 2012 6:20:54 PM UTC-7, Bill Karwin wrote:
>>
>> Have you tried passing user and password with discrete options instead of
>> through the DSN?
>>
>> pt-table-sync --user $USER --password $PASS ....etc...
>>
>> I think I have found that the DSN doesn't work as expected in some PT
>> tools.
>>
>> Regards,
>> Bill Karwin
>>
>> On Apr 9, 2012, at 6:10 PM, Aaron Lee wrote:
>>
>> > Hi,
>> >
>> > I am trying to fix my master-master replication which is currently out
>> > of sync. Unfortunately I keep getting Access denied error
>> >
>> > root@A: pt-table-sync --dry-run --sync-to-master h=B,u=root,p=XXX
>> > failed with
>> > DBI connect(';host=B;mysql_read_default_group=client','root',...)
>> > failed: Access denied for user 'root'@'B' (using password: YES) at
>> > /usr/bin/pt-table-sync line 1382
>> >
>> > However doing the following on A
>> > mysql -h B -u root -pXXX
>> >
>> > works perfectly fine
>> >
>> > Any ideas?
>> >
>> > -Aaron
>

> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/percona-discussion/-/sUENbcBD0fEJ.
>
> To post to this group, send email to percona-d...@googlegroups.com.
> To unsubscribe from this group, send email to
> percona-discuss...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/percona-discussion?hl=en.

[Kaiwang Chen]

2012/4/15 Jervin R <jervi...@percona.com>:

> Do you have any ~/.my.cnf or /etc/.../my.cnf which stores the root password
> by chance? Is it correct?

my.cnf provides defaults, which will be overridden by password in DSN
he specified. I guess it's not the point.

Thanks,
Kaiwang

> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.
> To view this discussion on the web visit

> https://groups.google.com/d/msg/percona-discussion/-/tXpxTBFxXBEJ.

[Julius S]

Just to add my resolution - it looks like (and the documentation states) that when syncing from A -> B and specifying B with --replicate, pt-table-sync connects to B, looks up the master, then connects back to that. So if you are physically running pt-table-sync on A and you do not have either 'user'@'%' or 'user'@'A' extant on A then pt-table sync won't be able to connect, as instead of using 'user'@'localhost' it's going to use 'user'@'A' in the DSN, Hence the weird mismatch of the hostnames Aaron noted in their second message. The solution for me was to add 'user'@'A' with appropriate privileges.

              -Julius

On Thursday, August 30, 2012 at 3:01:56 PM UTC-4, drew wrote:

I had to use the -p with my password after it to avoid the same exact error.

pt-table-sync --execute xxxxxxx localhost -p xxxxxxx

On Friday, June 1, 2012 11:06:55 AM UTC-4, Jean-Marie de Boer wrote:

Not sure if the OP is still looking for an answer, anyway I just stumbled onto this. In my case the problem arose because the root user had a different password on each server. Setting identical root passwords on both boxes solved the issue.

- JM