MySQL Bugs: #107941: Corruption in 8.0.29 related to instant DDL

Bug #107941	Corruption in 8.0.29 related to instant DDL
Submitted:	21 Jul 2022 19:47	Modified:	6 Dec 2022 8:13
Reporter:	Marc Reilly	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	corruption crash, instant ddl, instant ddl

Description:
Issue:
We have been seeing frequent corruptions and failed recoveries in 8.0.29 that seem to be related to instant DDL.

The crashes have produced multiple signatures depending on the DDL operation, column location and workload being executed.

High level steps:
1. Execute instant DDL against a table running on a version <8.0.29
2. Upgrade to 8.0.29
3. Execute a instant ddl operation on the same table adding a new column to the middle of the table.
4. Execute workload against the table
5. Observe unrecoverable crash.

How to repeat:
1. Create two mysql deployments, one on 8.0.27 and another on 8.0.29.
```
dbdeployer deploy single 8.0.29
dbdeployer deploy single 8.0.27
```

2. Using sysbench we are going to generate some dummy data. Create two schemas, one for sysbench and another for our instant ddl tests
/home/ec2-user/sandboxes/msb_8_0_27/use -e "create database sysbench_2"
/home/ec2-user/sandboxes/msb_8_0_27/use -e "create database sysbench"

3. Load data into our sysbench schema. We will use this data to seed our DDL test tables
```
../sysbench ~/sysbench/src/lua/oltp_read_write.lua --db-driver=mysql --mysql-user=root --mysql-password=msandbox --mysql-db=sysbench --threads=10 --rand-type=uniform --db-ps-mode=disable --tables=10 --table-size=25000 --mysql-socket=/tmp/mysql_sandbox8027.sock --mysql-port=8027 --time=0 --report-interval=1 prepare
```

4. In our second schema, create a table called sysbench_2.sbtest1 without the default sysbench pad column and seed the db with data from sysbench.sbtest1 
```
/home/ec2-user/sandboxes/msb_8_0_27/use -e "insert into sysbench_2.sbtest1 ( k,c) select k,c from sysbench.sbtest1;"
/home/ec2-user/sandboxes/msb_8_0_27/use -e "CREATE TABLE sysbench_2.sbtest1 ( id int NOT NULL AUTO_INCREMENT, k int NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci"
/home/ec2-user/sandboxes/msb_8_0_27/use -e "insert into sysbench_2.sbtest1 ( k,c) select k,c from sysbench.sbtest1;"
/home/ec2-user/sandboxes/msb_8_0_27/use -e "insert into sysbench_2.sbtest1 ( k,c) select k,c from sysbench.sbtest1;"
```

5. Execute three instant DDLs to add columns to the end of the table. At the start and end check the INSTANT_COLS column in innodb_tables to verify our tables have instant DDLs
```
/home/ec2-user/sandboxes/msb_8_0_27/use -e "select * from information_schema.innodb_tables where INSTANT_COLS>0;"
/home/ec2-user/sandboxes/msb_8_0_27/use -e "alter table sysbench_2.sbtest1 add column pad varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL, ALGORITHM=instant;"
/home/ec2-user/sandboxes/msb_8_0_27/use -e "alter table sysbench_2.sbtest1 add column pad_2 varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL, ALGORITHM=instant;"
/home/ec2-user/sandboxes/msb_8_0_27/use -e "alter table sysbench_2.sbtest1 add column pad_3 varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL, ALGORITHM=instant;"
/home/ec2-user/sandboxes/msb_8_0_27/use -e "select * from information_schema.innodb_tables where INSTANT_COLS>0;"

# Should have this output:
+----------+--------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+--------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1073 | sysbench_2/sbtest1 |   33 |      9 |    12 | Dynamic    |             0 | Single     |            3 |                  0 |
+----------+--------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
```

6. We can now upgrade from 8.0.27 to 8.0.29. In 8.0.29 instant DDL is default and there are some new page/redo formats- which is what we were originally suspicious about. 
```
dbdeployer admin upgrade msb_8_0_27 msb_8_0_29
```

Now that we are upgraded we can reproduce some crashes. Here I have four different combinations on 8.0.29 DDLs that will produce different stack.

Repro 1:
1. Now that we are upgraded let’s add some new columns to the table. Here I’m adding a column to the end of the table and to the middle of the table.

/home/ec2-user/sandboxes/msb_8_0_29/use -e "select * from information_schema.innodb_tables where INSTANT_COLS>0;"
/home/ec2-user/sandboxes/msb_8_0_29/use -e "alter table sysbench_2.sbtest1 add column pad_4 varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL, ALGORITHM=instant;"
/home/ec2-user/sandboxes/msb_8_0_29/use -e "alter table sysbench_2.sbtest1 add column pad_middle varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL after k, ALGORITHM=instant;"

```
/home/ec2-user/sandboxes/msb_8_0_29/use -e "alter table sysbench_2.sbtest1 add column pad_middle varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL after pad_2, ALGORITHM=instant;"
```

2. Now run sysbench against 8.0.29 and you will crash immediately getting stuck in a crash loop. see attached `repr_1.log` for stacks
```
../sysbench ~/sysbench/src/lua/oltp_read_write.lua --db-driver=mysql --mysql-user=root --mysql-password=msandbox --mysql-db=sysbench_2 --threads=10 --rand-type=uniform --db-ps-mode=disable --tables=1 --table-size=10000 --mysql-socket=/tmp/mysql_sandbox8029.sock --mysql-port=8029 --time=0 --report-interval=1 run
```

Repro 2:
1. Here we are adding column pad middle between instant columns pad_2 and pad_3.  see attached `repr_2.log` for stacks
```
/home/ec2-user/sandboxes/msb_8_0_29/use -e "alter table sysbench_2.sbtest1 add column pad_middle varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL after pad_2, ALGORITHM=instant;"
```

2. Now run sysbench against 8.0.29 and you will crash immediately getting stuck in a crash loop. see attached `repr_2.log` for stacks
```
../sysbench ~/sysbench/src/lua/oltp_read_write.lua --db-driver=mysql --mysql-user=root --mysql-password=msandbox --mysql-db=sysbench_2 --threads=10 --rand-type=uniform --db-ps-mode=disable --tables=1 --table-size=10000 --mysql-socket=/tmp/mysql_sandbox8029.sock --mysql-port=8029 --time=0 --report-interval=1 run
```

repro 3:
1. Here before upgrading to 8.0.29 in step 6, I initialize the first 15 thousand rows of the table. These rows will then be initialized so DML/Select to them after upgrade should not cause a crash
```
/home/ec2-user/sandboxes/msb_8_0_27/use sysbench_2 -e 'update sbtest1 set pad="test" where id<10000;'
/home/ec2-user/sandboxes/msb_8_0_27/use sysbench_2 -e 'update sbtest1 set pad_2="test" where id<10000;'
/home/ec2-user/sandboxes/msb_8_0_27/use sysbench_2 -e 'update sbtest1 set pad_3="test" where id<10000;'
```

2. Issue upgrade
```
dbdeployer admin upgrade msb_8_0_27 msb_8_0_29
```

3. Now that we are upgraded let’s add some new columns to the table. Here I’m adding a column to the end of the table and to the middle of the table.
```
/home/ec2-user/sandboxes/msb_8_0_29/use -e "select * from information_schema.innodb_tables where INSTANT_COLS>0;"
/home/ec2-user/sandboxes/msb_8_0_29/use -e "alter table sysbench_2.sbtest1 add column pad_4 varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL, ALGORITHM=instant;"
/home/ec2-user/sandboxes/msb_8_0_29/use -e "alter table sysbench_2.sbtest1 add column pad_middle varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL after k, ALGORITHM=instant;"
```

4. Add the instant col between two other instant cols:
```
/home/ec2-user/sandboxes/msb_8_0_29/use -e "alter table sysbench_2.sbtest1 add column pad_middle varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL after pad_2, ALGORITHM=instant;"
```

5. Start sysbench but put --table-rows=1000, you will not crash as you are touching rows which are initialized.
```
../sysbench ~/sysbench/src/lua/oltp_read_write.lua --db-driver=mysql --mysql-user=root --mysql-password=msandbox --mysql-db=sysbench_2 --threads=10 --rand-type=uniform --db-ps-mode=disable --tables=1 --table-size=1000 --mysql-socket=/tmp/mysql_sandbox8029.sock --mysql-port=8029 --time=10 --report-interval=1 run
```

6. Now manually update the column k, again no crash since this is before the instant ddl column
```
mysql [localhost:8029] {msandbox} (sysbench_2) > show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `pad` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL,
  `pad_2` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL,
  `pad_3` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=65535 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)mysql [localhost:8029] {msandbox} (sysbench_2) > begin;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8029] {msandbox} (sysbench_2) > update sbtest1 set pad = "im not gonna crash" where id < 1000;
Query OK, 999 rows affected (0.01 sec)
Rows matched: 999  Changed: 999  Warnings: 0
```

7. 1. Now start a transaction where you will update a row where id > 15,000. this will cause a crash once the update touches a uninitialized row that was touched using a 8.0.27 instant DDL. See `repro_3.log` for stacks
```
mysql [localhost:8029] {msandbox} (sysbench_2) > update sbtest1 set pad = "im gonna crash" where id > 1000;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 104
ERROR:
Can't connect to the server
```

repro_1.log

Attachment: repro_1.log (application/octet-stream, text), 33.91 KiB.

repro_2.log

Attachment: repro_2.log (application/octet-stream, text), 35.91 KiB.

repro_3.log

Attachment: repro_3.log (application/octet-stream, text), 38.01 KiB.

Hi Mr. Reilly,

Thank you for your bug report.

We have attempted to reproduce the crash on the standalone 8.0.29, without any success.

Hence, can you send us the stacktrace that MySQL always prints when it crashes ?????

Thanks a lot in advance.

Hi,

I have attached all backtraces and stacks to the report yesterday as repro_1.log,repro_2.log,repro_3.log. There are many different types of stacks that can be produced depending on the DDL order.

Here is a repro in the most basic form: 

dbdeployer deploy single 8.0.29
dbdeployer deploy single 8.0.27

/home/ec2-user/sandboxes/msb_8_0_27/use -e "create database sysbench"

# Load a single table using sysbench
../sysbench ~/sysbench/src/lua/oltp_read_write.lua --db-driver=mysql --mysql-user=root --mysql-password=msandbox --mysql-db=sysbench --threads=1 --rand-type=uniform --db-ps-mode=disable --tables=1 --table-size=25000 --mysql-socket=/tmp/mysql_sandbox8027.sock --mysql-port=8027 --time=0 --report-interval=1 prepare

# Add three instant cols to the sysbench table
/home/ec2-user/sandboxes/msb_8_0_27/use sysbench -e "select * from information_schema.innodb_tables where INSTANT_COLS>0;"
/home/ec2-user/sandboxes/msb_8_0_27/use sysbench -e "alter table sbtest1 add column instant_pad_1 varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL, ALGORITHM=instant;"
/home/ec2-user/sandboxes/msb_8_0_27/use sysbench -e "alter table sbtest1 add column instant_pad_2 varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL, ALGORITHM=instant;"
/home/ec2-user/sandboxes/msb_8_0_27/use sysbench -e "alter table sbtest1 add column instant_pad_3 varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL, ALGORITHM=instant;"
/home/ec2-user/sandboxes/msb_8_0_27/use sysbench -e "select * from information_schema.innodb_tables where INSTANT_COLS>0;"
/home/ec2-user/sandboxes/msb_8_0_27/use sysbench -e "show create table sbtest1"

# Table should now look like this
[ec2-user@ip-172-31-3-208 lua]$ /home/ec2-user/sandboxes/msb_8_0_29/use sysbench -e "show create table sbtest1\G"
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `instant_pad_1` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL,
  `instant_pad_2` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL,
  `instant_pad_3` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=25001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# Upgrade in place to 8.0.29
[ec2-user@ip-172-31-3-208 lua]$ dbdeployer admin upgrade msb_8_0_27 msb_8_0_29
stop /home/ec2-user/sandboxes/msb_8_0_27
stop /home/ec2-user/sandboxes/msb_8_0_29
Data directory msb_8_0_27/data moved to msb_8_0_29/data
...... sandbox server started

The data directory from msb_8_0_29/data is preserved in msb_8_0_29/data-msb_8_0_29
The data directory from msb_8_0_27/data is now used in msb_8_0_29/data
msb_8_0_27 is not operational and can be deleted

AFTER UPGRADE:
[ec2-user@ip-172-31-3-208 lua]$ /home/ec2-user/sandboxes/msb_8_0_29/use sysbench -e "alter table sbtest1 add column 8029_pad_4 varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL, ALGORITHM=instant;"

[ec2-user@ip-172-31-3-208 lua]$ /home/ec2-user/sandboxes/msb_8_0_29/use sysbench -e "alter table sbtest1 add column 8029_pad_middle varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL after k, ALGORITHM=instant;"

# Your table should now look like this:
[ec2-user@ip-172-31-3-208 lua]$ /home/ec2-user/sandboxes/msb_8_0_29/use sysbench -e "show create table sbtest1\G"
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `8029_pad_middle` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL,
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `instant_pad_1` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL,
  `instant_pad_2` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL,
  `instant_pad_3` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL,
  `8029_pad_4` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=25001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

# Now run sysbench and you immediately crash:
[ec2-user@ip-172-31-3-208 lua]$ ../sysbench ~/sysbench/src/lua/oltp_read_write.lua --db-driver=mysql --mysql-user=root --mysql-password=msandbox --mysql-db=sysbench --threads=10 --rand-type=uniform --db-ps-mode=disable --tables=1 --table-size=25000 --mysql-socket=/tmp/mysql_sandbox8029.sock --mysql-port=8029 --time=0 --report-interval=1 run
WARNING: Both event and time limits are disabled, running an endless test
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 10
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 13013 AND 13112 ORDER BY c'
FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'COMMIT'

see blow stacks

# Crash for this scenario - plenty others already attached for other combinarions:
2022-07-22T18:14:10.126595Z 21 [ERROR] [MY-011855] [InnoDB] Page old data size 15396 new data size 9122, page old max ins size 819 new max ins size 7093
2022-07-22T18:14:10.126623Z 21 [ERROR] [MY-011856] [InnoDB] Submit a detailed bug report to http://bugs.mysql.com
2022-07-22T18:14:10.126631Z 21 [ERROR] [MY-013183] [InnoDB] Assertion failure: btr0cur.cc:3654:rec thread 139978624358144
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.
18:14:10 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f4eec000e80
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 = 7f4f502e4d60 thread_stack 0x100000
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x1f80d4e]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(print_fatal_signal(int)+0x2eb) [0x103587b]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(my_server_abort()+0x5e) [0x103597e]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(my_abort()+0xa) [0x1f7b4fa]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x30c) [0x223b96c]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld() [0x227c25e]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld() [0x21bb36a]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld() [0x21bf1a1]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(row_upd_step(que_thr_t*)+0xc1c) [0x21c16cc]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld() [0x2180941]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(row_update_for_mysql(unsigned char const*, row_prebuilt_t*)+0x30) [0x2182c40]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(ha_innobase::update_row(unsigned char const*, unsigned char*)+0x2a2) [0x2077c72]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(handler::ha_update_row(unsigned char const*, unsigned char*)+0x1eb) [0x113d05b]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(Sql_cmd_update::update_single_table(THD*)+0x2e2d) [0xfc488d]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(Sql_cmd_update::execute_inner(THD*)+0xb5) [0xfc5185]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x2c5) [0xf46e55]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(mysql_execute_command(THD*, bool)+0xab8) [0xee5c18]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x403) [0xeeaa63]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xe40) [0xeebda0]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(do_command(THD*)+0x103) [0xeede93]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld() [0x1026898]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld() [0x24c068a]
/lib64/libpthread.so.0(+0x744b) [0x7f4f91a5444b]
/lib64/libc.so.6(clone+0x3f) [0x7f4f8fcb956f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f4eec03a9e0): UPDATE sbtest1 SET c='44419835746-47261079738-92717398671-57594746832-23087729761-10127811586-06478858560-05705075145-02944560480-95764218582' WHERE id=24145
Connection ID (thread ID): 21
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.
2022-07-22T18:14:10.201620Z mysqld_safe Number of processes running now: 0
2022-07-22T18:14:10.204005Z mysqld_safe mysqld restarted
2022-07-22T18:14:10.437871Z 0 [System] [MY-010116] [Server] /home/ec2-user/opt/mysql/8.0.29/bin/mysqld (mysqld 8.0.29) starting as process 17989
2022-07-22T18:14:10.443236Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
18:14:10 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x0
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 = 0 thread_stack 0x100000
2022-07-22T18:14:10.583844Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: rec.cc:392 thread 140076553266944
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.
18:14:10 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x0
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 = 0 thread_stack 0x100000
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x1f80d4e]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(print_fatal_signal(int)+0x2eb) [0x103587b]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(handle_fatal_signal+0x65) [0x10358e5]
/lib64/libpthread.so.0(+0x118e0) [0x7f66340d88e0]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld() [0xd6f4ae]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(page_cur_delete_rec(page_cur_t*, dict_index_t const*, unsigned long const*, mtr_t*)+0x75) [0x21417c5]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(page_cur_parse_delete_rec(unsigned char*, unsigned char*, buf_block_t*, dict_index_t*, mtr_t*)+0xcb) [0x2141e9b]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld() [0x210f278]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(recv_recover_page_func(bool, buf_block_t*)+0x827) [0x2111417]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(buf_page_io_complete(buf_page_t*, bool)+0x4a9) [0x229bf89]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(fil_aio_wait(unsigned long)+0x11a) [0x238f1ea]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld() [0x21da1e0]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)(unsigned long), unsigned long> > >::_M_run()+0xb0) [0x21da3d0]
/home/ec2-user/opt/mysql/8.0.29/bin/mysqld() [0x277a69f]
/lib64/libpthread.so.0(+0x744b) [0x7f66340ce44b]
/lib64/libc.so.6(clone+0x3f) [0x7f663233356f]
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.
2022-07-22T18:14:10.665590Z mysqld_safe mysqld from pid file /home/ec2-user/sandboxes/msb_8_0_29/data/mysql_sandbox8029.pid ended

Hi Marc,

Please check the 8.0.30 release notes for bug numbers: 34233264, 34243694, 34181432, 34302445 once it's released.  They should address this issue.

regards,
sbester

Hi Mr. Reilly,

We are still not able to repeat the problem that you describe.

Your issue should be resolved in 8.0.30, so let us know how did it go with 8.0.30, once that it comes out and you install it, do upgrade and run your application .....

Hi Mr. Reilly,

We have two issues.

First, you did not try version 8.0.30, since 8.0.29 has a number of upgrade bugs reported and fixed.

Second, we do not support MDS Aurora nor MDS MySQL, as provided by the Amazon.

This is still reproducible on 8.0.30. Attached some scripts that I hope will help you see the same behavior.

Step 1: Setup environment: Install dbdeployer and sysbench
$ bash repro_install_sysbench_dbdeployer.sh

Step 2: Run repro scripts:
each repro scrip will
1. Delete old sandbox and data
2. reinstall fresh 8.0.28 and 8.0.30 MySQL
3. Alter table
4. upgrade 8.0.28 sandbox to 8.0.30
5. Run SQL SQL to cause crash

$ bash repro1.sh
MySQL error log in 8030:
```
2022-09-30T23:12:07.185593Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: rec.cc:391 thread 140395414476544
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.
23:12:07 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7fb038000b20
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 = 7fb05acecbf8 thread_stack 0x100000
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x1fbe99e]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(print_fatal_signal(int)+0x2eb) [0x106677b]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(my_server_abort()+0x5e) [0x106687e]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(my_abort()+0xa) [0x1fb8d3a]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x30c) [0x229515c]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x22a33e1]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(page_cur_search_with_match_bytes(buf_block_t const*, dict_index_t const*, dtuple_t const*, page_cur_mode_t, unsigned long*, unsigned long*, unsigned long*, unsigned long*, page_cur_t*)+0x1d6) [0x2198e06]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(btr_cur_search_to_nth_level(dict_index_t*, unsigned long, dtuple_t const*, page_cur_mode_t, unsigned long, btr_cur_t*, unsigned long, char const*, unsigned long, mtr_t*)+0x1cd4) [0x22deaf4]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(row_search_on_row_ref(btr_pcur_t*, unsigned long, dict_table_t*, dtuple_t const*, mtr_t*)+0xcf) [0x220180f]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x21f8539]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x21f940c]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(row_purge_step(que_thr_t*)+0xa4b) [0x21fd01b]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(que_run_threads(que_thr_t*)+0xbc0) [0x21b64c0]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(srv_worker_thread()+0x47d) [0x223482d]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)()> > >::_M_run()+0xc4) [0x2157a14]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x27e42df]
/lib64/libpthread.so.0(+0x744b) [0x7fb0c852044b]
/lib64/libc.so.6(clone+0x3f) [0x7fb0c678456f]
```

$ bash repro1.sh
MySQL error log in 8030:
```
2022-09-30T23:12:07.185593Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: rec.cc:391 thread 140395414476544
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.
23:12:07 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7fb038000b20
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 = 7fb05acecbf8 thread_stack 0x100000
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x1fbe99e]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(print_fatal_signal(int)+0x2eb) [0x106677b]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(my_server_abort()+0x5e) [0x106687e]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(my_abort()+0xa) [0x1fb8d3a]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x30c) [0x229515c]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x22a33e1]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(page_cur_search_with_match_bytes(buf_block_t const*, dict_index_t const*, dtuple_t const*, page_cur_mode_t, unsigned long*, unsigned long*, unsigned long*, unsigned long*, page_cur_t*)+0x1d6) [0x2198e06]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(btr_cur_search_to_nth_level(dict_index_t*, unsigned long, dtuple_t const*, page_cur_mode_t, unsigned long, btr_cur_t*, unsigned long, char const*, unsigned long, mtr_t*)+0x1cd4) [0x22deaf4]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(row_search_on_row_ref(btr_pcur_t*, unsigned long, dict_table_t*, dtuple_t const*, mtr_t*)+0xcf) [0x220180f]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x21f8539]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x21f940c]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(row_purge_step(que_thr_t*)+0xa4b) [0x21fd01b]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(que_run_threads(que_thr_t*)+0xbc0) [0x21b64c0]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(srv_worker_thread()+0x47d) [0x223482d]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)()> > >::_M_run()+0xc4) [0x2157a14]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x27e42df]
/lib64/libpthread.so.0(+0x744b) [0x7fb0c852044b]
/lib64/libc.so.6(clone+0x3f) [0x7fb0c678456f]
```

repro2.sh: crash loop

$ bash repro2.sh
MySQL err log in 8030
```
22:56:38 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f8278000b20
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 = 7f82836edbf8 thread_stack 0x100000
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x1fbe99e]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(print_fatal_signal(int)+0x2eb) [0x106677b]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(handle_fatal_signal+0x65) [0x10667e5]
/lib64/libpthread.so.0(+0x118e0) [0x7f8304f2d8e0]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(page_cur_search_with_match_bytes(buf_block_t const*, dict_index_t const*, dtuple_t const*, page_cur_mode_t, unsigned long*, unsigned long*, unsigned long*, unsigned long*, page_cur_t*)+0x165) [0x2198d95]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(btr_cur_search_to_nth_level(dict_index_t*, unsigned long, dtuple_t const*, page_cur_mode_t, unsigned long, btr_cur_t*, unsigned long, char const*, unsigned long, mtr_t*)+0x1cd4) [0x22deaf4]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(row_search_on_row_ref(btr_pcur_t*, unsigned long, dict_table_t*, dtuple_t const*, mtr_t*)+0xcf) [0x220180f]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x21f8539]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x21f940c]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(row_purge_step(que_thr_t*)+0xa4b) [0x21fd01b]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(que_run_threads(que_thr_t*)+0xbc0) [0x21b64c0]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(srv_worker_thread()+0x47d) [0x223482d]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)()> > >::_M_run()+0xc4) [0x2157a14]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x27e42df]
/lib64/libpthread.so.0(+0x744b) [0x7f8304f2344b]
/lib64/libc.so.6(clone+0x3f) [0x7f830318756f]
```

repro3.sh: crashes and restarts, prob a case for crash loop too:
$ bash repro3.sh
MySQL err log in 8030
```
2022-09-30T23:07:20.767862Z 24 [ERROR] [MY-013183] [InnoDB] Assertion failure: rec.cc:391 thread 140640720484096
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.
23:07:20 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7fe900000e80
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 = 7fe9782f6d20 thread_stack 0x100000
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x1fbe99e]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(print_fatal_signal(int)+0x2eb) [0x106677b]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(my_server_abort()+0x5e) [0x106687e]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(my_abort()+0xa) [0x1fb8d3a]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x30c) [0x229515c]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x22a33e1]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(page_cur_search_with_match(buf_block_t const*, dict_index_t const*, dtuple_t const*, page_cur_mode_t, unsigned long*, unsigned long*, page_cur_t*, rtr_info*)+0x1c0) [0x21985e0]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(btr_root_raise_and_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t const*, mtr_t*)+0x5b8) [0x22d22d8]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(btr_cur_pessimistic_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t*, unsigned char**, big_rec_t**, que_thr_t*, mtr_t*)+0x655) [0x22d5475]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(btr_insert_on_non_leaf_level(unsigned int, dict_index_t*, unsigned long, dtuple_t*, ut::Location, mtr_t*)+0x2a6) [0x22c6e46]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(btr_page_split_and_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t const*, mtr_t*)+0xa2c) [0x22d071c]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(btr_cur_pessimistic_insert(unsigned int, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t*, unsigned char**, big_rec_t**, que_thr_t*, mtr_t*)+0x2a5) [0x22d50c5]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(btr_cur_pessimistic_update(unsigned long, btr_cur_t*, unsigned long**, mem_block_info_t**, mem_block_info_t*, big_rec_t**, upd_t*, unsigned long, que_thr_t*, unsigned long, unsigned long, mtr_t*, btr_pcur_t*)+0x82f) [0x22d956f]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x22169d5]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x221a791]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(row_upd_step(que_thr_t*)+0xbff) [0x221ccbf]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x21de0b1]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(row_update_for_mysql(unsigned char const*, row_prebuilt_t*)+0x30) [0x21de5b0]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(ha_innobase::update_row(unsigned char const*, unsigned char*)+0x2a2) [0x20b9042]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(handler::ha_update_row(unsigned char const*, unsigned char*)+0x1eb) [0x116fcab]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(Sql_cmd_update::update_single_table(THD*)+0x1d90) [0xfe24b0]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(Sql_cmd_update::execute_inner(THD*)+0xb5) [0xfe2c95]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x2c5) [0xf60fb5]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(mysql_execute_command(THD*, bool)+0xad8) [0xeff0f8]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x403) [0xf04163]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x2080) [0xf066f0]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld(do_command(THD*)+0x1df) [0xf076af]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x10574b8]
/home/ec2-user/opt/mysql/8.0.30/bin/mysqld() [0x2526cda]
/lib64/libpthread.so.0(+0x744b) [0x7fe9a96fc44b]
/lib64/libc.so.6(clone+0x3f) [0x7fe9a796056f]

```

repro 3

Attachment: repro3.sh (text/x-sh), 5.83 KiB.

repro 2

Attachment: repro2.sh (text/x-sh), 4.70 KiB.

repro 1

Attachment: repro1.sh (text/x-sh), 4.99 KiB.

Install dbdeployer and sysbench - setup env for bug repro

Attachment: repro_install_sysbench_dbdeployer.sh (text/x-sh), 2.19 KiB.

> Second, we do not support MDS Aurora nor MDS MySQL, as provided by the Amazon.

Also, just to clarify: All the above scenarios from the beginning of this bug report have been done using the Generic Linux binary downloaded from https://dev.mysql.com/ on a vanilla EC2 linux instance using dbdeployer for ease of repro. 

Latest done using:
https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz
https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.28-linux-glibc2.12-x86_64.tar...

Also attached rhel 8 scripts if thats easier. Same steps, just diff directory/package names

Attachment: rhel8scripts.zip (application/zip, text), 6.34 KiB.

updated affected versions

Hi Mr. Reilly,

Sorry, but we are still not able to reproduce your test case.

We must stress that we do not use products named `sandbox` and `dbdeployer'. We do use, however sysbench. We were not able to repeat your problem only with sysbench and DDL.

Can't repeat.

Hi Mr. Reilly,

We have been able to reproduce your bug report, without the tools that you have used.

This is now a verified bug.

Hi,
I have just tested on newly released 8.0.31 and I'm no longer able to repro. I couldn't see this bug report ref in the release notes, can you confirm it is resolved?
Thanks!
Marc

Hi Mr. Reilly,

This bug has been resolved by one of our internal bug reports.

This is now a bug that is a duplicate of the internal bug, resolved in 8.0.31.

Thank you , very much ......

Posted by developer:
 
Fixed by patch for Bug#34488482

Thank you, Nisha .....

it was already noticed that the patch is applied to 8.0.31.

Bug #112309 marked as duplicate of this one