pt-table-checksum fails on older MySQL without utf8mb4-support

“别再抱怨你此生找不到一个对的人，当初的数学选择题就四个，你也找不到对的答案啊”

前言

昨天帮同事解决主从同步的问题。

原因是同事使用Django开发了一套运维平台。但是里面的表使用了FOREIGN KEY，在插入的是报错。造成了主从同步错误，数据量不大，使用pt-table-checksum来校验主从数据

操作

一、先跳过主从错误，主从同步关系恢复正常。

STOP SLAVE sql_thread;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE sql_thread;
SHOW SLAVE STATUS\G

二、使用 pt-table-checksum

pt-table-checksum --set-vars innodb_lock_wait_timeout=200 --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=***** --host=*** --port=*** --user=*** --password='*****' --recursion-method='processlist'

Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=1"].  The current value for innodb_lock_wait_timeout is 50.  If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL.

Checking if all tables can be checksummed ...
Starting checksum ...
Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=1"].  The current value for innodb_lock_wait_timeout is 50.  If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL.

05-13T18:42:19 Error executing EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, convert(`uuid` using utf8mb4), convert(`city` using utf8mb4), convert(`bassert` using utf8mb4), convert(`hostname` using utf8mb4), `idc_id`, `dp_id`, convert(`vlanip` using utf8mb4), convert(`wlanip` using utf8mb4), convert(`remote_ip` using utf8mb4), convert(`network_card` using utf8mb4), convert(`mac` using utf8mb4), convert(`serverown` using utf8mb4), convert(`status` using utf8mb4), convert(`rack` using utf8mb4), convert(`unit` using utf8mb4), convert(`sysversion` using utf8mb4), `order_time`, convert(`comment` using utf8mb4), `update_time`, `onlinetime`, `offlinetime`, convert(`roles` using utf8mb4), convert(`services` using utf8mb4), convert(`install_status` using utf8mb4), convert(`service_env` using utf8mb4), convert(`host_type` using utf8mb4), convert(`kvm_local` using utf8mb4), CONCAT(ISNULL(`order_time`), ISNULL(`onlinetime`), ISNULL(`offlinetime`), ISNULL(`roles`), ISNULL(`services`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `bportal`.`server_basic_info` /*explain checksum table*/: DBD::mysql::st execute failed: Unknown character set: 'utf8mb4' [for Statement "EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, convert(`uuid` using utf8mb4), convert(`city` using utf8mb4), convert(`bassert` using utf8mb4), convert(`hostname` using utf8mb4), `idc_id`, `dp_id`, convert(`vlanip` using utf8mb4), convert(`wlanip` using utf8mb4), convert(`remote_ip` using utf8mb4), convert(`network_card` using utf8mb4), convert(`mac` using utf8mb4), convert(`serverown` using utf8mb4), convert(`status` using utf8mb4), convert(`rack` using utf8mb4), convert(`unit` using utf8mb4), convert(`sysversion` using utf8mb4), `order_time`, convert(`comment` using utf8mb4), `update_time`, `onlinetime`, `offlinetime`, convert(`roles` using utf8mb4), convert(`services` using utf8mb4), convert(`install_status` using utf8mb4), convert(`service_env` using utf8mb4), convert(`host_type` using utf8mb4), convert(`kvm_local` using utf8mb4), CONCAT(ISNULL(`order_time`), ISNULL(`onlinetime`), ISNULL(`offlinetime`), ISNULL(`roles`), ISNULL(`services`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `bportal`.`server_basic_info` /*explain checksum table*/"] at /usr/bin/pt-table-checksum line 12302.

05-13T18:42:19 Error checksumming table bportal.server_basic_info: Error executing checksum query: DBD::mysql::st execute failed: Unknown character set: 'utf8mb4' [for Statement "REPLACE INTO `test`.`checksums` (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(`uuid` using utf8mb4), convert(`city` using utf8mb4), convert(`bassert` using utf8mb4), convert(`hostname` using utf8mb4), `idc_id`, `dp_id`, convert(`vlanip` using utf8mb4), convert(`wlanip` using utf8mb4), convert(`remote_ip` using utf8mb4), convert(`network_card` using utf8mb4), convert(`mac` using utf8mb4), convert(`serverown` using utf8mb4), convert(`status` using utf8mb4), convert(`rack` using utf8mb4), convert(`unit` using utf8mb4), convert(`sysversion` using utf8mb4), `order_time`, convert(`comment` using utf8mb4), `update_time`, `onlinetime`, `offlinetime`, convert(`roles` using utf8mb4), convert(`services` using utf8mb4), convert(`install_status` using utf8mb4), convert(`service_env` using utf8mb4), convert(`host_type` using utf8mb4), convert(`kvm_local` using utf8mb4), CONCAT(ISNULL(`order_time`), ISNULL(`onlinetime`), ISNULL(`offlinetime`), ISNULL(`roles`), ISNULL(`services`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `bportal`.`server_basic_info` /*checksum table*/" with ParamValues: 0='bportal', 1='server_basic_info', 2=1, 3=undef, 4=undef, 5=undef] at /usr/bin/pt-table-checksum line 11691.


            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
05-13T18:42:19      2      0        0          0       1       0   0.008 bportal.server_basic_info

于是排除问题：

查看MySQL 版本
查看MySQL 字符集

查看数据库和表的字符集

google查找问题原因

发现篇文章和该错误几乎一致。说到pt-table-checksum已经不支持MySQL 5.1版本。

Thank you for the report.

The error is expected since  character set utf8mb4 Unknown to the MySQL 5.1

MySQL 5.1 is not a supported version: https://www.percona.com/services/support/mysql-support/percona-toolkit-supported-platforms-and-versions

写了一个patch

pt-table-checksum打补丁

1	patch pt-table-checksum < pt-table-checksum.utf8mb4.patch

执行命令

pt-table-checksum --set-vars innodb_lock_wait_timeout=200 --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=***** --host=*** --port=*** --user=*** --password='*****' --recursion-method='processlist'

发现没有上面的问题，一切都正常。在执行

1	pt-table-sync --replicate=test.checksums --databases=*** --charset=utf8 h=,P=,u=,p='**' --execute

总结

使用MySQL 5.1版本的数据库赶快升级吧。太折腾人了。

参考

Percona: jira