Bug #109868 Assertion failure: btr0cur.cc:4024:page_zip || optim_err != DB_UNDERFLOW thread
Submitted: 1 Feb 2023 4:20 Modified: 1 Feb 2023 10:33
Reporter: shengchun cao Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.0.30 OS:Any
Assigned to: CPU Architecture:Any

[1 Feb 2023 4:20] shengchun cao
Description:
We created a table in MySQL 8.0.27, and added some columns(use instant algorithm default),and then, MySQL was updated to 8.0.30.
MySQL 8.0.30 crashed when rollback a transaction which contained an update SQL.

Crash info and stack:
2023-02-01T03:25:47.357710Z 10 [ERROR] [MY-013183] [InnoDB] Assertion failure: btr0cur.cc:4024:page_zip || optim_err != DB_UNDERFLOW thread 0x70000a1ba000
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.
03:25:47 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7fbb2a902e00
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 = 70000a1b9f50 thread_stack 0x100000
0   mysqld                              0x000000010a12942c my_print_stacktrace(unsigned char const*, unsigned long) + 60
1   mysqld                              0x00000001095ef2e8 print_fatal_signal(int) + 408
2   mysqld                              0x00000001095ef4c6 my_server_abort() + 70
3   mysqld                              0x000000010a12177a my_abort() + 10
4   mysqld                              0x000000010a5a5b2b ut_dbg_assertion_failed(char const*, char const*, unsigned long long) + 395
5   mysqld                              0x000000010a247d7f 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 long, unsigned long long, mtr_t*, btr_pcur_t*) + 3279
6   mysqld                              0x000000010a5330f2 row_undo_mod_clust_low(undo_node_t*, unsigned long**, mem_block_info_t**, mem_block_info_t*, dtuple_t const**, unsigned char*, que_thr_t*, mtr_t*, unsigned long) + 242
7   mysqld                              0x000000010a531a28 row_undo_mod(undo_node_t*, que_thr_t*) + 6440
8   mysqld                              0x000000010a53422c row_undo_step(que_thr_t*) + 860
9   mysqld                              0x000000010a4ddf9c que_run_threads(que_thr_t*) + 476
10  mysqld                              0x000000010a58add8 trx_rollback_to_savepoint_low(trx_t*, trx_savept_t*) + 328
11  mysqld                              0x000000010a58b2b3 trx_rollback_low(trx_t*) + 563
12  mysqld                              0x000000010a58b047 trx_rollback_for_mysql(trx_t*) + 87
13  mysqld                              0x000000010a3ba3e3 innobase_rollback(handlerton*, THD*, bool) + 147
14  mysqld                              0x0000000109066338 ha_rollback_low(THD*, bool) + 344
15  mysqld                              0x0000000109580f43 trx_coordinator::rollback_in_engines(THD*, bool) + 35
16  mysqld                              0x0000000109e0e47d MYSQL_BIN_LOG::rollback(THD*, bool) + 157
17  mysqld                              0x0000000109065da5 ha_rollback_trans(THD*, bool) + 133
18  mysqld                              0x00000001095851d2 trans_rollback(THD*) + 114
19  mysqld                              0x000000010944bf1a mysql_execute_command(THD*, bool) + 9610
20  mysqld                              0x00000001094484e2 dispatch_sql_command(THD*, Parser_state*) + 1106
21  mysqld                              0x0000000109446d6b dispatch_command(THD*, COM_DATA const*, enum_server_command) + 7403
22  mysqld                              0x0000000109447a0d do_command(THD*) + 525
23  mysqld                              0x00000001095d5264 handle_connection(void*) + 436
24  mysqld                              0x000000010a6258df pfs_spawn_thread(void*) + 335
25  libsystem_pthread.dylib             0x00007ff80572d4e1 _pthread_start + 125
26  libsystem_pthread.dylib             0x00007ff805728f6b thread_start + 15

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fbaec814630): ROLLBACK
Connection ID (thread ID): 10
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.

How to repeat:
-- step 1: create a table in MySQL 8.0.27.
CREATE TABLE `table_1` (
  `id` varchar(50) NOT NULL,
  `field_1` varchar(100) DEFAULT NULL,
  `field_2` varchar(50) DEFAULT NULL,
  `field_3` varchar(300) DEFAULT NULL,
  `field_4` varchar(300) DEFAULT NULL,
  `field_5` varchar(50) DEFAULT NULL,
  `field_6` varchar(50) DEFAULT NULL,
  `field_7` varchar(50) DEFAULT NULL,
  `field_8` text,
  `field_9` varchar(10) DEFAULT NULL,
  `field_10` varchar(50) DEFAULT NULL,
  `field_11` varchar(100) DEFAULT NULL,
  `field_12` varchar(100) DEFAULT NULL,
  `field_13` varchar(150) DEFAULT NULL,
  `field_14` decimal(20,5) DEFAULT NULL,
  `field_15` varchar(30) DEFAULT NULL,
  `field_16` decimal(20,5) DEFAULT NULL,
  `field_17` varchar(10) DEFAULT NULL,
  `field_18` varchar(50) DEFAULT NULL,
  `field_19` varchar(10) DEFAULT NULL,
  `field_20` varchar(50) DEFAULT NULL,
  `field_21` varchar(20) DEFAULT NULL,
  `field_22` varchar(50) DEFAULT NULL,
  `field_23` varchar(50) DEFAULT NULL,
  `field_24` varchar(100) DEFAULT NULL,
  `field_25` varchar(50) DEFAULT NULL,
  `field_26` varchar(100) DEFAULT NULL,
  `field_27` varchar(50) DEFAULT NULL,
  `field_28` varchar(150) DEFAULT NULL,
  `field_29` text,
  `field_30` varchar(10) DEFAULT NULL,
  `field_31` varchar(100) DEFAULT NULL,
  `field_32` varchar(50) DEFAULT NULL,
  `field_33` varchar(200) DEFAULT NULL,
  `field_34` decimal(20,5) DEFAULT NULL,
  `field_35` decimal(20,5) DEFAULT NULL,
  `field_36` varchar(50) DEFAULT NULL,
  `field_37` varchar(50) DEFAULT NULL,
  `field_38` datetime DEFAULT NULL,
  `field_39` datetime DEFAULT NULL,
  `field_40` varchar(50) DEFAULT NULL,
  `field_41` varchar(16) DEFAULT NULL,
  `field_42` varchar(20) DEFAULT NULL,
  `field_43` varchar(100) DEFAULT NULL,
  `field_44` varchar(50) DEFAULT NULL,
  `field_45` varchar(100) DEFAULT NULL,
  `field_46` varchar(50) DEFAULT NULL,
  `field_47` varchar(100) DEFAULT NULL,
  `field_48` varchar(50) DEFAULT NULL,
  `field_49` varchar(100) DEFAULT NULL,
  `field_50` varchar(50) DEFAULT NULL,
  `field_51` varchar(100) DEFAULT NULL,
  `field_52` varchar(50) DEFAULT NULL,
  `field_53` varchar(150) DEFAULT NULL,
  `field_54` varchar(100) DEFAULT NULL,
  `field_55` decimal(20,5) DEFAULT NULL,
  `field_56` decimal(20,5) DEFAULT NULL,
  `field_57` decimal(20,5) DEFAULT NULL,
  `field_58` decimal(20,5) DEFAULT NULL,
  `field_59` decimal(20,5) DEFAULT NULL,
  `field_60` decimal(20,5) DEFAULT NULL,
  `field_61` decimal(20,5) DEFAULT NULL,
  `field_62` decimal(20,5) DEFAULT NULL,
  `field_63` decimal(20,5) DEFAULT NULL,
  `field_64` decimal(20,5) DEFAULT NULL,
  `field_65` varchar(50) DEFAULT NULL,
  `field_66` text,
  `field_67` varchar(100) DEFAULT NULL,
  `field_68` datetime DEFAULT NULL,
  `field_69` datetime DEFAULT NULL,
  `field_70` decimal(20,5) DEFAULT NULL,
  `field_71` datetime DEFAULT NULL,
  `field_72` datetime DEFAULT NULL,
  `field_73` varchar(100) DEFAULT NULL,
  `field_74` varchar(20) DEFAULT NULL,
  `field_75` varchar(50) DEFAULT NULL,
  `field_76` varchar(50) DEFAULT NULL,
  `field_77` varchar(50) DEFAULT NULL,
  `field_78` varchar(50) DEFAULT NULL,
  `field_79` varchar(50) DEFAULT NULL,
  `field_80` varchar(50) DEFAULT NULL,
  `field_81` varchar(50) DEFAULT NULL,
  `field_82` varchar(100) DEFAULT NULL,
  `field_83` varchar(100) DEFAULT NULL,
  `field_84` varchar(100) DEFAULT NULL,
  `field_85` varchar(100) DEFAULT NULL,
  `field_86` varchar(100) DEFAULT NULL,
  `field_87` varchar(100) DEFAULT NULL,
  `field_88` varchar(100) DEFAULT NULL,
  `field_89` varchar(100) DEFAULT NULL,
  `field_90` varchar(100) DEFAULT NULL,
  `field_91` varchar(100) DEFAULT NULL,
  `field_92` varchar(100) DEFAULT NULL,
  `field_93` varchar(100) DEFAULT NULL,
  `field_94` varchar(100) DEFAULT NULL,
  `field_95` varchar(100) DEFAULT NULL,
  `field_96` varchar(100) DEFAULT NULL,
  `field_97` varchar(100) DEFAULT NULL,
  `field_98` varchar(100) DEFAULT NULL,
  `field_99` varchar(100) DEFAULT NULL,
  `field_100` varchar(100) DEFAULT NULL,
  `field_101` varchar(100) DEFAULT NULL,
  `field_102` varchar(100) DEFAULT NULL,
  `field_103` varchar(100) DEFAULT NULL,
  `field_104` varchar(100) DEFAULT NULL,
  `field_105` varchar(100) DEFAULT NULL,
  `field_106` varchar(100) DEFAULT NULL,
  `field_107` varchar(200) DEFAULT NULL,
  `field_108` varchar(150) DEFAULT NULL,
  `field_109` varchar(100) DEFAULT NULL,
  `field_110` varchar(100) DEFAULT NULL,
  `field_111` varchar(100) DEFAULT NULL,
  `field_112` varchar(100) DEFAULT NULL,
  `field_113` varchar(100) DEFAULT NULL,
  `field_114` varchar(100) DEFAULT NULL,
  `field_115` varchar(100) DEFAULT NULL,
  `field_116` varchar(100) DEFAULT NULL,
  `field_117` varchar(100) DEFAULT NULL,
  `field_118` varchar(100) DEFAULT NULL,
  `field_119` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- step 2: add columns(use instant column default) in MySQL 8.0.27
ALTER TABLE table_1
ADD `field_120` varchar(100) DEFAULT NULL,
ADD `field_121` varchar(100) DEFAULT NULL,
ADD `field_122` varchar(100) DEFAULT NULL,
ADD `field_123` varchar(100) DEFAULT NULL,
ADD `field_124` varchar(100) DEFAULT NULL,
ADD `field_125` datetime DEFAULT NULL,
ADD `field_126` datetime DEFAULT NULL,
ADD `field_127` varchar(50) DEFAULT NULL,
ADD `field_128` varchar(400) DEFAULT NULL,
ADD `field_129` varchar(400) DEFAULT NULL,
ADD `field_130` varchar(480) DEFAULT NULL,
ADD `field_131` int DEFAULT NULL,
ADD `field_132` varchar(255) DEFAULT NULL;

-- step 3: insert a record in MySQL 8.0.30, dont't need explicit transction
INSERT INTO table_1(`id`, `field_1`, `field_2`, `field_3`, `field_4`, `field_5`, `field_6`, `field_7`, `field_8`, `field_9`, `field_10`, `field_11`, `field_12`, `field_13`, `field_14`, `field_15`, `field_16`, `field_17`, `field_18`, `field_19`, `field_20`, `field_21`, `field_22`, `field_23`, `field_24`, `field_25`, `field_26`, `field_27`, `field_28`, `field_29`, `field_30`, `field_31`, `field_32`, `field_33`, `field_34`, `field_35`, `field_36`, `field_37`, `field_38`, `field_39`, `field_40`, `field_41`, `field_42`, `field_43`, `field_44`, `field_45`, `field_46`, `field_47`, `field_48`, `field_49`, `field_50`, `field_51`, `field_52`, `field_53`, `field_54`, `field_55`, `field_56`, `field_57`, `field_58`, `field_59`, `field_60`, `field_61`, `field_62`, `field_63`, `field_64`, `field_65`, `field_66`, `field_67`, `field_68`, `field_69`, `field_70`, `field_71`, `field_72`, `field_73`, `field_74`, `field_75`, `field_76`, `field_77`, `field_78`, `field_79`, `field_80`, `field_81`, `field_82`, `field_83`, `field_84`, `field_85`, `field_86`, `field_87`, `field_88`, `field_89`, `field_90`, `field_91`, `field_92`, `field_93`, `field_94`, `field_95`, `field_96`, `field_97`, `field_98`, `field_99`, `field_100`, `field_101`, `field_102`, `field_103`, `field_104`, `field_105`, `field_106`, `field_107`, `field_108`, `field_109`, `field_110`, `field_111`, `field_112`, `field_113`, `field_114`, `field_115`, `field_116`, `field_117`, `field_118`, `field_119`, `field_120`, `field_121`, `field_122`, `field_123`, `field_124`, `field_125`, `field_126`, `field_127`, `field_128`, `field_129`, `field_130`, `field_131`, `field_132`) 
VALUES ('abcd1234-1ab2-11ab-a123-1234567a89b0', '123456A78901234', '123456789', 'abcdefghijabcdefghij1', '123456789123456789abc', 'abcdefghijklmn', NULL, 'ab1cd2ef3gh4ij5lmno', 'abc123def.abc123defabc123def-abc123def456-abc123def456ghi-abc123defabc123def123', '12', 'abc123def', 'abc123defabc123defabc123def456', '123456780123456780123456780123', 'abc123defabc123defabc123def140123456200abcdefWPabc123defabc123def', 8.00000, 'abc', NULL, NULL, 'abcdef', 'abc123def', NULL, '12345678', 'abcdefghi', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '8', 'abc(123456789def)', '123456AB12345678', 'abc123defabc123defabc123defabc123defabc123defabc123def2ABC123abcdef123', 9.00000, 88.00000, NULL, NULL, NULL, NULL, NULL, 'abc', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'A12345678912345', '1234abc123defabc123defabc123defabc123def123', NULL, 12345.14000, 12345.13000, 12345.12000, 1234.28000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, NULL, NULL, NULL, '2023-01-01 00:00:00', NULL, NULL, '2023-01-01 00:00:00', '2023-01-01 00:00:00', NULL, 'abc123456', NULL, NULL, '123456789', NULL, 'AB123456DEC-12', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'abc', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'ABCD_EF_123456', NULL, NULL, NULL, NULL, NULL);

-- step 4: update the record in MySQL 8.0.30, need start a transaction explicit
BEGIN;
UPDATE table_1 SET `field_23`='null', `field_24`='null', `field_21`='12345678', `field_22`='abcdefghi', `field_32`='123456AB12345678', `field_33`='abc123xyzabc123xyzabc123xyzabc123xyzabc123xyzabc123xyzabc123xyz', `field_30`='12', `field_31`='abc' WHERE `id`='abcd1234-1ab2-11ab-a123-1234567a89b0';
ROLLBACK;

Suggested fix:
Code of function rec_init_offsets_comp_ordinary(), in file storage/innobase/rem/rec.h, may be changed like this:
static inline uint16_t rec_init_null_and_len_comp(const rec_t *rec,
                                                  const dict_index_t *index,
                                                  const byte **nulls,
                                                  const byte **lens,
                                                  uint16_t *n_null) {
  uint16_t non_default_fields = // line 799
      static_cast<uint16_t>(dict_index_get_n_fields(index)); // line 800
    ......
    // rec_new_is_versioned(rec) can be change to: is_store_version(index, non_default_fields)
    if (rec_new_is_versioned(rec)) { // line 840
        ......
    }
    ......
}
[1 Feb 2023 6:32] MySQL Verification Team
Hello lin min,

Thank you for the report and test case.
I can confirm using your test case that when upgrading from 8.0.27 to 8.0.30/8.0.31 issue can be observed where as no issues observed when upgraded from 8.0.27 to 8.0.32. 

Please note that we don't fix bugs in old versions, don't back-port bug fixes, so you need to check with latest MySQL Server version(8.0.32). So, please upgrade and inform us if problem still exists along with a test case to reproduce. Thank you.

regards,
Umesh
[1 Feb 2023 6:33] MySQL Verification Team
8.27 to 8.0.30, 8.0.31 and 8.0.32 upgrade results

Attachment: 109868_8.0.27_30_31_32.results (application/octet-stream, text), 84.94 KiB.

[1 Feb 2023 10:33] shengchun cao
Hello, Umesh

Thank you for your verification.
I have checked that the bug was fixed in MySQL 8.0.32.
[1 Feb 2023 11:51] MySQL Verification Team
Thank you for confirming, closing the issue for now.

regards,
Umesh
[7 Feb 2023 11:54] huahua xu
Hi all:

The issue had been fixed by the commit: https://github.com/mysql/mysql-server/commit/7b9830480b75aa1d940635585807bdbc4d2dd2df