[MDEV-30655] IMPORT TABLESPACE fails with column count or index count mismatch Created: 2023-02-15  Updated: 2024-02-06

Status: In Review
Project: MariaDB Server
Component/s: Documentation, Storage Engine - InnoDB
Affects Version/s: 10.4.18, 10.11.1, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Critical
Reporter: Hartmut Holzgraefe Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 1
Labels: None


 Description   

When trying to import a table from a mariabackup taken from a 10.4.18 instance using mariabackup --prepare --export and ALTER TABLE ... IMPORT TABLESPACE I'm getting

ERROR 1808 (HY000): Schema mismatch (Number of columns don't match, table has 9 columns but the tablespace meta-data file has 10 columns)

when importing into 10.4.18 to 10.4.22; and for later versions, all the way up to 10.11

ERROR 1808 (HY000): Schema mismatch (Number of indexes don't match, table has 5 indexes but the tablespace meta-data file has 6 indexes)



 Comments   
Comment by Thirunarayanan Balathandayuthapani [ 2023-02-15 ]

Following test case in 10.4.18 throws the error:

ERROR 1808 (HY000): Schema mismatch (Number of indexes don't match, table has 1 indexes but the tablespace meta-data file has 2 indexes)

Test case:

--source include/have_innodb.inc
 
# FTS TABLE
 
CREATE TABLE t1(f1 CHAR(10), fulltext f_idx(f1))engine=innodb;
 
INSERT INTO t1 values("thiru");
 
ALTER TABLE t1 DROP INDEX f_idx;
 
FLUSH TABLE t1 FOR EXPORT;
 
let MYSQLD_DATADIR =`SELECT @@datadir`;
 
perl;
 
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
 
ib_backup_tablespaces("test", "t1");
 
EOF
 
UNLOCK TABLES;
 
DROP TABLE t1;
 
 
 
# New table without FTS index
 
CREATE TABLE t1(f1 CHAR(10))Engine=InnoDB;
 
ALTER TABLE t1 DISCARD TABLESPACE;
 
perl;
 
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
 
ib_discard_tablespaces("test", "t1");
 
ib_restore_tablespaces("test", "t1");
 
EOF
 
 
 
--disable_warnings
 
ALTER TABLE t1 IMPORT TABLESPACE;
 
--enable_warnings
 
SHOW CREATE TABLE t1;
 
SELECT COUNT(*) FROM t1;
 
DROP TABLE t1;

If we run the above test case in 10.4.22 :

'ALTER TABLE t1 IMPORT TABLESPACE' failed: 1808: Schema mismatch (Number of columns don't match, table has 4 columns but the tablespace meta-data file has 5 columns)

But in latest 10.4:

query 'ALTER TABLE t1 IMPORT TABLESPACE' failed: 1808: Schema mismatch (Number of indexes don't match, table has 1 indexes but the tablespace meta-data file has 2 indexes)

If we use the old .cfg file in latest 10.4 and 10.5 version then we get the same error:

MariaDB [test]> ALTER TABLE t1 IMPORT TABLESPACE;
 
ERROR 1808 (HY000): Schema mismatch (Number of indexes don't match, table has 1 indexes but the tablespace meta-data file has 2 indexes)

InnoDB can import the tablespace without .cfg file if the table doesn't have any secondary
index. (latest 10.4, latest 10.5)

--source include/have_innodb.inc
 
# FTS TABLE
 
CREATE TABLE t1(f1 CHAR(10), fulltext f_idx(f1))engine=innodb;
 
INSERT INTO t1 values("thiru");
 
ALTER TABLE t1 DROP INDEX f_idx;
 
FLUSH TABLE t1 FOR EXPORT;
 
let MYSQLD_DATADIR =`SELECT @@datadir`;
 
perl;
 
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
 
ib_backup_tablespaces("test", "t1");
 
EOF
 
UNLOCK TABLES;
 
DROP TABLE t1;
 
 
 
# New table without FTS index
 
CREATE TABLE t1(f1 CHAR(10))Engine=InnoDB;
 
ALTER TABLE t1 DISCARD TABLESPACE;
 
perl;
 
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
 
ib_discard_tablespaces("test", "t1");
 
ib_restore_tablespaces("test", "t1");
 
EOF
 
 
 
--echo # Remove .cfg file
 
--remove_file $MYSQLD_DATADIR/test/t1.cfg
 
--disable_warnings
 
ALTER TABLE t1 IMPORT TABLESPACE;
 
--enable_warnings
 
SHOW CREATE TABLE t1;
 
SELECT COUNT(*) FROM t1;
 
DROP TABLE t1;

Workaround can be like
1) Remove .cfg file and remove all secondary indexes from table
2) import the tablespace
3) ALTER TABLE … FORCE, ADD INDEX.. (add all secondary indexes)

Comment by Thirunarayanan Balathandayuthapani [ 2023-02-16 ]

When importing the table which dealt with FTS index before, InnoDB can throw the
schema mismatch error due to internal FTS column(FTS_DOC_ID),
FTS_DOC_ID_INDEX index.

To workaround this scenario, user should follow the steps
1) Remove the .cfg file
2) Remove the secondary indexes from new tablespace
3) Import the tablespace
4) Do table rebuild and add the secondary index.
ALTER TABLE t1 FORCE, ADD INDEX ...

It would be great to document this behavior

Comment by Anne Strasser (Inactive) [ 2023-04-12 ]

I've created and linked a docscollab ticket and assigned a writer to this
task.

https://mariadbcorp.atlassian.net/browse/DOCSCOLLAB-851

On Mon, Mar 27, 2023 at 5:39 AM Julien Fritsch (Jira) <jira@mariadb.org>

Comment by Marko Mäkelä [ 2023-12-19 ]

Side note: While MDEV-26137 faciliated a cleaner IMPORT workflow, I think that it would suffer from this same problem.

thiru, I think that we can improve on this: If the .cfg file says that there is an ‘extra’ FTS_DOC_ID column and possible hidden FTS_DOC_ID_INDEX, we could actually add them to the InnoDB dict_table_t (and SYS_COLUMNS, SYS_INDEXES, SYS_FIELDS) when importing.

Comment by Thirunarayanan Balathandayuthapani [ 2024-01-30 ]

https://github.com/MariaDB/server/pull/3029

Comment by Marko Mäkelä [ 2024-01-31 ]

This is a step to the right direction, but I think that this needs a bit more work, especially to improve the test coverage of the changes to the data dictionary tables and the dictionary cache.

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