mysql connection timeout on small traffic server

Question

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?

Answer 1

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.

Answer 2

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.