MySQL "wait_timeout" option won't save

Question

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? :-(

Answer 1

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;

Answer 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

Answer 3

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.