MySQL crashes on long indexes

[Keyur]

We run Percona server 5.5.25a, but this bug is from upstream. I opened one on the main MySQL bug tracker (http://bugs.mysql.com/bug.php?id=66413), but it has been marked Private and won't show up in Google.

*Description*:
MySQL 5.5.21 and greater crashes with a SIGABRT when InnoDB tables have index sizes close to the limit of 3072.

The bug seems to have occurred due to the changes in http://bugs.mysql.com/bug.php?id=57480. The buffer to hold the search key in ha_innodb.cc was changed from variable size to a fixed 3074 bytes. This is not enough to hold the index for the table shown below. This table will need 3076 bytes to hold the index ((255*3+2)*4 + 8). An assertion is triggered and MySQLd restarts.

*How to repeat*:
CREATE TABLE `crash_test` (
  `column1` varchar(255) NOT NULL,
  `column2` varchar(255) NOT NULL,
  `column3` varchar(255) NOT NULL,
  `column4` varchar(255) NOT NULL,
  `column5` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`column1`,`column2`,`column3`,`column4`,`column5`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

(Note the index length above does not exceed the InnoDB limit of 3072, because the VARCHAR length bytes are not included in that calculation)

INSERT INTO crash_test (column1, column2, column3, column4, column5) VALUES ("abc", "abc", "abc", "abc", 9);
INSERT INTO crash_test (column1, column2, column3, column4, column5) VALUES ("abc", "abc", "abc", "abc", 10);

SELECT * FROM crash_test WHERE column1 = 'abc' ORDER BY column5 DESC;

And MySQL has now crashed.

*Trimmed error log*:
02:20:51 UTC - mysqld got signal 6 ;
key_buffer_size=402653184
read_buffer_size=1048576
max_used_connections=2
max_threads=1500
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5018313 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x34f23740
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 = 48ed20d0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x76ad1e]
/usr/sbin/mysqld(handle_fatal_signal+0x386)[0x655ba6]
/lib64/libpthread.so.0[0x2b8c70a42be0]
/lib64/libc.so.6(gsignal+0x35)[0x2b8c71c6b285]
/lib64/libc.so.6(abort+0x110)[0x2b8c71c6cd30]
/usr/sbin/mysqld[0x79bb7a]
/usr/sbin/mysqld[0x7812e8]
/usr/sbin/mysqld[0x7828ea]
/usr/sbin/mysqld[0x7165bd]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x5f)[0x58019f]
/usr/sbin/mysqld[0x58ad23]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x11fe)[0x594b0e]
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x1c0)[0x595e10]
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x16f)[0x5966ff]
/usr/sbin/mysqld[0x55ab40]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x3b8c)[0x55ec2c]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0xfe)[0x560f6e]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1357)[0x562347]
/usr/sbin/mysqld(_Z10do_commandP3THD+0xc2)[0x562672]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xf2)[0x5efd62]
/usr/sbin/mysqld(handle_one_connection+0x4a)[0x5efe3a]
/usr/sbin/mysqld(pfs_spawn_thread+0x57)[0x77af47]
/lib64/libpthread.so.0[0x2b8c70a3a77d]
/lib64/libc.so.6(clone+0x6d)[0x2b8c71d0e25d]

*Workarounds*:
Use smaller indexes

[Raghavendra D Prabhu (raghavendra-prabhu)]

Confirmed on PS 5.5.27.

Backtrace with intermediate backtraces:

Breakpoint 1, row_sel_convert_mysql_key_to_innobase (tuple=0x7ffff41c1cc0, buf=0x7fffb400f288 "", buf_len=3074, index=0x7ffff41c18c0, key_ptr=0x7fffc42edb88 "\003", key_len=767, trx=0x7ffff41c30c0)
    at /media/Vone/Percona-Server-5.5.27-rel28.0/storage/innobase/row/row0sel.c:2315
2315 /media/Vone/Percona-Server-5.5.27-rel28.0/storage/innobase/row/row0sel.c: No such file or directory.
#0 row_sel_convert_mysql_key_to_innobase (tuple=0x7ffff41c1cc0, buf=0x7fffb400f288 "", buf_len=3074, index=0x7ffff41c18c0, key_ptr=0x7fffc42edb88 "\003", key_len=767, trx=0x7ffff41c30c0) at /media/Vone/Percona-Server-5.5.27-rel28.0/storage/innobase/row/row0sel.c:2315
#1 0x0000000000850bfa in ha_innobase::records_in_range (this=0x7fffb400eea0, keynr=<optimized out>, min_key=0x7fffc42ebcf0, max_key=0x7fffc42ebd10) at /media/Vone/Percona-Server-5.5.27-rel28.0/storage/innobase/handler/ha_innodb.cc:8584
#2 0x0000000000765726 in check_quick_keys (param=param@entry=0x7fffc42eda30, idx=idx@entry=0, key_tree=key_tree@entry=0x7fffb40233c0, min_key=min_key@entry=0x7fffc42edb88 "\003", min_key_flag=min_key_flag@entry=0, min_keypart=min_keypart@entry=-1,
    max_key=max_key@entry=0x7fffc42eea86 "\003", max_key_flag=max_key_flag@entry=0, max_keypart=max_keypart@entry=-1) at /media/Vone/Percona-Server-5.5.27-rel28.0/sql/opt_range.cc:7807
#3 0x0000000000765963 in check_quick_select (param=param@entry=0x7fffc42eda30, idx=idx@entry=0, tree=0x7fffb40233c0, update_tbl_stats=update_tbl_stats@entry=true) at /media/Vone/Percona-Server-5.5.27-rel28.0/sql/opt_range.cc:7578
#4 0x0000000000765bd5 in get_key_scans_params (param=param@entry=0x7fffc42eda30, tree=tree@entry=0x7fffb4022e78, index_read_must_be_used=index_read_must_be_used@entry=false, update_tbl_stats=update_tbl_stats@entry=true, read_time=read_time@entry=1.8999999999999999,
    estimated_records=estimated_records@entry=0x7fffc42ec008) at /media/Vone/Percona-Server-5.5.27-rel28.0/sql/opt_range.cc:4966
#5 0x000000000076f63d in SQL_SELECT::test_quick_select (this=<optimized out>, thd=0x241d3c0, keys_to_use=..., prev_tables=<optimized out>, limit=<optimized out>, force_quick_range=<optimized out>) at /media/Vone/Percona-Server-5.5.27-rel28.0/sql/opt_range.cc:2322
#6 0x00000000005d06cd in get_quick_record_count (limit=18446744073709551615, keys=0x7fffb4006148, table=0x7fffb400e5c0, select=0x7fffb40064d8, thd=0x241d3c0) at /media/Vone/Percona-Server-5.5.27-rel28.0/sql/sql_select.cc:2641
#7 make_join_statistics (join=join@entry=0x7fffb4020de0, tables_arg=0x7fffb4004e38, conds=0x7fffb4005500, keyuse_array=keyuse_array@entry=0x7fffb4022398) at /media/Vone/Percona-Server-5.5.27-rel28.0/sql/sql_select.cc:3090
#8 0x00000000005d3777 in JOIN::optimize (this=this@entry=0x7fffb4020de0) at /media/Vone/Percona-Server-5.5.27-rel28.0/sql/sql_select.cc:1082
#9 0x00000000005d6b47 in mysql_select (thd=thd@entry=0x241d3c0, rref_pointer_array=rref_pointer_array@entry=0x241f868, tables=0x7fffb4004e38, wild_num=1, fields=..., conds=<optimized out>, og_num=1, order=0x7fffb4005768, group=0x0, having=0x0, proc_param=0x0,
    select_options=select_options@entry=2147748608, resul...

[Keyur (keyurdg)]

Oracle has released the fix for this in 5.5.29:

"InnoDB: If a table was defined with an index key length very close to the upper length limit of 3072, a query against that table could cause a serious error. (Bug #14500557, Bug #66413)"

https://dev.mysql.com/doc/refman/5.5/en/news-5-5-29.html

[Laurynas Biveinis (laurynas-biveinis)]

Keeping it open, will need verification with Percona Server 5.5.29.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-579