Bug #23184 SELECT causes server crash
Submitted: 11 Oct 2006 17:44 Modified: 28 Nov 2006 19:35
Reporter: Victoria Reznichenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.26 OS:Linux (linux)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: backport_050030SP1

[11 Oct 2006 17:44] Victoria Reznichenko
Description:
SELECT statement crashes MySQL server.

back trace:
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1158191472 (LWP 2021)]
0x000000000083d6e0 in my_strnncollsp_simple ()
(gdb) Quit
(gdb) bt
#0  0x000000000083d6e0 in my_strnncollsp_simple ()
#1  0x0000000000572390 in Field_string::cmp ()
#2  0x00000000005255ed in simple_str_key_cmp ()
#3  0x00000000005fc303 in buffpek_compare ()
#4  0x0000000000828eb3 in _downheap ()
#5  0x00000000005fc217 in merge_walk ()
#6  0x00000000005fbb06 in Unique::walk ()
#7  0x0000000000525f48 in Item_sum_count_distinct::val_int ()
#8  0x0000000000515d59 in Item::send ()
#9  0x000000000057f3c0 in select_send::send_data ()
#10 0x00000000005deebd in end_send_group ()
#11 0x00000000005de56a in evaluate_join_record ()
#12 0x00000000005d829b in sub_select ()
#13 0x00000000005de1e9 in do_select ()
#14 0x00000000005d33ed in JOIN::exec ()
#15 0x00000000005d4611 in mysql_select ()
#16 0x00000000005d07ff in handle_select ()
#17 0x00000000005a18d7 in mysql_execute_command ()
#18 0x00000000005a7e95 in mysql_parse ()
#19 0x000000000059ff64 in dispatch_command ()
#20 0x000000000059faa3 in do_command ()
#21 0x000000000059f21d in handle_one_connection ()
#22 0x0000002a957a3c64 in start_thread () from /lib64/tls/libpthread.so.0
#23 0x0000002a95e61243 in thread_start () from /lib64/tls/libc.so.6
#24 0x0000000000000000 in ?? ()

table is very big (830M compressed dump file) though I wasn't able to reproduce this problem with less data.

How to repeat:
1. restore table from dump file
2. run SELECT statement mentioned below.
[24 Oct 2006 15:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14277

ChangeSet@1.2284, 2006-10-24 18:33:32+03:00, gkodinov@macbook.gmz +4 -0
  Bug #23184: SELECT causes server crash
   Item::val_xxx() may be called by the server several times at execute time 
   for a single query. Calls to val_xxx() may be very expensive and sometimes
   (count(distinct), sum(distinct), avg(distinct)) not possible.
   To avoid that problem the results of calculation for these aggregate 
   functions are cached so that val_xxx() methods just return the calculated 
   value for the second and subsequent calls.
[30 Oct 2006 10:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14541

ChangeSet@1.2284, 2006-10-30 12:15:02+02:00, gkodinov@macbook.gmz +4 -0
  Bug #23184: SELECT causes server crash
   Item::val_xxx() may be called by the server several times at execute time 
   for a single query. Calls to val_xxx() may be very expensive and sometimes
   (count(distinct), sum(distinct), avg(distinct)) not possible.
   To avoid that problem the results of calculation for these aggregate 
   functions are cached so that val_xxx() methods just return the calculated 
   value for the second and subsequent calls.
[31 Oct 2006 9:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14591

ChangeSet@1.2284, 2006-10-31 11:01:27+02:00, gkodinov@macbook.gmz +4 -0
  Bug #23184: SELECT causes server crash
   Item::val_xxx() may be called by the server several times at execute time 
   for a single query. Calls to val_xxx() may be very expensive and sometimes
   (count(distinct), sum(distinct), avg(distinct)) not possible.
   To avoid that problem the results of calculation for these aggregate 
   functions are cached so that val_xxx() methods just return the calculated 
   value for the second and subsequent calls.
[23 Nov 2006 15:33] Davide Ferrari
I have a similiar problem, this is what I get in my mysqld.err

0x818e95c
0x81e1993
0x81f8ab5
0x81fa282
0x81fcca3
0x815c3ee
0x815b565
0x815beeb
0x811969d
0x8122c0e
0x80fb997
0x81063c0
0x81df3d4
0x81f8637
0x81eca4f
0x81ecb07
0x81f9eed
0x81fe9f1
0x81fef4a
0x81ff8c8
0x81aaeb0
0x81b0ae4
0x81b107e
0x81b2652
0x81b3444
0xb7ddf294
0xb7c2232e
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8e44e48 = select distinct ViajePedido.*,viajePedidoEstados.*,concat(date_format(viajeFechas.fechaInicio ,'%e-%c-%Y'),'/',date_format(viajeFechas.fechaFin ,'%e-%c-%Y'))as fecha, viajes.Nombre as NombreViaje,viajeProveedores.Nombre as Proveedor,viajeDestinos.*,viajeOwner.aliasOwner as propietario_ord, @anticipada := group_concat(distinct VC.idCategoria) like '%17%' as reservaAnticipada, @precio := (select sum(valorTotal) as precio from detallePrecioViajePedido where idviajePedido=ViajePedido.idviajePedido group by MonedaTxt) as precio, (select MonedaTxt as MonedaTxt from detallePrecioViajePedido where idviajePedido=ViajePedido.idviajePedido group by MonedaTxt) as MonedaTxt, cast(lpad(format((VIA_pedidos_prioridades.punts + (@precio/250) + if(convert(ViajePedido.fechaInicioPedido,datetime) < date_add(now(),interval 7 day),20,0) + if(ViajePedido.fechaPedido < date_sub(now(),interval 3 day),20,if(ViajePedido.fechaPedido < date_sub(now(),interval 2 day),10,if(ViajePedido.fechaPedido < date_sub(now(),interval 1 day),5,0))) +
thd->thread_id=50
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

and this is the resolve_stack_dump output:

0x818e95c handle_segfault + 668
0x81e1993 _Z11copy_fieldsP15TMP_TABLE_PARAM + 35
0x81f8ab5 _Z10end_updateP4JOINP13st_join_tableb + 133
0x81fa282 _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 1202
0x81fcca3 _ZN4JOIN4execEv + 1635
0x815c3ee _ZN30subselect_single_select_engine4execEv + 158
0x815b565 _ZN14Item_subselect4execEv + 21
0x815beeb _ZN24Item_singlerow_subselect8val_realEv + 27
0x811969d _ZN22Item_func_set_user_var5checkEb + 301
0x8122c0e _ZN22Item_func_set_user_var8val_realEv + 30
0x80fb997 _ZN4Item13save_in_fieldEP5Fieldb + 455
0x81063c0 _ZN17Item_result_field20save_in_result_fieldEb + 32
0x81df3d4 _Z10copy_funcsPP4Item + 52
0x81f8637 _Z15end_write_groupP4JOINP13st_join_tableb + 599
0x81eca4f _Z20evaluate_join_recordP4JOINP13st_join_tableiPc + 447
0x81ecb07 _Z10sub_selectP4JOINP13st_join_tableb + 119
0x81f9eed _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 285
0x81fe9f1 _ZN4JOIN4execEv + 9137
0x81fef4a _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 362
0x81ff8c8 _Z13handle_selectP3THDP6st_lexP13select_resultm + 456
0x81aaeb0 _Z21mysql_execute_commandP3THD + 9888
0x81b0ae4 _Z11mysql_parseP3THDPcj + 612
0x81b107e _Z16dispatch_command19enum_server_commandP3THDPcj + 1326
0x81b2652 _Z10do_commandP3THD + 258
0x81b3444 handle_one_connection + 3076
0xb7ddf294 _end + -1351138236
0xb7c2232e _end + -1352960802
[23 Nov 2006 15:34] Davide Ferrari
I was forgetting...this testcase 
http://lists.mysql.com/commits/14591

instead works smoothly, no crashes at all.
[27 Nov 2006 17:15] Georgi Kodinov
Pushed in 5.0.32/5.1.14-beta
[28 Nov 2006 19:35] Paul Dubois
Noted in 5.0.32, 5.1.14 changelogs.

Calculation of COUNT(DISTINCT), AVG(DISTINCT), or SUM(DISTINCT) when
they are referenced more than once in a single query with GROUP BY
could cause a server crash.
[22 Dec 2006 0:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17290

ChangeSet@1.2309, 2006-12-22 01:03:28+01:00, msvensson@neptunus.(none) +4 -0
  Bug #23184: SELECT causes server crash
   Item::val_xxx() may be called by the server several times at execute time 
   for a single query. Calls to val_xxx() may be very expensive and sometimes
   (count(distinct), sum(distinct), avg(distinct)) not possible.
   To avoid that problem the results of calculation for these aggregate 
   functions are cached so that val_xxx() methods just return the calculated 
   value for the second and subsequent calls.