Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29208

mariabackup skips imported tables

    XMLWordPrintable

    Details

      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
      

        Attachments

          Activity

            People

            Assignee:
            lizardo Luis Eduardo Oliveira Lizardo
            Reporter:
            sumark Marek Hlavka
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.