Description:
Server Crashes when I execute this statement.
delete from CHEF_PRESENCE where SESSION_ID not in
(select SESSION_ID from CHEF_SESSION where SESSION_START = SESSION_END)
In a script, just after performing a select on the tables
select * from CHEF_PRESENCE where SESSION_ID not in
(select SESSION_ID from CHEF_SESSION where SESSION_START = SESSION_END);
We have 3 other boxes running identical software and an identical application, none of the others crash.
Err stack is
Version: '4.1.9-standard-log' socket: '/tmp/mysql.sock' port: 3306 Official MySQL-standard binary
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=268435456
read_buffer_size=1044480
max_used_connections=2
max_connections=100
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 466543 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x8942288
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfe5e918, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x808bab3
0x82db038
0x80bf200
0x80bf880
0x80bf467
0x80bf3fd
0x80bfdb7
0x80be4b9
0x80bb6b9
0x8075c66
0x80729ba
0x8073789
0x834f011
0x805d608
0x8354761
0x805c1cf
0x80d7a04
0x809c896
0x809f319
0x80999e8
0x80993b8
0x8098a87
0x82d87ec
0x83020ba
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 0x8957078 = delete from CHEF_PRESENCE where SESSION_ID not in (select SESSION_ID from CHEF_SESSION where SESSION_START = SESSION_END)
thd->thread_id=2
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.
Number of processes running now: 0
050407 17:20:16 mysqld restarted
050407 17:20:16 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050407 17:20:16 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 191828.
InnoDB: Doing recovery: scanned up to log sequence number 0 191828
InnoDB: Last MySQL binlog file position 0 79, file name ./waterdock-bin.003061
050407 17:20:16 InnoDB: Flushing modified pages from the buffer pool...
050407 17:20:16 InnoDB: Started; log sequence number 0 191828
/home/mysql/bin/mysqld: ready for connections.
Version: '4.1.9-standard-log' socket: '/tmp/mysql.sock' port: 3306 Official MySQL-standard binary
Stack analysis is
0x808bab3 handle_segfault + 423
0x82db038 pthread_sighandler + 184
0x80bf200 add_key_field__FPP11key_field_tUiP4ItemP5FieldbPP4ItemUiUx + 296
0x80bf880 add_key_fields__FP13st_join_tablePP11key_field_tPUiP4ItemUx + 1288
0x80bf467 add_key_fields__FP13st_join_tablePP11key_field_tPUiP4ItemUx + 239
0x80bf3fd add_key_fields__FP13st_join_tablePP11key_field_tPUiP4ItemUx + 133
0x80bfdb7 update_ref_and_keys__FP3THDP16st_dynamic_arrayP13st_join_tableUiP4ItemUxP13st_select_lex + 139
0x80be4b9 make_join_statistics__FP4JOINP13st_table_listP4ItemP16st_dynamic_array + 1653
0x80bb6b9 optimize__4JOIN + 457
0x8075c66 exec__30subselect_single_select_engine + 70
0x80729ba exec__14Item_subselect + 38
0x8073789 val_int__17Item_in_subselect + 25
0x834f011 val_int_result__4Item + 21
0x805d608 val_int__17Item_in_optimizer + 52
0x8354761 val__13Item_int_func + 21
0x805c1cf val_int__13Item_func_not + 27
0x80d7a04 mysql_delete__FP3THDP13st_table_listP4ItemP11st_sql_listUlUl + 1332
0x809c896 mysql_execute_command__FP3THD + 8338
0x809f319 mysql_parse__FP3THDPcUi + 169
0x80999e8 dispatch_command__F19enum_server_commandP3THDPcUi + 1572
0x80993b8 do_command__FP3THD + 188
0x8098a87 handle_one_connection + 615
0x82d87ec pthread_start_thread + 220
0x83020ba thread_start + 4
How to repeat:
In my case, just re-run the script ... works (or not) every time
select '-- find all sessions still open from closed servers';
select session_server, session_id, session_user, session_ip, date_format(session_start,'%Y-%m-%e %H:%i:%s')
from CHEF_SESSION where SESSION_START = SESSION_END
and SESSION_SERVER not in
(select SERVER_ID from SAKAI_CLUSTER);
select '- find all open sessions more than 1 day old';
select session_server, session_id, session_user, session_ip, date_format(session_start,'%Y-%m-%e %H:%i:%s')
from CHEF_SESSION where SESSION_START = SESSION_END
and DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 12 HOUR) > SESSION_START;
select '-- and close them';
update CHEF_SESSION set SESSION_END = CURRENT_TIMESTAMP
where SESSION_START = SESSION_END
and DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 12 HOUR) > SESSION_START;
select '-- find all the presence records still around from closed sessions';
select * from CHEF_PRESENCE where SESSION_ID not in
(select SESSION_ID from CHEF_SESSION where SESSION_START = SESSION_END);
select '-- delete all the presence records still around from closed sessions';
delete from CHEF_PRESENCE where SESSION_ID not in (select SESSION_ID from CHEF_SESSION where SESSION_START = SESSION_END);
select '-- Done';
Suggested fix:
No idea, sorry
