MySQL Bugs: #40842: Serious freeze in Mysql 5.1.x versions

Bug #40842	Serious freeze in Mysql 5.1.x versions
Submitted:	19 Nov 2008 8:50	Modified:	14 Apr 2009 17:34
Reporter:	Jerry Potokar	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.1.x	OS:	Windows (windows server 2003)
Assigned to:		CPU Architecture:	Any
Tags:	freeze lock

Description:
I am runnging 5.0.67 with no problems, but every time I upgrade to some of the 5.1. releases in 24 hours my mysql freezes and I get too many connections. After trying to quit it never stops, I have to reboot.

I can not see what is wrong. I have a huge site, mysql is working quite much, but it has to be something seriously wrong with all 5.1 releases I've tried up to 5.1.29 because my site is running fine on every 5.0 release I've tried. It runs for months with no problem. But every time I upgrade I get a too many connections error. I try to look into the connections and there are about 200 of them (this is my limit in ini) and all are waiting indefenitely.

I am running Windows 2003 with myODBC 5.1, using ASP on my site. I have used 3.51 releases of myOdbc with no luck either.

I am sorry I can not be more of help, please tell me how to log things so I can see what triggers the lock. it happens about 12-24 hours after I upgrade to 5.1. I update all the tables and I am using just the basic stuff, select, update and insert, no replication and other more advanced stuff.

All my pages are utf-8, all my tables are utf-8.

I've also upgraded my other server that has 20 not so intensive sites and everything is fine. I know it has to be down to some of my code on this site, but it shouldn't freeze like this in any occasion I guess.

I've moved my site to a brand new server but same thing happens, so it is a Mysql 5.1.x bug not a server misconfiguration I think.

If you can help me in what I should check to nail this thing, please let me know.

Jernej

How to repeat:
I have no idea :-(

Please, provide some more information about the situation. Send my.ini, upload your error log from problematic 5.1.x server, send SHOW GLOBAL STATUS, SHOW PROCESSLIST and SHOW INNODB STATUS at the moment of freezing.

Here is my.ini. For other files I'll change my server again to mysql 5.1.29 and when it freezes I'll send you other data. How do I log errors?

# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is C:\mysql\data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password	= your_password
port		=3306
socket		= /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]

replicate-do-db = kulinarika
log-warnings
ft_min_word_len=3

basedir=C:\\Program Files\\SWsoft\\Plesk\\Databases\\MySQL
datadir=C:\\Program Files\\SWsoft\\Plesk\\Databases\\MySQL\\Data
default-character-set=utf8
default-storage-engine=MYISAM

log-slow-queries
long_query_time=1
#log-queries-not-using-indexes

port		=3306
socket		= /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size =256K
read_buffer_size =1M
read_rnd_buffer_size =256K
myisam_sort_buffer_size = 64M
thread_cache_size =4
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency =4

# 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	= 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir		= /tmp/		
#log-update 	= /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=16M
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=500
wait_timeout = 900
old_passwords=1
max_user_connections=50
thread_stack=196608
binlog_cache_size=32768
net_read_timeout=30
net_retry_count=10
net_write_timeout=30
open_files_limit=0

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

I am using myISAM only no InnoDB.

Error log is just a <hostname>.err file in the datadir (C:\Program Files\SWsoft\Plesk\Databases\MySQL\Data in your case).

If you are not going to use row-based logging any time soon (new feature of 5.1.x, see http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html) you may ignore that warning. Do you (plan to) use binary logging for replication or point-in-time recovery?

I'll not use binary logging any time soon. I need to have a working Mysql 5.1 first ;-/

Thanx, as you see all the queries get locked and whole mysql freezes. I am not claiming every my query is 100% optimised, but I think it should not lock this way. I am using mySql since version 4 and all the new versions worked. This time every 5.1. release there was has this problem. I am sorry I didn't report this sooner.

Have you found anything in regard to this bug?

I have sent everything you asked for. I would expect some feedback on your part :-(

It's nice to have a feeback from you :-(

Sorry for a delay with this bug report. This slow query:

102	kulinarika	localhost:1789	kulinarika	Query	331	Copying to tmp table	SELECT
o.uporabnik, o.email as emailobiskovalci, r.ImeRecepta, r.KratekOpis, r.datum,
r.kategorija, 

(not complete query, just a part of it) was a reason of your freeze. Then UPDATE on the same MyISAM table happened and all other, later, SELECTs to the same table where forced to wait in queue after UPDATE. So, the problem was related to a different query plan in 5.1.29 v.s 5.067 that led to slow query execution, I think.

Please, try to use 5.1.30 GA version. In case of the same problem, please, check what query is NOT locked in SHOW FULL PROCESSLIST, and send the EXPLAIN results for it.

Hm, maybe you are right, but the server totaly freezed, I waited minutes and nothing happened :-( The truth is the server is very busy.

we have exactly problem on freebsd 7 64bit, and mysql 5.1.30.
i see one UPDATE request which is doing nothing and lock all the new requests until it reaches the max connections. when it's completly full, the system is just
doing nothing. mysql.server stop does not work and i have to kill all mysql processes and start again.

actually, non-locked requests are SELECT with either the state: freeing items, or sending datas

Stéphane,

Please, send the results of SHOW FULL PROCESSLIST at the moment when you have this problem.

Thank you for the feedback.

> I'll not use binary logging any time soon. I need to have a working Mysql 5.1 first ;-/

Could you please turn binary log off and try if it solves the problem?

Ohhh, disabling binary log did the trick! It is running 36 hours and before it froze in 1-2 hours!!!

But... can you fix it? Do you know what causes this? You have an idea because you suggested this? I need binary logging for replication (standard one).

Well after 36 hours and just after I wrote you the copy tmp table froze again :-( I have reverted back to 5.0.67 -(

Please see bug: http://bugs.mysql.com/bug.php?id=42907 maybe your server is queried with that kind of queries. Thanks in advance.

Not that I am aware of, no. In my language we don't use apostrophes in searches like '. I do use mysql full text search in binary mode on myisam tables and UTF-8 is my charset. Everything else matches...

Jerry, et al, is this still happening in 5.1.32?

I did not install it, I read documentation and I haven't find any info that anything regarding that issue is fixed.

Jerry, please turn off the query cache with SET GLOBAL query_cache_size = 0; and tell us whether it solves your problem over in bug #41901. We're currently working to produce a test case for a bug with occasional hangs when the query cache is turned on.

If you'd like to check whether you're possibly affected use:

gdb -p <pid of mysqld>
set pagination off
thread apply all bt

and look for lots of threads waiting on
Query_cache::wait_while_table_flush_is_in_progress

If you can't confirm that yourself and if you have tried turning off the query cache and that hasn't helped, you might want to upload the gdb output so one of our bugs team members can take a look.

I tried 5.1.32, still freeze on copying tmp tables, I had to reboot server. 5.0.77 seems to work. All 5.0 releases are working and none 5.1.x releases are not working. :-((( I'll never be able to upgrade to mysql 5.1 :-(

EI can not make query cache to 0 as my page is high traffic and query cache helps too much to be able to set it to 0, sorry.

Setting query cache to 0 stops the problem with "freeing items" but there is still a problem where threads will lock up with status "sending data".

This seems to often occur on generally slow queries (1 - 8 seconds) and when another thread is inserting/updating/deleting to one of the tables being queried.

Jerry,

1. Try turning off the adaptive hash index. That can sometimes cause a temporary lockup when temporary tables are being used but we thought that had been fixed. Always possible that you've found a new way or that the fix isn't complete.

2. If you still see the problem, use the GDB commands I gave earlier at a time when the server is locked and upload the output so one of our bugs team members can take a look.

I am using ONLY MyISAM tables! Is there any hope for fix in 5.1.33 that is now out?

Jerry, in 5.1.33 there are fixes to several possible causes of crashes so you  should try 5.1.33. If that doesn't solve the problem to you then the GDB commands are the way to go so we can stop guessing and instead see what your server is really doing. Also, what is the output of these two commands when the server has been running for quite a long time:

SHOW VARIABLES LIKE 'qc%';
SHOW VARIABLES LIKE 'uptime';

Your earlier output showed only 16 queries from the query cache per second and that's not enough for turning it off for a little while to check that it is not the cause of a problem to cause performance issues, unless your server is extremely underpowered for its load. We expect normal servers to be able to handle thousands of selects per second, so 16 is usually insignificant.

Ian, sending data includes InnoDB sending data to the core server to prepare for sorting, so it's not inherently unexpected to see that. However, there was an area we improved in 5.0 where InnoDB would hold a lock on the adaptive hash index for too long while doing that, so you should try turning off the adaptive hash index to see if that solves your problem. If it doesn't, turn on the innodb lock monitor and check SHOW ENGINE INNODB STATUS to see whether a traditional row lock is involved. If it's not a normal row lock that is blocking the work, then it'll be time to file another bug with SHOW ENGINE INNODB STATUS and GDB output so we can see what's happening; you should also report that turning off the adaptive hash index didn't help, so we don't need to suspect that.

I have to learn how to use GDB commans, I am just a mysql user, so that is new field for me.

I'll try turning off the query cache and let you know.

Is GDB available on Windows? I only read about Linux version.

The debugger for Windows is WinDBG you can get it at:

http://www.microsoft.com/whdc/devtools/debugging/default.mspx

notice there are packages for 32-bit and 64-bit.

If you type "SET GLOBAL query_cache_size = 0;" if your server is locked, it will start working again. No need to restart the mysqld process.

This looks like a duplicate of this bug report:
http://bugs.mysql.com/bug.php?id=43758

Marking it accordingly.