MySQL Bugs: #27029: alter table ... enable keys crashes mysqld on large table

Bug #27029	alter table ... enable keys crashes mysqld on large table
Submitted:	11 Mar 2007 8:07	Modified:	23 Jun 2007 8:35
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	<=5.0.38	OS:	Windows (windows)
Assigned to:	Iggy Galarza	CPU Architecture:	Any
Tags:	ALTER TABLE, crash, myisam

Description:
this bulk loading procedure leads to a crash;

1) create table .. engine=myisam;
2) alter table .. disable keys;
3) insert a few billion records into the table
4) alter table .. enable keys; <CRASH HERE>

Stack trace had the following functions on the stack:

_mi_dpointer()
_mi_make_key()
sort_key_read()
find_all_keys()
_create_index_by_sort()
mi_repair_by_sort()
ha_myisam::repair()
ha_myisam::enable_indexes()
mysql_alter_table()
mysql_execute_command()
mysql_parse()
dispatch_command()
do_command()
handle_one_connection()
pthread_start()

See attached files for more detailed debugging information.

How to repeat:
##warning: large testcase. need huge tmpdir and datadir.
drop table if exists `t1`;
create table `t1` (`c1` mediumint unsigned not null,`c2` mediumint unsigned not null,`c3` bigint unsigned not null, key `c1` (`c1`,`c2`,`c3`)) engine=myisam default charset=utf8;
alter table `t1` disable keys;
insert into t1 values (1,2,3),(4,5,6),(6,7,8),(1,4,7);
insert into t1 select * from t1; #8
insert into t1 select * from t1; #16
insert into t1 select * from t1; #32
insert into t1 select * from t1; #64
insert into t1 select * from t1; #128
insert into t1 select * from t1; #256
insert into t1 select * from t1; #512
insert into t1 select * from t1; #1024
insert into t1 select * from t1; #2048
insert into t1 select * from t1; #4096
insert into t1 select * from t1; #8172
insert into t1 select * from t1; #16384
insert into t1 select * from t1; #32768
insert into t1 select * from t1; #65536
insert into t1 select * from t1; #131072
insert into t1 select * from t1; #262144
insert into t1 select * from t1; #524288
insert into t1 select * from t1; #1048576
insert into t1 select * from t1; #2097152
insert into t1 select * from t1; #4194304
insert into t1 select * from t1; #8388608
insert into t1 select * from t1; #16777216
insert into t1 select * from t1; #33554432
insert into t1 select * from t1; #67108864
insert into t1 select * from t1; #134217728
insert into t1 select * from t1; #268435456
insert into t1 select * from t1; #536870912
insert into t1 select * from t1; #1073741824
insert into t1 select * from t1; #2147483648
insert into t1 select * from t1 limit 2147483647; #4294967295
alter table t1 enable keys; #crash

Suggested fix:
.

some outputs from debugger, 5.0.38

Attachment: bug_debug_info.txt (text/plain), 10.10 KiB.

debug trace output from -#F:L:t:n:i:d:O,keys.log

Attachment: debug_trace_keys.log (application/octet-stream, text), 208.04 KiB.

the table files. unrar, then unrar again.

Attachment: t1_table_64GB_rar.rar (application/octet-stream, text), 44.81 KiB.

I can't seem to repeat this on linux.  fyi, i have 64-bit windows XP and both 32-bit and 64-bit mysqld-nt and mysqld-debug binaries crashed instantly after running the ALTER TABLE t1 ENABLE KEYS.

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/29317

ChangeSet@1.2485, 2007-06-21 12:45:56-04:00, iggy@amd64.(none) +4 -0
  Bug#27029 alter table ... enable keys crashes mysqld on large table
  - When creating an index for the sort, the number of rows plus 1 is used 
  to allocate a buffer.  In this test case, the number of rows 4294967295 
  is the max value of an unsigned integer, so when 1 was added to it, a 
  buffer of size 0 was allocated causing the crash.
  - Create new test suite for this bug's test suite as per QA.

Pushed into 5.1.20-beta

Pushed into 5.0.46

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.46 and 5.1.20 changelogs.

Hi, i've installed 5.1.20 on my Gentoo Linux Box running Kernel 2.6.16.

MySQL is still die when i run the statement:
/*!40000 ALTER TABLE `KUNDE` DISABLE KEYS */
....
/*!40000 ALTER TABLE `...` ENABLE KEYS */  <<--- CRASH

The Query is a result of an mysqldump result with enabled option --disable-keys

Now, when i'm import the mysqldump-result, mysql is restarting.

---- MYSQL-LOG-FILE: ----
070730  0:38:55 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=268435456
read_buffer_size=16773120
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5210871 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 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...
frame pointer is NULL, did you compile with
-fomit-frame-pointer? Aborting backtrace!
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
070730 00:38:55 mysqld_safe Number of processes running now: 0
070730 00:38:55 mysqld_safe mysqld restarted