6

MySQL is currently set to a wait_timeout of 28800 on our server which I understand to be the default. I've been attempting to change this to 60 as recommended by a consultant, but the changes don't seem to take for long. Here's what I've tried while logged into Linux as root and MySQL as a database user:

1) Logging into MySQL and:

show variables like '%wait_timeout%';
set wait_timeout=60;

2) Editing the my.cnf file under /etc and adding the following line to both [mysqld] and [mysqld_safe]:

wait_timeout=60

When I try #1 and choose to show the wait_timeout variable right after, it shows correctly as 60. However, if I wait a minute or two, it reverts back to 28800 again. When I try #2, the option doesn't seem to take at all, even after restarting Apache and MySQL.

The my.cnf seems to be the only such version of the file that I can find.

I've tried the following, but no arguments show:

mysql --print-defaults

When restarting Apache, I've been using:

/usr/sbin/apachectl graceful

When restarting MySQL, I've been using:

/etc/init.d/mysqld restart

Any ideas? :-(

3 Answers 3

10

I'm afraid you might have run into one of the several gotchas of MySQL. See this bug report.

If I have understood everything right, the MySQL command line client causes mysqld to use interactive_timeout instead of wait_timeout.

What does the following query return to you?

SELECT @@global.wait_timeout, @@session.wait_timeout;

2
  • 1
    Thanks. This returns the following: @@global.wait_timeout = 60, @@session.wait_timeout = 28800. If I wanted to change the session wait_timeout, how would I go about doing that?
    – MattB
    Nov 9, 2010 at 19:43
  • You do not know my pain, I was trying to fix this from 4 hrs, I even reconfigured instance for this. You saved me. Thank you. I was only trying the wait_timeout and did not read from my.ini file, but interactive_timeout setting did the job.
    – surpavan
    Apr 17, 2021 at 18:05
2

1. Edit my.cnf (the MySQL configuration file).

Ubuntu 16.04

 sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Debian

 sudo vi /etc/mysql/my.cnf

Centos

 sudo vi /etc/my.cnf

2. Locate the timeout configuration and adjust it to fit your server.

[mysqld]
wait_timeout = 31536000
interactive_timeout = 31536000

3. Save the changes and exit the editor.

4. Restart MySQL to apply the changes as follows:

sudo /etc/init.d/mysql restart
0

Apache isn't involved with this process so you can skip anything dealing with it.

As for MySQL, have you tried looking to see if there are other my.cnf files that may be overriding the base one? Also, instead of restarting actually shutdown the MySql server and then start it. It may be that you have a thread or two that stays open causing the value to revert.

1
  • There are no other my.cnf files. I have not tried restarting the server itself or stopping/starting MySQL, but it sounds like this may be something to try. So far I've only used "restart".
    – MattB
    Nov 9, 2010 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.