-
Notifications
You must be signed in to change notification settings - Fork 261
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
[Bug]: run tpcc test and then restore report Duplicate entry #16100
Comments
尝试复现 mysql> show create table bmsql_history ;
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bmsql_history | CREATE TABLE `bmsql_history` (
`hist_id` INT NOT NULL AUTO_INCREMENT,
`h_c_id` INT DEFAULT NULL,
`h_c_d_id` INT DEFAULT NULL,
`h_c_w_id` INT DEFAULT NULL,
`h_d_id` INT DEFAULT NULL,
`h_w_id` INT DEFAULT NULL,
`h_date` TIMESTAMP DEFAULT NULL,
`h_amount` DECIMAL(6,2) DEFAULT NULL,
`h_data` VARCHAR(24) DEFAULT NULL,
PRIMARY KEY (`hist_id`)
) |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table `bmsql_history_restore`(`hist_id` INT NOT NULL AUTO_INCREMENT,`h_c_id` INT DEFAULT NULL,`h_c_d_id` INT DEFAULT NULL,`h_c_w_id` INT DEFAULT NULL,`h_d_id` INT DEFAULT NULL,`h_w_id` INT DEFAULT NULL,`h_date` TIMESTAMP DEFAULT NULL,`h_amount` DECIMAL(6,2) DEFAULT NULL,`h_data` VARCHAR(24) DEFAULT NULL,PRIMARY KEY (`hist_id`));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into `bmsql_history_restore` select * from `bmsql_history` {snapshot = 'sp_01'};
Query OK, 300000 rows affected (3.27 sec)
恢复成功,而且数据量正确 |
用restore 报错 mysql> restore account sys database tpcc table `bmsql_history` from snapshot sp_01;
ERROR 1062 (HY000): Duplicate entry '8193' for key 'hist_id' |
测试数据量最大的表restore,正常恢复 mysql> restore account sys database tpcc table bmsql_order_line from snapshot sp_01;
Query OK, 0 rows affected (12 min 57.49 sec) 恢复后数据一切正常 mysql> select count(*) from bmsql_order_line;
+----------+
| count(*) |
+----------+
| 2998810 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from bmsql_order_line{snapshot = 'sp_01'};
+----------+
| count(*) |
+----------+
| 2998810 |
+----------+
1 row in set (0.04 sec) |
mysql> restore account sys database tpcc table `bmsql_history` from snapshot sp_01;
ERROR 1062 (HY000): Duplicate entry '16385' for key 'hist_id'
mysql>
|
--load file create snapshot sp_01 for account sys;
restore account sys database tpcc table `bmsql_history` from snapshot sp_01; |
|
单独开事务执行是没有问题的 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists `bmsql_history`;
Query OK, 0 rows affected (0.01 sec)
mysql> use tpcc;
Database changed
mysql>
mysql> create table `bmsql_history` (`hist_id` INT NOT NULL AUTO_INCREMENT,`h_c_id` INT DEFAULT NULL,`h_c_d_id` INT DEFAULT NULL,`h_c_w_id` INT DEFAULT NULL,`h_d_id` INT DEFAULT NULL,`h_w_id` INT DEFAULT NULL,`h_date` TIMESTAMP DEFAULT NULL,`h_amount` DECIMAL(6,2) DEFAULT NULL,`h_data` VARCHAR(24) DEFAULT NULL,PRIMARY KEY (`hist_id`));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into `tpcc`.`bmsql_history` SELECT * FROM `tpcc`.`bmsql_history` {snapshot = 'sp_01'};
Query OK, 300000 rows affected (1.93 sec)
mysql> commit;
Query OK, 0 rows affected (0.48 sec) |
mo-service-backend.log 分别打印了frontend/backend执行的snapshot进行对比, 发现frontend比backend多了点东西, 需要向相关同事请教一下是什么, 打印日志的基本格式如下所示 // func restoreToDatabaseOrTable
if tblName != "" {
if b, _ := bh.(*backExec); b != nil {
fmt.Println("---")
fmt.Println("drop table")
fmt.Println(b.backSes.txnHandler.txnOp.GetOverview().Meta.SnapshotTS)
fmt.Println("---")
}
if err = bh.Exec(toCtx, "drop table if exists "+dbName+"."+tblName); err != nil {
return
}
}
|
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> drop table if exists `bmsql_history`;
Query OK, 0 rows affected (0.03 sec)
mysql> use tpcc;
Database changed
mysql>
mysql> show full tables from `tpcc` {snapshot = 'sp_01'};
+------------------+------------+
| Tables_in_tpcc | Table_type |
+------------------+------------+
| bmsql_config | BASE TABLE |
| bmsql_customer | BASE TABLE |
| bmsql_district | BASE TABLE |
| bmsql_history | BASE TABLE |
| bmsql_item | BASE TABLE |
| bmsql_new_order | BASE TABLE |
| bmsql_oorder | BASE TABLE |
| bmsql_order_line | BASE TABLE |
| bmsql_stock | BASE TABLE |
| bmsql_warehouse | BASE TABLE |
+------------------+------------+
10 rows in set (0.02 sec)
mysql> show create table `tpcc`.`bmsql_history` {snapshot = 'sp_01'};
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bmsql_history | CREATE TABLE `bmsql_history` (
`hist_id` INT NOT NULL AUTO_INCREMENT,
`h_c_id` INT DEFAULT NULL,
`h_c_d_id` INT DEFAULT NULL,
`h_c_w_id` INT DEFAULT NULL,
`h_d_id` INT DEFAULT NULL,
`h_w_id` INT DEFAULT NULL,
`h_date` TIMESTAMP DEFAULT NULL,
`h_amount` DECIMAL(6,2) DEFAULT NULL,
`h_data` VARCHAR(24) DEFAULT NULL,
PRIMARY KEY (`hist_id`)
) |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> CREATE TABLE `bmsql_history` (
-> `hist_id` INT NOT NULL AUTO_INCREMENT,
-> `h_c_id` INT DEFAULT NULL,
-> `h_c_d_id` INT DEFAULT NULL,
-> `h_c_w_id` INT DEFAULT NULL,
-> `h_d_id` INT DEFAULT NULL,
-> `h_w_id` INT DEFAULT NULL,
-> `h_date` TIMESTAMP DEFAULT NULL,
-> `h_amount` DECIMAL(6,2) DEFAULT NULL,
-> `h_data` VARCHAR(24) DEFAULT NULL,
-> PRIMARY KEY (`hist_id`)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into `tpcc`.`bmsql_history` SELECT * FROM `tpcc`.`bmsql_history` {snapshot = 'sp_01'};
Query OK, 300000 rows affected (8.63 sec)
mysql> commit;
Query OK, 0 rows affected (0.48 sec) |
在statement 执行入口添加log -----------------------
executeStmtWithIncrStmt
sql : restore account sys database tpcc table `bmsql_history` from snapshot sp_01
snapshotTs : 1715844568861921000
-----------------------
-----------------------
executeStmtWithIncrStmt
sql : drop table if exists tpcc.bmsql_history
snapshotTs : 1715844568865944000
-----------------------
-----------------------
executeStmtWithIncrStmt
sql : show full tables from `tpcc` {snapshot = 'sp_01'}
snapshotTs : 1715844568884104000
-----------------------
-----------------------
executeStmtWithIncrStmt
sql : show create table `tpcc`.`bmsql_history` {snapshot = 'sp_01'}
snapshotTs : 1715844568887886000
-----------------------
-----------------------
executeStmtWithIncrStmt
sql : CREATE TABLE `bmsql_history` (
`hist_id` INT NOT NULL AUTO_INCREMENT,
`h_c_id` INT DEFAULT NULL,
`h_c_d_id` INT DEFAULT NULL,
`h_c_w_id` INT DEFAULT NULL,
`h_d_id` INT DEFAULT NULL,
`h_w_id` INT DEFAULT NULL,
`h_date` TIMESTAMP DEFAULT NULL,
`h_amount` DECIMAL(6,2) DEFAULT NULL,
`h_data` VARCHAR(24) DEFAULT NULL,
PRIMARY KEY (`hist_id`)
)
snapshotTs : 1715844568887886000
-----------------------
-----------------------
executeStmtWithIncrStmt
sql : insert into `tpcc`.`bmsql_history` SELECT * FROM `tpcc`.`bmsql_history` {snapshot = 'sp_01'}
snapshotTs : 1715844568903943000
----------------------- |
frontend 执行的log -----------------------
executeStmtWithIncrStmt
sql : drop table if exists tpcc.bmsql_history
snapshotTs : 1715845293989984000
-----------------------
-----------------------
executeStmtWithIncrStmt
sql : show full tables from `tpcc` {snapshot = 'sp_01'}
snapshotTs : 1715845319434256000
-----------------------
-----------------------
executeStmtWithIncrStmt
sql : show create table `tpcc`.`bmsql_history` {snapshot = 'sp_01'}
snapshotTs : 1715845329498667000
-----------------------
-----------------------
executeStmtWithIncrStmt
sql : CREATE TABLE `bmsql_history` ( `hist_id` INT NOT NULL AUTO_INCREMENT, `h_c_id` INT DEFAULT NULL, `h_c_d_id` INT DEFAULT NULL, `h_c_w_id` INT DEFAULT NULL, `h_d_id` INT DEFAULT NULL, `h_w_id` INT DEFAULT NULL, `h_date` TIMESTAMP DEFAULT NULL, `h_amount` DECIMAL(6,2) DEFAULT NULL, `h_data` VARCHAR(24) DEFAULT NULL, PRIMARY KEY (`hist_id`) )
snapshotTs : 1715845333868618000
-----------------------
-----------------------
executeStmtWithIncrStmt
sql : insert into `tpcc`.`bmsql_history` SELECT * FROM `tpcc`.`bmsql_history` {snapshot = 'sp_01'}
snapshotTs : 1715845337754797000
----------------------- |
The cause had been located, wait for PR |
|
Got it, deal with it as soon as possible |
Positioning |
问题原因详情已经 确认,请参考文档 |
Is there an existing issue for the same bug?
Branch Name
main
Commit ID
newest
Other Environment Information
Actual Behavior
Expected Behavior
No response
Steps to Reproduce
Additional information
No response
The text was updated successfully, but these errors were encountered: