Errors are reported when using pt-table-checksum to check MySQL8.0.25 and MySQL5.7.34

Polyglot Projects Percona Toolkit
1

mater:8.0.25 with my.cnf:
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake

slave:5.7.34

version:percona-toolkit-3.3.1

command line:
pt-table-checksum --defaults-file=~/etc/my.cnf --host=‘127.0.0.1’ --user=‘root’ --password=‘root123’ --port=3306 --replicate=test.checksums --no-check-binlog-format --databases=db1

slave reports errors:
[ERROR] Slave SQL for channel ‘’: Error ‘Character set ‘#255’ is not a compiled character set and is not specified in the ‘/home/mysql/share/charsets/Index.xml’ file’ on query. Default database: ‘test’. Query: ‘CREATE DATABASE IF NOT EXISTS test /* pt-table-checksum */’, Error_code: 22
2021-07-26T11:44:12.751369Z 72 [Warning] Slave: Character set ‘#255’ is not a compiled character set and is not specified in the ‘/home/mysql/share/charsets/Index.xml’ file Error_code: 22

I have set skip-character-set-client-handshake ,why dose mysql still report errors？

1 Like
2

Can you manually create a table in 5.7 using the new utf8mb4 / utf8mb4_general_ci? If not, this is the reason for the failure; your 5.7 does not have the same character sets that MySQL 8 has. You would need to force pt-table-checksum to create the database/table it needs using latin1 so that it is compatible with your 8.0 and 5.7.

1 Like
3

thanks.
‘Create Statement’ can be executed successfully in 5.7.
CREATE DATABASE TEST /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci / /!80016 DEFAULT ENCRYPTION=‘N’ */;

‘utf8mb4_0900_ai_ci’ is identified by #255

1 Like
4

https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

utf8mb4_0900_ai_ci does not exist in 5.7. You must have this collation set on some random table/database in your 8.0 setup. You’ll need to convert this to something available in 5.7.

1 Like
5

But I have set ‘skip-character-set-client-handshake’ to avoid this compatibility,why it doesn’t work.

http://www.tocker.ca/2013/09/10/improving-mysqls-default-configuration.html
“I would rather like to see that option go! Or at least it should be exposed as a variable as well (refer: MySQL Bugs: #69997: Please expose skip-character-set-client-handshake as a variable). Besides setting skip-character-set-client-handshake to FALSE will only have effect when a charset is specified in mysql_options(). Any client can execute SET NAMES after connection (and I believe that is the way most applications do – the only practical difference between the two is support for non-server-default-charset-characters in username and password).”

I have try to modify pt-table-checksum like this,it can work well.
1648 if ( my ($charset) = $cxn_string =~ m/charset=([\w]+)/ ) {
1649 #$sql = qq{/!40101 SET NAMES “$charset”/};
1650 $sql = qq{/!40101 SET NAMES “utf8”/};

1 Like
6

I would not be surprised at all if this parameter did not affect replication. There are a few parameters like that which only affect non-replication clients.

1 Like
7

OK,thanks，it’s not the best solution.

1 Like