MySQL Bugs: #35174: Query w/ Many Subqueries and Views Causes Crash

Bug #35174	Query w/ Many Subqueries and Views Causes Crash
Submitted:	10 Mar 2008 0:00	Modified:	28 May 2008 15:09
Reporter:	Stephen Johnston	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.37	OS:	Linux (Redhat)
Assigned to:		CPU Architecture:	Any

Description:
I am having a problem where the following query crashes mySQL periodically. I can't see a reason for it in the error log, but suspect a memory issue due to the subselects or the views or a memory issue that is evoking a bug in mySQL. The OS is 32bit Redhat. Fairly standard settings. All tables in this query are innodb, innodb_buffer_pool = 2200m, tmp_table_size = 32m, max_heap_table = 16m, system has 8 gigs of ram, connections during failure are sometimes as low as 2 or 3, system is dedicated to mySQL only.

The query is various forms of the following.

=============== QUERY ============

select SQL_NO_CACHE 
`vre`.`guild_id` AS `guild_id`, 
`cp`.`character_id` AS `character_id`, 
`cp`.`character_name` AS `character_name`,
`cp`.`hier_1` AS `hier_1`, 
`cp`.`hier_2` AS `hier_2`, 
`cp`.`hier_3` AS `hier_3`, 
`cp`.`hier_4` AS `hier_4`, 
`cp`.`level` AS `level`,
/* 8th field */
(
ifnull(sum(`vre`.`total`), 0) + ifnull((select sum(t1.earned_amount) from rr_adjustment_details t1, rr_adjustment t2 
where t1.pool_id = 35 and t2.pool_id = 35 and t1.adjust_id = t2.adjust_id and t2.guild_id = 48 and t1.character_id = cp.character_id group by t1.character_id), 0)
)
AS `earned`,
/* 9th field */
(
ifnull((select sum(spent) from uv_rm_spent where guild_id = 48 and character_id = `cp`.`character_id` 
and pool_id = 35 group by guild_id, character_id),0) 
+ ifnull((select sum(t1.spent_amount) from rr_adjustment_details t1, rr_adjustment t2 where t1.pool_id = 35 and t2.pool_id = 35 and t1.adjust_id = t2.adjust_id 
and t2.guild_id = 48 and t1.character_id = cp.character_id group by t1.character_id), 0)
) 
AS `spent`,
/* 10th field */
/* REGULAR DKP */
(
sum(`vre`.`total`) 
- ifnull((select sum(spent) from uv_rm_spent where 
pool_id = 35 and guild_id = 48 and character_id = `cp`.`character_id` group by guild_id, character_id),0) 
+ ifnull((select sum(t1.earned_amount) from rr_adjustment_details t1, rr_adjustment t2 where 
t1.pool_id = 35 and t2.pool_id = 35 and t1.adjust_id = t2.adjust_id and t2.guild_id = 48 and t1.character_id = cp.character_id group by t1.character_id), 0)
- ifnull((select sum(t1.spent_amount) from rr_adjustment_details t1, rr_adjustment t2 where 
t1.pool_id = 35 and t2.pool_id = 35 and t1.adjust_id = t2.adjust_id and t2.guild_id = 48 and t1.character_id = cp.character_id group by t1.character_id) , 0)
+ ifnull((select sum(t1.total_amount) from rr_adjustment_details t1, rr_adjustment t2 where 
t1.pool_id = 35 and t2.pool_id = 35 and t1.adjust_id = t2.adjust_id and t2.guild_id = 48 and t1.character_id = cp.character_id group by t1.character_id) , 0) 
) 
AS `current` ,(
select count(`uv_raid_event_character_detail`.`raid_event_id`) AS `total_events` 
from `uv_raid_event_character_detail` 
where pool_id = 35 and (`uv_raid_event_character_detail`.`date` >= (curdate() - interval 30 day)) and guild_id = 48 and `uv_raid_event_character_detail`.`character_id` = `cp`.`character_id`
and attendance = 1 
group by character_id, `uv_raid_event_character_detail`.`guild_id` 
) 
as raids_attended_30,
/* 12th field */
(
select count(`vw_new_guild_events`.`raid_event_id`) 
AS `total_events` from `vw_new_guild_events` where (`vw_new_guild_events`.`date` >= (curdate() - interval 30 day)) AND guild_id = 48 and pool_id = 35 and attendance = 1 group by guild_id 
) 
as guild_total_last_30, 
/* 13th field */
( 
select count(`uv_raid_event_character_detail`.`raid_event_id`) AS `total_events` from `uv_raid_event_character_detail` 
where pool_id = 35 and (`uv_raid_event_character_detail`.`date` >= (curdate() - interval 60 day)) and guild_id = 48 and `uv_raid_event_character_detail`.`character_id` = `cp`.`character_id` 
and attendance = 1
group by character_id, `uv_raid_event_character_detail`.`guild_id` 
) 
as raids_attended_60, 
/* 14th field */
(
select count(`vw_new_guild_events`.`raid_event_id`) AS `_` from `vw_new_guild_events` 
where (`vw_new_guild_events`.`date` >= (curdate() - interval 60 day)) AND guild_id = 48 and pool_id = 35 and attendance = 1 group by guild_id 
) 
as guild_total_last_60, 
/* 15th field */
(
select max(date) from uv_raid_event_character_detail where character_id = `cp`.`character_id` and guild_id = 48 and pool_id = 35 
) 
as last_raid ,`cp`.`game_id` AS `game_id`,`cp`.`gender` AS `gender` from `character_profile` `cp` join `uv_rm_earned` `vre` , character_raid_status crs where 
vre.pool_id = 35 and `cp`.`character_id` = `vre`.`character_id` 
and vre.guild_id = 48 
and cp.deleted != 1 and crs.character_id = cp.character_id and crs.guild_id = 48 and crs.active = 1 group by 
guild_id, character_id order by 2 asc

=============== END QUERY ======================== 

How to repeat:
Run the query above.

Stack traces for two crashes follow:

First Stack Trace:

0x8181560 handle_segfault + 656
0x81cb0be store_val_in_field(Field*, Item*, enum_check_fields) + 238
0x81cb1eb store_val_in_field(Field*, Item*, enum_check_fields) + 539
0x81cb1eb store_val_in_field(Field*, Item*, enum_check_fields) + 539
0x81cba79 JOIN::remove_subq_pushed_predicates(Item**) + 1785
0x81dec0e JOIN::optimize() + 2654
0x8153fcf subselect_single_select_engine::exec() + 655
0x815324e Item_subselect::exec() + 46
0x8153455 Item_singlerow_subselect::val_real() + 21
0x812a657 Item_func_ifnull::real_op() + 23
0x811e0eb Item_func_numhybrid::val_real() + 59
0x8102832 Item::save_in_field(Field*, bool) + 482
0x810c4a0 Item_result_field::save_in_result_field(bool) + 32
0x81cc5b5 copy_funcs(Item**) + 37
0x81d830a create_virtual_tmp_table(THD*, List<create_field>&) + 2234
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d8d14 sub_select(JOIN*, st_join_table*, bool) + 756
0x81e400f JOIN::exec() + 2015
0x81e5d60 mysql_select(THD*, Item***, st_table_list*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_ord + 368
0x81e668a handle_select(THD*, st_lex*, select_result*, unsigned long) + 314
0x819825f mysql_execute_command(THD*) + 6527
0x819dc4f mysql_parse(THD*, char*, unsigned int) + 495
0x819e1d5 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1269
0x819f66d do_command(THD*) + 173
0x81a01a0 handle_one_connection + 2512
0x73245b (?)
0x64224e (?)

Second Stack Trace:

0x8181560 handle_segfault + 656
0x81cb0be store_val_in_field(Field*, Item*, enum_check_fields) + 238
0x81cb1eb store_val_in_field(Field*, Item*, enum_check_fields) + 539
0x81cb1eb store_val_in_field(Field*, Item*, enum_check_fields) + 539
0x81cba79 JOIN::remove_subq_pushed_predicates(Item**) + 1785
0x81dec0e JOIN::optimize() + 2654
0x8153fcf subselect_single_select_engine::exec() + 655
0x815324e Item_subselect::exec() + 46
0x8153455 Item_singlerow_subselect::val_real() + 21
0x812a657 Item_func_ifnull::real_op() + 23
0x811e0eb Item_func_numhybrid::val_real() + 59
0x8102832 Item::save_in_field(Field*, bool) + 482
0x810c4a0 Item_result_field::save_in_result_field(bool) + 32
0x81cc5b5 copy_funcs(Item**) + 37
0x81d830a create_virtual_tmp_table(THD*, List<create_field>&) + 2234
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d89e8 create_virtual_tmp_table(THD*, List<create_field>&) + 3992
0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
0x81d8d14 sub_select(JOIN*, st_join_table*, bool) + 756
0x81e400f JOIN::exec() + 2015
0x81e5d60 mysql_select(THD*, Item***, st_table_list*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_ord + 368
0x81e668a handle_select(THD*, st_lex*, select_result*, unsigned long) + 314
0x819825f mysql_execute_command(THD*) + 6527
0x819dc4f mysql_parse(THD*, char*, unsigned int) + 495
0x819e1d5 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1269
0x819f66d do_command(THD*) + 173
0x81a01a0 handle_one_connection + 2512
0x73245b (?)
0x64224e (?)

Thank you for the bug report. Could you please try with latest released
version ( the version reported is quite older). Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Stephen,

MySQL 5.0.37 is very old. We need to know if you will get this issue by using our newest version 5.0.51a.

The system this bug is occuring in services over 300,000 users. I am unable to upgrade our production database at this time to find out if maybe it fixes a bug in mySQL. Especially since there are other issues that have been noted that seem even worse in some more recent versions. I've rewritten the query to work another way and the system has stopped crashing. Sorry I can't be more help.

Stephen,

I will change status here again to "need feedback". Please, when you will update some when, then let us know, if this fixed your problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".