MySQL Bugs: #116778: mysql crash when optimize table and update

Bug #116778	mysql crash when optimize table and update
Submitted:	26 Nov 2024 4:28	Modified:	26 Nov 2024 6:51
Reporter:	zongyi chen (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.40, 8.4.3, 9.1.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
doing optimize table and update same time will lead to crash.

-- session 1
CREATE TABLE `t1` (
  `a` int DEFAULT NULL,
  `b` text,
  `c` varchar(15) GENERATED ALWAYS AS (substr(`b`,1,1)) VIRTUAL,
  KEY `key_a` (`a`)
) ROW_FORMAT=REDUNDANT;

insert into t1(a, b) values(0,'test');

SET debug_sync = 'alter_table_update_log signal send_concurrent WAIT_FOR concurrent_done';

OPTIMIZE TABLE t1;

-- session 2
SET debug_sync = 'now wait_for send_concurrent';
UPDATE test.t1 SET a = a + 1;
SET debug_sync = 'now signal concurrent_done';

mysqld will crash

How to repeat:
as Description

Hello zongyi chen,

Thank you for the report and test case.
I quickly attempted to reproduce in 8.0.40 debug build with provided test case but not seeing reported issue. Is there anything I'm missing here? Also, could you please confirm exact version in which you observed this? Thank you.

-- 8.0.40 debug build

BugNumber=116778
rm -rf $BugNumber/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version=debug --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 2>&1 &

-- session 1
bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.40-debug MySQL Community Server - GPL - Debug

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `t1` (
    ->   `a` int DEFAULT NULL,
    ->   `b` text,
    ->   `c` varchar(15) GENERATED ALWAYS AS (substr(`b`,1,1)) VIRTUAL,
    ->   KEY `key_a` (`a`)
    -> ) ROW_FORMAT=REDUNDANT;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> insert into t1(a, b) values(0,'test');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> SET debug_sync = 'alter_table_update_log signal send_concurrent WAIT_FOR concurrent_done';
Query OK, 0 rows affected (0.00 sec)

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

-- session 2

bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.40-debug MySQL Community Server - GPL - Debug

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SET debug_sync = 'now wait_for send_concurrent';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test.t1 SET a = a + 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SET debug_sync = 'now signal concurrent_done';
Query OK, 0 rows affected (0.00 sec)

regards,
Umesh

i see you session optimize table only take 0.11 sec.

However, I think it needs to be blocked.after SET debug_sync = 'alter_table_update_log signal send_concurrent WAIT_FOR concurrent_done';

You can refer to the file mysql-test/suite/innodb/t/innodb_bug34750489.test

and my mysql version is 8038,which commit is a2528f9eddf2cb1a024e9f459296f95fbdd0ed25

Thank you for the feedback.

regards,
Umesh