Public bug reported:
When pt-archiver archives data, the row with the largest primary key is
lost.Through MySQL general log, you can see that the issued select statement
uses the "primary key" < "max primary key", which causes the data of the row
where "max primary key" is lost.
# pt-archiver --version
pt-archiver 3.0.12
How to repeat:
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql>insert into t1 values(NULL,'aaa','2019-01-01 00:00:00');
mysql>insert into t1 values(NULL,'aaa','2019-01-31 08:00:00');
mysql>insert into t1 values(NULL,'aaa','2019-02-15 10:10:00');
mysql>insert into t1 values(NULL,'aaa','2019-02-28 10:10:00');
mysql>insert into t1 values(NULL,'aaa','2019-03-28 10:10:00');
mysql>insert into t1 values(NULL,'aaa','2019-03-28 12:10:00');
mysql>insert into t1 values(NULL,'aaa','2019-03-28 18:10:00');
mysql>insert into t1 values(NULL,'aaa','2019-04-05 10:10:00');
mysql>insert into t1 values(99,'aaa','2019-01-05 10:10:00');
mysql>insert into t1 values(100,'aaa','2019-01-05 10:10:00');
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> select * from t1;
+-----+------+---------------------+
| id | name | datetime |
+-----+------+---------------------+
| 1 | bbb | 2019-01-01 00:00:00 |
| 2 | aaa | 2019-01-31 08:00:00 |
| 3 | aaa | 2019-02-15 10:10:00 |
| 4 | aaa | 2019-02-28 10:10:00 |
| 5 | aaa | 2019-03-28 10:10:00 |
| 6 | aaa | 2019-03-28 12:10:00 |
| 7 | aaa | 2019-03-28 18:10:00 |
| 8 | aaa | 2019-04-05 10:10:00 |
| 99 | aaa | 2019-01-05 10:10:00 |
| 100 | aaa | 2019-01-05 10:10:00 |
+-----+------+---------------------+
10 rows in set (0.00 sec)
# pt-archiver --version
pt-archiver 3.0.12
# pt-archiver --source u=archiver,p=archiver,h=192.168.122.1,P=3306,D=db,t=t1
--dest u=archiver,p=archiver,h=192.168.122.1,P=3306,D=db,t=t2 --where="datetime
<= '2019-04-01 00:00:00'" --progress=1000 --statistics --bulk-insert
--bulk-delete --txn-size=1000 --limit=1000 --no-delete --no-check-charset
TIME ELAPSED COUNT
2019-04-18T18:35:12 0 0
2019-04-18T18:35:12 0 8
Started at 2019-04-18T18:35:12, ended at 2019-04-18T18:35:12
Source: D=db,P=3306,h=192.168.122.1,p=...,t=t1,u=archiver
Dest: D=db,P=3306,h=192.168.122.1,p=...,t=t2,u=archiver
SELECT 8
INSERT 0
DELETE 0
Action Count Time Pct
select 2 0.0008 10.10
bulk_inserting 1 0.0005 6.34
commit 2 0.0002 3.06
print_bulkfile 8 -0.0000 -0.10
other 0 0.0062 80.60
mysql> select * from t2;
+----+------+---------------------+
| id | name | datetime |
+----+------+---------------------+
| 1 | bbb | 2019-01-01 00:00:00 |
| 2 | aaa | 2019-01-31 08:00:00 |
| 3 | aaa | 2019-02-15 10:10:00 |
| 4 | aaa | 2019-02-28 10:10:00 |
| 5 | aaa | 2019-03-28 10:10:00 |
| 6 | aaa | 2019-03-28 12:10:00 |
| 7 | aaa | 2019-03-28 18:10:00 |
| 99 | aaa | 2019-01-05 10:10:00 |
+----+------+---------------------+
8 rows in set (0.00 sec)
MySQL general.log
2019-04-18T18:35:12.456372+08:00 53 Query set autocommit=0
2019-04-18T18:35:12.456592+08:00 53 Query SHOW VARIABLES LIKE
'wait\_timeout'
2019-04-18T18:35:12.457837+08:00 53 Query SET SESSION
wait_timeout=10000
2019-04-18T18:35:12.458037+08:00 53 Query SELECT @@SQL_MODE
2019-04-18T18:35:12.458188+08:00 53 Query SET
@@SQL_QUOTE_SHOW_CREATE = 1/*!40101,
@@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2019-04-18T18:35:12.458329+08:00 53 Query SELECT version()
2019-04-18T18:35:12.458523+08:00 53 Query SHOW VARIABLES LIKE
'version%'
2019-04-18T18:35:12.459589+08:00 53 Query SHOW ENGINES
2019-04-18T18:35:12.459937+08:00 53 Query SHOW VARIABLES LIKE
'innodb_version'
2019-04-18T18:35:12.461116+08:00 53 Query show variables like
'innodb_rollback_on_timeout'
2019-04-18T18:35:12.462065+08:00 53 Query /*!40101 SET
@OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE :=
@@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
2019-04-18T18:35:12.462181+08:00 53 Query USE `db`
2019-04-18T18:35:12.462371+08:00 53 Query SHOW CREATE TABLE
`db`.`t1`
2019-04-18T18:35:12.462546+08:00 53 Query /*!40101 SET @@SQL_MODE
:= @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2019-04-18T18:35:12.469688+08:00 53 Query SHOW VARIABLES LIKE
'wsrep_on'
2019-04-18T18:35:12.470870+08:00 53 Query SHOW VARIABLES LIKE
'wsrep_on'
2019-04-18T18:35:12.472094+08:00 53 Query SHOW VARIABLES LIKE
'version%'
2019-04-18T18:35:12.473145+08:00 53 Query SHOW ENGINES
2019-04-18T18:35:12.473497+08:00 53 Query SHOW VARIABLES LIKE
'innodb_version'
2019-04-18T18:35:12.474629+08:00 53 Query SELECT MAX(`id`) FROM
`db`.`t1`
2019-04-18T18:35:12.475051+08:00 53 Query SELECT
CONCAT(@@hostname, @@port)
2019-04-18T18:35:12.476004+08:00 53 Query SELECT /*!40001
SQL_NO_CACHE */ `id` FROM `db`.`t1` FORCE INDEX(`PRIMARY`) WHERE (datetime <=
'2019-04-01 00:00:00') AND (`id` < '100') ORDER BY `id` LIMIT 1000
2019-04-18T18:35:12.481812+08:00 53 Query SELECT 'pt-archiver
keepalive'
2019-04-18T18:35:12.482494+08:00 53 Query SELECT 'pt-archiver
keepalive'
2019-04-18T18:35:12.482662+08:00 53 Query SELECT /*!40001
SQL_NO_CACHE */ `id` FROM `db`.`t1` FORCE INDEX(`PRIMARY`) WHERE (datetime <=
'2019-04-01 00:00:00') AND (`id` < '100') AND ((`id` > '99')) ORDER BY `id`
LIMIT 1000
2019-04-18T18:35:12.483250+08:00 53 Query commit
mysql> insert into t1 values(101,'aaa','2019-01-05 10:10:00');
# pt-archiver --source
u=archiver,p=archiver,h=192.168.122.1,P=3306,D=db,t=t1 --dest
u=archiver,p=archiver,h=192.168.122.1,P=3306,D=db,t=t2 --where="datetime
<= '2019-04-01 00:00:00'" --progress=1000 --statistics --bulk-insert
--bulk-delete --txn-size=1000 --limit=1000 --no-delete --no-check-
charset
mysql> select * from t2;
+-----+------+---------------------+
| id | name | datetime |
+-----+------+---------------------+
| 1 | bbb | 2019-01-01 00:00:00 |
| 2 | aaa | 2019-01-31 08:00:00 |
| 3 | aaa | 2019-02-15 10:10:00 |
| 4 | aaa | 2019-02-28 10:10:00 |
| 5 | aaa | 2019-03-28 10:10:00 |
| 6 | aaa | 2019-03-28 12:10:00 |
| 7 | aaa | 2019-03-28 18:10:00 |
| 99 | aaa | 2019-01-05 10:10:00 |
| 100 | aaa | 2019-01-05 10:10:00 |
+-----+------+---------------------+
# pt-archiver --source u=archiver,p=archiver,h=192.168.122.1,P=3306,D=db,t=t1
--dest u=archiver,p=archiver,h=192.168.122.1,P=3306,D=db,t=t2 --where="datetime
<= '2019-04-01 00:00:00'" --progress=1000 --statistics --bulk-insert
--bulk-delete --txn-size=1000 --limit=1000 --no-check-charset
mysql> select * from t1;
+-----+------+---------------------+
| id | name | datetime |
+-----+------+---------------------+
| 8 | aaa | 2019-04-05 10:10:00 |
| 101 | aaa | 2019-01-05 10:10:00 |
+-----+------+---------------------+
2 rows in set (0.00 sec)
mysql> select * from t2;
+-----+------+---------------------+
| id | name | datetime |
+-----+------+---------------------+
| 1 | bbb | 2019-01-01 00:00:00 |
| 2 | aaa | 2019-01-31 08:00:00 |
| 3 | aaa | 2019-02-15 10:10:00 |
| 4 | aaa | 2019-02-28 10:10:00 |
| 5 | aaa | 2019-03-28 10:10:00 |
| 6 | aaa | 2019-03-28 12:10:00 |
| 7 | aaa | 2019-03-28 18:10:00 |
| 99 | aaa | 2019-01-05 10:10:00 |
| 100 | aaa | 2019-01-05 10:10:00 |
+-----+------+---------------------+
9 rows in set (0.00 sec)
** Affects: percona-toolkit (Ubuntu)
Importance: Undecided
Status: New
** Summary changed:
- pt-archiver los他a row of data
+ pt-archiver lost a row of data
--
You received this bug notification because you are a member of Ubuntu
Bugs, which is subscribed to Ubuntu.
https://bugs.launchpad.net/bugs/1825365
Title:
pt-archiver lost a row of data
To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/percona-toolkit/+bug/1825365/+subscriptions
--
ubuntu-bugs mailing list
ubuntu-bugs@lists.ubuntu.com
https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
