[MDEV-32350] Can't selectively restore sequences using innodb tables from backup - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.5.21, 10.3(EOL), 10.4(EOL), 10.6
Fix Version/s: 10.5.27, 10.6.20, 10.11.10, 11.2.6, 11.4.4
Component/s: mariabackup, Sequences, Storage Engine - InnoDB
Labels:
None

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

MDEV-35144 CREATE TABLE ... LIKE uses current innodb_compression_default instead of the create value

Closed

MDEV-10139 Support for SEQUENCE objects

Closed

Activity

Descending order - Click to sort in ascending order

Michael Widenius added a comment - 2024-10-16 15:10

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

Michael Widenius added a comment - 2024-10-16 15:10 Fixed bug, worked with Elna with QA questions (resoluted in a new independent MDEV).

Elena Stepanova added a comment - 2024-10-14 12:06

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.

Elena Stepanova added a comment - 2024-10-14 12:06 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.

Elena Stepanova added a comment - 2024-10-13 11:34 - 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.

Elena Stepanova added a comment - 2024-10-13 11:34 - 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.

Michael Widenius added a comment - 2024-10-08 15:22 - edited

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

Michael Widenius added a comment - 2024-10-08 15:22 - edited Pushed to bb-10.5-monty for testing by Elena and buildbot

Michael Widenius added a comment - 2024-06-24 13:37

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

Michael Widenius added a comment - 2024-06-24 13:37 I will do my best to get this fixed this week.

Marko Mäkelä added a comment - 2023-10-06 09:51

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 Mäkelä added a comment - 2023-10-06 09:51 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

People

Assignee:: Michael Widenius

Reporter:: Hartmut Holzgraefe

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2023-10-04 09:04

Updated:: 2024-10-16 15:10

Resolved:: 2024-10-16 15:10

Git Integration

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

MariaDB Server