mysql variable resets after exit

Question

The web interface for accounting / time tracking software (Clients and Profits X) requires a open connection to its mysql database. Right now the web interface is not used on a regular basis and this times out. When someone DOES want to use the web interface it is broken at that point until I restart the program.

I am trying to set the max_timeout in mysql on a OSX Snow Leopard server to 5 days but it keeps resetting to the default of 8hrs. You can see from the following code snippits that I set both the global and session variables but after I exit and log back into mysql the session variable is reset. I do not pretend to be an expert with SQL so I may just be being stupid. This is on a OSX SL Server although I do not know if its specific to this platform or not.


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

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

mysql> SHOW VARIABLES LIKE 'wait%';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| wait_timeout  | 432000 | 
+---------------+--------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'wait%';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| wait_timeout  | 432000 | 
+---------------+--------+
1 row in set (0.00 sec)

mysql> exit
Bye
osxserver:~ admin$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.92-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE 'wait%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'wait%';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| wait_timeout  | 432000 | 
+---------------+--------+
1 row in set (0.00 sec)

I have the wait_timeout set in /etc/my.cnf under [mysqld] although I am unsure if this sets the session variable or the global variable


server:~ admin$ grep wait_timeout /etc/my.cnf
wait_timeout = 432000

I would highly suggest that you contact the vendor and ask them to correct the issue. First, keeping an open connection for literally days from a website to the db server is a bad idea for performance and other reasons. Second, the site itself should be reestablishing the connection whenever the pages are loaded... This is trivial to accomplish. What you've described is NOT common practice and I'm going to guess might be just a mix up in an understanding of the actual problem. — NotMe, Oct 07 '11 at 15:52
I realize this is bad form. Unfortunately its not a mix up. I was told by the vendor this was the problem and that this is how to resolve it. I am not a fan of C&PX at all. — digitaladdictions, Oct 07 '11 at 16:04
The web interface for the program can be ran from any client. So my "Server" is just a mac running the C&PX client like any other user would. I have to leave the GUI up with the client running for the web server to be running. — digitaladdictions, Oct 07 '11 at 16:08
For those wanting a link to C&P's requirements: http://www.cnp-x.com/my/server_recs.html I don't really have a response for their "performance notes" section. — NotMe, Oct 07 '11 at 16:36

score 1 · Answer 1 · answered Oct 07 '11 at 16:14

1

You could try using the init_connect trigger to set the value on each connect

example here http://shinguz.blogspot.com/2010/02/logging-users-to-mysql-error-log.html

answered Oct 07 '11 at 16:14

Miademora

146
3

score 1 · Accepted Answer · answered Oct 07 '11 at 16:39

You might look through this bug report on mysql.

http://bugs.mysql.com/bug.php?id=33123

It highlights several ways to check the timeout settings.

However, the main thing to take away from the link is that the wait_timeout can be overridden by the connection string used to connect to the database server.

In the case of the interactive client, it sets the session wait_timeout to 28,800. Effectively overriding your config settings. So that can't be used as a "test" to see if it's working.

As long as C & P X isn't overriding that value in their connection string then you ought to be okay.

The program still stops responding overnight but with this info I can go back to the vendor and claim I set the wait_timeout as they suggested and move on to other troubleshooting. — digitaladdictions, Oct 07 '11 at 16:48

score 0 · Answer 3 · answered Oct 04 '13 at 18:55

Your client's .my.cnf might have a wait_timeout that re-initializes the timeout back to 8 hours when you re-login, effectively overriding the global value for that new session. Also, since your example uses the mysql shell, it might set your session wait_timeout to interactive_wait_timeout. This value can come from the global settings, or once again, from your client's .my.cnf.

mysql variable resets after exit

3 Answers3