B4J Question Jar crash on JDBC timeout MySQL

[alwaysbusy]

Setup:

I got 4 identical .jar files that run a select query on a MySQL server (on Debian 8.08) every 5 seconds.

After approx 7 hours, all 4 jars crash at the same time with an error (catched in the Application_Error event).

my Initialization:

SQL.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://192.xxx.xxx.xxx:3306/c25database?characterEncoding=utf8", login, password)

(RuntimeException) java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 23.918 milliseconds ago.  The last packet sent successfully to the server was 18.916 milliseconds ago.
main._appstart (java line: 626)
java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 23.918 milliseconds ago.  The last packet sent successfully to the server was 18.916 milliseconds ago.
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:119)
   at anywheresoftware.b4a.objects.Timer$TickTack$1.run(Timer.java:118)
   at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:30)
   at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:26)
   at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:148)
   at b4j.example.main._appstart(main.java:626)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
   at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
   at b4j.example.main.main(main.java:29)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 23.918 milliseconds ago.  The last packet sent successfully to the server was 18.916 milliseconds ago.
   at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
   at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
   at java.lang.reflect.Constructor.newInstance(Unknown Source)
   at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
   at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:983)
   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3457)
   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3357)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3797)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962)
   at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:342)
   at anywheresoftware.b4j.objects.SQL.ExecQuerySingleResult2(SQL.java:390)
   at b4j.example.dbmclass._sqlselectforupdatewithpre(dbmclass.java:246)
   at b4j.example.main._runtimer_tick(main.java:775)
   at sun.reflect.GeneratedMethodAccessor40.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
   ... 12 more
Caused by: java.net.SocketTimeoutException: Read timed out
   at java.net.SocketInputStream.socketRead0(Native Method)
   at java.net.SocketInputStream.socketRead(Unknown Source)
   at java.net.SocketInputStream.read(Unknown Source)
   at java.net.SocketInputStream.read(Unknown Source)
   at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:100)
   at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:143)
   at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:173)
   at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2946)
   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3367)
   ... 27 more

 

[Harris]

every 5 seconds.???

What's your pool size?

 

[keirS]

Have you tried using connection pooling?

every 5 seconds.????

Doesn't appear to be using a connection pool. That's the first thing to try.

 

[Harris]

Have you tried using connection pooling?


Doesn't appear to be using a connection pool. That's the first thing to try.

It would die long before 7 hours without using a pool... 7 minutes more likely (or seconds hitting every 5)...

 

[alwaysbusy]

every 5 seconds

Yes, I'm very impatient...

I don't use a pool, it is a stand-alone non-UI B4J app (not using jServer, just jSQL). I open the connection in AppStart() once, then do my query very 5 seconds in a timer.So I thought at most with 4 jars, I have 4 connections, no? I thought using jServer would've been overkill.

So you guys suggest I use jServer (so I can use the connection pool) and then request/close a connection every 5 seconds?

 

[Harris]

Yes, I'm very impatient...

I don't use a pool, it is a stand-alone non-UI B4J app (not using jServer, just jSQL). I open the connection in AppStart() once, then do my query very 5 seconds in a timer.So I thought at most with 4 jars, I have 4 connections, no? I thought using jServer would've been overkill.

So you guys suggest I use jServer (so I can use the connection pool) and then request/close a connection every 5 seconds?

Geez, sounds like more overkill.... But you could try it and test the outcome... I have mine set to 100 (for no particular reason).
Something is killing your connection when left open...

 

[alwaysbusy]

Maybe some background to explain what I'm doing:

We have an ancient PHP app that is used to upload files from a device to the server (althought I would love to replace the PHP with B4J, I can't).
Once the device has uploaded the files (the result on the server is gzipped, encrypted 64K chucks of the files), it writes a record in a table (CLOUD_NOTIFY). This happens thousands of times/hour as there are approx 20.000 devices that use this upload system.
All workes fine for years now so there is no problem there.

Now we are writing a new part in B4J that needs to check this table (CLOUD_NOTIFY) every 5 seconds for new records. It should be horizontal scalable (adding such 'checkers' if needed). Such a jar 'reserves' a row in the table, reads it and uses its information to download all the 'chunks' of the files and 'rebuilds' them to the original files. When finished, it restarts its timer and 5 seconds later checks again if there isn't a new record (and so on).

And this system works fine too (for about 7 hours that is).

I do know, if this had to be written from the ground up, it would look completely different. But it can't. We do need to support the 'old' upload system.

 

[keirS]

Yes, I'm very impatient...

I don't use a pool, it is a stand-alone non-UI B4J app (not using jServer, just jSQL). I open the connection in AppStart() once, then do my query very 5 seconds in a timer.So I thought at most with 4 jars, I have 4 connections, no? I thought using jServer would've been overkill.

So you guys suggest I use jServer (so I can use the connection pool) and then request/close a connection every 5 seconds?

Short answer is use a connection pool. Long answer:

If you don't want to use connection pooling then you can do a number of tests on the connection. This is an example of the sort of thing I use:

Sub IsValidConnection(SQLConnection As SQL) As Boolean
Dim JO As JavaObject
Dim TimeoutSecs As Int = 1
JO = SQLConnection
JO = JO.GetFieldJO("connection")
Return JO.RunMethod("isValid",Array As Object (TimeoutSecs))
End Sub

Sub IsConnectionClosed(SQLConnection As SQL) As Boolean
    Dim JO As JavaObject
    JO = SQLConnection
    JO = JO.GetFieldJO("connection")
    Return JO.RunMethod("isClosed",Null)
  

End Sub

Sub GetConnectionWarnings(SQLConnection As SQL)
    Dim JO As JavaObject
    JO = SQLConnection
    JO = JO.GetFieldJO("connection")
    JO = JO.RunMethodJO("getWarnings",Null)
    Do While JO.IsInitialized
        Log(JO.RunMethod("toString",Null))
        JO = JO.RunMethodJO("getNextWarning",Null)
    Loop

End Sub

Sub GetResultSetWarnings(R As ResultSet)
    Dim JO As JavaObject
    JO = R
    JO = JO.RunMethodJO("getWarnings",Null)
Do While JO.IsInitialized
    Log(JO.RunMethod("toString",Null))
    JO = JO.RunMethodJO("getNextWarning",Null)
Loop

End Sub

 

[keirS]

Maybe some background to explain what I'm doing:

We have an ancient PHP app that is used to upload files from a device to the server (althought I would love to replace the PHP with B4J, I can't).
Once the device has uploaded the files (the result on the server is gzipped, encrypted 64K chucks of the files), it writes a record in a table (CLOUD_NOTIFY). This happens thousands of times/hour as there are approx 20.000 devices that use this upload system.
All workes fine for years now so there is no problem there.

Now we are writing a new part in B4J that needs to check this table (CLOUD_NOTIFY) every 5 seconds for new records. It should be horizontal scalable (adding such 'checkers' if needed). Such a jar 'reserves' a row in the table, reads it and uses its information to download all the 'chunks' of the files and 'rebuilds' them to the original files. When finished, it restarts its timer and 5 seconds later checks again if there isn't a new record (and so on).

And this system works fine too (for about 7 hours that is).

I do know, if this had to be written from the ground up, it would look completely different. But it can't. We do need to support the 'old' upload system.

if you have control over the MySQL server then you are doing this the wrong way IMO. The way it should be done is the MySQL server should notify a BJ server app that new records have been added. In MySQL you need a UDF to do this which are written in C/C++. Fortunately someone has already written one that pretty does what you need.

 

[alwaysbusy]

Fortunately someone has already written one that pretty does what you need.

I'll definitely look into this, as we were just saying to each other 'wouldn't it be nice if MySQL could have a trigger that could call our B4J server'

Just to test, I've changed it to a connection pool now and started up the 4 jars again. I'll post back what the results are.

 

[alwaysbusy]

Running 24h+ using the pool and still going strong...

 

[Harris]

Encouraging...
Although I don't hit the db every five seconds, many vehicles upload data, reports are run and other fun activity happens hourly - and it runs perfectly for months.
This is not a testament to my coding skills, but rather a clear cut example of the exceptional development tools used to make it work so well.
As Mr. T would say - "Pity The Poor Fool" who has not yet discovered B4X and associates...

 

[Erel]

You should definitely use ConnectionPool if you need to keep a remote connection for a long time.

ConnectionPool always tests the connection and make sure that it is alive before you can use it. The other option is to implement a similar test yourself and assume that the connection will be broken from time to time.