Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

pt-table-checksum reports error if recursion method is DSN

Description

When I run the below command against mariadb 10.5 master server, I got errors: 

/export/apps/fsserv/sw/bin/percona-toolkit/latest/pt-table-checksum  --host localhost --user fsmonitor --socket /export/apps/fsserv/data/mysql/mysql_UAT/my.sock --nocreate-replicate-table --databases RefDB --nocheck-binlog-format --nocheck-replication-filters --defaults-file=/export/apps/fsserv/.local/etc/.mysql.UAT.cnf --recursion-method=DSN=D=percona,t=dsns

The error is like this: 

Checking if all tables can be checksummed ...
Starting checksum ...
08-23T13:36:53 Error checksumming table RefDB.AutoRpt: DBI connect(';;mysql_read_default_group=client','',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /export/apps/fsserv/sw/bin/percona-toolkit/latest/pt-table-checksum line 1636.

            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
08-23T13:36:53      1      0        0          0       1       0   0.020 RefDB.AutoRpt
08-23T13:36:53 Error checksumming table RefDB.BHM_ModelDictionary: DBI connect(';;mysql_read_default_group=client','',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /export/apps/fsserv/sw/bin/percona-toolkit/latest/pt-table-checksum line 1636.

08-23T13:36:53      1      0        0          0       1       0   0.010 RefDB.BHM_ModelDictionary
08-23T13:36:53 Error checksumming table RefDB.BHM_Taxonomy: DBI connect(';;mysql_read_default_group=client','',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /export/apps/fsserv/sw/bin/percona-toolkit/latest/pt-table-checksum line 1636.

 

The table I created in percona.dsns has the below data: 

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

id

parent_id

dsn                                

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

 1

        1

h=loncbstratappc15.uk.db.com,P=4206

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

 

I have also enabled the debug log, and the logging around the error is like this: 

  1. OobNibbleIterator:7299 7586 Done nibbling past boundaries

  2. NibbleIterator:6697 7586 Done nibbling

  3. pt_table_checksum:11323 7586 Checking slave diffs

  4. Cxn:3806 7586 loncbstratappc12.uk.db.com SHOW VARIABLES LIKE 'wsrep_on'

  5. DSNParser:1585 7586 DBI:mysql:;;mysql_read_default_group=client

  6. DSNParser:1634 7586 DBI:mysql:;;mysql_read_default_group=client   undef   undef mysql_enable_utf8=>0, ShowErrorStatement=>1, AutoCommit=>0, RaiseError=>1, PrintError=>0

  7. DSNParser:1634 7586 DBI:mysql:;;mysql_read_default_group=client   undef   undef mysql_enable_utf8=>0, ShowErrorStatement=>1, AutoCommit=>0, RaiseError=>1, PrintError=>0

  8. OobNibbleIterator:7307 7586 Finish nibble_sth

  9. OobNibbleIterator:7307 7586 Finish explain_nibble_sth
    08-23T13:40:05 Error checksumming table RefDB.AutoRpt: DBI connect(';;mysql_read_default_group=client','',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /export/apps/fsserv/sw/bin/percona-toolkit/latest/pt-table-checksum line 1636.

 

I have actually attempted a fix of the script: 

I modified this line in routine get_cxn_from_dsn_table: 

 push @cxn, $make_cxn->(dsn_string => $dsn_string);

to 

 push @cxn, $make_cxn->(dsn_string => $dsn_string, parent=>$args{dsn});

 

Which seems to address the issue. 

 

Environment

Linux

Activity

Show:

Przemyslaw Malkowski January 3, 2025 at 12:20 PM

The last working version for me was 3.4.0:

$ percona-toolkit-3.4.0/bin/pt-table-checksum --user=msandbox --password=msandbox --host=127.0.0.1 --port=22435 --databases=db1 --recursion-method=dsn=h=127.0.0.1,D=percona,t=dsns,P=22435 --no-check-binlog-format Checking if all tables can be checksummed ... Starting checksum ... TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 01-03T12:14:46 0 1 5 0 1 0 0.312 db1.sbtest2

But later it fails:

$ pt-table-checksum --version pt-table-checksum 3.7.0
$ pt-table-checksum --user=msandbox --password=msandbox --host=127.0.0.1 --port=22435 --databases=db1 --recursion-method=dsn=h=127.0.0.1,D=percona,t=dsns,P=22435 --no-check-binlog-format Checking if all tables can be checksummed ... Starting checksum ... 01-03T12:19:51 Error checksumming table db1.sbtest2: DBI connect(';;mysql_read_default_group=client','',...) failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) at /usr/bin/pt-table-checksum line 1639. TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 01-03T12:19:51 1 0 5 0 1 0 0.023 db1.sbtest2

yoann.lacancellera December 29, 2024 at 1:34 PM

I found the error to originate from https://github.com/percona/percona-toolkit/blob/3.x/bin/pt-table-checksum#L12682

sub wait_for_replicas { my (%args) = @_; my @required_args = qw(source_dbh source_replica replicas); foreach my $arg ( @required_args ) { die "I need a $arg argument" unless $args{$arg}; } my ($source_dbh, $ms, $replicas) = @args{@required_args}; foreach my $replica ( @$replicas ) { my $dp = $replica->{DSNParser}; my $mdbh = $dp->get_dbh($dp->get_cxn_params($replica->{parent}));

The error is triggered when $replica->{parent} is undef

Aaditya Dubey November 27, 2023 at 10:31 AM
Edited

Hi ,

Thank you for the report.
Verified as described:

Works Fine With 3.4.0 $:~/percona-toolkit-3.4.0/bin$ ./pt-table-checksum  h=127.0.0.1,u=adi,p=msandbox,P=21728 --databases=test  --nocheck-binlog-format --nocheck-replication-filters --defaults-file=/user/sandboxes/rsandbox_ps8_0_27/node1/my.sandbox.cnf --recursion-method=dsn=localhost,D=percona,t=dsns Checking if all tables can be checksummed ... Starting checksum ...             TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE 11-27T10:17:09      0      0        5          0       1       0   0.356 test._t1_new 11-27T10:17:10      0      0    10000          0       4       0   0.466 test.sbtest1 11-27T10:17:10      0      0        5          0       1       0   0.349 test.t1 Error With 3.5.4 $:~/percona-toolkit-3.5.4/bin$ ./pt-table-checksum  h=127.0.0.1,u=adi,p=msandbox,P=21728 --databases=test  --nocheck-binlog-format --nocheck-replication-filters --defaults-file=/user/sandboxes/rsandbox_ps8_0_27/node1/my.sandbox.cnf --recursion-method=dsn=localhost,D=percona,t=dsns Checking if all tables can be checksummed ... Starting checksum ... 11-27T10:18:51 Error checksumming table test._t1_new: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'adi'@'localhost' (using password: NO) at ./pt-table-checksum line 1636.             TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE 11-27T10:18:51      1      0        5          0       1       0   0.059 test._t1_new 11-27T10:18:51 Error checksumming table test.sbtest1: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'adi'@'localhost' (using password: NO) at ./pt-table-checksum line 1636. 11-27T10:18:51      1      0    10000          0       4       0   0.235 test.sbtest1 11-27T10:18:51 Error checksumming table test.t1: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'adi'@'localhost' (using password: NO) at ./pt-table-checksum line 1636. 11-27T10:18:51      1      0        5          0       1       0   0.067 test.t1 Error With 3.5.5[Latest One] as well $:~/percona-toolkit-3.5.5/bin$ ./pt-table-checksum  h=127.0.0.1,u=adi,p=msandbox,P=21728 --databases=test  --nocheck-binlog-format --nocheck-replication-filters --defaults-file=/user/sandboxes/rsandbox_ps8_0_27/node1/my.sandbox.cnf --recursion-method=dsn=localhost,D=percona,t=dsns Checking if all tables can be checksummed ... Starting checksum ... 11-27T10:21:03 Error checksumming table test._t1_new: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'adi'@'localhost' (using password: NO) at ./pt-table-checksum line 1636.             TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE 11-27T10:21:03      1      0        5          0       1       0   0.050 test._t1_new 11-27T10:21:03 Error checksumming table test.sbtest1: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'adi'@'localhost' (using password: NO) at ./pt-table-checksum line 1636. 11-27T10:21:03      1      0    10000          0       4       0   0.192 test.sbtest1 11-27T10:21:03 Error checksumming table test.t1: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'adi'@'localhost' (using password: NO) at ./pt-table-checksum line 1636. 11-27T10:21:03      1      0        5          0       1       0   0.089 test.t1

In case of MariaDB!

$:~/percona-toolkit-3.5.5/bin$ ./pt-table-checksum h=127.0.0.1,u=adi,p=msandbox,P=23420 --databases=test --nocheck-binlog-format --nocheck-replication-filters --defaults-file=/user/sandboxes/rsandbox_mdb10_5_19/master/my.sandbox.cnf --recursion-method=dsn=192.168.1.5,D=percona,t=dsns,u=rpl_user,p=msandbox,P=23420 Checking if all tables can be checksummed ... Starting checksum ... 11-27T12:12:11 Error checksumming table test.bill_groups: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'adi'@'localhost' (using password: NO) at ./pt-table-checksum line 1636.             TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE 11-27T12:12:11      1      0       39          0       1       0   0.021 test.bill_groups 11-27T12:12:11 Error checksumming table test.t1: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'adi'@'localhost' (using password: NO) at ./pt-table-checksum line 1636. 11-27T12:12:11      1      0        4          0       1       0   0.019 test.t1 11-27T12:12:11 Error checksumming table test.t2: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'adi'@'localhost' (using password: NO) at ./pt-table-checksum line 1636. 11-27T12:12:11      1      0        0          0       1       0   0.019 test.t2

Sending to engineering for further review and updates.

Steffen Pohlen October 6, 2023 at 4:21 PM

Hi,

I think I ran into the same issue.

After upgrading to Percona Toolkit 3.5.5 I'm getting connection errors from pt-table-checksum that do not occur with versions 3.3.1 or 3.4.0. (MySQL 5.7.42 on Debian 10)

Somehow between creating and checking the checksum records, the 3.5.4/5 versions seem to forget the connection information and try to connect as the system user (root@localhost), not with the connection data specified on the command line.

The call I'm using is basically this:

./pt-table-checksum.3.4.0 --recursion-method dsn=D=percona,t=dsns6 --replicate=percona.checksum_db01_02 --create-replicate-table --no-check-binlog-format --defaults-file=${CNFFILE} --tables=test_schema.user_data

That works perfectly with 3.3.1 and 3.4.0. With versions 3.5.4 or 3.5.5, the error is:

Error checksumming table test_schema.user_data: DBI connect(';;mysql_read_default_group=client','',…) failed: Access denied for user 'root'@'localhost' (using password: NO) at ./pt-table-checksum.3.5.4 line 1636.

In the master MySQL error.log I see:

2023-10-06T14:20:15.227046+02:00 668740 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. […]
2023-10-06T14:20:15.233841+02:00 668742 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2023-10-06T14:20:15.235383+02:00 668743 [Note] Access denied for user 'root'@'localhost' (using password: NO)

I tried using both a DSN and the user/password/host parameters instead of the CNFFILE, but the error remains the same.

The checksum record is created an can be seen on both servers.

It looks like the error occurs when pt-table-checksum tries to check the checksum records, because it shows me “ERRORS 1”, but “DIFFS 0” in the results (but DIFFS should be 1, because the table was patched to different data on the slave).

Excerpt of the output with PT_DEBUG=1:

# pt_table_checksum:11741 11062 REPLACE INTO percona.checksum_db01_02 (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', id, convert(data using utf8mb4), convert(name using utf8mb4), CONCAT(ISNULL(data), ISNULL(name)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM test_schema.user_data /checksum table/ lower boundary: upper boundary:
# pt_table_checksum:11762 11062 SHOW WARNINGS
# pt_table_checksum:11768 11062 Ignoring warning: 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE… SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave.
# Retry:8142 11062 Try code succeeded
# pt_table_checksum:11224 11062 Nibble time: 0.000952959060668945
# NibbleIterator:6701 11062 0 rows in nibble 1
# NibbleIterator:6713 11062 No rows in nibble or nibble skipped
# pt_table_checksum:11288 11062 Total avg rate: 5246
# WeightedAvgRate:9570 11062 Master op time: 5 n / 0.000952959060668945 s
# WeightedAvgRate:9582 11062 Initial avg rate: 5246.8151113335 n/s
# WeightedAvgRate:9586 11062 Adjust n to 2623
# pt_table_checksum:11317 11062 Updated chunk size: 2623
# ReplicaLagWaiter:8765 11062 Checking slave lag
# MasterSlave:5509 11062 DBI::db=HASH(0x55b0a51ad748) SHOW SLAVE STATUS
# ReplicaLagWaiter:8782 11062 DB02 slave lag: 0
# ReplicaLagWaiter:8815 11062 All slaves caught up
# MySQLStatusWaiter:9459 11062 Checking status variables
# pt_table_checksum:10793 11062 SHOW GLOBAL STATUS LIKE ? Threads_running
# MySQLStatusWaiter:9462 11062 Threads_running = 2
# MySQLStatusWaiter:9489 11062 All var vals are low enough
# OobNibbleIterator:7324 11062 Done nibbling past boundaries
# NibbleIterator:6722 11062 Done nibbling
# pt_table_checksum:11348 11062 Checking slave diffs
# Cxn:3807 11062 DB01 SHOW VARIABLES LIKE 'wsrep_on'
# DSNParser:1585 11062 DBI:mysql:;;mysql_read_default_group=client
# DSNParser:1634 11062 DBI:mysql:;;mysql_read_default_group=client undef undef RaiseError=>1, PrintError=>0, ShowErrorStatement=>1, mysql_enable_utf8=>0, AutoCommit=>0
# DSNParser:1634 11062 DBI:mysql:;;mysql_read_default_group=client undef undef RaiseError=>1, PrintError=>0, ShowErrorStatement=>1, mysql_enable_utf8=>0, AutoCommit=>0
# OobNibbleIterator:7332 11062 Finish explain_nibble_sth
# OobNibbleIterator:7332 11062 Finish nibble_sth
10-06T14:59:44 Error checksumming table test_schema.user_data: DBI connect(';;mysql_read_default_group=client','',…) failed: Access denied for user 'root'@'localhost' (using password: NO) at ./pt-table-checksum.3.5.4 line 1636.

 

 

Details

Assignee

Reporter

Priority

Components

Affects versions

Fix versions

Needs QA

Yes

Smart Checklist

Created August 23, 2023 at 12:44 PM
Updated January 3, 2025 at 12:20 PM