# Query Routinely Crashing Server **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [March 9, 2008, 5:02pm UTC](https://forums.percona.com/t/query-routinely-crashing-server/667 "2008-03-09T17:02:56Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![StephenJ](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/stephenj/32/771_2.png) [@StephenJ](https://forums.percona.com/u/StephenJ)\ **Post date:** [March 9, 2008, 5:02pm UTC](https://forums.percona.com/t/query-routinely-crashing-server/667/1 "2008-03-09T17:02:56Z")
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\&) + 2234 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 3992 0x81d8a8c sub\_select(JOIN\*, st\_join\_table\*, bool) + 108 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 3992 0x81d8a8c sub\_select(JOIN\*, st\_join\_table\*, bool) + 108 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 3992 0x81d8a8c sub\_select(JOIN\*, st\_join\_table\*, bool) + 108 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 3992 0x81d8a8c sub\_select(JOIN\*, st\_join\_table\*, bool) + 108 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 3992 0x81d8a8c sub\_select(JOIN\*, st\_join\_table\*, bool) + 108 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 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\&) + 2234 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 3992 0x81d8a8c sub\_select(JOIN\*, st\_join\_table\*, bool) + 108 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 3992 0x81d8a8c sub\_select(JOIN\*, st\_join\_table\*, bool) + 108 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 3992 0x81d8a8c sub\_select(JOIN\*, st\_join\_table\*, bool) + 108 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 3992 0x81d8a8c sub\_select(JOIN\*, st\_join\_table\*, bool) + 108 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 3992 0x81d8a8c sub\_select(JOIN\*, st\_join\_table\*, bool) + 108 0x81d89e8 create\_virtual\_tmp\_table(THD\*, List\&) + 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.