MySQL, wait_timeout, Mac OS X Mountain Lion - how do i set it?

Question

I need to change the wait_timeout for my MySQL database, but when I set this parameter in the my.cnf class (either in /etc or in /private/etc, because one of them is a link to the other), I still get the same default value when I type show variables in MySQL.

How do I change that value on Mac OS X then?

UPDATE I tried setting the value using mysql console, but it didn't help:

mysql> set global wait_timeout = 333;
Query OK, 0 rows affected (0.00 sec)


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

It did work without the word "global", though, but that affects only the current connection.

Answer 1

I test your case on my mac air. 1.Just modify the session variable wait_timeout=50

mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+
3 rows in set (0.00 sec)
mysql> set wait_timeout=50;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 50       |
+--------------------------+----------+
3 rows in set (0.00 sec)

2.If you want change the global variable,there two ways.one is run command in console ,the other is edit the file my.cnf and restart your mysql service.

mysql> set global  wait_timeout=51;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

Exit and log in mysql again. mysql> show variables like '%wait_timeout%';

+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+
3 rows in set (0.00 sec)

Here is the question,why it doesn't works? in fact, you will get it while you meet another variable named interactive_timeout. for detail ,if a interactive client connect to mysql ,the timeout value is initialized from value of global interactive_timeout.Here's all the values:

mysql> show variables like '%_timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+

Hope it's helpful.

Answer 2

I believe you're running into a few different problems:

1) For interactive clients like the mysql client program (and not for most web applications), session wait_timeout is initialised from interactive_timeout when your client connects, so changing the global wait_timeout has no effect on the effective wait_timeout for your mysql client. This is covered in the MySQL documentation here:

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.

2) You're changing a global setting (set global ...) and then looking at session variables ('show variables' instead of 'show global variables')

3) Session variables are only reinitialised from global variables when you reconnect

For the solution, the following should set the effective wait_timeout for your mysql client to 333:

set global wait_timeout=333
set global interactive_timeout=333

Then disconnect.

To persist the changes across restarts, you can add the following to the [mysqld] section of my.cnf:

wait_timeout=333
interactive_timeout=333

If the reason you're making these changes is because of a problem a client that doesn't set CLIENT_INTERACTIVE is having, then you can disregard interactive_timeout.

Before making changes, I recommend reviewing this blog post and the MySQL documentation to ensure you understand how these settings function and the impact of changing them.

Answer 3

I'm assuming you did not restart mysql (which would have picked up the new value from the config file), so you probably just have to change the run time value via the mysql command line:

set global wait_timeout = $value;

Hope that helps.