1

How can I set up MySQL so that connections never time out? And how do I even check to see what the current settings for variables such as wait_timeout and interactive_timeout, etc. are? Given that these variables are not defined in the config shown below.

Here is the situation:

A low (micro) traffic web server sometimes does not get a database transaction for a couple of weeks. Mysql is running on the same server box as are a few private web applications. I set autoReconnect=true in the connection string, but when I check back days later, I always notice that the web application cannot connect to the database, and when I open the catalina.out log, it gives the following:

Caused by: java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite0(Native Method) ~[na:1.7.0_75]
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113) ~[na:1.7.0_75]
    at java.net.SocketOutputStream.write(SocketOutputStream.java:159) ~[na:1.7.0_75]
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) ~[na:1.7.0_75]
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) ~[na:1.7.0_75]
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3969) ~[mysql-connector-java-5.1.27.jar:na]
    ... 111 common frames omitted

I then restart tomcat and the webapps are able to make database connections again, but the problem repeats, and the long periods involved mean that it is not reasonable to test waiting days to see when the broken pipe error will resurface.

I have read many postings about setting max timeout for mysql, including the tutorial at this link, but they describe variables that are not defined in the conf files on my machine. For example, the mysql config files on the server in question are:

/etc/my.cnf is:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

As you can see, the file includes the contents of the my.cnf.d folder, which are three files, as follows:

/etc/my.cnf.d/client.cnf is:

#
# These two groups are read by the client library
# Use it for options that affect all clients, but not the server
#

[client]

# This group is not read by mysql client library,
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]

/etc/my.cnf.d/mysql-clients.cnf is:

#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#

[mysql]

[mysql_upgrade]

[mysqladmin]

[mysqlbinlog]

[mysqlcheck]

[mysqldump]

[mysqlimport]

[mysqlshow]

[mysqlslap]

/etc/my.cnf.d/server.cnf is:

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]

# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

[mariadb-5.5]

Tomcat's server.xml defines timeout as 20 seconds (20,000 milliseconds), which obviously does not refer to MySQL's global wait_timeout. But the only reference to a timeout in /opt/tomcat/conf/server.xml is as follows:

<Connector port="8082" protocol="HTTP/1.1"
           connectionTimeout="20000"
           redirectPort="8445" />

I confirmed that the 20000 connectionTimeout setting refers to milliseconds by reading this link.

I read this other posting about settings from the spring mvc application level, but the answers refer to setting application level variables that are less than the my.cnf settings. I hesitate to fiddle with the application level if I cannot at least see the server level settings, which are not in the versions of my.cnf and include files shown above. Nonetheless, my dataSource definition in the spring mvc web app xml config is:

<context:property-placeholder location="classpath:spring/data-access.properties" system-properties-mode="OVERRIDE"/>

<!-- DataSource configuration for the tomcat jdbc connection pool -->
<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource"
      p:driverClassName="${jdbc.driverClassName}" p:url="${jdbc.url}"
      p:username="${jdbc.username}" p:password="${jdbc.password}"/>  

Finally, the variables referenced in the above dataSource bean are defined in a property file (data-access.properties) as follows:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/dbasename?autoReconnect=true
jdbc.username=username
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.MySQLDialect
jpa.database=MYSQL
jpa.showSql=false

So how do I set up MySQL so that connections never time out? And how do I even check to see what the current settings for variables such as wait_timeout and interactive_timeout, etc. are? Given that they are not in the config files shown above.


EDIT:


As per @RickJames' suggestion, I edited /etc/my.cnf to become the following:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
wait_timeout = 2147483
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

I then stopped and restarted the database by typing systemctl stop mariadb.service, followed by systemctl start mariadb.service. But when I subsequently logged into MySQL and followed @Verace's suggestion by typing show variables like '%timeout%';, the result was:

+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| deadlock_timeout_long      | 50000000 |
| deadlock_timeout_short     | 10000    |
| 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     |
| thread_pool_idle_timeout   | 60       |
| wait_timeout               | 28800    |
+----------------------------+----------+
13 rows in set (0.00 sec)

What am I doing wrong?

3
  • In the [mysqld] section add wait_timeout = 99999999 (some large number). – Rick James Apr 7 '15 at 1:49
  • Since it is a program connecting, interactive_timeout is probably not relevant. – Rick James Apr 7 '15 at 1:50
  • @RickJames The max setting for wait_timeout is 2147483. Does this evaluate to a little over 24 days? How do you handle the situation in which no one submits data into the web form of a small traffic site for arbitrarily longer than 25+ days? – CodeMed Apr 7 '15 at 3:09
3

It is bad practice to sit there connected for arbitrarily long. It is better to have wait_timeout be something more like 30 (seconds) and write code to deal with disconnects. Reconnecting to MySQL is quite quick, so it is not a performance issue. If the app is idle for (say) 30 seconds, it should expect to get an error and have to reconnect.

Keep in mind that network glitches, etc, can cause disconnects. Hence, you need to code for disconnects anyway.

Edit

Do not use AUTORECONNECT with InnoDB, it can cause nasty glitches. Instead, catch 'not connected' error after each query and restart the transaction. Another approach is to execute a 'ping' whenever you might be coming back from a long idle period.

4
  • autoReconnect=true does not work. Also, your response does not give any other specific, tangible way to accomplish the results that you mention. Therefore, your response is a comment and not an answer. – CodeMed Apr 7 '15 at 16:03
  • Edited my answer. – Rick James Apr 7 '15 at 23:59
  • There are many valid reasons for the connection to go down! Make sure your application can handle it or you will get problems at random times. Get the habit to do it right so you won't leave a running service that is hard to replace or restart hanging because someone needed to restart the MySQL server! – Samuel Åslund Nov 23 '15 at 9:35
1

You wrote

And how do I even check to see what the current settings for variables such as wait_timeout and interactive_timeout, etc. are?

Use "show variables" as below (from the MySQL client)

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| 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       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+
12 rows in set (0.00 sec)
mysql> 

wait_timeout is set to 28800 (8hrs)

You also wrote:

Given that these variables are not defined in the config shown below.

What's happening here is that not every one of the ~500 variables that govern MySQL's behaviour is specified in my.cnf. That file is largely used to modify defaults.

Finally, you wrote:

So how do I set up MySQL so that connections never time out?

From here

You have

wait_timeout

Command-Line Format --wait_timeout=#
System Variable Name    wait_timeout
Variable Scope  Global, Session
Dynamic Variable    Yes
Permitted Values (Windows)  Type    integer
Default 28800
Min Value   1
Max Value   2147483      <<<<============== ****WINDOWS****
Permitted Values (Other)    Type    integer
Default 28800
Min Value   1
Max Value   31536000   <<<<================ ****UNIX***

Note "Max Value" - that happens to be the number of seconds in a year - so you can't set it never to timeout, but I would suggest that a site with no traffic for a year is hardly worth it! :-) Set the value in the [mysql] section as suggested by @RickJames.

7
  • Please see EDIT at the end of my OP with results of following your advice. What am I doing wrong? Also, I estimated 2147483 as less than 25 days. What formula are you using to convert it into a full year? – CodeMed Apr 7 '15 at 3:30
  • ((2147483 seconds / 60 = 35791 minutes)/60 = 596 hours) / 24 = 24 days. Is this the wrong formula? – CodeMed Apr 7 '15 at 3:35
  • 31536000 = 60 * 60 * 24 * 365 = 1 year in seconds. @CodeMed has given the duration of the other value. Put a line in the[mysql] section of my.cnf "wait_timeout = 31536000" - et voilà! – Vérace Apr 7 '15 at 3:38
  • OK, but there is no [mysql] section in my.cnf, only a [mysqld] section. And placing it there and restarting the database service at the CentOS command line as shown in my EDIT did not change anything. What am I doing wrong, as described in the steps in my EDIT? – CodeMed Apr 7 '15 at 3:50
  • Oh, I see the confusion - strange copy and paste behaviour. I've added arrows pointing out the Max Value for Windows (2147483) and Unix (31536000). If you check the page that I quoted from the MySQL docco, you'll see that in the "Permitted Values" sections, there is a distinction between Windows and Others. For some reason, when I copied over the table, these didn't come over. Check the table now. You're on Unix, so your Max Value is a year. – Vérace Apr 7 '15 at 3:50

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.