MySQL Bugs: #11071: SELECT crashes MySQL server

Bug #11071	SELECT crashes MySQL server
Submitted:	3 Jun 2005 12:59	Modified:	27 Jun 2005 13:27
Reporter:	Victoria Reznichenko	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	4.1 bk tree	OS:	Linux (linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
The following SELECT query crashes MySQl server:

SELECT faq_stats.faq_id,m2g_1.lvl1_id,count(distinct m2g_2.lvl1_id),SUM(faq_stats.faq_hits)/count(*)*count(distinct faq_stats.faq_date),DATE_FORMAT(DATE_ADD(faq_stats.faq_date, INTERVAL 7200 SECOND), '%Y/%m'),IFNULL(lbls.label, '< No Category >') FROM faq_stats,faqs LEFT OUTER JOIN map2faq_group m2g_1 ON m2g_1.faq_group_id = faqs.faq_group_id AND m2g_1.tbl = 14 LEFT OUTER JOIN map2faq_group m2g_2 ON m2g_2.faq_group_id = faqs.faq_group_id AND m2g_2.tbl = 13 LEFT OUTER JOIN labels lbls ON m2g_1.lvl1_id = lbls.label_id AND lbls.tbl = 14 AND lbls.lang_id = 1 AND lbls.fld = 1 WHERE faqs.faq_id = faq_stats.faq_id AND (m2g_1.lvl1_id IN (0,12,11,6,2,7,5,1,10,9,8,3,4) OR m2g_1.lvl1_id IS NULL) AND (faq_stats.faq_date BETWEEN '2005-04-30 22:00:00' AND '2005-05-31 21:59:59') GROUP BY m2g_1.lvl1_id,IFNULL(lbls.label, '< No Category >'),5,faq_stats.faq_id ORDER BY 2;

bakctrace:
(gdb) bt
#0  0x081540f9 in String::real_alloc (this=0x8c88ab4, arg_length=4294967295) at sql_string.cc:57
#1  0x08105b6b in new_Item_buff (item=0x8c888b8) at sql_string.h:48
#2  0x081a2162 in alloc_group_fields (join=0x8c81658, group=0x8c81308) at sql_select.cc:8635
#3  0x081a2100 in make_group_fields (main_join=0x8c81658, curr_join=0x8c81658) at sql_select.cc:8615
#4  0x08191db8 in JOIN::exec (this=0x8c81658) at sql_select.cc:1350
#5  0x081929f9 in mysql_select (thd=0x8c52b00, rref_pointer_array=0x8c52d34, tables=0x8c63038, wild_num=0, fields=@0x8c52c98, conds=0x8c811f8,
    og_num=5, order=0x8c81608, group=0x8c81308, having=0x0, proc_param=0x0, select_options=2156153344, result=0x8c81648, unit=0x8c52b48,
    select_lex=0x8c52c30) at sql_select.cc:1603
#6  0x0818f03c in handle_select (thd=0x8c52b00, lex=0x8c52b3c, result=0x8c81648) at sql_select.cc:193
#7  0x0816b8ec in mysql_execute_command (thd=0x8c52b00) at sql_parse.cc:2085
#8  0x081705e2 in mysql_parse (thd=0x8c52b00,
    inBuf=0x8c61de8 "SELECT faq_stats.faq_id,m2g_1.lvl1_id,count(distinct m2g_2.lvl1_id),SUM(faq_stats.faq_hits)/count(*)*count(distinct faq_stats.faq_date),DATE_FORMAT(DATE_ADD(faq_stats.faq_date, INTERVAL 7200 SECOND), "..., length=865) at sql_parse.cc:4189
#9  0x0816a37c in dispatch_command (command=COM_QUERY, thd=0x8c52b00,
    packet=0x8c54d51 "SELECT faq_stats.faq_id,m2g_1.lvl1_id,count(distinct m2g_2.lvl1_id),SUM(faq_stats.faq_hits)/count(*)*count(distinct faq_stats.faq_date),DATE_FORMAT(DATE_ADD(faq_stats.faq_date, INTERVAL 7200 SECOND), "..., packet_length=866) at sql_parse.cc:1505
#10 0x08169d62 in do_command (thd=0x8c52b00) at sql_parse.cc:1318
#11 0x0816929a in handle_one_connection (arg=0x8c52b00) at sql_parse.cc:1050
#12 0xb7e41f1b in pthread_start_thread () from /lib/libpthread.so.0
#13 0xb7e41f9f in pthread_start_thread_event () from /lib/libpthread.so.0
#14 0xb7d70fda in clone () from /lib/libc.so.6

How to repeat:
1. restore table from the dump (file rightnow_crash.dump2.gz was uploaded to our ftp)
2. run SELECT query.

Duplicate of bug #11088

I tested this bug report with the latest 4.1 and got the following result:

mysql> SELECT faq_stats.faq_id,m2g_1.lvl1_id,count(distinct m2g_2.lvl1_id),SUM(faq_stats.faq_hits)/count(*)*count(distinct faq_stats.faq_
date),DATE_FORMAT(DATE_ADD(faq_stats.faq_date, INTERVAL 7200 SECOND), '%Y/%m'),IFNULL(lbls.label, '< No Category >') FROM faq_stats,faqs
LEFT OUTER JOIN map2faq_group m2g_1 ON m2g_1.faq_group_id = faqs.faq_group_id AND m2g_1.tbl = 14 LEFT OUTER JOIN map2faq_group m2g_2 ON m
2g_2.faq_group_id = faqs.faq_group_id AND m2g_2.tbl = 13 LEFT OUTER JOIN labels lbls ON m2g_1.lvl1_id = lbls.label_id AND lbls.tbl = 14 A
ND lbls.lang_id = 1 AND lbls.fld = 1 WHERE faqs.faq_id = faq_stats.faq_id AND (m2g_1.lvl1_id IN (0,12,11,6,2,7,5,1,10,9,8,3,4) OR m2g_1.l
vl1_id IS NULL) AND (faq_stats.faq_date BETWEEN '2005-04-30 22:00:00' AND '2005-05-31 21:59:59') GROUP BY m2g_1.lvl1_id,IFNULL(lbls.label
, '< No Category >'),5,faq_stats.faq_id ORDER BY 2;
ERROR 3 (HY000): Error writing file '/tmp/MYC9FKwM' (Errcode: 28)

/tmp has 1.6G ..

This is not a bug, just the query requires about 4G (gigabytes) of disk space to be succefully executed.

In this query grouping made over lbls.label field which is of 'text' type. Sort key length for this type will be 1K * 2M records of source data = 2G temporary storage + 2G required by filesort algorithm to sort that data.

Assuming that each 'label' field value in 'labels' table has it's own unique label_id value it's possible to modify GROUP BY calues like this: "GROUP BY m2g_1.lvl1_id, IFNULL(lbls.label_id , -1), 5, faq_stats.faq_id". This modification will lower disk space requirement to only 200M and will make query perform _much_ faster.