Bug #26895 mysql restart automatically every a few minutes
Submitted: 7 Mar 2007 2:24 Modified: 8 Jul 2010 4:39
Reporter: song he Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.15-log, 5.0.33 OS:Linux (Linux version 2.6.9-5.ELsmp (bhc)
Assigned to: CPU Architecture:Any
Tags: MySQL, restart

[7 Mar 2007 2:24] song he
Description:
my mysql server keep restarting automatically every a few minutes.The following is the scrap from err file.

070306 19:04:34  mysqld restarted
070306 19:04:34 [Warning] Changed limits: max_open_files: 65535  max_connections: 16384  table_cache: 24570
070306 19:04:34 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysql1-relay-bin' to avoid this problem.
070306 19:04:34 [Note] /server/soft/mysql/libexec/mysqld: ready for connections.
Version: '5.0.15-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
070306 19:04:34 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000240' at position 555094562, relay log './mysql1-relay-bin.000640' position: 6689
070306 19:04:34 [Note] Slave I/O thread: connected to master 'repl@mysql4:3306',  replication started in log 'mysql-bin.000240' at position 555094562
 ^Hmysqld 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=6
max_connections=16384
threads_connected=2
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 196480 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0xa5c38d28
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=0xa5e4d12c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x815dd16
0x7758b8
(nil)
0x81af95e
0x81b7df2
0x81be376
0x81be9be
0x8174793
0x817bf04
0x817c908
Stack trace seems successful - bottom reached
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 0x90a2860 = select count(*) from `secondmarket_list` where  `verify`= 1 AND `Del`= 0  and `list_type` = '2'  and `district_id` = '10'
thd->thread_id=589
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

How to repeat:
when there's query requests,it happens very often.
[7 Mar 2007 2:27] song he
OS:Linux version 2.6.9-5.ELsmp (bhcompile@decompose.build.redhat.com) (gcc version 3.4.3 20041212 (Red Hat 3.4.3-9.EL4))

TOP: 

top - 10:24:58 up 12:06,  4 users,  load average: 1.25, 1.18, 1.18
Tasks:  96 total,   1 running,  95 sleeping,   0 stopped,   0 zombie
Cpu(s): 11.4% us,  6.3% sy,  0.0% ni, 82.1% id,  0.0% wa,  0.0% hi,  0.1% si
Mem:   8306616k total,  5481764k used,  2824852k free,    70632k buffers
Swap:  2031608k total,        0k used,  2031608k free,  5177728k cached
[7 Mar 2007 2:28] song he
# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
#socket          = /server/soft/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 1M
net_buffer_length = 16K
myisam_sort_buffer_size = 8M
#addnew config
wait_timeout = 120
back_log = 100
read_buffer_size = 1M
thread_cache = 32
skip-innodb
skip-bdb
#skip-name-resolve
join_buffer_size = 1M
query_cache_size = 32M
query_cache_type = 1
interactive_timeout = 120

set-variable = max_connections=16384
set-variable = max_connect_errors=500
log-slow-queries = slow_query.log
long_query_time = 5
#log-long-format

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 4
slave-skip-errors=all
[7 Mar 2007 7:09] Sveta Smirnova
Thank you for the report.

But MySQL 5.0.15 is quite old. Please try with current 5.0.33 and say us result.
[20 Mar 2007 6:59] Christopher Neill
I'm having a similar problem with 5.0.33 with FreeBSD 6.2-STABLE:

070319 23:36:36  mysqld restarted
070319 23:36:36 [Warning] Changed limits: max_open_files: 11095  max_connections: 750  table_cache: 5167
070319 23:36:37  InnoDB: Started; log sequence number 421 820663478
070319 23:36:37 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.33'  socket: '/data/mysql/mysql.sock'  port: 3633  FreeBSD port: mysql-server-5.0.33
070319 23:36:38  mysqld restarted

It was built under 6.2-RELEASE, I will do a reinstall with it built from the latest CVSup for /usr/ports now that I did a build/installworld for 6.2-STABLE and get back to you.
[7 Apr 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[9 Apr 2007 8:24] Sveta Smirnova
Thank you, Christopher, for the feedback.

Please provide your error log. Also upgrade to current 5.0.37
[9 May 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[8 Jun 2010 3:26] Sherman Adelson
I'm seeing a similar problem in a more recent mysql.

$mysql -V
mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1

From my log:

100521 20:56:42 [Note] /usr/libexec/mysqld: Normal shutdown

100521 20:56:42  InnoDB: Starting shutdown...
100521 20:56:45  InnoDB: Shutdown completed; log sequence number 1 1565181190
100521 20:56:45 [Note] /usr/libexec/mysqld: Shutdown complete

100521 20:56:45  mysqld ended

100521 20:56:46  mysqld started
100521 20:56:46 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
100521 20:56:46 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
100521 20:56:46 [Warning] option 'innodb_additional_mem_pool_size': signed value 512000 adjusted to 524288
100521 20:56:46  InnoDB: Started; log sequence number 1 1565181190
100521 20:56:46 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
100521 21:06:52 [Note] /usr/libexec/mysqld: Normal shutdown

100521 21:06:54  InnoDB: Starting shutdown...
100521 21:06:57  InnoDB: Shutdown completed; log sequence number 1 1565417749
100521 21:06:57 [Note] /usr/libexec/mysqld: Shutdown complete

100521 21:06:57  mysqld ended

100521 21:06:57  mysqld started
100521 21:06:57 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
100521 21:06:57 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
100521 21:06:57 [Warning] option 'innodb_additional_mem_pool_size': signed value 512000 adjusted to 524288
100521 21:06:57  InnoDB: Started; log sequence number 1 1565417749
100521 21:06:57 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
100521 21:09:59 [Note] /usr/libexec/mysqld: Normal shutdown

100521 21:10:01  InnoDB: Starting shutdown...
100521 21:10:04  InnoDB: Shutdown completed; log sequence number 1 1565460914
100521 21:10:04 [Note] /usr/libexec/mysqld: Shutdown complete

100521 21:10:04  mysqld ended

100521 21:10:04  mysqld started
100521 21:10:04 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
100521 21:10:04 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
100521 21:10:04 [Warning] option 'innodb_additional_mem_pool_size': signed value 512000 adjusted to 524288
100521 21:10:04  InnoDB: Started; log sequence number 1 1565460914
100521 21:10:04 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

Sometimes when mysqld restarts it doesn't shutdown properly:

100603  9:18:28  InnoDB: Started; log sequence number 1 1998605431
100603  9:18:28 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
100607 15:56:34 [ERROR] Can't create thread to kill server

Number of processes running now: 0
100607 19:28:08  mysqld restarted
100607 19:28:08 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
100607 19:28:08 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
100607 19:28:08 [Warning] option 'innodb_additional_mem_pool_size': signed value 512000 adjusted to 524288
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
100607 19:28:08  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...
100607 19:28:10  InnoDB: Started; log sequence number 1 2081730950
100607 19:28:10 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

Also it shutdown once and didn't restart. I had to start it back up manually. 

I hope this helps track down the problem.
[8 Jun 2010 4:39] MySQL Verification Team
@song: consider using 5.1.47 or 5.0.91 instead of 5.0.15 and see if "select count(*) from `secondmarket_list` where  `verify`= 1 AND
`Del`= 0  and `list_type` = '2'  and `district_id` = '10'" still crashes.

@Christopher: consider using 5.1.47 or 5.0.91 instead of 5.0.33.

@Sherman:  consider trying 5.1.47 or 5.0.91 also.  additionally, your shutdowns are "normal", such 
           as due to 'mysqladmin shutdown'.
           also, you have a badly mis-considered my.cnf, so please fix the wrong values in there.
           take note of each "[Warning]" in the error log to guide you.

@all: 5.1.47 is the current GA version!  if a crash happens we need a stack trace from mysqld.
      this bug is truly useless, since there is not a single piece of useful
      information to diagnose a crash, and no reason to believe all reports are similar in cause.
Add to my.cnf:

[mysqld_safe]
core_file_size=unlimited

[mysqld]
core-file

http://forge.mysql.com/wiki/MySQL_Internals_Porting#Debugging_a_MySQL_Server
http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_core-file
http://dev.mysql.com/doc/refman/5.0/en/mysqld-safe.html#option_mysqld_safe_core-file-size
[8 Jul 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".