Spontaneous loss of MySQL connectivity (persistence)

logan889 · May 10, 2017, 9:04pm

Is the MySQL persistence add-on not able to reconnect? Am I missing some settings for reconnecting?

I see no problems with the MySQL server, and the “connection is not valid” came spontaneously while trying to write new data, and continues. Complete loss of database connectivity.

2017-05-04 15:58:19.821 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'mysql.persist'
2017-05-04 15:58:22.508 [INFO ] [.dashboard.internal.DashboardService] - Started dashboard at /start
2017-05-04 15:58:22.773 [INFO ] [basic.internal.servlet.WebAppServlet] - Started Basic UI at /basicui/app
2017-05-04 15:58:22.836 [INFO ] [arthome.ui.paper.internal.PaperUIApp] - Started Paper UI at /paperui
2017-05-04 15:58:22.885 [INFO ] [panel.internal.HABPanelDashboardTile] - Started HABPanel at /habpanel
2017-05-04 15:58:23.148 [INFO ] [ui.habmin.internal.servlet.HABminApp] - Started HABmin servlet at /habmin
2017-05-04 15:58:23.358 [INFO ] [al.protocol.ip.MySensorsIpConnection] - Successfully connected to MySensors Bridge.
2017-05-04 15:58:23.375 [WARN ] [al.protocol.ip.MySensorsIpConnection] - Network Sanity Checker thread disabled from bridge configuration
2017-05-05 20:46:07.629 [WARN ] [rs.internal.protocol.MySensorsWriter] - Message returned from queue is null
2017-05-07 18:29:09.171 [WARN ] [rs.internal.protocol.MySensorsWriter] - Message returned from queue is null
2017-05-08 18:29:09.177 [WARN ] [rs.internal.protocol.MySensorsWriter] - Message returned from queue is null
2017-05-09 18:29:09.178 [WARN ] [rs.internal.protocol.MySensorsWriter] - Message returned from queue is null
2017-05-10 09:45:00.051 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-05-10 09:45:00.053 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

I have nothing fancy in my mysql config. I connect via an stunnel.

cat << EOF | sudo tee /etc/openhab2/services/mysql.cfg
# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://127.0.0.1:3306/openhab

# the database user
user=openhabserver

# the database password
password=mypassword

EOF

I did not see any data in my hapanel graphs, as it could not read from the database.

Restarting openhab2 and connection is restored. Graphs are again populated.

Dim · May 11, 2017, 4:58am

Are you using the native MySQL Persistence Add-On or the JDBC Persistence MySQL Add-On?

I don’t know what could be wrong with your connection, but I would try to use the JDBC one to see if the situation will improve. Uninstall MySQL, remove mysql.cfg and fix your /etc/openhab2/services/jdbc.cfg file in the same way:

url=jdbc:mysql://127.0.0.1:3306/openhab
user=openhabserver
password=mypassword

Then, you will also need to fix your /etc/openhab2/persistence/jdbc.persist file.

How are you using stunnel? (it’s not clear for me from your post)

Ps: Delete also your /var/lib/openhab2/config/org/openhab/mysql.config file to be on the safe side (it may include old/wrong config parameters)

logan889 · May 11, 2017, 11:25am

I use MySQL persistence (persistence-mysql - 1.9.0), not the JDBC one. The configuration documentation you link to makes note of the property “reconnectCnt”, but does not mention any default value or if not setting this will ever attempt to reconnect. Seems like an odd default behavior, if it never recovers without having this set. How can I find out if this is the reason for this problem?

The JDBC add-on has more configuration options, so I may give this a try.

For stunnel I use PSK based authentication. OpenHAB2 is running in a separate VM, and this stunnel connects the VM running MySQL. The MySQL server has individual loopback interfaces for each remote VM, to be able to use host based access control even through stunnel.

Why stunnel? I want as much of the VM-to-VM communication as possible to be encrypted, and the MySQL built-in encryption adds too much overhead for new connections. This is less of a problem with services which use persistent connections, like OpenHAB, but is slow for new connections from traditional web services. Using PSK authentication is the fastest alternative for stunnel, and provides a simpler yet secure form of both server and client authentication than using certificates on both sides.