[Bug]: restore primary table from pitr report 'internal error: can not drop database. It has been referenced by foreign keys'.

[Ariznawlll]

Is there an existing issue for the same bug?

• [X] I have checked the existing issues.

Branch Name

main

Commit ID

b84863895

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

Expected Behavior

如果恢复的时候从表存在，则跳过恢复主表，所以不应该报错

Steps to Reproduce

create pitr p01 range 10 'd';
drop database if exists test03;
create database test03;
use test03;

select now();   -->标记点

drop table if exists pri01;
create table pri01(
                     deptno int unsigned comment '部门编号',
                     dname varchar(15) comment '部门名称',
                     loc varchar(50)  comment '部门所在位置',
                     primary key(deptno)
) comment='部门表';

insert into pri01 values (10,'ACCOUNTING','NEW YORK');
insert into pri01 values (20,'RESEARCH','DALLAS');

drop database if exists test02;
create database test02;
use test02;
drop table if exists aff01;
create table aff01(
                    empno int unsigned auto_increment COMMENT '雇员编号',
                    ename varchar(15) comment '雇员姓名',
                    job varchar(10) comment '雇员职位',
                    mgr int unsigned comment '雇员对应的领导的编号',
                    hiredate date comment '雇员的雇佣日期',
                    sal decimal(7,2) comment '雇员的基本工资',
                    comm decimal(7,2) comment '奖金',
                    deptno int unsigned comment '所在部门',
                    primary key(empno),
                    constraint `c1` foreign key (deptno) references test03.pri01(deptno));

insert into aff01 values (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,10);

restore database test03 from pitr p01 "";   -->标记点对应的时间戳

Additional information

No response

[YANGGMM]

drop database if exists test03; create database test03; use test03;

create snapshot sp01 for account sys;

drop table if exists pri01; create table pri01( deptno int unsigned comment '部门编号', dname varchar(15) comment '部门名称', loc varchar(50) comment '部门所在位置', primary key(deptno) ) comment='部门表';

insert into pri01 values (10,'ACCOUNTING','NEW YORK'); insert into pri01 values (20,'RESEARCH','DALLAS');

drop database if exists test02; create database test02; use test02; drop table if exists aff01; create table aff01( empno int unsigned auto_increment COMMENT '雇员编号', ename varchar(15) comment '雇员姓名', job varchar(10) comment '雇员职位', mgr int unsigned comment '雇员对应的领导的编号', hiredate date comment '雇员的雇佣日期', sal decimal(7,2) comment '雇员的基本工资', comm decimal(7,2) comment '奖金', deptno int unsigned comment '所在部门', primary key(empno), constraint c1 foreign key (deptno) references test03.pri01(deptno));

insert into aff01 values (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,10);

restore account sys database test03 from snapshot sp01 ;

[YANGGMM]

fixed，snapshot 和 pitr都需要测试 @Ariznawlll

[Ariznawlll]

mysql> select git_version();
+---------------+
| git_version() |
+---------------+
| f9b836823     |
+---------------+
1 row in set (0.00 sec)

create pitr p01 range 10 'd';
drop database if exists test03;
create database test03;
use test03;

select now();   -->标记点

drop table if exists pri01;
create table pri01(
                     deptno int unsigned comment '部门编号',
                     dname varchar(15) comment '部门名称',
                     loc varchar(50)  comment '部门所在位置',
                     primary key(deptno)
) comment='部门表';

insert into pri01 values (10,'ACCOUNTING','NEW YORK');
insert into pri01 values (20,'RESEARCH','DALLAS');

drop database if exists test02;
create database test02;
use test02;
drop table if exists aff01;
create table aff01(
                    empno int unsigned auto_increment COMMENT '雇员编号',
                    ename varchar(15) comment '雇员姓名',
                    job varchar(10) comment '雇员职位',
                    mgr int unsigned comment '雇员对应的领导的编号',
                    hiredate date comment '雇员的雇佣日期',
                    sal decimal(7,2) comment '雇员的基本工资',
                    comm decimal(7,2) comment '奖金',
                    deptno int unsigned comment '所在部门',
                    primary key(empno),
                    constraint `c1` foreign key (deptno) references test03.pri01(deptno));

insert into aff01 values (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,10);

restore database test03 from pitr p01 ""; 

pitr测试结果：

snapshot测试结果：

测试✅