B4J Question Jar crash on JDBC timeout MySQL

Discussion in 'B4J Questions' started by alwaysbusy, Jan 12, 2018.

Similar threads

B4J Question B4J MySQL Connection
B4J Tutorial SQL Tutorial
B4J Question MySQL error (seems to time out)
B4J Question JRDC2 no longer works
B4J Question SQL Syntax
  1. alwaysbusy

    alwaysbusy Well-Known Member Licensed 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:
    Code:
    SQL.Initialize2("com.mysql.jdbc.Driver""jdbc:mysql://192.xxx.xxx.xxx:3306/c25database?characterEncoding=utf8", login, password)
    Code:
    (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
     
  2. Harris

    Harris Well-Known Member Licensed User

    every 5 seconds.???

    What's your pool size?
     
  3. keirS

    keirS Well-Known Member Licensed User

    Have you tried using connection pooling?
    Doesn't appear to be using a connection pool. That's the first thing to try.
     
  4. Harris

    Harris Well-Known Member Licensed User

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

    alwaysbusy Well-Known Member Licensed 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?
     
  6. Harris

    Harris Well-Known Member Licensed User

    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...
     
  7. alwaysbusy

    alwaysbusy Well-Known Member Licensed 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.
     
  8. keirS

    keirS Well-Known Member Licensed User

    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:

    Code:
    Sub IsValidConnection(SQLConnection As SQLAs 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 SQLAs 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
     
    inakigarm and alwaysbusy like this.
  9. keirS

    keirS Well-Known Member Licensed User


    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.
     
    Harris likes this.
  10. alwaysbusy

    alwaysbusy Well-Known Member Licensed User

    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.
     
    Harris likes this.
  11. alwaysbusy

    alwaysbusy Well-Known Member Licensed User

    Running 24h+ using the pool and still going strong... :)
     
    keirS and Harris like this.
  12. Harris

    Harris Well-Known Member Licensed 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...
     
    alwaysbusy likes this.
  13. Erel

    Erel Administrator Staff Member Licensed 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.
     
    Harris likes this.
Loading...