Bug #114544 MySQL crash after too many INSTANT DDL
Submitted: 3 Apr 2024 12:28 Modified: 3 Apr 2024 13:04
Reporter: Luciano Mollea Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.36 OS:MacOS
Assigned to: CPU Architecture:ARM

[3 Apr 2024 12:28] Luciano Mollea 
Description:
MySQL 8.0.36 installed via HomeBrew.
MacOS 13.6.6 on Apple M1 Pro

I was testing the instant DDL on my local to understand what would happen if the total row version were to exceed 64.

After I accumulated about 64 DDL, instead of getting the following error like in the documentation (https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-colum...)

> ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.

The DDL went through but it just took more time (like it did an INPLACE DDL).

Now if I want to inspect the ROW_VERSION from the INFORMATION_SCHEMA using 
> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES;
MySQL crashes with the following stack trace in the log

2024-04-03T10:57:39.336457Z 8 [ERROR] [MY-013183] [InnoDB] Assertion failure: dict0dict.cc:3453:for_table || ref_table thread 0x16f3e7000
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.
2024-04-03T10:57:39Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x10780f400
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...
stack_bottom = 16f3e6f28 thread_stack 0x100000
0   mysqld                              0x0000000103115558 my_print_stacktrace(unsigned char const*, unsigned long) + 68
1   mysqld                              0x000000010281d588 print_fatal_signal(int) + 568
2   mysqld                              0x000000010281d78c my_server_abort() + 88
3   mysqld                              0x000000010310fcb4 my_abort() + 20
4   mysqld                              0x00000001033de28c ut_dbg_assertion_failed(char const*, char const*, unsigned long long) + 400
5   mysqld                              0x00000001031c1fc4 dict_foreign_add_to_cache(dict_foreign_t*, char const**, bool, bool, dict_err_ignore_t) + 980
6   mysqld                              0x00000001031d5b34 dd_table_load_fk_from_dd(dict_table_t*, dd::Table const*, char const**, dict_err_ignore_t, bool) + 1488
7   mysqld                              0x00000001031d5d14 dd_table_load_fk(dd::cache::Dictionary_client*, char const*, char const**, dict_table_t*, dd::Table const*, THD*, bool, bool, std::__1::deque<char const*, ut::allocator<char const*, ut::detail::allocator_base_pfs<char const*>>>*) + 108
8   mysqld                              0x00000001031d9bb8 dict_table_t* dd_open_table_one<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*, std::__1::deque<char const*, ut::allocator<char const*, ut::detail::allocator_base_pfs<char const*>>>&) + 5792
9   mysqld                              0x00000001031cb018 dict_table_t* dd_open_table<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*) + 60
10  mysqld                              0x00000001031cae94 dd_table_open_on_dd_obj(THD*, dd::cache::Dictionary_client*, dd::Table const&, dd::Partition const*, char const*, dict_table_t*&, TABLE const*) + 1320
11  mysqld                              0x00000001031cbc74 dd_table_open_on_id_low(THD*, MDL_ticket**, unsigned long long) + 1244
12  mysqld                              0x00000001031cb230 dd_table_open_on_id(unsigned long long, THD*, MDL_ticket**, bool, bool) + 456
13  mysqld                              0x00000001031dbd78 dd_process_dd_tables_rec_and_mtr_commit(mem_block_info_t*, unsigned char const*, dict_table_t**, dict_table_t*, MDL_ticket**, mtr_t*) + 440
14  mysqld                              0x00000001032c16a0 i_s_innodb_tables_fill_table(THD*, Table_ref*, Item*) + 536
15  mysqld                              0x0000000102755ce0 do_fill_information_schema_table(THD*, Table_ref*, Item*) + 140
16  mysqld                              0x0000000102536184 MaterializeInformationSchemaTableIterator::Init() + 104
17  mysqld                              0x00000001025348c0 LimitOffsetIterator::Init() + 32
18  mysqld                              0x000000010279b21c Query_expression::ExecuteIteratorQuery(THD*) + 736
19  mysqld                              0x000000010279b4e8 Query_expression::execute(THD*) + 48
20  mysqld                              0x0000000102743438 Sql_cmd_dml::execute_inner(THD*) + 140
21  mysqld                              0x0000000102742d88 Sql_cmd_dml::execute(THD*) + 740
22  mysqld                              0x0000000102703378 mysql_execute_command(THD*, bool) + 3604
23  mysqld                              0x00000001027015fc dispatch_sql_command(THD*, Parser_state*) + 680
24  mysqld                              0x00000001027005dc dispatch_command(THD*, COM_DATA const*, enum_server_command) + 5712
25  mysqld                              0x0000000102700d6c do_command(THD*) + 364
26  mysqld                              0x000000010280b6a4 handle_connection(void*) + 476
27  mysqld                              0x0000000103440c24 pfs_spawn_thread(void*) + 288
28  libsystem_pthread.dylib             0x0000000193493fa8 _pthread_start + 148
29  libsystem_pthread.dylib             0x000000019348eda0 thread_start + 8

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (12c37a430): /* ApplicationName=IntelliJ IDEA 2023.2.5 */ SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES
Connection ID (thread ID): 8
Status: NOT_KILLED

How to repeat:
I loaded ~12M rows into this test table

CREATE TABLE data
(
    id            BIGINT AUTO_INCREMENT
        PRIMARY KEY,
    customer_id   VARCHAR(36)                         NOT NULL,
    currency_code VARCHAR(3)                          NOT NULL,
    amount        DOUBLE(8, 2)                        NOT NULL,
    createdAt     TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    hbc           VARCHAR(32)                         NULL
);

Ran these 4 DDL over and over to understand what would happen if I reached 64 versions.

alter TABLE data add COLUMN platform VARCHAR(32);

alter table data add COLUMN device VARCHAR(32);

alter table data drop column device;

alter table data drop column platform;

Usually the DDL ran in ~20ms. One of those ran in 11sec. After that reading for data works, but reading from INFORMATION_SCHEMA.INNODB_TABLES crashes the server.
[3 Apr 2024 13:04] MySQL Verification Team 
Hi Mr. Mollea,

Thank you for your bug report.

We have tried to repeat your test case on macOS 14.4 with M2 CPU.

We inserted circa 50.000 rows. We ran those 4 (four) DDLs 256 times, which makes it 1024 DDLs in total.

We then ran 

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES;

and got the following output:

TABLE_ID	NAME	FLAG	N_COLS	SPACE	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE	INSTANT_COLS	TOTAL_ROW_VERSIONS
1054	mysql/replication_group_configuration_version	161	5	4294967294	Dynamic	0	General	0	0
4193	mysql/component	161	6	4294967294	Dynamic	0	General	0	0
4220	mysql/ndb_binlog_index	161	15	4294967294	Dynamic	0	General	0	0
4248	mysql/columns_priv	161	10	4294967294	Dynamic	0	General	0	0
4249	mysql/db	161	25	4294967294	Dynamic	0	General	0	0
4250	mysql/default_roles	161	7	4294967294	Dynamic	0	General	0	0
4251	mysql/engine_cost	161	9	4294967294	Dynamic	0	General	0	0
4253	mysql/global_grants	161	7	4294967294	Dynamic	0	General	0	0
4281	mysql/help_category	161	7	4294967294	Dynamic	0	General	0	0
4282	mysql/help_keyword	161	5	4294967294	Dynamic	0	General	0	0
4283	mysql/help_relation	161	5	4294967294	Dynamic	0	General	0	0
4280	mysql/help_topic	161	9	4294967294	Dynamic	0	General	0	0
4259	mysql/plugin	161	5	4294967294	Dynamic	0	General	0	0
4260	mysql/password_history	161	7	4294967294	Dynamic	0	General	0	0
4262	mysql/proxies_priv	161	10	4294967294	Dynamic	0	General	0	0
4263	mysql/role_edges	161	8	4294967294	Dynamic	0	General	0	0
4264	mysql/servers	161	12	4294967294	Dynamic	0	General	0	0
4265	mysql/server_cost	161	7	4294967294	Dynamic	0	General	0	0
4268	mysql/replication_asynchronous_connection_failover	161	9	4294967294	Dynamic	0	General	0	0
4269	mysql/replication_asynchronous_connection_failover_managed	161	7	4294967294	Dynamic	0	General	0	0
4270	mysql/replication_group_member_actions	161	9	4294967294	Dynamic	0	General	0	0
4272	mysql/tables_priv	161	11	4294967294	Dynamic	0	General	0	0
4274	mysql/time_zone_name	161	5	4294967294	Dynamic	0	General	0	0
4275	mysql/time_zone_leap_second	161	5	4294967294	Dynamic	0	General	0	0
4276	mysql/time_zone_transition	161	6	4294967294	Dynamic	0	General	0	0
4277	mysql/time_zone_transition_type	161	8	4294967294	Dynamic	0	General	0	0
4252	mysql/func	161	7	4294967294	Dynamic	0	General	0	0
4254	mysql/gtid_executed	161	6	4294967294	Dynamic	0	General	0	0
4279	mysql/slave_master_info	161	36	4294967294	Dynamic	0	General	0	0
4267	mysql/slave_worker_info	161	16	4294967294	Dynamic	0	General	0	0
4271	mysql/slave_relay_log_info	161	18	4294967294	Dynamic	0	General	0	0
4261	mysql/procs_priv	161	11	4294967294	Dynamic	0	General	0	0
4278	mysql/user	161	54	4294967294	Dynamic	0	General	0	0
4273	mysql/time_zone	161	5	4294967294	Dynamic	0	General	0	0
1062	sys/sys_config	33	7	1	Dynamic	0	Single	0	0
4313	test/data	33	9	2943	Dynamic	0	Single	0	35

Hence, we advise you to use our own binaries, available from:

https://dev.mysql.com/downloads/mysql/

You will find there versions 8.0.36 and 8.3.0 for your macOS and ARM CPU.

Can't repeat.
[3 Apr 2024 13:50] Frederic Descamps 
Hi Mr. Mollea,

The error message about the 64 versions is displayed ONLY if you specify the INSTANT as ALGORITHM.

See https://blogs.oracle.com/mysql/post/mysql-innodbs-instant-schema-changes-what-dbas-should-...

But that doesn't explain the crash of course.
[3 Apr 2024 13:52] MySQL Verification Team 
Thank you, Fred.
[3 Apr 2024 14:07] MySQL Verification Team 
Hi,

We have added INSTANT and there is still no crash:

ERROR 4092 (HY000) at line 220: Maximum row versions reached for table test/data. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
ERROR 4092 (HY000) at line 222: Maximum row versions reached for table test/data. No more columns can be added or dropped instantly. Please use COPY/INPLACE.

TABLE_ID	NAME	FLAG	N_COLS	SPACE	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE	INSTANT_COLS	TOTAL_ROW_VERSIONS
1054	mysql/replication_group_configuration_version	161	5	4294967294	Dynamic	0	General	0	0
4193	mysql/component	161	6	4294967294	Dynamic	0	General	0	0
4220	mysql/ndb_binlog_index	161	15	4294967294	Dynamic	0	General	0	0
4248	mysql/columns_priv	161	10	4294967294	Dynamic	0	General	0	0
4249	mysql/db	161	25	4294967294	Dynamic	0	General	0	0
4250	mysql/default_roles	161	7	4294967294	Dynamic	0	General	0	0
4251	mysql/engine_cost	161	9	4294967294	Dynamic	0	General	0	0
4253	mysql/global_grants	161	7	4294967294	Dynamic	0	General	0	0
4281	mysql/help_category	161	7	4294967294	Dynamic	0	General	0	0
4282	mysql/help_keyword	161	5	4294967294	Dynamic	0	General	0	0
4283	mysql/help_relation	161	5	4294967294	Dynamic	0	General	0	0
4280	mysql/help_topic	161	9	4294967294	Dynamic	0	General	0	0
4259	mysql/plugin	161	5	4294967294	Dynamic	0	General	0	0
4260	mysql/password_history	161	7	4294967294	Dynamic	0	General	0	0
4262	mysql/proxies_priv	161	10	4294967294	Dynamic	0	General	0	0
4263	mysql/role_edges	161	8	4294967294	Dynamic	0	General	0	0
4264	mysql/servers	161	12	4294967294	Dynamic	0	General	0	0
4265	mysql/server_cost	161	7	4294967294	Dynamic	0	General	0	0
4268	mysql/replication_asynchronous_connection_failover	161	9	4294967294	Dynamic	0	General	0	0
4269	mysql/replication_asynchronous_connection_failover_managed	161	7	4294967294	Dynamic	0	General	0	0
4270	mysql/replication_group_member_actions	161	9	4294967294	Dynamic	0	General	0	0
4272	mysql/tables_priv	161	11	4294967294	Dynamic	0	General	0	0
4274	mysql/time_zone_name	161	5	4294967294	Dynamic	0	General	0	0
4275	mysql/time_zone_leap_second	161	5	4294967294	Dynamic	0	General	0	0
4276	mysql/time_zone_transition	161	6	4294967294	Dynamic	0	General	0	0
4277	mysql/time_zone_transition_type	161	8	4294967294	Dynamic	0	General	0	0
4252	mysql/func	161	7	4294967294	Dynamic	0	General	0	0
4254	mysql/gtid_executed	161	6	4294967294	Dynamic	0	General	0	0
4279	mysql/slave_master_info	161	36	4294967294	Dynamic	0	General	0	0
4267	mysql/slave_worker_info	161	16	4294967294	Dynamic	0	General	0	0
4271	mysql/slave_relay_log_info	161	18	4294967294	Dynamic	0	General	0	0
4261	mysql/procs_priv	161	11	4294967294	Dynamic	0	General	0	0
4278	mysql/user	161	54	4294967294	Dynamic	0	General	0	0
4273	mysql/time_zone	161	5	4294967294	Dynamic	0	General	0	0
1062	sys/sys_config	33	7	1	Dynamic	0	Single	0	0
4320	test/data	33	9	2950	Dynamic	0	Single	0	64

You can see that version is indeed 64.

We can't repeat your crash, so please use our binary.