[MDEV-18543] IMPORT TABLESPACE fails after instant DROP COLUMN Created: 2019-02-11  Updated: 2022-04-26  Resolved: 2021-10-26

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.4
Fix Version/s: 10.4.22, 10.5.13, 10.6.5

Type: Bug Priority: Critical
Reporter: Marko Mäkelä Assignee: Eugene Kosov (Inactive)
Resolution: Fixed Votes: 6
Labels: instant, portability

Issue Links:
Blocks
blocks MDEV-18762 Support easy restore of partial backup Closed
blocks MDEV-23874 Crash in ALTER TABLE ... IMPORT TABLE... Closed
blocks MDEV-24352 General error: 1808 Schema mismatch (... Closed
blocks MDEV-26847 improve encryption/compression check ... Open
Duplicate
is duplicated by MDEV-25066 IMPORT TABLESPACE not possible if sou... Closed
PartOf
includes MDEV-26621 assertion failue "index->table->persi... Closed
Problem/Incident
is caused by MDEV-15562 Instant DROP COLUMN or changing the o... Closed
Relates
relates to MDEV-20590 Introduce a file format constraint to... Closed
relates to MDEV-21549 IMPORT TABLESPACE fails to adjust all... Closed
relates to MDEV-27272 Crash on EXPORT/IMPORT tablespace wit... Closed
relates to MDEV-11658 Simpler, faster IMPORT of InnoDB tables Open
relates to MDEV-18295 IMPORT TABLESPACE fails with instant-... Closed

 Description   

After the MDEV-18295 fix, IMPORT TABLESPACE will fail to import data files on which columns have been instantly reordered or dropped (MDEV-15562).

The problem is demonstrated by this part of the test innodb.instant_alter_import:

--echo # Import a data file that contains instant DROP COLUMN metadata
 
alter table t2 drop x;
 
alter table t1 drop x, force;
 
alter table t1 discard tablespace;
 
 
 
flush tables t2 for export;
 
--move_file $MYSQLD_DATADIR/test/t2.cfg $MYSQLD_DATADIR/test/t1.cfg
 
--copy_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_DATADIR/test/t1.ibd
 
unlock tables;
 
 
 
--error ER_TABLE_SCHEMA_MISMATCH
 
alter table t1 import tablespace;

The IMPORT TABLESPACE code should read the metadata record and the metadata BLOB early, to augment the metadata. Because the pages have not been adjusted yet, the buffer pool will have to be bypassed while reading the pages.

 Comments   
Comment by Marko Mäkelä [ 2019-03-07 ]

I think that it should be worth trying to use the buffer pool also for the first phase of IMPORT. This may be challenging, because:

  1. if I remember correctly, the fil_space_t for the being-imported tablespace would be created a bit later
  2. the space_id in the file could happen to point to some unrelated fil_space_t in the system
  3. we are not (yet) passing the fil_space_t to the buffer pool interfaces (most notably, fil_io() is looking it up

If you manage to clean this up, it would pave the way for MDEV-11658 and to a future where we no longer need to write a tablespace ID to every page of the file. Else, we will have to jump through some hoops and duplicate some code so that we can access the metadata record and BLOB completely outside the buffer pool (performing a number of single-page reads, I suppose).

Comment by Juan Gabriel Covas [ 2019-08-22 ]

Under MariaDB 10.4.7, I want to report another "schema_mismatch" error on IMPORT TABLESPACE when an alter ADD column is applied (when is NOT added as the LAST column). Didn't want to open a new issue, you'll say.

CREATE TABLE IF NOT EXISTS `altertest` (
 
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 
  `category` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
 
  `name` varchar(200) NOT NULL DEFAULT '',
 
  `hidden` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;


> ALTER TABLE testdb.altertest ADD `newcol` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 \
 
AFTER `category`; -- as last column works, to trigger the error it must be added after `category` for example
 
> ALTER TABLE testdb.altertest DISCARD TABLESPACE
 
[...]
 
> ALTER TABLE testdb.altertest IMPORT TABLESPACE

ERROR 1808 (HY000): Schema mismatch (Index field name newcol doesn't match tablespace metadata field name name for field position 4)

Comment by Marko Mäkelä [ 2019-08-23 ]

jgcovas, thanks, your example should be due to this same bug. ADD COLUMN somewhere else as the last column will involve instantly changing the order of columns, and IMPORT TABLESPACE currently fails to read the mapping of clustered index fields to columns from the hidden metadata record.

Comment by Juan Gabriel Covas [ 2019-08-23 ]

No problem. For now, for some projects that we rely on partial backups from mariabackup to pull customer databases, and therefore IMPORT TABLESPACE is needed, we took care of rebuilding the problematic schemas metadata with

ALTER TABLE dbname.tablename FORCE;

And enforcing ,ALGORITHM=copy or ,FORCE after ALTER statements from now on, (or setting alter_algorithm=copy) but is a bit annoying having to take care of this... I'm also thinking, what about people that will try to partially restore old backups? Is the solution a full restore to be able to recover the tables without IMPORT TABLESPACE? Thanks.

Comment by Marko Mäkelä [ 2020-01-22 ]

jgcovas, you seem to be requesting MDEV-20590 to be implemented.

Comment by Dave [ 2021-08-26 ]

I've just started hitting this error "ERROR 1808 (HY000): Schema mismatch (Index field name...".
Given the ALTER statements have already taken place, what resolution is there to import a tablespace in the interim while we await a solution to be implemented?
Thanks.

Comment by Juan Gabriel Covas [ 2021-09-01 ]

Hello. I'm not sure if the severity of this is correctly understood. If anyone rely on "mariabackup" for
partial backups and the tablespace metadata is corrupted (just do an instant ALTER column which is not "add_last"), those backups are NOT usable after that, and they CANNOT be restored (by importing namespace). Unless I'm wrong or don't know something else.

Solution before taking a partial mariabackup is doing "ALTER TABLE dbname.tablename FORCE;" on ALL tables before a daily backup for example, just in case some table got the metadata corrupted, but that's not reasonable for big setups / volumes of tables (the operation can take a quite long time).

Edited: sorry, I didn't review MDEV-20590 (final comments) which may add to alieviate this

Comment by Dave [ 2021-09-09 ]

I've tried the suggested `ALTER TABLE ... FORCE` before the backup being taken, but this still doesn't work for me. My backup solution uses a schema export which is imported into the destination database first, then the tables are copied over as per the advised process, but this error persists on some tables even after doing a force alter.
Any reason why that wouldn't work? Anyone got any work around for this? Our entire backup/restore process is currently considered "At-Risk" just now.

Comment by Eugene Kosov (Inactive) [ 2021-09-16 ]

This bug might be a separate one but let it live in this issue for now.

--source include/have_innodb.inc
 
 
 
--let $MYSQLD_DATADIR= `SELECT @@datadir`
 
 
 
CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, i1 INT) ENGINE=INNODB;
 
 
 
CREATE TABLE t2 (id INT PRIMARY KEY AUTO_INCREMENT, i1 INT, i2 INT) ENGINE=INNODB;
 
ALTER TABLE t2 DROP COLUMN i2, ALGORITHM=INSTANT;
 
ALTER TABLE t2 DISCARD TABLESPACE;
 
 
 
FLUSH TABLE t1 FOR EXPORT;
 
 
 
--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd
 
--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg
 
 
 
UNLOCK TABLES;
 
ALTER TABLE t2 IMPORT TABLESPACE;
 
 
 
DROP TABLE t2, t1;


#7  0x00007f4eaafab7f2 in __GI___assert_fail (assertion=0x5ef979 "index->table->persistent_autoinc", file=0x5877eb "/home/kevgs/work/m/bb-10.4-kevgs/storage/innobase/btr/btr0btr.cc", line=1391, function=0x594c03 "void btr_write_autoinc(dict_index_t *, ib_uint64_t, bool)") at assert.c:101
 
#8  0x0000000001437e95 in btr_write_autoinc (index=0x7f4e580389a0, autoinc=0, reset=false) at btr0btr.cc:1391
 
#9  0x0000000001657a0e in row_import_for_mysql (table=0x7f4e58036c60, prebuilt=0x7f4e581728d0) at row0import.cc:4423
 
#10 0x00000000012c6012 in ha_innobase::discard_or_import_tablespace (this=0x7f4e580896c0, discard=0 '\000') at ha_innodb.cc:13111
 
#11 0x0000000000c9745d in handler::ha_discard_or_import_tablespace (this=0x7f4e580896c0, discard=0 '\000') at handler.cc:4611
 
#12 0x000000000101bc8c in mysql_discard_or_import_tablespace (thd=0x7f4e58000d28, table_list=0x7f4e580136e8, discard=false) at sql_table.cc:6062
 
#13 0x00000000010ce6c5 in Sql_cmd_discard_import_tablespace::execute (this=0x7f4e58013db8, thd=0x7f4e58000d28) at sql_alter.cc:557
 
#14 0x0000000000f2424e in mysql_execute_command (thd=0x7f4e58000d28) at sql_parse.cc:6192
 
#15 0x0000000000f116aa in mysql_parse (thd=0x7f4e58000d28, rawbuf=0x7f4e58013600 "ALTER TABLE t2 IMPORT TABLESPACE", length=32, parser_state=0x7f4ea9573628, is_com_multi=false, is_next_command=false) at sql_parse.cc:7995

Comment by Matthias Leich [ 2021-09-29 ] 

origin/bb-10.6-MDEV-18543-instant-import-bugs 73f639178f7bc351e7fc937edc729b98136e02e2 2021-09-29T16:13:52+06:00
 
performed well in RQG testing.
 
The scenario used was
 
Random
 
- Copy the ibd file to the target location only if there is not yet an ibd file.
 
  Do not copy the corresponding cfg file.
 
- ALTER TABLE <target> IMPORT TABLESPACE
 
- ALTER TABLE <target> DISCARD TABLESPACE
 
- DROP TABLE <target>
 
- CREATE TABLE <target> LIKE <source> ;  ALTER TABLE <target>  <Modify layout>
 
Never drop a target ibd file because that is the job of DISCARD and DROP TABLE.
 
 
 
This worked fine when going with one user only.
 
 
 
When trying that with two concurrent users I hit
 
sdp:/data/Results/1632923193/TBR-1201/dev/shm/vardir/1632923193/55/1/rr
 
_RR_TRACE_DIR="." rr replay --mark-stdio
 
 
 
[rr 1176510 81937]mysqld: /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/storage/innobase/os/os0file.cc:1490: bool os_file_rename_func(const char*, const char*): Assertion `!exists' failed.
 
(rr) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x00004825335be859 in __GI_abort () at abort.c:79
 
#2  0x00004825335be729 in __assert_fail_base (fmt=0x482533754588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x556cd7273540 "!exists", 
    file=0x556cd7271f00 "/data/Server/bb-10.6-MDEV-18543-instant-import-bugs/storage/innobase/os/os0file.cc", line=1490, function=<optimized out>) at assert.c:92
 
#3  0x00004825335cff36 in __GI___assert_fail (assertion=0x556cd7273540 "!exists", file=0x556cd7271f00 "/data/Server/bb-10.6-MDEV-18543-instant-import-bugs/storage/innobase/os/os0file.cc", line=1490, 
    function=0x556cd72734e0 "bool os_file_rename_func(const char*, const char*)") at assert.c:101
 
#4  0x0000556cd5d4080a in os_file_rename_func (oldpath=0x603000015bb0 "./test/#sql-alter-11f3be-f.ibd", newpath=0x604000067bd0 "./test/imp_table1_innodb_varchar_255.ibd")
 
    at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/storage/innobase/os/os0file.cc:1490
 
#5  0x0000556cd61d4d56 in fil_rename_tablespace (id=0, old_path=0x0, new_path_in=0x604000067b90 "./test/imp_table1_innodb_varchar_255.ibd") at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/storage/innobase/fil/fil0fil.cc:1920
 
#6  0x0000556cd61d43bc in fil_space_t::rename (this=0x6120000ef5c0, path=0x604000067b90 "./test/imp_table1_innodb_varchar_255.ibd", log=true, replace=false) at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/storage/innobase/fil/fil0fil.cc:1861
 
#7  0x0000556cd613e97a in dict_table_t::rename_tablespace (this=0x618000144908, new_name=0x450771c8dcb0 "test/imp_table1_innodb_varchar_255", replace=false)
 
    at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/storage/innobase/dict/dict0dict.cc:1440
 
#8  0x0000556cd613f31c in dict_table_rename_in_cache (table=0x618000144908, new_name=0x450771c8dcb0 "test/imp_table1_innodb_varchar_255", rename_also_foreigns=true, replace_new_file=false)
 
    at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/storage/innobase/dict/dict0dict.cc:1500
 
#9  0x0000556cd5e9624c in row_rename_table_for_mysql (old_name=0x450771c8def0 "test/#sql-alter-11f3be-f", new_name=0x450771c8dcb0 "test/imp_table1_innodb_varchar_255", trx=0x4f6808963e88, use_fk=true)
 
    at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/storage/innobase/row/row0mysql.cc:2915
 
#10 0x0000556cd5b23379 in innobase_rename_table (trx=0x4f6808963e88, from=0x450771c8ec80 "./test/#sql-alter-11f3be-f", to=0x450771c8eec0 "./test/imp_table1_innodb_varchar_255", use_fk=true)
 
    at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/storage/innobase/handler/ha_innodb.cc:13652
 
#11 0x0000556cd5b25d88 in ha_innobase::rename_table (this=0x62b000138390, from=0x450771c8ec80 "./test/#sql-alter-11f3be-f", to=0x450771c8eec0 "./test/imp_table1_innodb_varchar_255")
 
    at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/storage/innobase/handler/ha_innodb.cc:13994
 
#12 0x0000556cd5220099 in handler::ha_rename_table (this=0x62b000138390, from=0x450771c8ec80 "./test/#sql-alter-11f3be-f", to=0x450771c8eec0 "./test/imp_table1_innodb_varchar_255")
 
    at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/sql/handler.cc:5301
 
#13 0x0000556cd4c9751b in mysql_rename_table (base=0x615000001498, old_db=0x450771c91170, old_name=0x450771c911a0, new_db=0x450771c91170, new_name=0x450771c91190, id=0x450771c911c0, flags=1)
 
    at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/sql/sql_table.cc:4970
 
#14 0x0000556cd4cbc8dd in mysql_alter_table (thd=0x62b00012d218, new_db=0x62b000131c18, new_name=0x62b000132030, create_info=0x450771c925d0, table_list=0x62b000134438, alter_info=0x450771c924a0, order_num=0, order=0x0, ignore=false, if_exists=false)
 
    at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/sql/sql_table.cc:10561
 
#15 0x0000556cd4e47741 in Sql_cmd_alter_table::execute (this=0x62b000134b90, thd=0x62b00012d218) at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/sql/sql_alter.cc:550
 
#16 0x0000556cd4a4cb8c in mysql_execute_command (thd=0x62b00012d218, is_called_from_prepared_stmt=false) at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/sql/sql_parse.cc:5997
 
#17 0x0000556cd4a5905e in mysql_parse (thd=0x62b00012d218, rawbuf=0x62b000134238 "ALTER TABLE imp_table1_innodb_varchar_255 DROP KEY col_text_latin1_key, ALGORITHM = COPY /* E_R Thread1 QNO 70 CON_ID 15 */", length=123, parser_state=0x450771c93b20)
 
    at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/sql/sql_parse.cc:8030
 
#18 0x0000556cd4a312c9 in dispatch_command (command=COM_QUERY, thd=0x62b00012d218, packet=0x629000bef219 " ALTER TABLE imp_table1_innodb_varchar_255 DROP KEY col_text_latin1_key, ALGORITHM = COPY /* E_R Thread1 QNO 70 CON_ID 15 */ ", 
    packet_length=125, blocking=true) at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/sql/sql_parse.cc:1896
 
#19 0x0000556cd4a2e6a1 in do_command (thd=0x62b00012d218, blocking=true) at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/sql/sql_parse.cc:1404
 
#20 0x0000556cd4e2dc1d in do_handle_one_connection (connect=0x608000002eb8, put_in_cache=true) at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/sql/sql_connect.cc:1418
 
#21 0x0000556cd4e2d4ae in handle_one_connection (arg=0x608000002eb8) at /data/Server/bb-10.6-MDEV-18543-instant-import-bugs/sql/sql_connect.cc:1312
 
#22 0x0000188a6bc24609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#23 0x00004825336bb293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
 
(rr)
 
 
 
I hit the same assert on
 
origin/10.6 03e4cb2484cc302bef2886dd3d76b3acdf7f5c23 2021-09-14T16:23:23+03:00
 
 
 
IMHO running that scenario with two concurrent users A and B is too dirty.
 
Just some obvious case maybe != what happens in the trace above.
 
A and B detect at the same point of time that there is no ibd file at target location.
 
A copies and runs IMPORT TABLESPACE.
 
B gets a bit later the CPU and starts the copying during A runs IMPORT TABLESPACE
 
or something else on that table.
 
 
 
Marko had a look into the rr trace of the run on 10.6 and confirmed that
 
running that scenario with more than one user is too dirty.

Comment by Marko Mäkelä [ 2021-10-01 ]

mleich, your scenario involves a race condition between any ALTER TABLE that is executed by ALGORITHM=COPY, and the DBA copying a data file by that name into the data directory, exactly between the 2 RENAME steps of ALTER TABLE. I have updated MDEV-11658 with a suggestion how to fix it.

Comment by Marko Mäkelä [ 2021-10-01 ]

Thank you. This looks mostly good. I posted a few review comments to the 10.4 version. Unfortunately, I think that it needs to be tested again after addressing those comments.

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