Query Routinely Crashing Server

[StephenJ]

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. This query is a key reporting query in our application and the underlying tables are highly optimized to make this query perform well at least from the standapoint of an explain. This query typically
processes hundreds of thousands of rows in about .05 seconds so performance wise it is acceptable, but the crashes are not.

=============== 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 ============

Stacke 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*, 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*, 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 (?)

Any help would be great. I realize there are methods to improve this query, particularly the abundance of correlated subqueries. However, the correlated subqueries are there because the math done in the query is modular and breaking the query up improves our maintenance and adding of different formulas. For now I am stuck with this query format and need to determine what the bug/issue is that is leading to a crash.