[MDEV-26492] Got error 126 / mysqld: Index for table 't' is corrupt (key_cache_segments variation) Created: 2021-08-28  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Storage Engine - MyISAM
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: corruption

Issue Links:
Duplicate
is duplicated by MDEV-27162 "ERROR 126 (HY000): Index for table #... Closed

 Description    

SET sql_mode='';
 
SET GLOBAL key_cache_segments=10;
 
SET GLOBAL key_buffer_size=20000;
 
CREATE TEMPORARY TABLE t (a INT KEY) ENGINE=MyISAM;
 
INSERT INTO t VALUES (1),(2);
 
SET GLOBAL key_cache_block_size=2048;
 
SELECT * FROM t UNION SELECT * FROM t;

Leads to:

10.7.0 05e29e177df243b700392b797e26cae43fd3181e (Debug)

 
10.7.0-dbg>SELECT * FROM t UNION SELECT * FROM t;
 
ERROR 126 (HY000): Index for table 'riadb-10.7.0-linux-x86_64-dbg/data/#sql-temptable-3c7125-4-0.MYI' is corrupt; try to repair it

Bug confirmed present in:
MariaDB: 10.2.41 (dbg), 10.2.41 (opt), 10.3.32 (dbg), 10.3.32 (opt), 10.4.22 (dbg), 10.4.22 (opt), 10.5.13 (dbg), 10.5.13 (opt), 10.6.5 (dbg), 10.6.5 (opt), 10.7.0 (dbg), 10.7.0 (opt)

Bug (or feature/syntax) confirmed not present in:
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.35 (dbg), 5.7.35 (opt), 8.0.26 (dbg), 8.0.26 (opt)

Note also the chopped off/added path in the error message.

 Comments   
Comment by Roel Van de Paar [ 2021-12-03 ] 

CREATE TEMPORARY TABLE t (a INT PRIMARY KEY) ENGINE=MyISAM;
 
SET GLOBAL key_cache_segments=2;
 
INSERT INTO t VALUES (0);
 
SET GLOBAL key_cache_segments=1;
 
SELECT a FROM t WHERE MATCH (a) AGAINST ('' IN BOOLEAN MODE);

Leads to:

10.8.0 bc57ff7cf79504a198d4752b4748340a4afd886c (Optimized)

 
10.8.0-opt>SELECT a FROM t WHERE MATCH (a) AGAINST ('' IN BOOLEAN MODE);
 
ERROR 126 (HY000): Index for table 'riadb-10.8.0-linux-x86_64-opt/data/#sql-temptable-2d0059-4-0.MYI' is corrupt; try to repair it

10.8.0 bc57ff7cf79504a198d4752b4748340a4afd886c (Optimized)

 
2021-12-03 21:41:59 4 [ERROR] mysqld: Index for table 'riadb-10.8.0-linux-x86_64-opt/data/#sql-temptable-2d0059-4-0.MYI' is corrupt; try to repair it
 
2021-12-03 21:41:59 4 [ERROR] Got error 126 when reading table '/test/MD121121-mariadb-10.8.0-linux-x86_64-opt/data/#sql-temptable-2d0059-4-0'
 
2021-12-03 21:41:59 4 [ERROR] mysqld: Index for table 't' is corrupt; try to repair it

Bug confirmed present in:
MariaDB: 10.2.42 (dbg), 10.2.42 (opt), 10.3.33 (dbg), 10.3.33 (opt), 10.4.23 (dbg), 10.4.23 (opt), 10.5.14 (dbg), 10.5.14 (opt), 10.6.6 (dbg), 10.6.6 (opt), 10.7.2 (dbg), 10.7.2 (opt), 10.8.0 (dbg), 10.8.0 (opt)

Confirmed not present in (the variable key_cache_segments does not exist in MySQL):
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.36 (dbg), 5.7.36 (opt), 8.0.27 (dbg), 8.0.27 (opt)

Comment by Sergei Petrunia [ 2022-08-09 ]

Looks like the issue is in MyISAM, not in the optimizer.

Comment by Roel Van de Paar [ 2023-01-21 ]

Additional testcase, same outcome

SET GLOBAL key_buffer_size=1000000,key_cache_block_size=200000,key_cache_segments=8;
 
CREATE TEMPORARY TABLE t (c INT AUTO_INCREMENT KEY,d INT) ENGINE=MyISAM;
 
INSERT INTO t(d) VALUES (1),(1);
 
SET GLOBAL key_buffer_size=50000;
 
SELECT 1 FROM t;

Comment by Roel Van de Paar [ 2023-02-24 ]

Two additional testcases:

CREATE TABLE t (c INT) ENGINE=MyISAM;
 
INSERT INTO t VALUES (0);
 
SELECT * FROM t INTO OUTFILE 'a';
 
SET GLOBAL key_cache_segments=1;
 
CREATE TEMPORARY TABLE t (a INT,KEY (a)) ENGINE=MyISAM;
 
INSERT INTO t VALUES (NULL);
 
SET GLOBAL key_cache_segments=1;
 
LOAD DATA INFILE 'a' INTO TABLE t;

Crashes the server with assertion !is_set() || (m_status == DA_OK_BULK && is_bulk_op()), apparently during the "Stage: 2 of 2 'End bulk insert' 0% of stage done" stage of the bulk insert. Happens on all versions (debug only), tested 10.3+.

11.0.1 f2dc4d4c10ac36a73b5c1eb765352d3aee808d66 (Debug)

 
mariadbd: /test/11.0_dbg/sql/sql_error.cc:335: void Diagnostics_area::set_ok_status(ulonglong, ulonglong, const char*): Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed.

11.0.1 f2dc4d4c10ac36a73b5c1eb765352d3aee808d66 (Debug)

 
Core was generated by `/test/MD180223-mariadb-11.0.1-linux-x86_64-dbg/bin/mariadbd --no-defaults --cor'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __pthread_kill_implementation (no_tid=0, signo=6, threadid=22491032254016)
 
    at ./nptl/pthread_kill.c:44
 
[Current thread is 1 (Thread 0x147499fcf640 (LWP 1265917))]
 
(gdb) bt
 
#0  __pthread_kill_implementation (no_tid=0, signo=6, threadid=22491032254016) at ./nptl/pthread_kill.c:44
 
#1  __pthread_kill_internal (signo=6, threadid=22491032254016) at ./nptl/pthread_kill.c:78
 
#2  __GI___pthread_kill (threadid=22491032254016, signo=signo@entry=6) at ./nptl/pthread_kill.c:89
 
#3  0x00001474d3cbf476 in __GI_raise (sig=sig@entry=6) at ../sysdeps/posix/raise.c:26
 
#4  0x00001474d3ca57f3 in __GI_abort () at ./stdlib/abort.c:79
 
#5  0x00001474d3ca571b in __assert_fail_base (fmt=0x1474d3e5a150 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55c7620ccd78 "!is_set() || (m_status == DA_OK_BULK && is_bulk_op())", file=0x55c7620ccc58 "/test/11.0_dbg/sql/sql_error.cc", line=335, function=<optimized out>) at ./assert/assert.c:92
 
#6  0x00001474d3cb6e96 in __GI___assert_fail (assertion=0x55c7620ccd78 "!is_set() || (m_status == DA_OK_BULK && is_bulk_op())", file=0x55c7620ccc58 "/test/11.0_dbg/sql/sql_error.cc", line=335, function=0x55c7620ccdb0 "void Diagnostics_area::set_ok_status(ulonglong, ulonglong, const char*)") at ./assert/assert.c:101
 
#7  0x000055c76157e577 in Diagnostics_area::set_ok_status (this=0x1473d0006b60, affected_rows=1, last_insert_id=last_insert_id@entry=0, message=message@entry=0x147499fcd8e0 "Records: 1  Deleted: 0  Skipped: 0  Warnings: 1") at /test/11.0_dbg/sql/sql_error.cc:335
 
#8  0x000055c7615baee4 in my_ok (message=0x147499fcd8e0 "Records: 1  Deleted: 0  Skipped: 0  Warnings: 1", id=0, affected_rows_arg=<optimized out>, thd=0x1473d0000d58) at /test/11.0_dbg/sql/sql_class.h:5667
 
#9  mysql_load (thd=thd@entry=0x1473d0000d58, ex=0x1473d00131f8, table_list=table_list@entry=0x1473d0013280, fields_vars=@0x1473d0005ec0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x1473d0013b10, last = 0x1473d0013b10, elements = 1}, <No data fields>}, set_fields=@0x1473d0005ef0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55c762aa7440 <end_of_list>, last = 0x1473d0005ef0, elements = 0}, <No data fields>}, set_values=@0x1473d0005ed8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55c762aa7440 <end_of_list>, last = 0x1473d0005ed8, elements = 0}, <No data fields>}, handle_duplicates=DUP_ERROR, ignore=<optimized out>, read_file_from_client=false) at /test/11.0_dbg/sql/sql_load.cc:827
 
#10 0x000055c7615cb345 in mysql_execute_command (thd=thd@entry=0x1473d0000d58, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/11.0_dbg/sql/sql_parse.cc:5027
 
#11 0x000055c7615cf7cf in mysql_parse (thd=thd@entry=0x1473d0000d58, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x147499fce2c0) at /test/11.0_dbg/sql/sql_parse.cc:8002
 
#12 0x000055c7615d1963 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x1473d0000d58, packet=packet@entry=0x1473d000ae19 "", packet_length=packet_length@entry=33, blocking=blocking@entry=true) at /test/11.0_dbg/sql/sql_class.h:242
 
#13 0x000055c7615d37bc in do_command (thd=0x1473d0000d58, blocking=blocking@entry=true) at /test/11.0_dbg/sql/sql_parse.cc:1407
 
#14 0x000055c7617246e2 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55c7648e1fd8, put_in_cache=put_in_cache@entry=true) at /test/11.0_dbg/sql/sql_connect.cc:1416
 
#15 0x000055c761724941 in handle_one_connection (arg=0x55c7648e1fd8) at /test/11.0_dbg/sql/sql_connect.cc:1318
 
#16 0x00001474d3d11b43 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#17 0x00001474d3da3a00 in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

And the related:

CREATE TABLE t (c INT) ENGINE=MyISAM;
 
INSERT INTO t VALUES (0);
 
SET GLOBAL key_cache_segments=1;
 
CREATE TEMPORARY TABLE t (a INT,KEY (a)) ENGINE=MyISAM;
 
INSERT INTO t VALUES (NULL);
 
SET GLOBAL key_cache_segments=1;
 
INSERT INTO t VALUES (0);

Produces the original ERROR 126 index corruption.

Comment by Roel Van de Paar [ 2023-03-25 ]

Please also test any patch with the following testcases:

# mysqld options required for replay:  --sql_mode=
 
SET SESSION enforce_storage_engine=MyISAM;
 
SET GLOBAL key_cache_segments=1;
 
CREATE TEMPORARY TABLE t (c TIME KEY,c2 TEXT BINARY CHARACTER SET 'BINARY' COLLATE 'BINARY',c3 CHAR(2) CHARACTER SET 'BINARY' COLLATE 'BINARY');
 
SET sql_select_limit=2;
 
INSERT INTO t (c) VALUES (7),(8),(9);
 
SET GLOBAL key_cache_segments=1;
 
SELECT * FROM t ORDER BY c;


SET GLOBAL key_cache_segments=2;
 
CREATE TEMPORARY TABLE t (a INT KEY) ENGINE=MyISAM;
 
INSERT INTO t VALUES (1);
 
INSERT INTO t VALUES (1+1);
 
SET GLOBAL key_cache_segments=0;
 
SELECT * FROM t;


SET @start_global_value=@@GLOBAL.sync_master_info;
 
SET GLOBAL key_cache_segments=@start_global_value;
 
CREATE TEMPORARY TABLE t2 (c BINARY,c2 DECIMAL UNSIGNED,c3 REAL(1,0) UNSIGNED ZEROFILL,KEY(c)) ENGINE=MyISAM;
 
INSERT INTO t2 (c) VALUES (8),(9);
 
SET GLOBAL key_cache_segments=2;
 
SELECT 1 FROM t2;

Generated at Thu Feb 08 09:45:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.