B4J Question Jar crash on JDBC timeout MySQL

alwaysbusy

Expert
Licensed User
Longtime User
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:
B4X:
SQL.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://192.xxx.xxx.xxx:3306/c25database?characterEncoding=utf8", login, password)

B4X:
(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

Expert
Licensed User
Longtime User
every 5 seconds.???

What's your pool size?
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
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)...
 
Upvote 0

alwaysbusy

Expert
Licensed User
Longtime User
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?
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
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...
 
Upvote 0

alwaysbusy

Expert
Licensed User
Longtime User
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.
 
Upvote 0

keirS

Well-Known Member
Licensed User
Longtime User
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:

B4X:
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
 
Upvote 0

keirS

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

alwaysbusy

Expert
Licensed User
Longtime User
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.
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
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...
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
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.
 
Upvote 0
Top