[MDEV-29208] mariabackup skips imported tables Created: 2022-07-29  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: mariabackup
Affects Version/s: 10.5.15
Fix Version/s: 10.5, 10.6

Type: Bug Priority: Major
Reporter: Marek Hlavka Assignee: Luis Eduardo Oliveira Lizardo
Resolution: Unresolved Votes: 0
Labels: mariabackup, transportable_tablespaces
Environment:

Debian 9


 Description   

If mariabackup is run after recent table import, freshly imported tables are not backed up.


Test case:

SESSION1: create table

 
create database newdb;
 
use newdb;
 
create table `newtable` (`a` bigint unsigned not null, `b` varchar(255) not null, primary key (`a`)) engine=INNODB;
 
insert into `newtable` select seq, concat("test_", seq) from seq_1_to_1000000;


SESSION2: backup newdb and prepare for import tablespace

 
mariabackup -u root --backup --databases="newdb" --target-dir=/var/lib/mysql/backup/standalone/newdb_$(date +"%Y%m%d%S")
 
mariabackup --prepare --export --use-memory=4G --innodb-io-capacity=20000 --target-dir=/var/lib/mysql/backup/standalone/2022072922
 
chown -R mysql:mysql /var/lib/mysql/backup/standalone/2022072922


SESSION1: discard tablespace

 
use newdb;
 
drop table `newtable`;
 
create table `newtable` (`a` bigint unsigned not null, `b` varchar(255) not null, primary key (`a`)) engine=INNODB;
 
alter table `newtable` discard tablespace;


SESSION2: copy data from backup dir

 
cp -a /var/lib/mysql/backup/standalone/2022072922/newdb/newtable.{ibd,cfg} /var/lib/mysql/data/standalone/newdb/


SESSION1: import tablespace

 
alter table `newtable` import tablespace;


Table details after import:

# table status
 
            Name: newtable
 
          Engine: InnoDB
 
         Version: 10
 
      Row_format: Dynamic
 
            Rows: 997627
 
  Avg_row_length: 41
 
     Data_length: 41500672
 
 Max_data_length: 0
 
    Index_length: 0
 
       Data_free: 5242880
 
  Auto_increment: NULL
 
     Create_time: 2022-07-29 17:24:44
 
     Update_time: NULL
 
      Check_time: NULL
 
       Collation: latin1_swedish_ci
 
        Checksum: NULL
 
  Create_options: 
         Comment: 
Max_index_length: 0
 
       Temporary: N
 
 
 
# desc 
+-------+---------------------+------+-----+---------+-------+
 
| Field | Type                | Null | Key | Default | Extra |
 
+-------+---------------------+------+-----+---------+-------+
 
| a     | bigint(20) unsigned | NO   | PRI | NULL    |       |
 
| b     | varchar(255)        | NO   |     | NULL    |       |
 
+-------+---------------------+------+-----+---------+-------+
 
 
 
# indexes
 
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| newtable |          0 | PRIMARY  |            1 | a           | A         |      997627 |     NULL | NULL   |      | BTREE      |         |               |
 
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


SESSION2: backup newdb again

 
mariabackup -u root --backup --databases="newdb" --target-dir=/var/lib/mysql/backup/standalone/newdb_v2_$(date +"%Y%m%d%S")


Table is indeed ignored by mariabackup and reported as deleted by DDL tracking function

# backup log output
 
[00] 2022-07-29 17:25:18 mariabackup: Generating a list of tablespaces
 
Skipping db: ...
 
[00] 2022-07-29 17:25:18 DDL tracking : create 60 "./newdb/newtable.ibd"
 
[00] 2022-07-29 17:25:18 DDL tracking : delete 60 "./newdb/newtable.ibd"
 
[00] 2022-07-29 17:25:18 DDL tracking : create 61 "./newdb/newtable.ibd"
 
[00] 2022-07-29 17:25:18 DDL tracking : delete 61 "./newdb/newtable.ibd"
 
[00] 2022-07-29 17:25:18 >> log scanned up to (5288638578)
 
...
 
Warning: We assume the  table was dropped during xtrabackup execution and ignore the tablespace newdb/newtable
 
 
 
# last event in the last binlog
 
mysql-bin.000131	2052	Gtid	5	2090	BEGIN GTID 0-5-18746
 
mysql-bin.000131	2090	Table_map	5	2141	table_id: 29 (newdb.newtable)
 
...
 
mysql-bin.000131	20958671	Write_rows_v1	5	20965190	table_id: 29 flags: STMT_END_F
 
mysql-bin.000131	20965190	Xid	5	20965217	COMMIT /* xid=6154 */
 
mysql-bin.000131	20965217	Gtid	5	20965255	GTID 0-5-18747
 
mysql-bin.000131	20965255	Query	5	20965366	use `newdb`; DROP TABLE `newtable` /* generated by server */
 
mysql-bin.000131	20965366	Gtid	5	20965404	GTID 0-5-18748
 
mysql-bin.000131	20965404	Query	5	20965582	use `newdb`; create table `newtable` (`a` bigint unsigned not null, `b` varchar(255) not null, primary key (`a`)) engine=INNODB
 
mysql-bin.000131	20965582	Gtid	5	20965620	GTID 0-5-18749
 
mysql-bin.000131	20965620	Query	5	20965725	use `newdb`; alter table `newtable` discard tablespace
 
mysql-bin.000131	20965725	Gtid	5	20965763	GTID 0-5-18750
 
mysql-bin.000131	20965763	Query	5	20965867	use `newdb`; alter table `newtable` import tablespace



 Comments   
Comment by Marko Mäkelä [ 2022-08-01 ]

I did not verify this bug, but I think that it is plausible that it does not work.

As far as I remember, no FILE_ record for the IMPORT TABLESPACE operation will be logged. The DDL tracking in backup is based on those records.

The DISCARD TABLESPACE will write a FILE_DELETE record, just like any DDL operation that is dropping or rebuilding a tablespace would.

Would the backup work after anything has been written to the imported table?

Comment by Marek Hlavka [ 2022-08-01 ]

Unfortunately, the table is not backed up even after a successfull DML following the import.

Comment by Marko Mäkelä [ 2022-08-06 ]

Can this be reproduced if the first invocations of mariabackup are replaced with

FLUSH TABLE newtable FOR EXPORT;
 
-- copy newtable.ibd and newtable.cfg for restoring them later
 
UNLOCK TABLES;

Can this be reproduced if there is a log checkpoint and a write to the table before the final mariabackup --backup invocation?

ALTER TABLE newtable IMPORT TABLESPACE;
 
SET GLOBAL innodb_max_dirty_pages_pct=0;
 
DELETE FROM newtable LIMIT 1;

Comment by Marek Hlavka [ 2022-08-09 ]

For the first case, where import source is not from a mariabackup, issue can be still reproduced, however for the second, it can't (the DML have no impact on the outcome, the table is still backed up).

Comment by Marko Mäkelä [ 2022-08-10 ]

Thank you, just like I expected. I would further expect that the backup would include the imported table even if there was no write to it. The relevant thing should be whether a log checkpoint occurred between the import and the backup attempt.

Generated at Thu Feb 08 10:06:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.