MySQL Bugs: #118818: Assertion failure row0pread.h:306:active >= n_threads happens when create thread fail for parallel scan

Bug #118818	Assertion failure row0pread.h:306:active >= n_threads happens when create thread fail for parallel scan
Submitted:	13 Aug 3:44	Modified:	18 Aug 12:12
Reporter:	Zeng Zihao	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.41, 8.0.43	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
We use pstress to test the mysql server. we found a bug that when we spawn thread fail for parallel scan, it will force to use the single thread mode to read data. However, it will crash when degradge to the single thread mode like following.

2025-08-12T00:41:25.040183Z 55 [Warning] [MY-013778] [InnoDB] DDL failed to create a thread to load an index, fall back to single thread
2025-08-12T00:41:25.046085Z 28 [Warning] [MY-013526] [InnoDB] Resource not available to create threads for parallel scan. Falling back to single thread mode.
2025-08-12T00:41:25.046158Z 28 [ERROR] [MY-013183] [InnoDB] Assertion failure: row0pread.h:306:active >= n_threads thread 281442769626240
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.
2025-08-12T00:41:25Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=ce69098b37f76ed365a8099eb69c9f2f8f1228e5
Thread pointer: 0xfff7c8000f90
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 = fff8804edc20 thread_stack 0x100000
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x30) [0x1dfbc00]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(print_fatal_signal(int)+0x314) [0xfbb724]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(my_server_abort()+0x90) [0xfbb8a0]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(my_abort()+0x14) [0x1df5fa4]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x26c) [0x1ff5a0c]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(Parallel_reader::spawn(unsigned long)+0x7c) [0x1f750dc]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(Parallel_reader::run(unsigned long)+0x90) [0x1f75190]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(ddl::Parallel_cursor::scan(std::vector<ddl::Builder*, ut::allocator<ddl::Builder*, ut::detail::allocator_base_pfs<ddl::Builder*> > >&)+0x9d4) [0x2113064]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(ddl::Loader::scan_and_build_indexes()+0xd4) [0x2110304]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(ddl::Loader::build_all()+0xf0) [0x2110440]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(ddl::Context::build()+0x28) [0x2106788]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(bool ha_innobase::inplace_alter_table_impl<dd::Partition>(TABLE*, Alter_inplace_info*)+0x348) [0x1e7a2f8]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(ha_innopart::inplace_alter_table(TABLE*, Alter_inplace_info*, dd::Table const*, dd::Table*)+0xa0) [0x1e87ee0]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld() [0xefc120]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(mysql_alter_table(THD*, char const*, char const*, HA_CREATE_INFO*, Table_ref*, Alter_info*)+0x378c) [0xf0c9dc]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(Sql_cmd_alter_table::execute(THD*)+0x388) [0x12bbf58]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(mysql_execute_command(THD*, bool)+0x19f8) [0xe70d48]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x360) [0xe73410]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1ac4) [0xe753a4]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld(do_command(THD*)+0x1a0) [0xe761a0]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld() [0xfaeb24]
/home/Ruby/percona_cr_arm_80_innodb/sql/bin/mysqld() [0x26464f0]
/usr/lib64/libpthread.so.0(+0x87b0) [0xffffaf4967b0]
/usr/lib64/libc.so.6(+0xd556c) [0xffffad20456c]

How to repeat:
As it is hard to make a thread creation fail, we use inject the error by using DEBUG_EXECUTE_IF.

[zzh@mysql-develop-0001 mysql-server]$ git diff storage/innobase/ddl/ddl0par-scan.cc
diff --git a/storage/innobase/ddl/ddl0par-scan.cc b/storage/innobase/ddl/ddl0par-scan.cc
index 534a322..477c5f4 100644
--- a/storage/innobase/ddl/ddl0par-scan.cc
+++ b/storage/innobase/ddl/ddl0par-scan.cc
@@ -371,6 +371,7 @@ dberr_t Parallel_cursor::scan(Builders &builders) noexcept {
   if (err == DB_SUCCESS) {
     err = reader.run(n_threads);
 
+    DBUG_EXECUTE_IF("simulate_no_resources", err = DB_OUT_OF_RESOURCES;);
     if (err == DB_OUT_OF_RESOURCES) {
       ut_a(!m_single_threaded_mode);

After we compile the code, and use the following sql can reproduce.

create database test;
use test;
create table t1 (c1 int, c2 int, primary key(c1));
insert into t1 values (1,2);
insert into t1 values (2,2);
insert into t1 values (3,2);
insert into t1 values (4,3);
set debug = "+d, simulate_no_resources";

Alter table t1 add index idx_1(c2);

It will crash when adding index.

Suggested fix:
The following code in Parallel_cursor::sacn release the threads double time  when DB_OUT_OF_RESOURCES occurs.  

    if (err == DB_OUT_OF_RESOURCES) {
      ut_a(!m_single_threaded_mode);

      ib::warn(ER_INNODB_OUT_OF_RESOURCES)
          << "Resource not available to create threads for parallel scan."
          << " Falling back to single thread mode.";

      reader.release_threads(n_threads);  ------ first time to release threads

      /* No need to for the extra thread states, release them. */
      for (auto builder : builders) {
        builder->fallback_to_single_thread();
      }

      err = reader.run(0);  ------second time to release threads
    }

The first time is reader.release_threads(n_threads);

The second time is within reads.run(0), in the following stack

Parallel_reader::run(0)
|-->  Parallel_reader::spawn(0);
      |--> Parallel_reader::release_unused_threads(max_threads() - 0);
           |--> Parallel_reader::release_threads(max_threads() - 0);

  static void release_threads(size_t n_threads) {
    const auto SEQ_CST = std::memory_order_seq_cst;
    auto active = s_active_threads.fetch_sub(n_threads, SEQ_CST);
    ut_a(active >= n_threads);
  }

After the first time release, the s_active_threads is 0; so active = 0。 n_thread is the max_thread() which must bigger than 0. Therefore the assertion failure.

Hello Zeng Zihao,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

-- 8.0.43

scl enable gcc-toolset-14 bash
MYSQL_VERSION="118818"
TARGET=/bv/ushastry/Work/Source/$MYSQL_VERSION
rm -rf /bv/ushastry/Work/Source/$MYSQL_VERSION
rm -rf bld/
mkdir bld && cd bld
rm -rf CMakeCache.txt

cmake .. -DWITH_BOOST=../boost/ -DBUILD_CONFIG=mysql_release -DWITH_DEBUG=1 -DWITH_SSL=system -DWITH_UNIT_TESTS=0  -DCMAKE_INSTALL_PREFIX=$TARGET -G Ninja
ninja
ninja install

[umshastr@bugs:/bv/ushastry/Work/Source/118818]$ cd mysql-test/
[umshastr@bugs:/bv/ushastry/Work/Source/118818/mysql-test]$ vi t/bug118818.test
[umshastr@bugs:/bv/ushastry/Work/Source/118818/mysql-test]$ ./mtr --nocheck-testcases bug118818 --debug-server
Logging: ./mtr  --nocheck-testcases bug118818 --debug-server
MySQL Version 8.0.43
Checking supported features
 - Binaries are debug compiled
Using 'all' suites
Collecting tests
Removing old var directory
Creating var directory '/bv/ushastry/Work/Source/118818/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
create table t1 (c1 int, c2 int, primary key(c1));
insert into t1 values (1,2);
insert into t1 values (2,2);
insert into t1 values (3,2);
insert into t1 values (4,3);
set debug = "+d, simulate_no_resources";
Alter table t1 add index idx_1(c2);
[ 50%] main.bug118818                            [ fail ]
        Test ended at 2025-08-18 14:09:14

CURRENT_TEST: main.bug118818
mysqltest: At line 8: Query 'Alter table t1 add index idx_1(c2)' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

-- 

#0  0x00007ff1cfe8bf1c in __pthread_kill_implementation () from /lib64/libc.so.6
#1  0x0000000004b37f99 in my_write_core (sig=6) at /bv/ushastry/Work/Source/mysql-8.0.43/mysys/stacktrace.cc:344
#2  0x00000000036d2e8b in handle_fatal_signal (sig=6) at /bv/ushastry/Work/Source/mysql-8.0.43/sql/signal_handler.cc:232
#3  <signal handler called>
#4  0x00007ff1cfe8bf1c in __pthread_kill_implementation () from /lib64/libc.so.6
#5  0x00007ff1cfe3eb46 in raise () from /lib64/libc.so.6
#6  0x00007ff1cfe28833 in abort () from /lib64/libc.so.6
#7  0x00000000036d3014 in my_server_abort () at /bv/ushastry/Work/Source/mysql-8.0.43/sql/signal_handler.cc:288
#8  0x0000000004b2e849 in my_abort () at /bv/ushastry/Work/Source/mysql-8.0.43/mysys/my_init.cc:264
#9  0x0000000004f4ef9e in ut_dbg_assertion_failed (expr=0x6e9addb "active >= n_threads", file=0x6e9ad90 "/bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/include/row0pread.h", line=306) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/ut/ut0dbg.cc:100
#10 0x0000000004bdc52d in Parallel_reader::release_threads (n_threads=4) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/include/row0pread.h:306
#11 0x0000000004e460fa in Parallel_reader::release_unused_threads (this=0x7ff1c03e0a70, unused_threads=4) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/include/row0pread.h:384
#12 0x0000000004e437d0 in Parallel_reader::spawn (this=0x7ff1c03e0a70, n_threads=0) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/row/row0pread.cc:1504
#13 0x0000000004e4386f in Parallel_reader::run (this=0x7ff1c03e0a70, n_threads=0) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/row/row0pread.cc:1522
#14 0x00000000051787b2 in ddl::Parallel_cursor::scan (this=0x7ff1140940f0, builders=std::vector of length 1, capacity 1 = {...}) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/ddl/ddl0par-scan.cc:393
#15 0x000000000516b822 in ddl::Loader::scan_and_build_indexes (this=0x7ff1c03e1510) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/ddl/ddl0loader.cc:449
#16 0x000000000516b949 in ddl::Loader::build_all (this=0x7ff1c03e1510) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/ddl/ddl0loader.cc:481
#17 0x0000000005153266 in ddl::Context::build (this=0x7ff1c03e15a0) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/ddl/ddl0ctx.cc:519
#18 0x0000000004c45129 in ha_innobase::inplace_alter_table_impl<dd::Table> (this=0x7ff114124ab0, altered_table=0x7ff114a39bf0, ha_alter_info=0x7ff1c03e2300) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/handler/handler0alter.cc:6353
#19 0x0000000004c1c816 in ha_innobase::inplace_alter_table (this=0x7ff114124ab0, altered_table=0x7ff114a39bf0, ha_alter_info=0x7ff1c03e2300, old_dd_tab=0x7ff11408e788, new_dd_tab=0x7ff11400f4a8) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/innobase/handler/handler0alter.cc:1565
#20 0x000000000359ba6d in handler::ha_inplace_alter_table (this=0x7ff114124ab0, altered_table=0x7ff114a39bf0, ha_alter_info=0x7ff1c03e2300, old_table_def=0x7ff11408e788, new_table_def=0x7ff11400f4a8) at /bv/ushastry/Work/Source/mysql-8.0.43/sql/handler.h:6179
#21 0x000000000357da5b in mysql_inplace_alter_table (thd=0x7ff114001040, schema=..., new_schema=..., table_def=0x7ff11408e788, altered_table_def=0x7ff11400f4a8, table_list=0x7ff11400b8e0, table=0x7ff1140ac8a0, altered_table=0x7ff114a39bf0, ha_alter_info=0x7ff1c03e2300, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, alter_ctx=0x7ff1c03e3210, columns=std::set with 0 elements, fk_key_info=0x7ff114a40e18, fk_key_count=0, fk_invalidator=0x7ff1c03e3140) at /bv/ushastry/Work/Source/mysql-8.0.43/sql/sql_table.cc:13567
#22 0x0000000003589e84 in mysql_alter_table (thd=0x7ff114001040, new_db=0x7ff11400bf30 "test", new_name=0x0, create_info=0x7ff1c03e5080, table_list=0x7ff11400b8e0, alter_info=0x7ff1c03e4f10) at /bv/ushastry/Work/Source/mysql-8.0.43/sql/sql_table.cc:17537
#23 0x0000000003bd4635 in Sql_cmd_alter_table::execute (this=0x7ff11400c0b8, thd=0x7ff114001040) at /bv/ushastry/Work/Source/mysql-8.0.43/sql/sql_alter.cc:350
#24 0x000000000349f5c2 in mysql_execute_command (thd=0x7ff114001040, first_level=true) at /bv/ushastry/Work/Source/mysql-8.0.43/sql/sql_parse.cc:4724
#25 0x00000000034a199e in dispatch_sql_command (thd=0x7ff114001040, parser_state=0x7ff1c03e6910) at /bv/ushastry/Work/Source/mysql-8.0.43/sql/sql_parse.cc:5385
#26 0x0000000003497573 in dispatch_command (thd=0x7ff114001040, com_data=0x7ff1c03e7a00, command=COM_QUERY) at /bv/ushastry/Work/Source/mysql-8.0.43/sql/sql_parse.cc:2055
#27 0x00000000034954ba in do_command (thd=0x7ff114001040) at /bv/ushastry/Work/Source/mysql-8.0.43/sql/sql_parse.cc:1440
#28 0x00000000036bdb04 in handle_connection (arg=0x2b22de70) at /bv/ushastry/Work/Source/mysql-8.0.43/sql/conn_handler/connection_handler_per_thread.cc:303
#29 0x00000000053be6bd in pfs_spawn_thread (arg=0x2b261b70) at /bv/ushastry/Work/Source/mysql-8.0.43/storage/perfschema/pfs.cc:3050
#30 0x00007ff1cfe8a1aa in start_thread () from /lib64/libc.so.6
#31 0x00007ff1cff0f2c0 in clone3 () from /lib64/libc.so.6

-- excerpts from error log

2025-08-18T12:09:13.397292Z 0 [System] [MY-010931] [Server] /bv/ushastry/Work/Source/118818/bin/mysqld-debug: ready for connections. Version: '8.0.43-debug'  socket: '/bv/ushastry/Work/Source/118818/mysql-test/var/tmp/mysqld.1.sock'  port: 13000  Source distribution.
2025-08-18T12:09:14.024593Z 8 [Warning] [MY-013526] [InnoDB] Resource not available to create threads for parallel scan. Falling back to single thread mode.
2025-08-18T12:09:14.024786Z 8 [ERROR] [MY-013183] [InnoDB] Assertion failure: row0pread.h:306:active >= n_threads thread 140676289168960
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.
2025-08-18T12:09:14Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=cf04a9ac20bf7e331143a87593fee6687a125572
Thread pointer: 0x7ff114001040
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 = 7ff1c03e7a30 thread_stack 0x100000