[MDEV-32350] Can't selectively restore sequences using innodb tables from backup Created: 2023-10-04  Updated: 2023-12-18

Status: Confirmed
Project: MariaDB Server
Component/s: mariabackup, Sequences, Storage Engine - InnoDB
Affects Version/s: 10.3, 10.4, 10.6, 10.5.21
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Critical
Reporter: Hartmut Holzgraefe Assignee: Michael Widenius
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-10139 Support for SEQUENCE objects Closed

 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.

 Comments   
Comment by Marko Mäkelä [ 2023-10-06 ]

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

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