MariaDB crashes in write_tail on a select from UTF8 tables with a simple join and order by

[Anton Khalikov]

Hi guys

We've found a query that crashes MariaDB both 5.2.9 and 5.2.10. This query is generated by CMS Joomla with Virtuemart plugin.

Used platform: Debian GNU/Linux Squeeze amd64.

The query:

SELECT SQL_CALC_FOUND_ROWS * FROM `i1rsk_virtuemart_products_ru_ru` as l JOIN `i1rsk_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `i1rsk_virtuemart_product_categories` ON p.`virtuemart_product_id` = `i1rsk_virtuemart_product_categories`.`virtuemart_product_id` LEFT JOIN `i1rsk_virtuemart_categories_ru_ru` as c ON c.`virtuemart_category_id` = `i1rsk_virtuemart_product_categories`.`virtuemart_category_id` WHERE ( p.`published`="1" AND `i1rsk_virtuemart_product_categories`.`virtuemart_category_id` = 5) ORDER BY product_name ASC LIMIT 0, 10;

I can also provide a test sql dump of database to reproduce this problem, but I can't be published, so I'd prefer to send it to someone's e-mail directly if possible.

After executing this query, MariaDB crashes with the following information written in log:

111229 9:28:14 [ERROR] 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=0
read_buffer_size=131072
max_used_connections=1
max_threads=402
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 879704 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f15067c6ef0
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 = 0x7f14e7b57e98 thread_stack 0x48000
/usr/sbin/mysqld(my_print_stacktrace+0x2e) [0x7f1503b171be]
/usr/sbin/mysqld(handle_segfault+0x405) [0x7f15037535d5]
/lib/libpthread.so.0(+0xef60) [0x7f1501d44f60]
/lib/libc.so.6(memcpy+0xa0) [0x7f15012b67f0]
/usr/sbin/mysqld(+0x5910ca) [0x7f15039860ca]
/usr/sbin/mysqld(+0x591fc0) [0x7f1503986fc0]
/usr/sbin/mysqld(_ma_write_init_block_record+0x149) [0x7f15039890b9]
/usr/sbin/mysqld(maria_write+0x6f7) [0x7f1503951c27]
/usr/sbin/mysqld(handler::ha_write_row(unsigned char*)+0x3c) [0x7f15038488ec]
/usr/sbin/mysqld(+0x3d08f8) [0x7f15037c58f8]
/usr/sbin/mysqld(+0x3be77e) [0x7f15037b377e]
/usr/sbin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x206) [0x7f15037bf2c6]
/usr/sbin/mysqld(+0x3be77e) [0x7f15037b377e]
/usr/sbin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x206) [0x7f15037bf2c6]
/usr/sbin/mysqld(+0x3be77e) [0x7f15037b377e]
/usr/sbin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x206) [0x7f15037bf2c6]
/usr/sbin/mysqld(+0x3be77e) [0x7f15037b377e]
/usr/sbin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x6e) [0x7f15037bf12e]
/usr/sbin/mysqld(+0x3ca537) [0x7f15037bf537]
/usr/sbin/mysqld(JOIN::exec()+0x4c1) [0x7f15037d1901]
/usr/sbin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x12a) [0x7f15037d381a]
/usr/sbin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x15d) [0x7f15037d424d]
/usr/sbin/mysqld(+0x369b0a) [0x7f150375eb0a]
/usr/sbin/mysqld(mysql_execute_command(THD*)+0x40e) [0x7f1503760e4e]
/usr/sbin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x2c1) [0x7f1503766501]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xcfb) [0x7f150376765b]
/usr/sbin/mysqld(do_command(THD*)+0xf4) [0x7f1503768174]
/usr/sbin/mysqld(handle_one_connection+0x14b) [0x7f150375a55b]
/lib/libpthread.so.0(+0x68ba) [0x7f1501d3c8ba]
/lib/libc.so.6(clone+0x6d) [0x7f150130602d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f15067e9278): SELECT SQL_CALC_FOUND_ROWS * FROM `i1rsk_virtuemart_products_ru_ru` as l JOIN `i1rsk_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `i1rsk_virtuemart_product_categories` ON p.`virtuemart_product_id` = `i1rsk_virtuemart_product_categories`.`virtuemart_product_id` LEFT JOIN `i1rsk_virtuemart_categories_ru_ru` as c ON c.`virtuemart_category_id` = `i1rsk_virtuemart_product_categories`.`virtuemart_category_id` WHERE ( p.`published`="1" AND `i1rsk_virtuemart_product_categories`.`virtuemart_category_id` = 5) ORDER BY product_name ASC LIMIT 0, 10
Connection ID (thread ID): 12
Status: NOT_KILLED

[Anton Khalikov (anton-khalikov)]

Umm, just noticed: the log dump says that key_buffer_size=0, which is wrong. Actual value:

MariaDB [(none)]> show variables like "key_buffer_size";
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| key_buffer_size | 134217728 |
+-----------------+-----------+
1 row in set (0.00 sec)

[Elena Stepanova (elenst)]

Hi,

Could you upload the dump to ftp://ftp.askmonty.org/private? Only MariaDB developers can read it.

Thanks

[Anton Khalikov (anton-khalikov)]

Hi Elena

Done, filename is bug_909635.sql

[Elena Stepanova (elenst)]

Hi Anton,

Thank you.

Here is a simplified test case:

CREATE TABLE t1 (
  a INT PRIMARY KEY,
  b CHAR(255),
  c VARCHAR(2048),
  d VARCHAR(18990),
  e CHAR(128),
  f CHAR(192)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t1 VALUES
  (1,'A','B','C','','D'),
  (2,'Abcdefghi','E','F','','G');

CREATE TABLE t2 (
  g INT PRIMARY KEY,
  h CHAR(32),
  i CHAR(255),
  j TEXT
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t2
  VALUES
  (1,'M','','H'),
  (2,'N','','H');

SELECT * FROM t1, t2
  WHERE a = g ORDER BY b;

Reproducible on 5.1.60, 5.2.10, 5.3, 5.5 with certain combinations of engines:
# MyISAM / MyISAM
# MyISAM / InnoDB
# Aria / MyISAM
# Aria / InnoDB
# InnoDB / InnoDB

[Elena Stepanova (elenst)]

In 5.3, reproducible with the default optimizer_switch as well as with all OFFs.

[Elena Stepanova (elenst)]

Stack trace from the current 5.3:

#3 <signal handler called>
#4 0x00007f87e79218b0 in memcpy () from /lib64/libc.so.6
#5 0x0000000000a16693 in write_tail (info=0x1daf358, block=0x1dc8920,
    row_part=0x2020202020204014 <Address 0x2020202020204014 out of bounds>, org_length=44)
    at ma_blockrec.c:1964
#6 0x0000000000a18cf6 in write_block_record (info=0x1daf358, old_record=0x0,
    record=0x1d78dc0 "", row=0x1daf3b0, bitmap_blocks=0x1daf3b0, head_block_is_read=0 '\000',
    row_pos=0x7f87dede8ff0, undo_lsn=1, old_record_checksum=0) at ma_blockrec.c:2900
#7 0x0000000000a1a862 in allocate_and_write_block_record (info=0x1daf358,
    record=0x1d78dc0 "", row=0x1daf3b0, undo_lsn=1) at ma_blockrec.c:3534
#8 0x0000000000a1a97a in _ma_write_init_block_record (info=0x1daf358, record=0x1d78dc0 "")
    at ma_blockrec.c:3574
#9 0x0000000000a575b6 in maria_write (info=0x1daf358, record=0x1d78dc0 "") at ma_write.c:157
#10 0x00000000009ef091 in ha_maria::write_row (this=0x1d786c0, buf=0x1d78dc0 "")
    at ha_maria.cc:1135
#11 0x000000000061a74b in handler::ha_write_tmp_row (this=0x1d786c0, buf=0x1d78dc0 "")
    at sql_class.h:3789
#12 0x000000000079a93d in end_write (join=0x1d6cd20, join_tab=0x1d75e18, end_of_records=false)
    at sql_select.cc:16558
#13 0x0000000000797c8a in evaluate_join_record (join=0x1d6cd20, join_tab=0x1d75af8, error=0)
    at sql_select.cc:15501
#14 0x00000000007974d4 in sub_select (join=0x1d6cd20, join_tab=0x1d75af8,
    end_of_records=false) at sql_select.cc:15304
#15 0x0000000000797c8a in evaluate_join_record (join=0x1d6cd20, join_tab=0x1d757d8, error=0)
    at sql_select.cc:15501
#16 0x00000000007974d4 in sub_select (join=0x1d6cd20, join_tab=0x1d757d8,
    end_of_records=false) at sql_select.cc:15304
#17 0x0000000000796ca2 in do_select (join=0x1d6cd20, fields=0x0, table=0x1d76e30,
    procedure=0x0) at sql_select.cc:14965
#18 0x0000000000776bbc in JOIN::exec (this=0x1d6cd20) at sql_select.cc:2262
#19 0x0000000000779105 in mysql_select (thd=0x1c2cbd8, rref_pointer_array=0x1c2f7a8,
    tables=0x1cb1208, wild_num=1, fields=..., conds=0x1cb1c30, og_num=1, order=0x1cb1ea8,
    group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x1cb1f88,
    unit=0x1c2f070, select_lex=0x1c2f558) at sql_select.cc:2925
#20 0x000000000076fd89 in handle_select (thd=0x1c2cbd8, lex=0x1c2efd0, result=0x1cb1f88,
    setup_tables_done_option=0) at sql_select.cc:283
#21 0x00000000006feb1c in execute_sqlcom_select (thd=0x1c2cbd8, all_tables=0x1cb1208)
    at sql_parse.cc:5148
#22 0x00000000006f5bd9 in mysql_execute_command (thd=0x1c2cbd8) at sql_parse.cc:2281
#23 0x00000000007014f7 in mysql_parse (thd=0x1c2cbd8,
    rawbuf=0x1cb0ff0 "SELECT * FROM t1, t2\nWHERE a = g ORDER BY b", length=43,
    found_semicolon=0x7f87dedeac98) at sql_parse.cc:6149
#24 0x00000000006f33ae in dispatch_command (command=COM_QUERY, thd=0x1c2cbd8,
    packet=0x1ca7b89 "SELECT * FROM t1, t2\nWHERE a = g ORDER BY b", packet_length=43)
    at sql_parse.cc:1227
#25 0x00000000006f26e2 in do_command (thd=0x1c2cbd8) at sql_parse.cc:922
#26 0x00000000006ef60a in handle_one_connection (arg=0x1c2cbd8) at sql_connect.cc:1193
#27 0x00007f87e8588a4f in sta...

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Problem is in temporary table (myisam).

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

I can't understand what is going wrong in the MyISAM engine, up to the engine everything looks OK (maybe empty line leads for this problem, but I can't say for sure).

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Sorry for mistyping. Of course it is (M)Aria tables. BTW it is why we can't reproduce it on MySQL (IMHO).

[Elena Stepanova (elenst)]

Test case is hidden in comment #4.

[Michael Widenius (monty)]

This crash happens if the internal temporary table row length, needed to resolve the query, exceeds 64K
(The query used a UTF8 varchar(18990), which requires up to 56972 space for storage)
Will push the fix to 5.1

[Sergey Petrunia (sergefp)]

(the fix was pushed)

[Anton Khalikov (anton-khalikov)]

Sorry guys, is there a plan for the near future releasing 5.1/5.2 packages with this bug fixed? Or should we migrate to 5.3 series instead?

[Michael Widenius (monty)]

We could do a new release of 5.1.61 and 5.2.11 early next week.
Sorry for the delay.
As there is a lot of work to do a release, we do older releases mainly when there is a security fix, some really really bad bug that hits a lot of people or if a support customer asks us to do so.