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

Can't selectively restore sequences using innodb tables from backup

Details

    Description

      When wanting to do a selective restore of a backup taken with mariabackup DISCARD TABLESPACE / IMPORT TABLESPACE is used after preparing the backup with mariabackup --prepare --export

      When having a SEQUENCE backed by an InnoDB table the related .ibd tablespace file can't be discarded though:

      MariaDB [test]> alter table test_sequence discard tablespace;
      		 
      ERROR 1031 (HY000): Storage engine SEQUENCE of the table `test`.`test_sequence` doesn't have this option

      So there does not seem to be a way to restore such a sequence from a physical backup like mariabackup.

      IMHO the SEQUENCE engine here should not just outright refuse such ALTER attempts, but pass them through to the underlying engine actually used for sequence storage.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35144 CREATE TABLE ... LIKE uses current innodb_compression_default instead of the create value

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Task - A task that needs to be done. MDEV-10139 Support for SEQUENCE objects

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            During the execution of the following, ha_innobase::discard_or_import_tablespace() is never invoked. ha_innobase::create() is.

            --source include/have_innodb.inc
            		 
            create sequence s engine=innodb;
            		 
            alter table s discard tablespace;
            		 
            alter table s import tablespace;
            		 
            drop sequence s;

            The error code is being returned by the base member function handler::ha_discard_or_import_tablespace() and reported here:

            10.4 a34b989f0c0ae8a968ac5b881a41a9143c9dcdeb

            		 
            #0  my_error (nr=1031, MyFlags=0) at /mariadb/10.4/mysys/my_error.c:109
            		 
            #1  0x0000561f8938740f in ha_sequence::print_error (this=<optimized out>, error=<optimized out>, errflag=<optimized out>) at /mariadb/10.4/sql/ha_sequence.cc:368
            		 
            #2  0x0000561f891623c8 in mysql_discard_or_import_tablespace (thd=thd@entry=0x7f5548018d68, table_list=table_list@entry=0x7f55480241d8, discard=true) at /mariadb/10.4/sql/sql_table.cc:6213
            		 
            #3  0x0000561f891d4ecf in Sql_cmd_discard_import_tablespace::execute (this=0x7f55480248d0, thd=0x7f5548018d68) at /mariadb/10.4/sql/sql_alter.cc:568
            		 
            #4  0x0000561f890ccc37 in mysql_execute_command (thd=thd@entry=0x7f5548018d68) at /mariadb/10.4/sql/sql_parse.cc:6218
            		 
            #5  0x0000561f890cdad9 in mysql_parse (thd=thd@entry=0x7f5548018d68, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7f55580ae390, is_com_multi=is_com_multi@entry=false, 
                is_next_command=is_next_command@entry=false) at /mariadb/10.4/sql/sql_parse.cc:8012
            		 
            #6  0x0000561f890cf506 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f5548018d68, packet=packet@entry=0x7f554800d3f9 "alter table s discard tablespace", 
                packet_length=packet_length@entry=32, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /mariadb/10.4/sql/sql_parse.cc:1857

            marko Marko Mäkelä added a comment - During the execution of the following, ha_innobase::discard_or_import_tablespace() is never invoked. ha_innobase::create() is. --source include/have_innodb.inc create sequence s engine=innodb; alter table s discard tablespace; alter table s import tablespace; drop sequence s; The error code is being returned by the base member function handler::ha_discard_or_import_tablespace() and reported here: 10.4 a34b989f0c0ae8a968ac5b881a41a9143c9dcdeb #0 my_error (nr=1031, MyFlags=0) at /mariadb/10.4/mysys/my_error.c:109 #1 0x0000561f8938740f in ha_sequence::print_error (this=<optimized out>, error=<optimized out>, errflag=<optimized out>) at /mariadb/10.4/sql/ha_sequence.cc:368 #2 0x0000561f891623c8 in mysql_discard_or_import_tablespace (thd=thd@entry=0x7f5548018d68, table_list=table_list@entry=0x7f55480241d8, discard=true) at /mariadb/10.4/sql/sql_table.cc:6213 #3 0x0000561f891d4ecf in Sql_cmd_discard_import_tablespace::execute (this=0x7f55480248d0, thd=0x7f5548018d68) at /mariadb/10.4/sql/sql_alter.cc:568 #4 0x0000561f890ccc37 in mysql_execute_command (thd=thd@entry=0x7f5548018d68) at /mariadb/10.4/sql/sql_parse.cc:6218 #5 0x0000561f890cdad9 in mysql_parse (thd=thd@entry=0x7f5548018d68, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7f55580ae390, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /mariadb/10.4/sql/sql_parse.cc:8012 #6 0x0000561f890cf506 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f5548018d68, packet=packet@entry=0x7f554800d3f9 "alter table s discard tablespace", packet_length=packet_length@entry=32, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /mariadb/10.4/sql/sql_parse.cc:1857
            monty Michael Widenius added a comment -

            I will do my best to get this fixed this week.

            monty Michael Widenius added a comment - I will do my best to get this fixed this week.
            monty Michael Widenius added a comment - - edited

            Pushed to bb-10.5-monty for testing by Elena and buildbot

            monty Michael Widenius added a comment - - edited Pushed to bb-10.5-monty for testing by Elena and buildbot
            elenst Elena Stepanova added a comment - - edited

            If a sequence is created via "CREATE TABLE .. LIKE <another sequence>", the tablespace cannot always be successfully imported, it depends on the settings:

            --source include/have_innodb.inc
            		 
             
            		 
            SET innodb_compression_default= ON;
            		 
             
            		 
            --let $datadir= `select @@datadir`
            		 
             
            		 
            CREATE SEQUENCE s ENGINE=InnoDB;
            		 
            CREATE TABLE s_import LIKE s;
            		 
             
            		 
            ALTER TABLE s_import DISCARD TABLESPACE;
            		 
            FLUSH TABLE s FOR EXPORT;
            		 
            --copy_file $datadir/test/s.ibd $datadir/test/s_import.ibd
            		 
            --copy_file $datadir/test/s.cfg $datadir/test/s_import.cfg
            		 
            UNLOCK TABLES;
            		 
            ALTER TABLE s_import IMPORT TABLESPACE;

            bb-10.5-monty 701ed9adc0000d40a6b744916b64215bb888d47b

            		 
            mysqltest: At line 15: query 'ALTER TABLE s_import IMPORT TABLESPACE' failed: 1808: Schema mismatch (Table flags don't match, server table has 0x36a1 and the meta-data file has 0x3021; .cfg file uses ROW_FORMAT=DYNAMIC)

            Comparing SHOW CREATE for the two sequences, the original one and created by "CREATE .. LIKE", the compression flag is only shown as a non-executable comment:

            CREATE TABLE `s` (
            		 
              `next_not_cached_value` bigint(21) NOT NULL,
            		 
              `minimum_value` bigint(21) NOT NULL,
            		 
              `maximum_value` bigint(21) NOT NULL,
            		 
              `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
            		 
              `increment` bigint(21) NOT NULL COMMENT 'increment value',
            		 
              `cache_size` bigint(21) unsigned NOT NULL,
            		 
              `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
            		 
              `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
            		 
            ) ENGINE=InnoDB SEQUENCE=1


            CREATE TABLE `s_import` (
            		 
              `next_not_cached_value` bigint(21) NOT NULL,
            		 
              `minimum_value` bigint(21) NOT NULL,
            		 
              `maximum_value` bigint(21) NOT NULL,
            		 
              `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
            		 
              `increment` bigint(21) NOT NULL COMMENT 'increment value',
            		 
              `cache_size` bigint(21) unsigned NOT NULL,
            		 
              `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
            		 
              `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
            		 
            ) ENGINE=InnoDB SEQUENCE=1 /* `PAGE_COMPRESSED`='ON' */

            but apparently it does affect one of the .cfg files but not another.

            elenst Elena Stepanova added a comment - - edited If a sequence is created via "CREATE TABLE .. LIKE <another sequence>", the tablespace cannot always be successfully imported, it depends on the settings: --source include/have_innodb.inc   SET innodb_compression_default= ON ;   --let $datadir= `select @@datadir`   CREATE SEQUENCE s ENGINE=InnoDB; CREATE TABLE s_import LIKE s;   ALTER TABLE s_import DISCARD TABLESPACE; FLUSH TABLE s FOR EXPORT; --copy_file $datadir/test/s.ibd $datadir/test/s_import.ibd --copy_file $datadir/test/s.cfg $datadir/test/s_import.cfg UNLOCK TABLES; ALTER TABLE s_import IMPORT TABLESPACE; bb-10.5-monty 701ed9adc0000d40a6b744916b64215bb888d47b mysqltest: At line 15: query 'ALTER TABLE s_import IMPORT TABLESPACE' failed: 1808: Schema mismatch ( Table flags don't match, server table has 0x36a1 and the meta-data file has 0x3021; .cfg file uses ROW_FORMAT= DYNAMIC ) Comparing SHOW CREATE for the two sequences, the original one and created by "CREATE .. LIKE", the compression flag is only shown as a non-executable comment: CREATE TABLE `s` ( `next_not_cached_value` bigint (21) NOT NULL , `minimum_value` bigint (21) NOT NULL , `maximum_value` bigint (21) NOT NULL , `start_value` bigint (21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used' , `increment` bigint (21) NOT NULL COMMENT 'increment value' , `cache_size` bigint (21) unsigned NOT NULL , `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed' , `cycle_count` bigint (21) NOT NULL COMMENT 'How many cycles have been done' ) ENGINE=InnoDB SEQUENCE =1 CREATE TABLE `s_import` ( `next_not_cached_value` bigint (21) NOT NULL , `minimum_value` bigint (21) NOT NULL , `maximum_value` bigint (21) NOT NULL , `start_value` bigint (21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used' , `increment` bigint (21) NOT NULL COMMENT 'increment value' , `cache_size` bigint (21) unsigned NOT NULL , `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed' , `cycle_count` bigint (21) NOT NULL COMMENT 'How many cycles have been done' ) ENGINE=InnoDB SEQUENCE =1 /* `PAGE_COMPRESSED`='ON' */ but apparently it does affect one of the .cfg files but not another.
            elenst Elena Stepanova added a comment -

            Apart from the above note which relates to the CREATE .. LIKE deficiency, the basic import scenario seems to be now working for sequences, and I didn't get anything new in regression tests, so I think it should be okay to push.

            elenst Elena Stepanova added a comment - Apart from the above note which relates to the CREATE .. LIKE deficiency, the basic import scenario seems to be now working for sequences, and I didn't get anything new in regression tests, so I think it should be okay to push.
            monty Michael Widenius added a comment -

            Fixed bug, worked with Elna with QA questions (resoluted in a new independent MDEV).

            monty Michael Widenius added a comment - Fixed bug, worked with Elna with QA questions (resoluted in a new independent MDEV).

            People

              monty Michael Widenius
              hholzgra Hartmut Holzgraefe
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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