7.1.3 Cluster PITR Filter Bug: Unable to Correctly Filter Databases and Tables

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 7.1.3集群PITR filter过滤bug,无法正确过滤库表

| username: dengqee

Bug Report
Clearly and accurately describe the issue you found. Providing any steps to reproduce the issue can help the development team address it promptly.
【TiDB Version】7.1.3
【Impact of the Bug】
When performing point-in-time recovery (PITR) for a specified database table, the recovery result is incorrect.
【Possible Steps to Reproduce the Issue】

  1. Create a log backup task on Cluster 1.
  2. Perform a full backup to S3 on Cluster 1.
  3. Create databases and tables in Cluster 1 and insert data.
create database test1;
create database test2;
create table test.t1(id int primary key);
create table test.t21(id int primary key);
create table test1.t1(id int primary key);
create table test1.t2(id int primary key);
create table test2.t1(id int primary key);
create table test1.t2(id int primary key);
insert into test.t1 values(1),(2);
insert into test.t2 values(1),(2);
insert into test1.t1 values(1),(2);
insert into test1.t2 values(1),(2);
insert into test2.t1 values(1),(2);
insert into test2.t2 values(1),(2);
  1. Once the log backup progresses to the current time, perform PITR to Cluster 2 and set the database table filter using the --filter parameter.
    Check the current checkpoint:
tiup br log status --task-name={task_name} --pd “{pd_addr]” --json

Execute the restore command and set --filter to “test.t1”:

tiup br restore point --pd "{pd_addr}" --storage='{s3_url}' --full-backup-storage='{full_backup_s3_url}' --restored-ts '{checkpoint}' --filter "test.t1" --s3.endpoint {s3_endpoint}
  1. Check the recovery status of Cluster 2.

【Observed Unexpected Behavior】

  1. All database table structures in test1 and test2 are restored in Cluster 2, but without data.
  2. No tables are present in the test database in Cluster 2.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA     |
| PERFORMANCE_SCHEMA |
| mysql              |
| test               |
| test1              |
| test2              |
+--------------------+
7 rows in set (0.01 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.01 sec)

mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| t1              |
| t2              |
+-----------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

mysql> use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| t1              |
| t2              |
+-----------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
Empty set (0.01 sec)

mysql> select * from t2;
Empty set (0.00 sec)

【Expected Behavior】
Only the test.t1 database should be restored to Cluster 2.

【Related Components and Specific Versions】
7.1.3
【Other Background Information or Screenshots】
Such as cluster topology, system and kernel versions, application information, etc. If the issue is related to SQL, please provide the SQL statements and related table schema information. If there are critical errors in the node logs, please provide the relevant log content or files. If some business-sensitive information is inconvenient to provide, please leave contact information for private communication.

| username: WalterWj | Original post link

It seems that BR’s PITR does not support table filtering… :thinking:

| username: luancheng | Original post link

From the provided case, it seems that there is a desire to filter incremental DDL, but the product currently does not support this feature. The main reason is that supporting incremental DDL would be quite complex, including handling corner cases like truncate table/rename table, which are difficult to manage. Therefore, do not use this parameter for recovery in a production environment.

I tried it with v7.1.3, and it works if you remove this parameter.

| username: Billmay表妹 | Original post link

Please describe your product requirements here:

| username: dba远航 | Original post link

Got it, it will be useful in the future.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.