pt-archiver utf8mb4 Wide character emjo error
Description
Environment
Smart Checklist
Activity
Lalit Choudhary June 11, 2020 at 11:26 AM
Hi @shawnloong.huang
Thank you for the report.
Validated as described.
Test case:
CREATE TABLE `user_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login_name` varchar(30) DEFAULT NULL,
`login_pass` varchar(200) DEFAULT NULL,
`address` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4
mysql [localhost] {msandbox} (test) > select * from user_info;
+----+------------+----------------------------------+-------------------------------------------+
| id | login_name | login_pass | address |
+----+------------+----------------------------------+-------------------------------------------+
| 1 | user1 | e0f1ecba054443075990a2497fcd006a | 安徽省鑫市永川巢湖路F座 744349 |
| 2 | user1 | e0f1ecba054443075990a2497fcd006a | 安徽省鑫市永川巢湖路F座 744349 |
| 3 | user1 | e0f1ecba054443075990a2497fcd006a | 安徽省鑫市永川巢湖路F座 744349 |
| 4 | user1 | e0f1ecba054443075990a2497fcd006a | 安徽省鑫市永川巢湖路F座 744349 |
| 5 | user1 | e0f1ecba054443075990a2497fcd006a | 安徽省鑫市永川巢湖路F座 744349 |
| 6 | user1 | e0f1ecba054443075990a2497fcd006a | 安徽省鑫市永川巢湖路F座 744349 |
| 7 | user1 | e0f1ecba054443075990a2497fcd006a | 福建省西安市清城茅路j座 545045 |
| 8 | user1 | e0f1ecba054443075990a2497fcd006a | 福建省西安市清城茅路j座 545045 |
| 9 | user1 | e0f1ecba054443075990a2497fcd006a | 福建省西安市清城茅路j座 545045 |
| 10 | user1 | e0f1ecba054443075990a2497fcd006a | 福建省西安市清城茅路j座 545045 |
+----+------------+----------------------------------+-------------------------------------------+
10 rows in set (0.00 sec)
Destination mysql server:
CREATE TABLE `user_info_copy` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login_name` varchar(30) DEFAULT NULL,
`login_pass` varchar(200) DEFAULT NULL,
`address` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
$ ../../mysql_tar/percona-toolkit-3.1.0/bin/pt-archiver --source A=utf8mb4,h=127.0.0.1,P=5730,u=msandbox,p=msandbox,D=test,t=user_info --dest h=127.0.0.1,P=5722,u=msandbox,p=msandbox,D=test,t=user_info_copy --where 'id=2' --progress 5 --limit=50 --txn-size 400 --bulk-insert --bulk-delete --purge --statistics --no-safe-auto-increment --no-version-check
TIME ELAPSED COUNT
2020-06-11T16:49:54 0 0
Wide character in print at ../../mysql_tar/percona-toolkit-3.1.0/bin/pt-archiver line 6769.
In other cases same work fine.
CREATE TABLE `sbtest95` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) CHARACTER SET latin1 NOT NULL DEFAULT '',
`pad` char(60) CHARACTER SET latin1 NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_95` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
$ ../../mysql_tar/percona-toolkit-3.1.0/bin/pt-archiver --source A=utf8mb4,h=127.0.0.1,P=5730,u=msandbox,p=msandbox,D=test,t=sbtest95 --dest h=127.0.0.1,P=5722,u=msandbox,p=msandbox,D=test,t=sbtest95 --where 'id=2' --progress 5 --limit=50 --txn-size 400 --bulk-insert --bulk-delete --purge --statistics --no-safe-auto-increment --no-version-check
TIME ELAPSED COUNT
2020-06-11T16:52:59 0 0
2020-06-11T16:52:59 0 1
Started at 2020-06-11T16:52:59, ended at 2020-06-11T16:52:59
Source: A=utf8mb4,D=test,P=5730,h=127.0.0.1,p=...,t=sbtest95,u=msandbox
Dest: A=utf8mb4,D=test,P=5722,h=127.0.0.1,p=...,t=sbtest95,u=msandbox
SELECT 1
INSERT 1
DELETE 1
Action Count Time Pct
commit 2 0.0086 19.43
select 2 0.0046 10.45
bulk_deleting 1 0.0042 9.55
bulk_inserting 1 0.0021 4.62
print_bulkfile 1 0.0000 0.03
other 0 0.0248 55.91
Aaron May 23, 2020 at 8:46 AM
Option"--Bulk-insert" conflict with utf8mb4 table !!!
I guess Maybe bulk-insert use CSV file can‘t support utf8mb4,
But the single-line mode is too slow, pt-archiver should support batch commit like this :
"insert into dest_table values (), (), ()..."
=============
single row mode , This is ok
pt-archiver --no-version-check --no-check-charset --no-delete --statistics --txn-size=1000 --limit=1000 --source \
A=utf8mb4,h=rr-.mysql.rds.aliyuncs.com,L=1,P=3306,u=,p='',D=arch_test,t=t_order_ext --dest \
A=utf8mb4,h=pc-.rwlb.rds.aliyuncs.com,L=1,P=3306,u=,p='',D=archiver_test,t=t_order_ext_202002 --where "create_time < '2020-02-01 01:00:00' "
----------------
bulk-insert mode , there is error
pt-archiver --no-version-check --no-check-charset --no-delete --bulk-insert --statistics --txn-size=1000 --limit=1000 --source \
A=utf8mb4,h=rr-.mysql.rds.aliyuncs.com,L=1,P=3306,u=,p='',D=arch_test,t=t_order_ext --dest \
A=utf8mb4,h=pc-.rwlb.rds.aliyuncs.com,L=1,P=3306,u=,p='',D=archiver_test,t=t_order_ext_202002 --where "create_time < '2020-02-01 01:00:00' "
2 Wide character in print at /usr/local/Cellar/percona-toolkit/3.0.13_1/libexec/bin/pt-archiver line 6768.
[log] 归档失败Wide character in print at /usr/local/Cellar/percona-toolkit/3.0.13_1/libexec/bin/pt-archiver line 6768.
The following data is false,
system@mysql-137-3-3306 17:21: [netdata]> select * from user_info limit 3\G 1. row *************************** id: 1 login_name: ming95 login_pass: e0f1ecba054443075990a2497fcd006a real_name: 刘建国 sex: M link_mobile: 13913984540 email: weiyang@gmail.com address: 安徽省鑫市永川巢湖路F座 744349 birthdate: 1998-10-08 id_card: 510725194612081053 create_time: 2020-04-25 22:50:38 modify_time: 2020-04-25 22:50:38 2. row *************************** id: 2 login_name: fu login_pass: 51b75e31e673fb5541717c6b6c3d7242 real_name: 刘博 sex: F link_mobile: 13123035804 email: yan15@gmail.com address: 福建省西安市清城茅路j座 545045 birthdate: 1908-10-06 id_card: 340123199009120367 create_time: 2020-04-25 22:50:38 modify_time: 2020-04-25 22:50:38 3. row *************************** id: 3 login_name: xiaxiao login_pass: 2242fe1295076125651269005c661694 real_name: xxxx sex: F link_mobile: 13020444126 email: juan04@gmail.com address: 福建省飞县闵行柳州路m座 954330 birthdate: 1983-12-05 id_card: 510401193007048595 create_time: 2020-04-25 22:50:38 modify_time: 2020-04-25 22:50:38/usr/bin/pt-archiver --source A=utf8mb4,h=192.168.137.3,P=3306,u=netdata,p=netdata,D=netdata,t=user_info --dest h=192.168.137.3,P=3306,u=netdata,p=netdata,D=netdata,t=his_user_info --where 'id=2' --progress 5000 --limit=5000 --txn-size 400 --bulk-insert --bulk-delete --purge --statistics --no-safe-auto-increment --no-version-check TIME ELAPSED COUNT 2020-05-10T17:29:16 0 0 Wide character in print at /usr/bin/pt-archiver line 6769.