Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Dec 22 '12 at 18:34

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
I did restart mysql, i then restarted my whole machine, but the result is still the same. I also tried setting the value in the mysql console, but to no avail. –  Ibolit Dec 23 '12 at 11:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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