pt-table-checksum : Access denied for user 'root'@'localhost'

General

Escalation

General

Escalation

Description

Team,

root@ip-172-31-32-135 ~]# pt-table-checksum --host agupta-test-1.cxxxxxxxxxxn.us-east-1.rds.amazonaws.com --user checksum --ask-pass --port 3306 --databases mysql --set-vars="time_zone='+01:00'"  --recursion-method dsn=D=percona,t=dsns --tables mysql.user --no-check-binlog-format --no-check-replication-filters --replicate percona.checksums --no-check-binlog-format
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...

# A software update is available:
02-21T13:12:44 Error checksumming table mysql.user: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at /bin/pt-table-checksum line 1639.

            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
02-21T13:12:44      1      0        9          0       1       0   0.047 mysql.user
[root@ip-172-31-32-135 ~]#

pt-table-checksum completed with access denied error for the root@localhost user even though this root user account hasn’t been used in the pt-table-checksum command, and we are using a dedicated percona.dsns table having a different user.

02-21T13:12:44 Error checksumming table mysql.user: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at /bin/pt-table-checksum line 1639.

This can be easily reproduced with the below architecture :

Primary : RDS Instance with binlog_format=STATEMENT (8.0.xx)

Replica : EC2 Instance with binlog_format=ROW (8.0.xx)

On RDS : 

mysql> select * from percona.dsns\G
*************************** 1. row ***************************
       id: 19
parent_id: NULL
      dsn: h=172.31.32.135,u=checksum,p=checksum,P=3306
1 row in set (0.01 sec)

select user,host from mysql.user where user='checksum';
+----------+---------------+
| user     | host          |
+----------+---------------+
| checksum | %             |
| checksum | 172.31.32.135 |
+----------+---------------+
2 rows in set (0.01 sec)

mysql> show grants for checksum@'%'\G
*************************** 1. row ***************************
Grants for checksum@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON . TO checksum@%
1 row in set (0.01 sec)

mysql> show grants for checksum@'172.31.32.135'\G
*************************** 1. row ***************************
Grants for checksum@172.31.32.135: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON . TO checksum@172.31.32.135
1 row in set (0.00 sec)

This is reproducible with pt-table-checksum 3.6.0 & latest version 3.7.0 as well.

pt-table-checksum --version
pt-table-checksum 3.7.0

Environment

None

AFFECTED CS IDs

CS0053203

Activity

Show:

Details

Assignee

Unassigned

Reporter

Abhinav Gupta

Priority

Medium

Components

pt-table-checksum

Affects versions

3.6.0

3.7.0

Needs QA

Yes

Smart Checklist

Created February 21, 2025 at 1:36 PM

Updated February 21, 2025 at 1:37 PM

Configure