ALTER TABLE…IMPORT TABLESPACE fails after DROP INDEX

A side effect of the MDEV-24589 bug fix is that a scenario like the following could freeze the data file in a state that is not accepted later:

ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2;

FLUSH TABLE t1 FOR EXPORT;

A later ALTER TABLE t1 IMPORT TABLESPACE could fail with:

CURRENT_TEST: innodb.innodb-alter

mysqltest: In included file "./suite/innodb/include/import.inc": 

included from /usr/share/mysql/mysql-test/suite/innodb/t/innodb-alter.test at line 606:

At line 32: query 'ALTER TABLE $dest_db.t1 IMPORT TABLESPACE' failed: 1815: Internal error: Cannot reset LSNs in table `dest_db`.`t1` : Data structure corruption

The reason for this would be that if purge had not been completed for the DROP INDEX operation, the data file would still contain index pages that are marked as allocated and belonging to the indexes that were dropped. This would trip a check in PageConverter::update_index_page().

We can tolerate this error and ignore the extraneous pages on import. The space that is allocated by them would never be freed.

To work around this error and ensure that all index pages will really be marked as free in the data file, one should wait for purge to complete between DROP INDEX and FLUSH TABLES…FOR EXPORT, for example by executing something like this (MDEV-16952):

SET @purge_freq= @@GLOBAL.innodb_purge_rseg_truncate_frequency;

SET GLOBAL innodb_purge_rseg_truncate_frequency=1;

ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2;

SET GLOBAL innodb_max_purge_lag_wait=0;

SET GLOBAL innodb_purge_rseg_truncate_frequency=@purge_freq;

FLUSH TABLE t1 FOR EXPORT;

Sorry, exporting and importing tablespaces is somewhat of a mess until MDEV-11658 is implemented. That task would require file format changes (storing the secondary index root page numbers inside the .ibd file) as well as make the export wait for the purge of history to complete.

We will work around this error by making IMPORT TABLESPACE tolerate such garbage pages. Warnings will be issued for each orphaned page:

2021-03-26  9:08:05 160 [Warning] InnoDB: Unknown index id 260 on page 4