Output of a slightly extended MTR test for MDEV-26131

MTR output                                                                                | mysql.err output

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION' ;

SET DEFAULT_STORAGE_ENGINE= 'InnoDB' ;

USE test ;

CREATE TABLE `t1` (

`col_varchar_255` varchar(255),

`col_int` int,

`col_text` text) ENGINE=innodb ;

FLUSH TABLES t1 FOR EXPORT ;

UNLOCK TABLES ;

CREATE TABLE imp_t1 LIKE t1 ;

ALTER TABLE imp_t1 ADD KEY idx ( col_text(13) ) ;

'copy ibd file around'

---- Before DISCARD TABLESPACE Thu Jul 22 09:19:01 PDT 2021

ALTER TABLE imp_t1 DISCARD TABLESPACE ;

---- Before IMPORT TABLESPACE Thu Jul 22 09:19:11 PDT 2021

ALTER TABLE imp_t1 IMPORT TABLESPACE ;                                                      2021-07-22  9:19:11 4 [ERROR] InnoDB: Operating system error number 2 in a file operation.

                                                                                            2021-07-22  9:19:11 4 [ERROR] InnoDB: The error means the system cannot find the path specified.

ERROR HY000: Got error 194 "Tablespace is missing for a table" from storage engine InnoDB   2021-07-22  9:19:11 4 [ERROR] InnoDB: Trying to import a tablespace, but could not open the tablespace file ./test/imp_t1.ibd

'copy ibd file around'

---- Before IMPORT TABLESPACE Thu Jul 22 09:19:21 PDT 2021

ALTER TABLE imp_t1 IMPORT TABLESPACE ;                                                      2021-07-22  9:19:21 4 [Note] InnoDB: Sync to disk

                                                                                            2021-07-22  9:19:21 4 [Note] InnoDB: Sync to disk - done!

                                                                                            2021-07-22  9:19:21 4 [Warning] InnoDB: Table `test`.`imp_t1` should have 2 indexes but the tablespace has 1 indexes

                          Q: Its obvious that the table `imp_t1` and the content of the ibd file do not fit together.

                             Why not just rollback any modification done for the IMPORT and emit an error message?

                             It is to be feared that there might be more inconsistencies and they can also lead to

                             server crashes (MDEV-26148, MDEV-26141) or crippled table content (MDEV-26155).

                                                                                            2021-07-22  9:19:21 4 [Note] InnoDB: Phase I - Update all pages

                                                                                            2021-07-22  9:19:21 4 [Note] InnoDB: Sync to disk

                                                                                            2021-07-22  9:19:21 4 [Note] InnoDB: Sync to disk - done!

                                                                                            2021-07-22  9:19:21 4 [Warning] InnoDB: Skip adjustment of root pages for index `idx`.

                                                                                            2021-07-22  9:19:21 4 [Note] InnoDB: Phase III - Flush changes to disk

                                                                                            2021-07-22  9:19:21 4 [Note] InnoDB: Phase IV - Flush complete

                                                                                            2021-07-22  9:19:21 4 [ERROR] InnoDB: Index `idx` of table `test`.`imp_t1` is corrupted

                                                                                            2021-07-22  9:19:21 4 [Note] InnoDB: Load corrupted index `idx` of table `test`.`imp_t1`

                          Q: Ok, we are keen to do whatever good we can.

                             But why the attempt to load an index which is already known to be corrupt?

                                                                                            2021-07-22  9:19:21 4 [ERROR] InnoDB: Trying to load index `idx` for table `test`.`imp_t1`, but the index tree has been freed!

                          Ok, some failure within the code.

                                                                                            2021-07-22  9:19:21 4 [Warning] InnoDB: Failed to load table `test`.`imp_t1`:Data structure corruption

ERROR HY000: Lost connection to server during query

DROP TABLE t1, imp_t1 ;

It is at least thinkable to

- store information about the table layout within the ibd file

- compare that information with the layout of the target table

and than detect really all inconsistencies.

Some rather simple system would than just deny the operation and avoid by that crashes, mangling of data like today.

And some extreme sophisticated system might be even capable to work around minor inconsistencies

like source column is INT and target column is BIGINT.

But as long as we do not have such features aborting the statement execution as soon as we are faced with the

first inconsistency is better.