Connection error with MariaDB [SOLVED]

[dkl]

Hello,

Since I have switched to MariaDB, I frequently encounter these errors while opening the webclient after not using it for a while. First connection of the day, for example.

It looks like a connection problem between subonic and MariaDB. I tried to solve this by adding "autoReconnect=true" in the db parameters, but this does not seems to solve anything.

When the error appears, it is enough to refresh the WebUI. But I have another client (with Volumio, a music server) that complains also about the connection problems, this is more annoying.

Should I modify something on the MariaDB side? Or in subsonic?

Thanks for your help.

Denis

My args in /etc/default/subsonic:

SUBSONIC_ARGS="--port=4040 --max-memory=200 --db=jdbc:mysql://localhost:3306/subsonic?user=root&password=<XXXX>&autoReconnect=true&characterEncoding=UTF-8"

The error in the webUI:
org.springframework.dao.DataAccessResourceFailureException
Message PreparedStatementCallback; SQL [select username, password, email, ldap_authenticated, bytes_streamed, bytes_downloaded, bytes_uploaded from user where username=?]; The last packet successfully received from the server was 42,548,907 milliseconds ago. The last packet sent successfully to the server was 42,548,909 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 42,548,907 milliseconds ago. The last packet sent successfully to the server was 42,548,909 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Java version Oracle Corporation 1.8.0_212
Operating system Linux 5.10.63-v7l+
Server jetty-6.1.x
Memory Used 50 of 100 MB

org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [select username, password, email, ldap_authenticated, bytes_streamed, bytes_downloaded, bytes_uploaded from user where username=?]; The last packet successfully received from the server was 42,548,907 milliseconds ago. The last packet sent successfully to the server was 42,548,909 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 42,548,907 milliseconds ago. The last packet sent successfully to the server was 42,548,909 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:103)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:641)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:670)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:678)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:710)
at net.sourceforge.subsonic.dao.AbstractDao.query(AbstractDao.java:84)
....

[dkl]

Hello,

Looks like a timeout problem on MariaDB side.

"wait_timeout" is set by default to 28800 secs, which is 8 hours, which means that the connection is closed by Mariadb after 8 hours of inactivity.

As far as I can see, adding "autoReconnect=true" in the connection line dones not solve the issue.

Changing on the fly the value of wait_timeout in MariaDB looks promising:

Code: Select all

MariaDB [(none)]> SET GLOBAL wait_timeout=31536000


31536000 sec is one year, should be enough... That's the maximum value anyway .

Once having done this, I cannot reproduce the problem. Looks good.

Now, it should be permanently set in the server parameters, that's in /etc/mysql/mariadb.conf.d/50-server.cnf in my implementation (RPI/Jessy).

So, edit this file, and add this line in the [mysqld] section:

Code: Select all

...
[mysqld]
wait_timeout = 31536000
#
# * Basic Settings
#
...


Then relaunch the server:

Code: Select all

sudo systemctl restart mysql

And finally check that the value is now correct after restart:

Code: Select all

pi@raspberrypi:~ $ mysql -uroot -hlocalhost -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.31-MariaDB-0+deb10u1 Raspbian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| wait_timeout  | 31536000 |
+---------------+----------+
1 row in set (0.004 sec)



Denis