MySQL Bugs: #115542: MY-012872 MySQL Issues crash on long running large table update

Bug #115542	MY-012872 MySQL Issues crash on long running large table update
Submitted:	8 Jul 2024 11:24	Modified:	8 Jul 2024 11:32
Reporter:	Rob de Wit	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	8.0.34-26	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:

Updating a large table with many records with following query yields error instead of updating the table with the new schema:

Query:

ALTER TABLE table1 DROP PRIMARY KEY, ADD COLUMN field1 TINYINT(1) NOT NULL DEFAULT 0, ADD INDEX (field1), ADD PRIMARY KEY (id, field2)

Error:

2024-07-04T17:08:55.931444Z 0 [ERROR] [MY-012872] [InnoDB] [FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2024-07-04T17:08:55.968478Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: srv0srv.cc:2076:ib::fatal triggered thread 139522478147328
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-07-04T17:08:55Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=94c0547a58d7f7768fa7bb5fbc1550776d428ac8
Server Version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85

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
/usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x219ba11]
/usr/local/mysql/bin/mysqld(print_fatal_signal(int)+0x3e3) [0x13aad13]
/usr/local/mysql/bin/mysqld(my_server_abort()+0x76) [0x13aaeb6]
/usr/local/mysql/bin/mysqld(my_abort()+0xe) [0x219575e]
/usr/local/mysql/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x32a) [0x240650a]
/usr/local/mysql/bin/mysqld(ib::fatal::~fatal()+0xde) [0x24091de]
/usr/local/mysql/bin/mysqld(srv_error_monitor_thread()+0x86f) [0x23967cf]
/usr/local/mysql/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)()> > >::_M_run()+0xc4) [0x22a99f4]
/usr/lib64/libstdc++.so.6(+0xc3b1f) [0x7f0ca6ebfb1f]
/lib64/libpthread.so.0(+0x819c) [0x7f0ca7e3319c]
/lib64/libc.so.6(clone+0x3f) [0x7f0ca6bc64cf]
Please help us make Percona Server better by reporting any
bugs at https://bugs.percona.com/

You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.

Table info:

  TABLE_CATALOG: def
   TABLE_SCHEMA: db1
     TABLE_NAME: table1
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 25341164
 AVG_ROW_LENGTH: 73
    DATA_LENGTH: 1868562432
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 4194304
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2023-11-09 14:02:47
    UPDATE_TIME: 2024-07-08 11:30:05
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb3_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 

How to repeat:
This was in production and a workaround was immediately deployed, changing the state of the database. We have not retried since.

Suggested fix:
Maybe wait a bit longer for the semaphore?