Bug #988 crash(restart) with FLUSH QUERY CACHE
Submitted: 4 Aug 2003 1:00 Modified: 16 Oct 2003 13:18
Reporter: thomas herzog Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0 OS:Any (any)
Assigned to: Oleksandr Byelkin

[4 Aug 2003 1:00] thomas herzog
Description:
i have a cronjob with:
20 * * * * /usr/local/mysql/bin/mysql -u root -p<my_pw> < $HOME/mysql_flush_query_cache.sql > flush.log 2>&1

in the mysql_flush_query_cache.sql are only:
FLUSH QUERY CACHE;

and sometimes the server restarts with following in the errorlog:
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=402653184
read_buffer_size=2093056
max_used_connections=32
max_connections=300
threads_connected=11
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1620813 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x5a32ad78
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=0xbf1ff268, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80d9a90
0x4003ad29
0x8152813
0x81525bb
0x8150fe9
0x80e91bd
0x80e71f2
0x80e7fba
0x80e3483
0x80e2edd
0x80e26ce
0x40035a19
0x401bf437
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/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 0x85bb650 = FLUSH QUERY CACHE
thd->thread_id=17419
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: 2
030804 00:20:01  mysqld restarted
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.14-max-log'  socket: '/tmp/mysql.sock'  port: 3306

the my.cnf entries for mysqld:
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 15M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
max_connections=300
server-id = 1
log-bin
skip-bdb
skip-innodb

server:
1033444k memory
16G SCSI RAID > 9.5G free
32.3 Queries per second avg

How to repeat:
run the cronjob

Suggested fix:
turn off the cronjob
[4 Aug 2003 4:45] Sinisa Milivojevic
I have run cron job each 5 minutes and after one hour still everything runs 
smoothly.
[4 Aug 2003 4:58] thomas herzog
it crashes average once a day,
and the server is clean and new

i have changed the cronjob time setting from
*/20 -> 20 * to now -> 20 */2

i will add the whole errorlog, i hope it helps ;O)
[4 Aug 2003 4:59] thomas herzog
the errorlog

Attachment: early.err (application/octet-stream, text), 51.70 KiB.

[12 Aug 2003 20:55] Boyd Gerber
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

I have had a cron job run it every 10 minutes for 18 hours and I have not been able to get a failure.  Is there any thing else you can add to duplicate this problem?
[18 Aug 2003 0:13] thomas herzog
i have changed my cronjob setting to */20
and set query_cache_type = 0 to our high loaded forum
the other websites have query_cache_type = 1
for now its run smoothly for 4 days
sorry
[18 Aug 2003 0:26] thomas herzog
addition:
also changed
php.ini -> mysql.max_persistent = 5 (before -1) -> typo3 use persistent connections

/etc/my.cnf now:

skip-locking
key_buffer = 256M
max_allowed_packet = 15M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

query_cache_type = 0
max_connections=150
server-id = 1
log-bin
[15 Sep 2003 3:55] Oleksandr Byelkin
Thnks to "Jocelyn Fournier" <joc@presence-pc.com> (who provided me core dump 
and mysql binary file, Now I am able to repet this crash, and I hope problem 
will be solved in short time.  
 
set GLOBAL query_cache_size=1048576; 
flush query cache; 
reset query cache; 
flush status; 
--disable_warnings 
drop table if exists t1,t2,t3,t4; 
--enable_warnings 
 
create table t1 (a int not null); 
insert into t1 values (1),(2),(3); 
create table t2 (a text not null); 
create table t3 (a text not null); 
insert into t3 values("1111111111111111111111111111111111111111111111111111"); 
insert into t2 select * from t3; 
insert into t3 select * from t2; 
insert into t2 select * from t3; 
insert into t3 select * from t2; 
insert into t2 select * from t3; 
insert into t3 select * from t2; 
insert into t2 select * from t3; 
insert into t3 select * from t2; 
insert into t2 select * from t3; 
insert into t3 select * from t2; 
drop table t2; 
create table t2 (a int not null); 
insert into t2 values (1),(2),(3); 
create table t4 (a int not null); 
insert into t4 values (1),(2),(3); 
 
select * from t4; 
select * from t2; 
select * from t1 as tt, t1 as ttt  where tt.a=1 and ttt.a=2; 
select * from t2; 
select * from t4; 
select * from t1 as tt, t1 as ttt  where tt.a=1 and ttt.a=2; 
select * from t2; 
select * from t4; 
select * from t1 as tt, t1 as ttt  where tt.a=1 and ttt.a=2; 
 
delete from t2 where a=1; 
flush query cache; 
select * from t3; 
delete from t4 where a=1; 
flush query cache; 
 
drop tables t1,t2,t3,t4;
[15 Sep 2003 6:14] Oleksandr Byelkin
ChangeSet 
  1.1570 03/09/15 15:16:13 bell@sanja.is.com.ua +4 -0 
  fixed server crash on moving query block with pointers to same table in it 
(BUG#988) 
  removed server options
[16 Oct 2003 13:18] Oleksandr Byelkin
Patch is pushed in current 4.0 sources repository and will be present in next 
releases of 4.0 and 4.1