MySQL Bugs: #11985: timeout doesn't work as expected

Bug #11985	timeout doesn't work as expected
Submitted:	16 Jul 2005 20:52	Modified:	12 Dec 2005 12:12
Reporter:	[ name withheld ]	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.24	OS:	Linux (Gentoo)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Sleeping threads stays connected after timeout:

mysql> show processlist;
+--------+------+-----------+------+---------+------+-------+------------------+
| Id     | User | Host      | db   | Command | Time | State | Info             |
+--------+------+-----------+------+---------+------+-------+------------------+
| 119430 | x | localhost | x | Sleep   | 1063 |       | NULL             |
| 129225 | x | localhost | x | Sleep   | 210  |       | NULL             |
| 131206 | x | localhost | x | Sleep   | 22   |       | NULL             |
| 131361 | root | localhost | NULL | Query   | 0    | NULL  | show processlist |
| 131369 | x | localhost | x | Sleep   | 8    |       | NULL             |
| 131442 | x | localhost | x | Sleep   | 1    |       | NULL             |
| 131443 | x | localhost | x | Sleep   | 0    |       | NULL             |
| 131444 | x | localhost | x | Sleep   | 0    |       | NULL             |
| 131445 | x | localhost | x | Sleep   | 0    |       | NULL             |
| 131446 | x | localhost | x | Sleep   | 0    |       | NULL             |
| 131448 | x | localhost | x | Sleep   | 0    |       | NULL             |
| 131449 | x | localhost | x | Sleep   | 0    |       | NULL             |
+--------+------+-----------+------+---------+------+-------+------------------+
12 rows in set (0.00 sec)

mysql> show variables like '%timeout%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| connect_timeout        | 10    |
| delayed_insert_timeout | 300   |
| interactive_timeout    | 10    |
| net_read_timeout       | 30    |
| net_write_timeout      | 20    |
| slave_net_timeout      | 10    |
| wait_timeout           | 10    |
+------------------------+-------+
7 rows in set (0.00 sec)

In my my.conf there are:
[mysqld]
set-variable    = connect_timeout=10
set-variable    = wait_timeout=10
set-variable    = interactive_timeout=10
set-variable    = slave_net_timeout=10
set-variable    = net_write_timeout=20
[...]

The same config has been working on FreeBSD without this problem. This seems to be Gentoo or Linux-releated.

How to repeat:
Install MySQL port from Gentoo portage tree.

#uname -a
Linux server.foo 2.6.12 #4 SMP Thu Jul 14 14:12:31 CEST 2005 i686 Intel(R) Xeon(TM) CPU 2.40GHz GenuineIntel GNU/Linux

Suggested fix:
unknown

we need some way to reproduce it. Can you suggest us  testcase? What clients did you use for that connections? Could you try binaries  from our web site (not gentoo port ) ?

We have standard mysql client Ver 12.22 Distrib 4.0.24 and php 4.3.11 compiled with "--with-mysql=/usr". This problem doesn't exist in Kernel 2.4.x with linux_threads. We can reproduce this bug with 100% efficiency on 2.6.x kernel with nptl threads. Please write what else do you need to know to look into this issue.

I'm sure MySQL binary from your website will work good, because I guess It comes with linux_threads or without any multi-threads.

I've checked one more thing todays: timeouts works in 2.6.x kernel with linux_threads, so problem is caused by using Native POSIX Thread Library for Linux.

duplicate http://bugs.mysql.com/15679

Hi!

I have the same problem with Linux 2.6.14.5 (patched with grsec) and MySQL 4.0.24 using Debian package mysql-server (4.0.24-10sarge1). We used the same MySQL binary with Linux 2.4 kernel on our webserver but the problem started when we changed to Linux 2.6. At the same time MySQL seemed to start using threads because now there is only three processes running.

I experienced the same problem on a dual-processor Dell PE2850 running kernel 2.6.13.

Reverting the kernel to 2.4.29 resolves the problem.

If you're seeing this problem please comment on bug #11897 instead. That's the one where active work is going on. Also note that bug #9678 fixed in 4.0.28, 4.1.22, 5.0.25 and 5.1.12 might have fixed this, so try those or later versions and comment abotu whether it works in bug #11897.