MySQL Bugs: #33123: wait_timeout ignored in /etc/my.conf

Bug #33123	wait_timeout ignored in /etc/my.conf
Submitted:	10 Dec 2007 20:46	Modified:	1 Aug 2009 7:17
Reporter:	Thomas Okken	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux (x86_64-icc-glibc23)
Assigned to:		CPU Architecture:	Any
Tags:	wait_timeout

Description:
I'm trying to set wait_timeout to something higher than the default value of 28800 seconds. When I do this interactively in the mysql command line client, it works, but when I try to make this permanent by modifying /etc/my.cnf, the setting is ignored.

How to repeat:
My /etc/my.cnf looks like this:

[mysqld]
basedir=/opt/mysql/server
character_set_server=utf8
wait_timeout=100000

But when I restart the server, and then query the wait_timeout variable in the mysql command line client, I still get the old value:

[mysql@ss-1 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

mysql>

Thank you for the report.

Please provide output of show global variables like '%timeout%';

[mysql@ss-1 ~]$ mysql -u root -p

[...]

mysql> show global variables like '%timeout%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| connect_timeout            | 5      |
| delayed_insert_timeout     | 300    |
| innodb_lock_wait_timeout   | 50     |
| innodb_rollback_on_timeout | OFF    |
| interactive_timeout        | 28800  |
| net_read_timeout           | 30     |
| net_write_timeout          | 60     |
| slave_net_timeout          | 3600   |
| table_lock_wait_timeout    | 50     |
| wait_timeout               | 100000 |
+----------------------------+--------+
10 rows in set (0.00 sec)

mysql> show variables like '%timeout%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 5     |
| delayed_insert_timeout     | 300   |
| innodb_lock_wait_timeout   | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout        | 28800 |
| net_read_timeout           | 30    |
| net_write_timeout          | 60    |
| slave_net_timeout          | 3600  |
| table_lock_wait_timeout    | 50    |
| wait_timeout               | 28800 |
+----------------------------+-------+
10 rows in set (0.00 sec)

mysql>

Please, send the entire /etc/my.cnf, including [mysql] and other sections.

/etc/my.cnf

Attachment: my.cnf (application/octet-stream, text), 81 bytes.

Thank you for the feedback.

Do you start mysqld with help of some script?

My MySQL start script

Attachment: mysql (application/octet-stream, text), 10.89 KiB.

I copied the mysql-5.0.45-linux-x86_64-icc-glibc23/support-files/mysql.server script to /etc/rc.d/init.d/mysql, and created the appropriate links to have it started in runlevels 2, 3, and 5. I attached a copy of that script for reference. Note that I used the script straight from the MySQL package, with no modifications.

Is there any update on this issue? I am also hitting the similar issue. 
I set global wait_timeout, close the session and reconnect to MySQL. I get the new global wait_timeout but session wait_timeout is reset to default. The variable set as global should be visible to new connection as session variable? Is this correct assumption? I see otherwise in MySQL 5.0.45.

Thanks

Sandeep Dubey

Please, specify how you check wait_timeout value for a new session? Using mysql client? Please, read the manual also, http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_wait_tim...:

"On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout."

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".

Valeriy, I checked the wait_timeout value using the mysql client, as described in my message dated 12 Dec 2007 16:20. I added the line "wait_timeout=100000" in /etc/my.cnf; I provided my version of /etc/my.cnf with my message dated 31 Dec 2007 15:59; I provided my version of the mysqld start script, which is simply an unmodified copy of BASEDIR/support-scripts/mysql.server.

I have read the manual page you mentioned, and many other manual pages and mailing list articles as well, but I have found nothing to indicate why setting wait_timeout in /etc/my.cnf would fail to take effect in new sessions. In fact, the manual indicates that setting of the global wait_timeout (which works, see the second set of results in my message dated 12 Dec 2007 16:20) would be used as the default for new sessions, but in fact, new sessions get wait_timeout set to 28800, and I am at a loss as to where that value comes from, and why it doesn't use the one that I provide.

Thank you for the feedback.

Please provide output of SELECT @@global.wait_timeout, @@session.wait_timeout;

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".

I have th same problem with version 5.0.51a-3ubuntu5.4
 on linux. Here is the output of your last inquiry :

mysql> SELECT @@global.wait_timeout, @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                    30 |                  28800 | 
+-----------------------+------------------------+
1 row in set (0.00 sec)

mysql> 

As you see, I set it to 30, but the session timeout is set back to the default value. I restarted the server of course.

ok, io get it, since I am using an interactive client, it uses the interactive_timeout instead.

Gabriel,

thank you for the feedback. So in your case this is not a bug.

Setting to "Need feedback" while waiting answer from Thomas.

When I run Sveta's query in the mysql CLI, I get this:

mysql> select @@global.wait_timeout, @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                100000 |                  28800 | 
+-----------------------+------------------------+
1 row in set (0.00 sec)

mysql> 

When I run it over a JDBC connection (using the same user and database), both values are 100000.

So, it looks like I'm OK, too, after all; I didn't realize that the timeouts were different in the CLI.

Thomas,

thank you for the feedback.

Set to "Not a Bug", because last comment..