#210 SH closes without error

[Lina Humbeck]

If the SH window is opened and no changes were done for a long time (e.g. overnight) SH sometimes just closes the window and the process is gone. It just happened two times. In the console I got the following error message, which implicates an issue with the MySQL connection.

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 50,881,976 milliseconds ago.  The last packet sent successfully to the server was 50,881,976 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 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3348)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1967)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620) 
    at com.mysql.jdbc.ConnectionImpl.setCatalog(ConnectionImpl.java:5116)
    at edu.udo.scaffoldhunter.model.db.SetSchemaConnectionProvider.getConnection(SetSchemaConnectionProvider.java:59)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:380)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:228)
    ... 57 more
Caused by: java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3329)
    ... 64 more

[Sven Schrinner]

MySQL has a variable called "wait_timeout" and default value 28800 seconds (=8 hours). That means, if no activity has been noticed for 8 hours, the SQL server closes it. 50,881,976 milliseconds are about 14 hours, though. The console text already hints that this might be the problem.

If you need to run SH for such a long time, you might just increase this variable (e.g. to 86400 , which would be 24 hours).

What we can do from the program's perspective is, to send "dummy queries" to the SQL server in very long (e.g. 1 hour) intervals to keep the connection alive.

[Lina Humbeck]

To increase the "wait_timeout" would be a solution from the user site.
What about the suggestion:

using the Connector/J connection property 'autoReconnect=true' to avoid this problem

? Would this be possible from the developer's site without drawbacks? I would assume that dummy queries could slow down the communication with the MySQL server if there are other applications using the MySQL server.

[Till Schäfer]

what is defniitely wrong here, is it is closing without any error message.

we might also do something like a keep-alive ping.

[Sven Schrinner]

The GUIController class now has a worker, which requests the existing datasets every 10 minutes, once the login was successful. A small test (with shorter times of course) showed that I do not get timeouts anymore. I am quite unsure about the way it is implemented and we might want a "cleaner" solution. At least, it works as intended.

One more thing: I cannot provoke any SH crashes via timeout. If I set the timeout window to 10 seconds, wait for the timeout inside a session and then do something, I get an exception for the timeout, but SH still works almost perfect. It seems that if the first request after the timeout fails (e.g. an SVG could not be read from the db and shows a red square as SVG), all the other requests do work as if the connection was reestablished. My SQL server settings are pretty much default, so I do not know, if it just my server settings or SH only crashes for certain kinds of failed server requests.

[Till Schäfer]

a cleaner solution might be to use a better configured connection pool in hibernate (that automaticall re-establishes connection). However, i am usure about the efford needed to implement this.

https://stackoverflow.com/questions/32622233/hibernate-property-to-keep-connections-alive

can you therefore add a small comment to your workaround that the connection pool solution should be evaluated or evaluate the solution yourself?

[Sven Schrinner]

I tried to setup a c3p0 connection pool, but it does not work yet. The first issue is to find the right version for each used library. I found the right c3p0 library for hibernate 4.3.6 (currently used version) and a couple of other required libraries with the correct version. It still does not work: On each start I get the message that the database is scheme is not correct, but if I choose to recreate it, it tells me that there is no connection to the database.

I think we should create a new ticket for this, if we want to move to c3p0 connection pool in the future. We also might upgrade the hibernate libraries for this purpose, to make sure that all used files have the correct version and there are incompatibilities.

[Till Schäfer]

i am fine with the provided workaround in this case.