[Bug]: restore multi database contains view table and fk table report no such table

[YANGGMM]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

main 1.2-dev

Commit ID

newest

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

Expected Behavior

No response

Steps to Reproduce

create database test03;
create database test04;
create database test05;

use test03;
drop table if exists departments;
create table departments (
                             department_id INT PRIMARY KEY,
                             department_name VARCHAR(100)
);

insert into departments (department_id, department_name)
values (1, 'HR'),
       (2, 'Engineering');

use test04;
drop table if exists employees;
create table employees (
                           employee_id INT PRIMARY KEY,
                           first_name VARCHAR(50),
                           last_name VARCHAR(50),
                           department_id INT,
                           FOREIGN KEY (department_id) REFERENCES test03.departments(department_id)
);

insert into employees values
                          (1, 'John', 'Doe', 1),
                          (2, 'Jane', 'Smith', 2),
                          (3, 'Bob', 'Johnson', 1);

use test04;
drop view if exists employee_view;
create view employee_view as select employee_id, first_name, last_name, department_id from test04.employees;
select * from employee_view;

use test03;
drop view if exists department_view;
create view department_view as select department_id, department_name from test03.departments;
select * from department_view;

use test05;
drop view if exists employee_with_department_view;
create view employee_with_department_view as
select e.employee_id, e.first_name, e.last_name, d.department_name
from test04.employee_view e join test03.department_view d on e.department_id = d.department_id;
select * from employee_with_department_view;

drop snapshot if exists sp100;
create snapshot sp100 for account sys;

drop database test04;
drop database test03;
drop database test05;

restore account sys from snapshot sp100; -->出错位置

Additional information

No response

[YANGGMM]

{"level":"INFO","time":"2024/05/22 14:59:09.935246 +0800","name":"log-service.frontend","caller":"frontend/snapshot.go:633","msg":"[sp100] start to create table: employees, create table sql: CREATE TABLE `employees` (\n  `employee_id` INT NOT NULL,\n  `first_name` VARCHAR(50) DEFAULT NULL,\n  `last_name` VARCHAR(50) DEFAULT NULL,\n  `department_id` INT DEFAULT NULL,\n  PRIMARY KEY (`employee_id`),\n  CONSTRAINT `018f9f0f-c423-7641-95f3-cd451283f847` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n)","uuid":"7c4dccb4-4d3c-41f8-b482-5251dc7a41bf"}

[YANGGMM]

origin create table sql:
create table employees (
                           employee_id INT PRIMARY KEY,
                           first_name VARCHAR(50),
                           last_name VARCHAR(50),
                           department_id INT,
                           FOREIGN KEY (department_id) REFERENCES test03.departments(department_id)
);

now create table sql:

CREATE TABLE `employees` (
                        \n  `employee_id` INT NOT NULL,
                        \n  `first_name` VARCHAR(50) DEFAULT NULL,
                        \n  `last_name` VARCHAR(50) DEFAULT NULL,
                         \n  `department_id` INT DEFAULT NULL,
                         \n  PRIMARY KEY (`employee_id`),
                        \n  CONSTRAINT `018f9f0f-c423-7641-95f3-cd451283f847` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n
);

[YANGGMM]

show create table 获取外键表定义的时候会丢掉db info

mysql

同一个db内的不用展示

[Ariznawlll]

commit：0a99a7a5fc08c9927172bc34fd5089fe8538e4ea
view涉及到多个db还是有问题，case如下：

drop database if exists test03;
drop database if exists test04;
drop database if exists test05;

create database test03;
create database test04;
create database test05;

use test03;
drop table if exists departments;
create table departments (
                             department_id INT PRIMARY KEY,
                             department_name VARCHAR(100)
);

insert into departments (department_id, department_name)
values (1, 'HR'),
       (2, 'Engineering');

use test04;
drop table if exists employees;
create table employees (
                           employee_id INT PRIMARY KEY,
                           first_name VARCHAR(50),
                           last_name VARCHAR(50),
                           department_id INT,
                           FOREIGN KEY (department_id) REFERENCES test03.departments(department_id)
);

insert into employees values
                          (1, 'John', 'Doe', 1),
                          (2, 'Jane', 'Smith', 2),
                          (3, 'Bob', 'Johnson', 1);

use test04;
drop view if exists employee_view;
create view employee_view as select employee_id, first_name, last_name, department_id from test04.employees;
select * from employee_view;

use test03;
drop view if exists department_view;
create view department_view as select department_id, department_name from test03.departments;
select * from department_view;

use test05;
drop view if exists employee_with_department_view;
create view employee_with_department_view as
select e.employee_id, e.first_name, e.last_name, d.department_name
from test04.employee_view e join test03.department_view d on e.department_id = d.department_id;
select * from employee_with_department_view;
-- @session

drop snapshot if exists sp100;
create snapshot sp100 for account acc01;

-- @session:id=1&user=acc01:test_account&password=111
drop database test04;
select * from test04.employee_view;
select * from test03.department_view;
select * from test05.employee_with_department_view;
-- @session

drop snapshot if exists sp101;
create snapshot sp101 for account acc01;

-- @session:id=1&user=acc01:test_account&password=111
drop database test03;
drop database test05;
select * from test04.employee_view;
select * from test03.department_view;
select * from test05.employee_with_department_view;
-- @session

restore account acc01 from snapshot sp100;   -->出问题地方

[Ariznawlll]

test done