首页云计算正文

pt-archiver Bug不会迁移max(id)那条数据

admin

2024-11-19 6 0条评论

首页
证书相关
pt-archiver Bug不会迁移max(id)那条数据

pt-archiver Bug不会迁移max(id)那条数据

发布日期：2017-04-01

工具版本: pt-archiver 2.2.14

源表名: ord_order

目标表名: ord_order_1

表结构相同:

1 2 3 4 5 6 7 8 9 10 11 12 13

root @ test 15 : 09 : 54 > show create table ord _order \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * Table : ord_order Create Table : CREATE TABLE ` ord_order ` ( ` order_id ` int ( 11 ) NOT NULL AUTO _INCREMENT COMMENT ‘订单ID’ , ` amount ` int ( 11 ) NOT NULL DEFAULT ‘0’ COMMENT ‘订单金额’ , ` create_time ` datetime NOT NULL DEFAULT CURRENT _TIMESTAMP COMMENT ‘创建时间’ , ` pay_type ` tinyint ( 4 ) NOT NULL DEFAULT ‘1’ COMMENT ‘支付类型’ , ` serial_num ` varchar ( 16 ) DEFAULT NULL COMMENT ‘余额交易流水号’ , PRIMARY KEY ( ` order_id ` ) , KEY ` idx $ pay_type ` ( ` pay_type ` ) ) ENGINE = InnoDB AUTO_INCREMENT = 185 DEFAULT CHARSET = utf8mb4 COMMENT = ‘订单表’ 1 row in set ( 0.00 sec )

使用如下语句始终会有一条数据迁移不了

1 2 3 4 5 6 7 8

pt – archiver \ — source h = 127.0.0.1 , D = test , t = ord_order , u = root , p = oracle \ — dest h = 127.0.0.1 , D = test , t = ord_order_1 , u = root , p = oracle \ — where ‘1=1’ \ — no – check – charset \ — limit = 10000 \ — progress = 10000 \ — statistics

迁移完后查看数据，还有一条数据存在

1 2 3 4 5 6 7

无奈之下只能打开 general_log 并且再次运行上面 pt-archiver 并查看日志

1 2 3 4 5 6

34 Query SELECT MAX ( ` order_id ` ) FROM ` test ` . ` ord_order ` 34 Query SELECT CONCAT ( @ @ hostname , @ @ port ) 35 Query SELECT CONCAT ( @ @ hostname , @ @ port ) 34 Query SELECT /*!40001 SQL_NO_CACHE */ ` order_id ` , ` amount ` , ` create_time ` , ` pay_type ` , ` serial_num ` FROM ` test ` . ` ord_order ` FORCE INDEX ( ` PRIMARY ` ) WHERE ( order _id > 1 ) AND ( ` order_id ` < ‘184’ ) ORDER BY ` order_id ` LIMIT 10000 34 Quit 35 Quit

可以看到主要的插叙语句，这里我们关注的SQL有

1 2

SELECT MAX ( ` order_id ` ) FROM ` test ` . ` ord_order ` SELECT /*!40001 SQL_NO_CACHE */ ` order_id ` , ` amount ` , ` create_time ` , ` pay_type ` , ` serial_num ` FROM ` test ` . ` ord_order ` FORCE INDEX ( ` PRIMARY ` ) WHERE ( order _id > 1 ) AND ( ` order_id ` < ‘184’ ) ORDER BY ` order_id ` LIMIT 10000

发现第二条语句多加了一个条件 (order_id < ‘184’)

很明显这样的条件是查询不到第 184 条记录的

这是我们只能是自行修改 pt-archiver 文件代码, 相关代码在5813行(不同版本的pt-archiver就不同)

原来:

1	5813 $ first _sql . = ” AND ($col < “ . $ q -> quote_val ( $ val ) . “)” ;

修改后:

1	5813 $ first _sql . = ” AND ($col <= “ . $ q -> quote_val ( $ val ) . “)” ;

修改后再次运行下面代码:

1 2 3 4 5 6 7 8

并查看日志：

1 2 3 4 5 6 7 8

48 Query SELECT MAX ( ` order_id ` ) FROM ` test ` . ` ord_order ` 48 Query SELECT CONCAT ( @ @ hostname , @ @ port ) 49 Query SELECT CONCAT ( @ @ hostname , @ @ port ) 48 Query SELECT /*!40001 SQL_NO_CACHE */ ` order_id ` , ` amount ` , ` create_time ` , ` pay_type ` , ` serial_num ` FROM ` test ` . ` ord_order ` FORCE INDEX ( ` PRIMARY ` ) WHERE ( 1 = 1 ) AND ( ` order_id ` <= ‘184’ ) ORDER BY ` order_id ` LIMIT 10000 49 Query INSERT INTO ` test ` . ` ord_order_1 ` ( ` order_id ` , ` amount ` , ` create_time ` , ` pay_type ` , ` serial_num ` ) VALUES ( ‘184’ , ‘0’ , ‘2016-11-17 10:58:33’ , ’12’ , NULL ) 49 Query commit 48 Query commit 48 Query SELECT /*!40001 SQL_NO_CACHE */ ` order_id ` , ` amount ` , ` create_time ` , ` pay_type ` , ` serial_num ` FROM ` test ` . ` ord_order ` FORCE INDEX ( ` PRIMARY ` ) WHERE ( 1 = 1 ) AND ( ` order_id ` <= ‘184’ ) AND ( ( ` order_id ` > ‘184’ ) ) ORDER BY ` order_id ` LIMIT 10000

文章转载来自：trustauth.cn

标签： wordpress教程

上一篇：MySQL管理工具MySQL Utilities — 查询某个连接用户的信息(19)

下一篇：MySQL管理工具MySQL Utilities — 复制移动审计日志(17)

pt-archiver Bug不会迁移max(id)那条数据

相关新闻

相关文章