B4J Question I use JRDC to connect to a remote ACCESS database, and everything works fine. However, this issue occurs after running for a while

[guandjy]

2025-02-01 18:32:22.798:WARN :cmva.ThreadPoolAsynchronousRunner:C3P0PooledConnectionPoolManager[identityToken->2rw2kkb81hi4hr3qaa3fo|419c5f1a]-AdminTaskTimer: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@6b6c04e0 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!

2025-02-01 18:32:22.800:WARN :cmva.ThreadPoolAsynchronousRunner:C3P0PooledConnectionPoolManager[identityToken->2rw2kkb81hi4hr3qaa3fo|419c5f1a]-AdminTaskTimer: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@6b6c04e0 -- APPARENT DEADLOCK!!! Complete Status: <|?Managed Threads: 3<|?Active Threads: 3<|?Active Tasks: <|??com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@6f301808<|???on thread: C3P0PooledConnectionPoolManager[identityToken->2rw2kkb81hi4hr3qaa3fo|419c5f1a]-HelperThread-#2<|??com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@52513706<|???on thread: C3P0PooledConnectionPoolManager[identityToken->2rw2kkb81hi4hr3qaa3fo|419c5f1a]-HelperThread-#0<|??com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@667d136d<|???on thread: C3P0PooledConnectionPoolManager[identityToken->2rw2kkb81hi4hr3qaa3fo|419c5f1a]-HelperThread-#1<|?Pending Tasks: <|??com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@5fc37e22<|Pool thread stack traces:<|?Thread[C3P0PooledConnectionPoolManager[identityToken->2rw2kkb81hi4hr3qaa3fo|419c5f1a]-HelperThread-#1,5,main]<|??app//net.ucanaccess.jdbc.DBReferenceSingleton.remove(DBReferenceSingleton.java:61)<|??app//net.ucanaccess.jdbc.DBReference.shutdown(DBReference.java:635)<|??app//net.ucanaccess.jdbc.DBReference$MemoryTimer.decrementActiveConnection(DBReference.java:107)<|??app//net.ucanaccess.jdbc.DBReference$MemoryTimer.access$800(DBReference.java:88)<|??app//net.ucanaccess.jdbc.DBReference.decrementActiveConnection(DBReference.java:370)<|??app//net.ucanaccess.jdbc.UcanaccessConnection.close(UcanaccessConnection.java:185)<|??app//com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:642)<|??app//com.mchange.v2.c3p0.impl.NewPooledConnection.closeMaybeCheckedOut(NewPooledConnection.java:255)<|??app//com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:622)<|??app//com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:1076)<|??app//com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)<|?Thread[C3P0PooledConnectionPoolManager[identityToken->2rw2kkb81hi4hr3qaa3fo|419c5f1a]-HelperThread-#2,5,main]<|??app//net.ucanaccess.jdbc.DBReference$MemoryTimer.incrementActiveConnection(DBReference.java:148)<|??app//net.ucanaccess.jdbc.DBReference$MemoryTimer.access$1300(DBReference.java:88)<|??app//net.ucanaccess.jdbc.DBReference.incrementActiveConnection(DBReference.java:549)<|??app//net.ucanaccess.jdbc.UcanaccessConnection.<init>(UcanaccessConnection.java:108)<|??app//net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:226)<|??app//com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)<|??app//com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)<|??app//com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)<|??app//com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)<|??app//com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)<|??app//com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)<|??app//com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)<|??app//com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)<|??app//com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)<|?Thread[C3P0PooledConnectionPoolManager[identityToken->2rw2kkb81hi4hr3qaa3fo|419c5f1a]-HelperThread-#0,5,main]<|??app//net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:76)<|??app//com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)<|??app//com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)<|??app//com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)<|??app//com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)<|??app//com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)<|??app//com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)<|??app//com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)<|??app//com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)<|??app//com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.ru


2025-02-01 18:33:22.825:WARN :cmva.ThreadPoolAsynchronousRunner:C3P0PooledConnectionPoolManager[identityToken->2rw2kkb81hi4hr3qaa3fo|419c5f1a]-AdminTaskTimer: Task com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@667d136d (in deadlocked PoolThread) failed to complete in maximum time 60000ms. Trying interrupt().
(ThreadPoolAsynchronousRunner.java:696)<|

'Class module
Sub Class_Globals
    Private pool As ConnectionPool
    Private DebugQueries As Boolean
    Private commands As Map
    Public serverPort As Int
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize
    Dim config As Map = LoadConfigMap
    
    pool.Initialize(config.Get("DriverClass"), config.Get("JdbcUrl"), config.Get("User"), _
        config.Get("Password"))
    
    Dim cpds As JavaObject =pool
    '连接池配置
    cpds.RunMethod("setInitialPoolSize", Array(5))     '初始连接数
    cpds.RunMethod("setMinPoolSize", Array(3))         '最小连接数
    cpds.RunMethod("setMaxPoolSize", Array(20))        '最大连接数
    cpds.RunMethod("setAcquireIncrement", Array(2))    '增量连接数
    
    '自动回收配置
    cpds.RunMethod("setMaxIdleTime", Array(300))       '连接最大闲置时间（秒）
    cpds.RunMethod("setUnreturnedConnectionTimeout", Array(300)) '未关闭连接超时回收
    cpds.RunMethod("setDebugUnreturnedConnectionStackTraces", Array(True)) '记录泄露堆栈
    
#if DEBUG
    DebugQueries = True
#else
    DebugQueries = False
#end if
    serverPort = config.Get("ServerPort")
    LoadSQLCommands(config)
End Sub

Private Sub LoadConfigMap As Map
    Return File.ReadMap(File.DirAssets, "config.properties")
End Sub

Public Sub GetCommand(Key As String) As String
    If commands.ContainsKey("sql." & Key) = False Then
        Log("*** Command not found: " & Key)
    End If

    Return commands.Get("sql." & Key)
End Sub

Public Sub GetConnection As SQL
    If DebugQueries Then LoadSQLCommands(LoadConfigMap)
    Return pool.GetConnection
End Sub


Private Sub LoadSQLCommands(config As Map)
    Dim newCommands As Map
    newCommands.Initialize
    For Each k As String In config.Keys
        If k.StartsWith("sql.") Then
            newCommands.Put(k, config.Get(k))
        End If
    Next
    commands = newCommands
End Sub

 

[guandjy]

Sub Class_Globals
    
    Private DateTimeMethods As Map
    
End Sub

Public Sub Initialize
    DateTimeMethods = CreateMap(91: "getDate", 92: "getTime", 93: "getTimestamp")
    
End Sub
 
Sub Handle(req As ServletRequest, resp As ServletResponse)
    Dim start As Long = DateTime.Now
    Dim q As String
    Dim in As InputStream = req.InputStream
    Dim method As String = req.GetParameter("method")
    Dim con As SQL
     Dim clientIP As String = req.GetHeader("X-Forwarded-For")
    If clientIP = "" Then clientIP = req.RemoteAddress
    
    
    Try
        
        con = Main.rdcConnector1.GetConnection
        If method = "query2" Then
            q = ExecuteQuery2(con, in, resp)

        Else if method = "batch2" Then
            q = ExecuteBatch2(con, in, resp)
        Else
            Log("Unknown method: " & method)
            resp.SendError(500, "unknown method")
        End If
    Catch
        Log(LastException)
        resp.SendError(500, LastException.Message)
    End Try
    If con <> Null And con.IsInitialized Then con.Close
    Dim timestamp As String = DateTime.Date(DateTime.Now) & " " & DateTime.Time(DateTime.Now)
    Log($"${timestamp},Command: ${q}, took: ${DateTime.Now - start}ms, client=${clientIP}"$)
End Sub



Private Sub ExecuteQuery2 (con As SQL, in As InputStream,  resp As ServletResponse) As String
    Dim ser As B4XSerializator
    Dim m As Map = ser.ConvertBytesToObject(Bit.InputStreamToBytes(in))
    Dim cmd As DBCommand = m.Get("command")
    Dim limit As Int = m.Get("limit")
    Dim rs As ResultSet = con.ExecQuery2(Main.rdcConnector1.GetCommand(cmd.Name), cmd.Parameters)
    If limit <= 0 Then limit = 0x7fffffff 'max int
    Dim jrs As JavaObject = rs
    Dim rsmd As JavaObject = jrs.RunMethod("getMetaData", Null)
    Dim cols As Int = rs.ColumnCount
    Dim res As DBResult
    res.Initialize
    res.columns.Initialize
    res.Tag = Null 'without this the Tag properly will not be serializable.
    For i = 0 To cols - 1
        res.columns.Put(rs.GetColumnName(i), i)
    Next
    res.Rows.Initialize
    Do While rs.NextRow And limit > 0
        Dim row(cols) As Object
        For i = 0 To cols - 1
            Dim ct As Int = rsmd.RunMethod("getColumnType", Array(i + 1))
            'check whether it is a blob field
            If ct = -2 Or ct = 2004 Or ct = -3 Or ct = -4 Then
                row(i) = rs.GetBlob2(i)
            Else if ct = 2 Or ct = 3 Then
                row(i) = rs.GetDouble2(i)
            Else If DateTimeMethods.ContainsKey(ct) Then
                Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1))
                If SQLTime.IsInitialized Then
                    row(i) = SQLTime.RunMethod("getTime", Null)
                Else
                    row(i) = Null
                End If
            Else
                row(i) = jrs.RunMethod("getObject", Array(i + 1))
            End If
        Next
        res.Rows.Add(row)
    Loop
    rs.Close
    Dim data() As Byte = ser.ConvertObjectToBytes(res)
    resp.OutputStream.WriteBytes(data, 0, data.Length)
    Return "query: " & cmd.Name
End Sub

Private Sub ExecuteBatch2(con As SQL, in As InputStream, resp As ServletResponse) As String
    Dim ser As B4XSerializator
    Dim m As Map = ser.ConvertBytesToObject(Bit.InputStreamToBytes(in))
    Dim commands As List = m.Get("commands")
    Dim res As DBResult
    res.Initialize
    res.columns = CreateMap("AffectedRows (N/A)": 0)
    res.Rows.Initialize
    res.Tag = Null
    Try
        con.BeginTransaction
        For Each cmd As DBCommand In commands
            con.ExecNonQuery2(Main.rdcConnector1.GetCommand(cmd.Name), _
                cmd.Parameters)
        Next
        res.Rows.Add(Array As Object(0))
        con.TransactionSuccessful
        con.ExecNonQuery("COMMIT;")
    Catch
        con.Rollback
        Log(LastException)
        resp.SendError(500, LastException.Message)
    End Try
    Dim data() As Byte = ser.ConvertObjectToBytes(res)
    resp.OutputStream.WriteBytes(data, 0, data.Length)
    Return $"batch (size=${commands.Size})"$
End Sub

'Non-UI application (console / server application)
#Region  Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
#End Region

'change based on the jdbc jar file
#AdditionalJar:  ucanaccess-5.0.0.jar
'#AdditionalJar: ucanaccess-4.0.4.jar
'#AdditionalJar: commons-lang-2.6.jar
'#AdditionalJar: commons-logging-1.1.3.jar

#AdditionalJar: commons-lang3-3.8.1
#AdditionalJar: commons-logging-1.2
'#AdditionalJar: hsqldb-2.3.1.jar
 
#AdditionalJar: hsqldb-2.6.1.jar
 
'#AdditionalJar: jackcess-2.1.11.jar
#AdditionalJar: jackcess-3.0.1-B4J.jar
'#AdditionalJar: jackcess-encrypt-2.1.4
'#AdditionalJar: postgresql-9.4.1207
#IgnoreWarnings: 15
    Sub Process_Globals
    Public srvr As Server
    Public rdcConnector1 As RDCConnector
    Public const VERSION As Float = 2.22
    Type DBCommand (Name As String, Parameters() As Object)
    Type DBResult (Tag As Object, Columns As Map, Rows As List)
End Sub

Sub AppStart (Args() As String)
    srvr.Initialize("")
    rdcConnector1.Initialize
    srvr.Port = rdcConnector1.serverPort
        srvr.AddHandler("/test", "TestHandler", False)
    srvr.AddHandler("/rdc", "RDCHandler", False)
    
    
    srvr.Start
    Log($"jRDC is running (version = $1.2{VERSION})"$)
    StartMessageLoop
End Sub

 

[Erel]

What happens after you get this warning?

Better to set the maximum pool size to 1 or 2. Access is a very limited db engine.

 

[guandjy]

收到警告后怎么办？

最好将最大池大小设置为1或2。Access是一个非常有限的数据库引擎。

It's gone later, the client can't connect. Previously, maxpool was not set to 20, resulting in the same timeout error.

 

[Erel]

Test it with maximum pool size of 1.

 

[guandjy]

(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@523d63bb -- timeout at awaitAvailable()

Is it really the only way to change to MySQL?

 

[EnriqueGonzalez]

(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@523d63bb -- timeout at awaitAvailable()

Is it really the only way to change to MySQL?

DB access is not meant for multiuser, at least not the level of jrdc.

That doesn't mean you need MySQL, if you are confortable with Microsoft products try sql server, if what you like is file databases then try Sqlite. both are significantly better.

 

[Erel]

Is it really the only way to change to MySQL?

Have you tried with a maximum pool size of 1???

 

[guandjy]

Have you tried with a maximum pool size of 1???

Yes, boss, changing it to 1 still won't work

 

[guandjy]

Have you tried with a maximum pool size of 1???

After n hours, an error occurred and the client started unable to connect

 

[guandjy]

DB access is not meant for multiuser, at least not the level of jrdc.

That doesn't mean you need MySQL, if you are confortable with Microsoft products try sql server, if what you like is file databases then try Sqlite. both are significantly bett

Thank you for your answer. I will consider your suggestion

 

[guandjy]

I will change the connection pool to HikariCP and try again. I will see the effect in a few hours

 

[kohlenach]

If you run your script on a windows desktop check your energy management and system config. I had the problem that the network adapter didn't wake up after return from stand by mode and the connection to the database was lost.

 

[Martin Fdez]

Something similar has happened to me before with RDC, jRDC2, and SQL Server.

I encountered Deadlock messages, and the user couldn't send data from the mobile device to the SQL Server tables. Restarting jRDC temporarily solved the issue, but after some time, the Deadlock would appear again. These tables are frequently used by multiple users (around 100 or more).

As a solution, what I have done for several years is move the records from the table to a historical table, keeping only the records from the last 1 or 2 months. This has worked well for me.

I don't know if this applies to your case, but it's an idea for a possible solution.

 

[MrKim]

MS Access is a LOUSY multiuser system. Fortunately Microsoft provides a free tool that will upsize your entire database to SQL Server and believe me, the free version of SQL server will scale far beyond anything you can do with Access. SSMS (SQL server management studio) is also free and is an excellent tool for developing and managing your database(s).
1. Install SQL Server free version.
2. Install SSMS.
3. Install the MS Upsizing wizard and upsize your database.

I will tell you the upsizing wizard is a little cryptic/non-intuitive but once you figure it out it works perfectly. We used start our less sophisticated customers with Access and when they grew big enough to need SQL server (> 5 users usually) we would use the upsizing wizard to upsize their data to SQL, Change the connection string in our app and voila! We were done.

I will warn when using the upsizer pay close attention to the data types you are converting TO. As I recall it defaults to a BIG date and our date data looked crazy. Make sure you convert datetime to SQL standard DateTime.

 

[guandjy]

Thank you for your guidance. After changing the connection pool to HikariCP, I have been running continuously for many days without any interruption