MySQL Bugs: #107517: Innodb import tablespace crash on tables with instant algorithm used

Bug #107517	Innodb import tablespace crash on tables with instant algorithm used
Submitted:	8 Jun 2022 16:14	Modified:	16 Aug 2022 19:23
Reporter:	Jan Zmeskal	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.29	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When table is created table "like" the original with algorithm=instant added/dropped columns, the table isn't same. It has no version info (version=0) and has final structure.

When tablespace is discarded, copied from original table and imported, mysql crashes . Copied without .cfg file, I'm sure the data in original table isn't changed, in production env. the original table is partitioned.

Crashed on:
8.0.29 Debian 10.12 amd64
8.0.29-debug Windows 11 10.0.22000 x64

How to repeat:

drop table if exists itest;

create table itest (id int, str1 varchar(20), index(str1) );

insert into itest values (1, 'val1');

alter table itest add column str2 varchar(20), algorithm=INSTANT;

insert into itest values (2,'val1', 'val2');

drop table if exists itest2;

#This creates table with same final structure but without data dictionary versioned columns - so the final table isn't "like" the original
create table itest2 like itest;

alter table itest2 discard tablespace;

#copy itest.ibd to itest2.ibd

alter table itest2 import tablespace;

#crash

# Error log :

2022-06-08T15:16:31.502677Z 8 [ERROR] [MY-013183] [InnoDB] Assertion failure: rec.h:806:!rec_new_is_versioned(rec) thread 47532
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
15:16:31 UTC - mysqld got exception 0x16 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x1dc99df95e0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff761ff4e43    mysqld-debug.exe!my_print_stacktrace()[stacktrace.cc:429]
7ff76023269d    mysqld-debug.exe!print_fatal_signal()[signal_handler.cc:130]
7ff760232a7f    mysqld-debug.exe!my_server_abort()[signal_handler.cc:241]
7ff761fae80a    mysqld-debug.exe!my_abort()[my_init.cc:263]
7ff7625743cd    mysqld-debug.exe!ut_dbg_assertion_failed()[ut0dbg.cc:99]
7ff76287ee6e    mysqld-debug.exe!rec_init_null_and_len_comp()[rec.h:806]
7ff76287f5bb    mysqld-debug.exe!rec_init_offsets_comp_ordinary()[rec.cc:554]
7ff7628802b9    mysqld-debug.exe!rec_init_offsets_new()[rec.cc:66]
7ff76287e280    mysqld-debug.exe!rec_init_offsets()[rec.cc:360]
7ff76287cc69    mysqld-debug.exe!rec_get_offsets_func()[rec.cc:418]
7ff7627e2216    mysqld-debug.exe!PageConverter::update_records()[row0import.cc:2387]
7ff7627e2046    mysqld-debug.exe!PageConverter::update_index_page()[row0import.cc:2476]
7ff7627e150b    mysqld-debug.exe!PageConverter::update_page()[row0import.cc:2537]
7ff7627e0ef4    mysqld-debug.exe!PageConverter::operator()()[row0import.cc:2685]
7ff76245881b    mysqld-debug.exe!fil_iterate()[fil0fil.cc:8852]
7ff7624369aa    mysqld-debug.exe!fil_tablespace_iterate()[fil0fil.cc:9147]
7ff7627d87b3    mysqld-debug.exe!row_import_for_mysql()[row0import.cc:4526]
7ff762183dc4    mysqld-debug.exe!ha_innobase::discard_or_import_tablespace()[ha_innodb.cc:15019]
7ff75fe844df    mysqld-debug.exe!handler::ha_discard_or_import_tablespace()[handler.cc:4894]
7ff7601af338    mysqld-debug.exe!Sql_cmd_discard_import_tablespace::mysql_discard_or_import_tablespace()[sql_table.cc:11178]
7ff7607454b3    mysqld-debug.exe!Sql_cmd_discard_import_tablespace::execute()[sql_alter.cc:410]
7ff760173712    mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:4576]
7ff76016cf70    mysqld-debug.exe!dispatch_sql_command()[sql_parse.cc:5207]
7ff76017819e    mysqld-debug.exe!dispatch_command()[sql_parse.cc:1961]
7ff760175708    mysqld-debug.exe!do_command()[sql_parse.cc:1352]
7ff75fd0f582    mysqld-debug.exe!handle_connection()[connection_handler_per_thread.cc:302]
7ff762af064f    mysqld-debug.exe!pfs_spawn_thread()[pfs.cc:2944]
7ff761fca51c    mysqld-debug.exe!win_thread_start()[my_thread.cc:73]
7fff32c62d20    ucrtbased.dll!_register_onexit_function()
7ffffb9f54e0    KERNEL32.DLL!BaseThreadInitThunk()
7ffffcfc485b    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (1dcb70a0030): alter table itest2 import tablespace
Connection ID (thread ID): 8
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Suggested fix:
Best "fix" would be implementing "MOVE PARTITION / MOVE TABLESPACE" feature with write-lock and possibility to move data to another data directory.

Since mysql lacks this feature, creating table with exactly same definition as original is necessary. The separate DD hides the necessary data about previous instant DDL changes . When there'd be switch of "create table newtbl like oldtbl with data dictionary", the import would work (when itest2 is created the same way, and than discarded tablespace, import works). In fact I don't understand why the DD is not even read-only accessible for being able to investigate the DDL versions introduced in 8.0.29

Hi Mr. Zmeskal,

Thank you for your bug report.

However, we could not repeat your problem.

First of all , you are dropping a table that does not exist.

Second, the only commands that will do copy are EXPORT and IMPORT. Second one requires the presence of the .cfg file, as documented in our Manual.

Hence, we can not repeat your problem.

The drop table is conditioned with "if exists", it's not necessary. Assuming empty database, this leads to crash (not SQL error but mysql crash)

create table itest (id int, str1 varchar(20), index(str1) );
insert into itest values (1, 'val1');
alter table itest add column str2 varchar(20), algorithm=INSTANT;
insert into itest values (2,'val1', 'val2');

create table itest2 like itest;
alter table itest2 discard tablespace;
# copy itest.ibd to itest2.ibd on filesystem
alter table itest2 import tablespace;

At least it should give SQL error not crash.

Copy of .ibd with .cfg file gained from previous FLUSH TABLES itest FOR EXPORT fixes the missing DD behavior.

Thank you for the feedback.
With the 8.0.29 debug build I'm able to reproduce reported behavior (regardless of algorithm=INSTANT used or not + with or without .cfg i.e FLUSH TABLES itest FOR EXPORT;).

With the exact steps I'm not seeing any assert/crash on release build. 

Joining the test details shortly.

regards,
Umesh

8.0.29 test results

Attachment: 107517_8.0.29.results (application/octet-stream, text), 9.49 KiB.

Further tests were done to confirm once again, concluded that:
(i) Issue is seen *only* on debug builds and when .cfg metadata is not copied.

Discussed internally with Core developer, confirmed that by design it reject import if any of the following is met i.e .CFG file is missing or Undefined behavior. User should not do this for tables with INSTANT ADD/DROP columns etc. Which we can confirm on release builds.

But looking at the doc, "ALTER TABLE ... IMPORT TABLESPACE does not require a .cfg metadata file to import a table" - https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html

Without the .cfg file, debug build is asserting. This sounds like a doc issue + when importing without a .cfg file, and a warning similar to the following is issued(instead of asserting as seen in debug builds): 

Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\
test\t.cfg', will attempt to import without schema verification
1 row in set (0.00 sec)

Joining the full test details shortly.

8.0.29 test results

Attachment: 107517_8.0.29_release_debug.results (application/octet-stream, text), 10.02 KiB.

Posted by developer:
 
Note:
-----
Quoting the documentation (https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html#innodb-table-import-usage...):
"However, metadata checks are not performed when importing without a .cfg file"

Tables with INSTANT ADD/DROP columns require .cfg file for IMPORT as the required metadata is present in the .cfg file.
One workaround is to rebuild the table with `OPTIMIZE TABLE itest` before `CREATE TABLE itest2 LIKE itest` (as shown below)

Please follow the documentation example for importing tables (https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html#innodb-table-import-examp...) or importing partitioned tables (https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html#innodb-table-import-parti...)

Observation on Release build (without `OPTIMIZE TABLE itest`):
--------------------------------------------------------------
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql>
mysql> drop table if exists itest;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table itest (id int, str1 varchar(20), index(str1) );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into itest values (1, 'val1');
Query OK, 1 row affected (0.00 sec)

mysql> alter table itest add column str2 varchar(20), algorithm=INSTANT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into itest values (2,'val1', 'val2');
Query OK, 1 row affected (0.01 sec)

mysql> drop table if exists itest2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table itest2 like itest;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table itest2 discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table itest2 import tablespace;
ERROR 1808 (HY000): Schema mismatch (Clustered index validation failed. Because the .cfg file is missing, table definition of the IBD file could be different. Or the data file itself is already corrupted.)
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                   |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/itest2.cfg', will attempt to import without schema verification                                               |
| Error   | 1808 | Schema mismatch (Clustered index validation failed. Because the .cfg file is missing, table definition of the IBD file could be different. Or the data file itself is already corrupted.) |
| Error   | 1034 | Incorrect key file for table 'itest2'; try to repair it                                                                                                                                   |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Observation on release build (with `OPTIMIZE TABLE itest`):
-----------------------------------------------------------
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql>
mysql> drop table if exists itest;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table itest (id int, str1 varchar(20), index(str1) );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into itest values (1, 'val1');
Query OK, 1 row affected (0.00 sec)

mysql> alter table itest add column str2 varchar(20), algorithm=INSTANT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into itest values (2,'val1', 'val2');
Query OK, 1 row affected (0.01 sec)

mysql> drop table if exists itest2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> OPTIMIZE TABLE itest;
+------------+----------+----------+-------------------------------------------------------------------+
| Table      | Op       | Msg_type | Msg_text                                                          |
+------------+----------+----------+-------------------------------------------------------------------+
| test.itest | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.itest | optimize | status   | OK                                                                |
+------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.04 sec)

mysql> drop table if exists itest2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table itest2 like itest;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table itest2 discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table itest2 import tablespace;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                     |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/itest2.cfg', will attempt to import without schema verification |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from itest2;
+------+------+------+
| id   | str1 | str2 |
+------+------+------+
|    1 | val1 | NULL |
|    2 | val1 | val2 |
+------+------+------+
2 rows in set (0.00 sec)

mysql>

Hello,

I don't know if I'm on stackoverflow or bugs.mysql.com :) . Actually I don't seek workaround solution or point to documentation stating "It doesn't work because we messed up partitioning functionality with instant algorithm alter", I'm sorry to tell. I'm just reporting scenario, that leads to crash of mysql server.

Regarding optimize table. Nice, but partitioned tables in practise aren't few records long, they're huge and with optimize table you cancel all instant algo alters. Then, the environment is different and is not affected with bug.

8.0.29 versioned schema imo is not stable functionality and it should be allowed explicitly in config as experimental (or even instant algorithm at all). Hidden BC break in documentation with "instant algo alter breaks partition exchange functionality" is serious in large installations.

It could be imho fixed with allowing to create another table with same versioned schema (hidden in data dictionary - even for read) as original table.

Jan

Posted by developer:
 
Fixed as of the upcoming 8.0.31 release:

In debug builds, importing a tablespace without a .cfg file for table
with an instantly added or dropped column raised an assertion failure.