MySQL Bugs: #115199: Multiple MySQL crashes

Bug #115199	Multiple MySQL crashes - Possible reason MVI Index
Submitted:	3 Jun 2024 13:40	Modified:	17 Oct 2024 10:26
Reporter:	Carlos Abrantes	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.36	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi MySQL,

we are facing MySQL crashes always with the same error log:

2024-06-02T22:21:17.262696Z 8791 [ERROR] [MY-014055] [InnoDB] [FATAL] Multi-values cannot be converted to MySQL format: Table name: `mydb`.`mytable` Index name: `myindex`
2024-06-02T22:21:17.262732Z 8791 [ERROR] [MY-013183] [InnoDB] Assertion failure: row0sel.cc:2513:ib::fatal triggered thread 140469662242560
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-06-02T22:21:17Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=d0511f6fcf40bda30be3796565c264e503ecc241
Thread pointer: 0x7fc24c6aedb0
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 = 7fc1a451cc10 thread_stack 0x100000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x2139601]
/usr/sbin/mysqld(print_fatal_signal(int)+0x2a2) [0xff5f72]
/usr/sbin/mysqld(my_server_abort()+0x75) [0xff61b5]
/usr/sbin/mysqld(my_abort()+0xe) [0x213349e]
/usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x309) [0x23846a9]
/usr/sbin/mysqld(ib::fatal::~fatal()+0xcf) [0x2386fef]
/usr/sbin/mysqld(row_sel_field_store_in_mysql_format_func(unsigned char*, mysql_row_templ_t const*, dict_index_t const*, unsigned char const*, unsigned long)+0x2a8) [0x22fad18]
/usr/sbin/mysqld() [0x22fb306]
/usr/sbin/mysqld() [0x22fb870]
/usr/sbin/mysqld(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long)+0x2b0c) [0x230464c]
/usr/sbin/mysqld(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function)+0x386) [0x2199d76]
/usr/sbin/mysqld(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function)+0x269) [0x1107d79]
/usr/sbin/mysqld(handler::read_range_first(key_range const*, key_range const*, bool, bool)+0x66) [0x1108ef6]
/usr/sbin/mysqld(ha_innobase::read_range_first(key_range const*, key_range const*, bool, bool)+0x27) [0x2167417]
/usr/sbin/mysqld(handler::multi_range_read_next(char**)+0x155) [0x1109535]
/usr/sbin/mysqld(handler::ha_multi_range_read_next(char**)+0x2c) [0x110577c]
/usr/sbin/mysqld(IndexRangeScanIterator::Read()+0x54) [0x1319df4]
/usr/sbin/mysqld(Sql_cmd_update::update_single_table(THD*)+0x12a8) [0xf6a918]
/usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x1e4) [0xeea6a4]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0xae7) [0xe86377]
/usr/sbin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x51b) [0xe89b7b]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x2351) [0xe8c4c1]
/usr/sbin/mysqld(do_command(THD*)+0x15b) [0xe8d03b]
/usr/sbin/mysqld() [0xfe6188]
/usr/sbin/mysqld() [0x284d0c4]
/lib64/libpthread.so.0(+0x81da) [0x7fc9626e61da]
/lib64/libc.so.6(clone+0x43) [0x7fc960c94e73]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fc24c4fe1d0): UPDATE mydb.mytable SET ts=0 WHERE ts<1717194060 AND 'AAA' MEMBER OF(classes->'$.type') AND ts!=0 LIMIT 5000
Connection ID (thread ID): 8791
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.

Running an explain over the mention query we can see that the used key (myindex) is a MVI:

| mytable |          1 | myindex       |            1 | NULL             | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | cast(json_extract(`classes`,_utf8mb4\'$.my_classes\') as char(40) array) |

In 8.0.30 with 274d there is no restarts, but in 8.0.36 with 20d we have 69 restarts.
The mentioned query in the backtrace runs multiple times per day.

How to repeat:
Unfortunately i can't provide an exact path (since the query stated in backtrace runs multiple times and the crash only happens in some), but i can say that the error log is always the same.  

Suggested fix:
The MySQL should not crash

Correction of the MVI:
| mytable |          1 | myindex       |            1 | NULL             | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | cast(json_extract(`classes`,_utf8mb4\'$.type\') as char(40) array) |

Hi Mr. Abrantes,

Thank you for your bug report.

We have found that your bug report is a duplicate of the following bug:

https://bugs.mysql.com/bug.php?id=113373

You can not access that bug since it is a Security Vulnerability bug. That is a crashing bug, like yours, but unlike your bug it has a full test case that always leads to the crash.

However, we have left a note that your bug is a duplicate of the above one, so that you get notified when that bug is fixed.

Duplicate.

Hi,

Thank you for the fast feedback.

Is there any target version where that bug will be fixed?

Thanks,
Carlos

Hi Mr. Abrantes,

We do not have target versions or releases for the fixes.

Every Development team has it's own schedule for features and bug fixes. However, those schedule change on weekly basis, so no fixing info can be promised.

When the bug is fixed, this page will be updated.

Hi, 

i have been following the release notes but i didn't yet found this or the internal one shared in it.

Can you please confirm if it wasn't really fixed? asking because seems such a important one (mysql crash) and 3 releases already since reported.

thanks

Hi,

It is fixed .......

Here is the text:

Fixed as of the upcoming MySQL Server 8.0.40, 8.4.3, and 9.1.0 releases, and here's the proposed changelog entry from the documentation team:

Disabled the optimizer for indexes with range conditions on
multiple-value virtual columns.