MySQL Bugs: #23175: MYISAM crash/repair failed during repair

Bug #23175	MYISAM crash/repair failed during repair
Submitted:	11 Oct 2006 13:28	Modified:	20 Oct 2006 17:41
Reporter:	Andrey Hristov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.0.27-BK, 4.1,5.0,5.1	OS:	Linux (Linux)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
Use the attached statements to reproduce it. Kudos to Sergey Vojtovich for finding this one. The server crashes even with the patch for bug#23074
The test case could be shortened by removing some INSERT INTO and putting few more INSERT INTO SELECT

Here is a backtrace:
a and b in the last stack frame are derefenced and *0x0 is not good.

(gdb) bt
#0  ha_key_cmp (keyseg=0x8d95ff0, a=0x0, b=0x0, key_length=2223, nextflag=4, diff_pos=0x41ffff80) at my_handler.c:141
#1  0x084ce911 in sort_key_cmp (sort_param=0x0, a=0x0, b=0x0) at mi_check.c:3358
#2  0x084f6605 in queue_insert (queue=0x42000020, element=0x8d95160 "") at queues.c:180
#3  0x084d7839 in merge_buffers (info=0x8d96158, keys=11, from_file=0x8d96220, to_file=0x0, sort_keys=0x8d8a0d0, lastbuff=0x8d95160, Fb=0x8d95160, Tb=0x8d95400) at sort.c:896
#4  0x084d7b14 in merge_index (info=0x0, keys=0, sort_keys=0x0, buffpek=0x8d95160, maxbuffer=0, tempfile=0x0) at sort.c:1006
#5  0x084d6846 in thr_write_keys (sort_param=0x8d96158) at sort.c:569
#6  0x084cc5d9 in mi_repair_parallel (param=0x420007b0, info=0x8d88930, name=0x0, rep_quick=0) at mi_check.c:2643
#7  0x082826a4 in ha_myisam::repair (this=0x8d9b5d0, thd=0x8d871d8, param=@0x420007b0, optimize=false) at ha_myisam.cc:684
#8  0x082821b9 in ha_myisam::repair (this=0x8d9b5d0, thd=0x8d871d8, check_opt=0x8d8782c) at ha_myisam.cc:598
#9  0x0827e033 in handler::ha_repair (this=0x8d9b5d0, thd=0x0, check_opt=0x0) at handler.cc:2139
#10 0x082b6317 in mysql_admin_table (thd=0x8d871d8, tables=0x8d90028, check_opt=0x8d8782c, operator_name=0x85c9892 "repair", lock_type=TL_WRITE, open_for_modify=false, no_warnings_for_error=false, extra_open_options=32, prepare_func=0x82b52f0 <prepare_for_repair>, operator_func=The value of variable 'operator_func' is distributed across several
locations, and GDB cannot access its value.

) at sql_table.cc:2335
#11 0x082b6e9a in mysql_repair_table (thd=0x0, tables=0x0, check_opt=0x8d8782c) at sql_table.cc:2544
#12 0x081c90aa in mysql_execute_command (thd=0x8d871d8) at sql_parse.cc:3208
#13 0x081d04ab in mysql_parse (thd=0x8d871d8, inBuf=0x8d93560 "REPAIR TABLE t1", length=15) at sql_parse.cc:5869
#14 0x081c5a18 in dispatch_command (command=COM_QUERY, thd=0x8d871d8, packet=0x8d92d41 "", packet_length=16) at sql_parse.cc:1766
#15 0x081c52f9 in do_command (thd=0x8d871d8) at sql_parse.cc:1550
#16 0x081c447f in handle_one_connection (arg=0x0) at sql_parse.cc:1181
#17 0x4005caa7 in start_thread () from /lib/tls/libpthread.so.0
#18 0x4018dc2e in clone () from /lib/tls/libc.so.6

How to repeat:
drop database if exists db2;
create database db2;
use db2;
CREATE TABLE t1(a CHAR(255), KEY(a));
INSERT INTO t1 VALUES(REPEAT('1', 255));
INSERT INTO t1 VALUES(REPEAT('2', 255));
INSERT INTO t1 VALUES(REPEAT('3', 255));
INSERT INTO t1 VALUES(REPEAT('4', 255));
INSERT INTO t1 VALUES(REPEAT('5', 255));
INSERT INTO t1 VALUES(REPEAT('6', 255));
INSERT INTO t1 VALUES(REPEAT('7', 255));
INSERT INTO t1 VALUES(REPEAT('8', 255));
INSERT INTO t1 VALUES(REPEAT('9', 255));
INSERT INTO t1 VALUES(REPEAT('a', 255));
INSERT INTO t1 VALUES(REPEAT('b', 255));
INSERT INTO t1 VALUES(REPEAT('c', 255));
INSERT INTO t1 VALUES(REPEAT('d', 255));
INSERT INTO t1 VALUES(REPEAT('e', 255));
INSERT INTO t1 VALUES(REPEAT('f', 255));
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
SET myisam_sort_buffer_size=4096;
SET myisam_repair_threads=2;
REPAIR TABLE t1;
SET myisam_repair_threads=@@global.myisam_repair_threads;
SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size;
DROP TABLE t1;

I had not got any crash with 5.0.27-BK-debug:

mysql> REPAIR TABLE t1;
+--------+--------+----------+-------------------------------------------------+

| Table  | Op     | Msg_type | Msg_text                                        |

+--------+--------+----------+-------------------------------------------------+

| db2.t1 | repair | error    | Internal error: Keys are not in order from sort |

| db2.t1 | repair | status   | OK                                              |

+--------+--------+----------+-------------------------------------------------+

2 rows in set (1 min 2.99 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.27-debug |
+--------------+

Please, send uname -a results from the system where MySQL server crashed.
1 row in set (0.00 sec)

afair it was mysql-4.1 on 32-bit i686 FC5. But anyway repair table error for consistent table matter.

Here is my 5.0 run (pretty fresh tree)
mysql> select version();
No connection. Trying to reconnect...
Connection id:    1
Current database: db2

+-------------------------------+
| version()                     |
+-------------------------------+
| 5.0.27-valgrind-max-debug-log |
+-------------------------------+
1 row in set (0.00 sec)

mysql> drop database if exists db2;
ERROR 1010 (HY000): Error dropping database (can't rmdir './db2/', errno: 17)
mysql> create database db2;
ERROR 1007 (HY000): Can't create database 'db2'; database exists
mysql> use db2;
Database changed
mysql> CREATE TABLE t1(a CHAR(255), KEY(a));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('1', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('2', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('3', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('4', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('5', 255));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('6', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('7', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('8', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('9', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('a', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('b', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('c', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('d', 255));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('e', 255));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(REPEAT('f', 255));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 30 rows affected (0.01 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 60 rows affected (0.01 sec)
Records: 60  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 120 rows affected (0.03 sec)
Records: 120  Duplicates: 0  Warnings: 0

mysql> SET myisam_sort_buffer_size=4096;
Query OK, 0 rows affected (0.00 sec)

mysql> SET myisam_repair_threads=2;
Query OK, 0 rows affected (0.00 sec)

mysql> REPAIR TABLE t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> SET myisam_repair_threads=@@global.myisam_repair_threads;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
ERROR:
Can't connect to the server

I was able to make simplier test case which shows, that this affects not only parellel repair, but also regular repair and bulk insert:
CREATE TABLE t1(a CHAR(255), KEY(a));
SET myisam_sort_buffer_size=4096;
INSERT INTO t1 VALUES
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),
('0'),('0'),('0'),('0'),('0'),('0'),('0');
SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size;
DROP TABLE t1;

Hi Sergey,
yes my observation when tracking down the crashing line was that it happens in the first thread during the first merge of buffers. So, actually it's seems quite possible that it can crash even without threads.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13866

ChangeSet@1.2530, 2006-10-18 17:57:29+05:00, svoj@mysql.com +3 -0
  BUG#23175 - MYISAM crash/repair failed during repair
  
  Repair table could crash a server if there is not sufficient
  memory (myisam_sort_buffer_size) to operate. Affects not only
  repair, but also all statements that use create index by sort:
  repair by sort, parallel repair, bulk insert.
  
  Return an error if there is not sufficient memory to store at
  least one key per BUFFPEK.
  
  Also fixed memory leak if thr_find_all_keys returns an error.

Pushed to 5.1.13, 5.0.27, and 4.1.22.

Noted in 4.1.22, 5.0.27, 5.1.13 changelogs.

The 5.0.x fix is in 5.0.30.