Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
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
-
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)
|
Attachments
Issue Links
- causes
-
-
- Closed
-
-
DOCSCOLLAB-851 Failed to load
Activity
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
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>
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.
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.
This is almost there. I think that we need to test this also when there exist virtual columns in the table, indexed or not.
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 TABLECREATE 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");EOFUNLOCK TABLES;DROP TABLE t1;# New table without FTS indexCREATE 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_warningsALTER TABLE t1 IMPORT TABLESPACE;--enable_warningsSHOW 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 TABLECREATE 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");EOFUNLOCK TABLES;DROP TABLE t1;# New table without FTS indexCREATE 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_warningsALTER TABLE t1 IMPORT TABLESPACE;--enable_warningsSHOW 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)