Bug #112694 MYSQL master crashes frequently after upgrade from 8.0.28 to 8.0.34
Submitted: 12 Oct 2023 0:00 Modified: 12 Oct 2023 10:23
Reporter: Uday Kanagala Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:8.0.34 OS:Linux
Assigned to: CPU Architecture:Any

[12 Oct 2023 0:00] Uday Kanagala
Description:
We have been experiencing frequent restarts on our MySQL database server, which is critical to our operations. These restarts occur during various scenarios:

1. Making structural changes (DDL) to tables, particularly those with indexes on JSON columns, though not exclusively.
2. Running direct queries against the information_schema database to retrieve view definitions.
3. Executing stored procedures that access the information_schema.
4. Random occurrences of these restarts, even when no DDL changes or information_schema queries are in progress.

We have taken several measures to address this issue, but unfortunately, it continues to persist:

1. A complete database rebuild, following a blue-green deployment approach, where we created a new environment and ran a full rebuild on all tables.
2. Increasing the database's capacity cpu/memory to handle the workload.
3. Enhancing the table cache size by 25%.

Despite these efforts, the problem of frequent server restarts remains unresolved.

crash1:
2023-10-01T00:21:24.030134Z 97931 [ERROR] [MY-013183] [InnoDB] Assertion failure: row0mysql.cc:984:magic2 == ROW_PREBUILT_FETCH_MAGIC_N thread 70588806989520
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.
2023-10-01T00:21:24Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x403353605000
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 = 40333cc3fe60 thread_stack 0x40000
/rdsdbbin/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x30) [0x1fbb750]
/rdsdbbin/mysql/bin/mysqld(print_fatal_signal(int)+0x31c) [0xfc9cdc]
/rdsdbbin/mysql/bin/mysqld(my_server_abort()+0x98) [0xfc9eb8]
/rdsdbbin/mysql/bin/mysqld(my_abort()+0x14) [0x1fb4094]
/rdsdbbin/mysql/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x27c) [0x2246a9c]
/rdsdbbin/mysql/bin/mysqld(row_prebuilt_free(row_prebuilt_t*, bool)+0x544) [0x219c664]
/rdsdbbin/mysql/bin/mysqld(ha_innobase::close()+0x30) [0x204c570]
/rdsdbbin/mysql/bin/mysqld(closefrm(TABLE*, bool)+0x188) [0xf68388]
/rdsdbbin/mysql/bin/mysqld(intern_close_table(TABLE*)+0x3c) [0xdce95c]
/rdsdbbin/mysql/bin/mysqld(Table_cache::add_used_table(THD*, TABLE*)+0x128) [0xdd66c8]
/rdsdbbin/mysql/bin/mysqld(open_table(THD*, Table_ref*, Open_table_context*)+0x14fc) [0xddebbc]
/rdsdbbin/mysql/bin/mysqld(open_tables(THD*, Table_ref**, unsigned int*, unsigned int, Prelocking_strategy*)+0x3c4) [0xddf6a4]
/rdsdbbin/mysql/bin/mysqld(open_tables_for_query(THD*, Table_ref*, unsigned int)+0x6c) [0xde07cc]
/rdsdbbin/mysql/bin/mysqld(Sql_cmd_dml::prepare(THD*)+0xe8) [0xec2d88]
/rdsdbbin/mysql/bin/mysqld(Sql_cmd_dml::execute(THD*)+0xd0) [0xec3b10]
/rdsdbbin/mysql/bin/mysqld(mysql_execute_command(THD*, bool)+0xf70) [0xe6fc70]
/rdsdbbin/mysql/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x394) [0xe72c94]
/rdsdbbin/mysql/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x171c) [0xe7499c]
/rdsdbbin/mysql/bin/mysqld(do_command(THD*)+0x1e0) [0xe75ca0]
/rdsdbbin/mysql/bin/mysqld() [0xfbbb68]
/rdsdbbin/mysql/bin/mysqld() [0x24b88b0]
/lib64/libpthread.so.0(+0x722c) [0x400004d6822c]
/lib64/libc.so.6(+0xd4a1c) [0x400004f5ca1c]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.

crash2:
the table_name below has indexes on json columns
2023-10-08T06:55:49.651229Z 61432 [ERROR] [MY-000000] [InnoDB] Invalid error codeCorrupted row found on fetch_cache for table: `db_name`.`table_name`
2023-10-08T06:55:49.651261Z 61432 [ERROR] [MY-013183] [InnoDB] Assertion failure: row0mysql.cc:988:magic2 == ROW_PREBUILT_FETCH_MAGIC_N thread 70590972188368
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.
2023-10-08T06:55:49Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x403378d11800
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 = 4033bdd24827 thread_stack 0x40000
/rdsdbbin/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x30) [0x1fbb430]
/rdsdbbin/mysql/bin/mysqld(print_fatal_signal(int)+0x31c) [0xfc9c7c]
/rdsdbbin/mysql/bin/mysqld(my_server_abort()+0x98) [0xfc9e58]
/rdsdbbin/mysql/bin/mysqld(my_abort()+0x14) [0x1fb3d74]
/rdsdbbin/mysql/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x27c) [0x224687c]

How to repeat:
There are few consistent pattern for reproduction. But, the restarts happened randomly in other unforeseen cases aswell:

1. Making structural changes (DDL) to tables, particularly those with indexes on JSON columns, though not exclusively.
2. Running direct queries against the information_schema database to retrieve view definitions.
3. Executing stored procedures that access the information_schema.
4. Random occurrences of these restarts, even when no DDL changes or information_schema queries are in progress.
[12 Oct 2023 10:23] MySQL Verification Team
Hi Mr. Kanagala,

Thank you for your bug report.

We have searched thoroughly our bug database and we have not found a single bug with a stacktrace remotely similar to yours.

Hence, what we require from you is a repeatable test case. This test case should consist of the set of SQL statements that always lead to the problem described. These should include table definitions, a necessary number of rows to repeat the problem and problematic queries themselves.

We can not proceed without having all this info.

Upgrading within 8.0 version is not known to cause any problems, especially for relatively late releases that you have used.

We are waiting on your full feedback.
[6 Nov 2023 15:37] Piotr Gasiorowski
We have been experiencing the same restart and crashes on MySQL RDS with 8.0.28 too, but so far unable to reproduce locally.

This is the query that causes crash and restart:

> SELECT * FROM table_systems WHERE type = 'hardware' AND subtype = 'sub17' AND  JSON_LENGTH(TPField) = 7  AND  JSON_CONTAINS(TPField, '[\"X3718\",\"64142\",\"M5246\",\"U2397\",\"S8326\",\"W6626\",\"P9704\"]')

> and the schema on that table is:

```
Create Table: CREATE TABLE `table_systems` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(64) NOT NULL,
  `subtype` varchar(255) DEFAULT NULL,
  `TPField` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type`),
  KEY `idx_type_subtype` (`type`,`subtype`),
  KEY `idx_tpfields` ((cast(`TPField` as char(255) array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
```
[7 Nov 2023 10:45] MySQL Verification Team
Hi Mr. Gasiorowski,

Can you supply us with sufficient amount of data so that we can try to repeat the problem ???
[7 Nov 2023 10:47] MySQL Verification Team
Also, please confirm that the above query is all that it takes to produce a crash.

We take crashing bugs very seriously, so we need to be able to reproduce them in order to escalate the problem to the team in charge.
[7 Nov 2023 17:01] Piotr Gasiorowski
Assertion failure: row0mysql.cc:995:magic2 == ROW_PREBUILT_FETCH_MAGIC_N

Attachment: mysql-error-running.log.2023-11-06.08 (application/octet-stream, text), 166.46 KiB.

[7 Nov 2023 17:05] Piotr Gasiorowski
I attached detailed crash log with debugging stack trace. If you need any more details please let me know and I will be more than happy to supply.

Just to confirm the above SELECT query is NOT the only factor to reproduce it.
I have been unable to reproduce it in any way on the production server as well as locally under simulated load while undergoing dynamic buffer resizing, which I initially thought was a related factor.
[8 Nov 2023 11:00] MySQL Verification Team
Hi Mr. Gasiorowski,

Can you supply us with sufficient amount of data so that we can try to
repeat the problem ???

We need:

* CREATE TABLE statement
* INSERT of all rows
* SELECT statement itself ........

We are waiting on your feedback.
[9 Nov 2023 9:54] Piotr Gasiorowski
Seeding table_systems

Attachment: seed.sql (application/octet-stream, text), 3.70 KiB.

[9 Nov 2023 11:35] MySQL Verification Team
Hi Mr. Gasiorowski,

We ran your test case and it worked without any problems.

We used our 8.0.35  binary, downloaded from dev.mysql.com.

SELECT returned no results what so ever, which is due to how you have defined WHERE conditions in your test case.

Can't repeat.
[9 Nov 2023 11:46] Piotr Gasiorowski
As mentioned previously, the query is not the only factor.
[9 Nov 2023 11:50] MySQL Verification Team
Hi,

As we wrote already, we need a fully repeatable test case.

Hence, add the factors that are needed to repeat the problem.

Can't repeat.