B4J Question jRDC2 - MySQL database connection

rgarnett1955

rgarnett1955

Active Member
Licensed User
Longtime User
I have an MySQL db connected via local host to a jRDC2 server.

I am accessing the server with a B4A App on my Android Pixel

The phone requests a small amount of data every five seconds. It works OK for maybe six to ten times then the server errors with:

Code: 
2025-02-05 20:01:00.958:INFO :cmvl.MLog:MLog-Init-Reporter: MLog clients using slf4j logging.
2025-02-05 20:01:01.232:INFO :cmvc.C3P0Registry:main: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2025-02-05 20:01:01.347:INFO :oejs.Server:main: jetty-11.0.9; built: 2022-03-30T17:44:47.085Z; git: 243a48a658a183130a8c8de353178d154ca04f04; jvm 14.0.1+7
2025-02-05 20:01:01.431:INFO :oejss.DefaultSessionIdManager:main: Session workerName=node0
2025-02-05 20:01:01.443:INFO :oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@5b1669c0{/,file:///G:/GreenhouseM11_DataAndApps/jRDCv2_mod_GH/jRDC_2025_mySQL/Objects/www,AVAILABLE}
2025-02-05 20:01:01.461:INFO :oejs.RequestLogWriter:main: Opened G:\GreenhouseM11_DataAndApps\jRDCv2_mod_GH\jRDC_2025_mySQL\Objects\logs\b4j-2025_02_05.request.log
2025-02-05 20:01:01.753:INFO :oejs.AbstractConnector:main: Started ServerConnector@6572421{HTTP/1.1, (http/1.1)}{192.168.1.114:17178}
2025-02-05 20:01:01.755:INFO :oejs.Server:main: Started Server@707194ba{STARTING}[11.0.9,sto=0] @1052ms
jRDC is running (version = 2.1)
jRDC is running without V1 support
Bound to: 192.168.1.114:17178
2025-02-05 20:01:02.891:INFO :cmvci.AbstractPoolBackedDataSource:qtp305808283-40: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hgeby9b81n7092k1mioq4z|2286778, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hgeby9b81n7092k1mioq4z|2286778, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/greenhouse_db?characterEncoding=utf8&autoReconnect=true&useSSL=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
Command: query: getGHTempsAndBattery, took: 341ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 210ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 5ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 6ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 14ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 61ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 21ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 6ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 55ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 4ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 5ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 63ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 5ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 6ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20012ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20015ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20002ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20009ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20012ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20003ms, client=192.168.1.140

So the server is connecting to the server and gets data, but then the connection dies and times out on the database connection.
To recover the server has to be restarted.

I looked up the error and it seems it might be a "connection leak" because it works for a number of tries with data rerturned within 500 ms usually much shorter than this some as low as 4ms.

I tried increasing the pool size to 5 but that made it worse, fewer successful transactions.

My config file for the jRDC servers:

Code: 
#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION
#DriverClass=com.mysql.jdbc.Driver
DriverClass=com.mysql.cj.jdbc.Driver

JdbcUrl=jdbc:mysql://localhost:3306/greenhouse_db?characterEncoding=utf8&autoReconnect=true&useSSL=false
PoolSize=5
User=jRDC
Password=Toyota_b1923_5519

#R Garnett
IPAddress=192.168.1.114
#Java server port
ServerPort=17178

#example of MS SQL Server configuration:
#DriverClass=net.sourceforge.jtds.jdbc.Driver
#JdbcUrl=jdbc:jtds:sqlserver://<server address>/<database>

#example of postegres configuration:
#JdbcUrl=jdbc:postgresql://localhost/test
#DriverClass=org.postgresql.Driver

#SQL COMMANDS
#sql.selectRecsForAveraging="SELECT * FROM OPENQUERY(PSMDB_WIN_SVR,'SELECT * FROM [main].[tblRealTimeData] ORDER  BY julianDateUTC DESC LIMIT 5;')";

sql.getGHTempsAndBattery= \
SELECT \
    UTC_DateTime, \
    InsideTempDegC, \
    OutsideTemperatureDegC, \
    BatteryVolts, \
    UTC_RecordAdded \
FROM tbl_process_analog \
ORDER BY UTC_DataTimeStamp DESC \
LIMIT 5;

# sql.getDailyData5min= \
# SELECT * FROM ( \
# SELECT \
    # UTC_DataTimeStamp, \
    # InsideTempDegC, \
    # OutsideTemperatureDegC, \
    # BatteryVolts \
# FROM  tblProcessAnalog \
# ORDER BY UTC_DataTimeStamp DESC \
# LIMIT 2880) tblTemp \
# ORDER BY UTC_DataTimeStamp ASC;


sql.getDailyData5min= \
SELECT * \
FROM \
(SELECT \
    UTC_DataTimeStampfir, \
    InsideTempDegCfir, \
    OutsideTempDegCfir, \
    BatteryVoltsfir \
FROM \
(SELECT \
    max(UTC_DataTimeStamp)        OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS UTC_DataTimeStampfir, \
    max(InsideTempDegC)           OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS InsideTempDegCfir, \
    max(OutsideTemperatureDegC)   OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS OutsideTempDegCfir, \
    avg(BatteryVolts)             OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS BatteryVoltsfir, \
    ROW_NUMBER()                   OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS rn \
 FROM  tbl_process_analog \
 ORDER BY tbl_process_analog.UTC_DataTimeStamp DESC \
 LIMIT 2880) tblTemp \
 WHERE (tblTemp.rn - 1) % 10 = 0) as tblTemp1 \
 ORDER BY UTC_DataTimeStampfir;
 



sql.getWeeklyAvg= \
SELECT \
    max(InsideTempDegC) As InsideTmperatureMax, \
    max(InsideTempDegC - OutsideTemperatureDegC) As InsideTemperatureRiseMax, \
    avg(InsideTempDegC) As InsideTmperatureAvg, \
    avg(InsideTempDegC - OutsideTemperatureDegC) As TemperatureRiseAvg \
from greenhouse_db.tbl_Process_Analog \
WHERE  \
    FROM_UNIXTIME(UTC_DataTimeStamp)  <= CURRENT_TIMESTAMP and FROM_UNIXTIME(UTC_DataTimeStamp) >= CURRENT_TIMESTAMP - (7 * 24 * 3600) \
    AND HOUR(FROM_UNIXTIME(UTC_DataTimeStamp)) >= 9 \
    AND HOUR(FROM_UNIXTIME(UTC_DataTimeStamp)) <= 17;


I am Using the latest mySQL Connector from the MySQL website:
#AdditionalJar: mysql-connector-j-9.2.0.jar

I tried another version:
#AdditionalJar: mysql-connector-java-5.1.49-bin

This didn't work either.

The MySQL is the community edition server V 8.0.41

Can anyone help me?
 
Sort by date Sort by votes
aeric said:
Try set bigger PoolSize, e.g 100
Click to expand...
I don't think this will work, 5 connections could serve hundreds of users

If you are using vanilla jrdc2 then the issue must be on MySQL side that is not closing the connections successfully, may be a setting.

Connections get lost but it seems you are on a lan. So thats neither an option
 
Upvote 0
rgarnett1955 said:
I have an MySQL db connected via local host to a jRDC2 server.

I am accessing the server with a B4A App on my Android Pixel

The phone requests a small amount of data every five seconds. It works OK for maybe six to ten times then the server errors with:

Code: 
2025-02-05 20:01:00.958:INFO :cmvl.MLog:MLog-Init-Reporter: MLog clients using slf4j logging.
2025-02-05 20:01:01.232:INFO :cmvc.C3P0Registry:main: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2025-02-05 20:01:01.347:INFO :oejs.Server:main: jetty-11.0.9; built: 2022-03-30T17:44:47.085Z; git: 243a48a658a183130a8c8de35
[QUOTE="rgarnett1955, post: 1014448, member: 35988"]
Where can I get the Latest jRDC2 is v2.23?
[/QUOTE]

3178d154ca04f04; jvm 14.0.1+7
2025-02-05 20:01:01.431:INFO :oejss.DefaultSessionIdManager:main: Session workerName=node0
2025-02-05 20:01:01.443:INFO :oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@5b1669c0{/,file:///G:/GreenhouseM11_DataAndApps/jRDCv2_mod_GH/jRDC_2025_mySQL/Objects/www,AVAILABLE}
2025-02-05 20:01:01.461:INFO :oejs.RequestLogWriter:main: Opened G:\GreenhouseM11_DataAndApps\jRDCv2_mod_GH\jRDC_2025_mySQL\Objects\logs\b4j-2025_02_05.request.log
2025-02-05 20:01:01.753:INFO :oejs.AbstractConnector:main: Started ServerConnector@6572421{HTTP/1.1, (http/1.1)}{192.168.1.114:17178}
2025-02-05 20:01:01.755:INFO :oejs.Server:main: Started Server@707194ba{STARTING}[11.0.9,sto=0] @1052ms
jRDC is running (version = 2.1)
jRDC is running without V1 support
Bound to: 192.168.1.114:17178
2025-02-05 20:01:02.891:INFO :cmvci.AbstractPoolBackedDataSource:qtp305808283-40: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hgeby9b81n7092k1mioq4z|2286778, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hgeby9b81n7092k1mioq4z|2286778, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/greenhouse_db?characterEncoding=utf8&autoReconnect=true&useSSL=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
Command: query: getGHTempsAndBattery, took: 341ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 210ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 5ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 6ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 14ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 61ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 21ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 6ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 55ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 4ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 5ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 63ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 5ms, client=192.168.1.140
Command: query: getGHTempsAndBattery, took: 6ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20012ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20015ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20002ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20009ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20012ms, client=192.168.1.140
(TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@760bc47e -- timeout at awaitAvailable()
Command: , took: 20003ms, client=192.168.1.140

So the server is connecting to the server and gets data, but then the connection dies and times out on the database connection.
To recover the server has to be restarted.

I looked up the error and it seems it might be a "connection leak" because it works for a number of tries with data rerturned within 500 ms usually much shorter than this some as low as 4ms.

I tried increasing the pool size to 5 but that made it worse, fewer successful transactions.

My config file for the jRDC servers:

Code: 
#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION
#DriverClass=com.mysql.jdbc.Driver
DriverClass=com.mysql.cj.jdbc.Driver

JdbcUrl=jdbc:mysql://localhost:3306/greenhouse_db?characterEncoding=utf8&autoReconnect=true&useSSL=false
PoolSize=5
User=jRDC
Password=Toyota_b1923_5519

#R Garnett
IPAddress=192.168.1.114
#Java server port
ServerPort=17178

#example of MS SQL Server configuration:
#DriverClass=net.sourceforge.jtds.jdbc.Driver
#JdbcUrl=jdbc:jtds:sqlserver://<server address>/<database>

#example of postegres configuration:
#JdbcUrl=jdbc:postgresql://localhost/test
#DriverClass=org.postgresql.Driver

#SQL COMMANDS
#sql.selectRecsForAveraging="SELECT * FROM OPENQUERY(PSMDB_WIN_SVR,'SELECT * FROM [main].[tblRealTimeData] ORDER  BY julianDateUTC DESC LIMIT 5;')";

sql.getGHTempsAndBattery= \
SELECT \
    UTC_DateTime, \
    InsideTempDegC, \
    OutsideTemperatureDegC, \
    BatteryVolts, \
    UTC_RecordAdded \
FROM tbl_process_analog \
ORDER BY UTC_DataTimeStamp DESC \
LIMIT 5;

# sql.getDailyData5min= \
# SELECT * FROM ( \
# SELECT \
    # UTC_DataTimeStamp, \
    # InsideTempDegC, \
    # OutsideTemperatureDegC, \
    # BatteryVolts \
# FROM  tblProcessAnalog \
# ORDER BY UTC_DataTimeStamp DESC \
# LIMIT 2880) tblTemp \
# ORDER BY UTC_DataTimeStamp ASC;


sql.getDailyData5min= \
SELECT * \
FROM \
(SELECT \
    UTC_DataTimeStampfir, \
    InsideTempDegCfir, \
    OutsideTempDegCfir, \
    BatteryVoltsfir \
FROM \
(SELECT \
    max(UTC_DataTimeStamp)        OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS UTC_DataTimeStampfir, \
    max(InsideTempDegC)           OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS InsideTempDegCfir, \
    max(OutsideTemperatureDegC)   OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS OutsideTempDegCfir, \
    avg(BatteryVolts)             OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS BatteryVoltsfir, \
    ROW_NUMBER()                   OVER (ORDER BY UTC_DataTimeStamp ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS rn \
 FROM  tbl_process_analog \
 ORDER BY tbl_process_analog.UTC_DataTimeStamp DESC \
 LIMIT 2880) tblTemp \
 WHERE (tblTemp.rn - 1) % 10 = 0) as tblTemp1 \
 ORDER BY UTC_DataTimeStampfir;
 



sql.getWeeklyAvg= \
SELECT \
    max(InsideTempDegC) As InsideTmperatureMax, \
    max(InsideTempDegC - OutsideTemperatureDegC) As InsideTemperatureRiseMax, \
    avg(InsideTempDegC) As InsideTmperatureAvg, \
    avg(InsideTempDegC - OutsideTemperatureDegC) As TemperatureRiseAvg \
from greenhouse_db.tbl_Process_Analog \
WHERE  \
    FROM_UNIXTIME(UTC_DataTimeStamp)  <= CURRENT_TIMESTAMP and FROM_UNIXTIME(UTC_DataTimeStamp) >= CURRENT_TIMESTAMP - (7 * 24 * 3600) \
    AND HOUR(FROM_UNIXTIME(UTC_DataTimeStamp)) >= 9 \
    AND HOUR(FROM_UNIXTIME(UTC_DataTimeStamp)) <= 17;


I am Using the latest mySQL Connector from the MySQL website:
#AdditionalJar: mysql-connector-j-9.2.0.jar

I tried another version:
#AdditionalJar: mysql-connector-java-5.1.49-bin

This didn't work either.

The MySQL is the community edition server V 8.0.41

Can anyone help me?
Click to expand...
 
Upvote 0
rgarnett1955 said:
Where can I get the Latest jRDC2 is v2.23?

I have tried bigger pools - makes no difference.
Click to expand...
Hi I found 2.23 and have checked the jRDC server I am using against it. It is not identical, as mine includes code for setting the client ip and port address and it has a mod to fix up a bug that occurs for mySQL date fields. The problem isn't with the jRDC code it is in the code that does the connection to the MySQL server port. I have tried different MySQL settings, max number of concurrent connections, I stopped the client that inserts records into the db, but it makes no difference. It gets 14 record-sets then crashes and has to be reset.

I have attached my jRDC server to this post.

I googled the error and it has something to do with the C3PO connection pool. This error seems pretty common and happens with databases other than MySQL, postgre, Vertica etc.

Here is one that seems interesting:
Stack Exchange C3PO Timeout

The C3PO pool can be configured in a native Java application, but as far as I can see not from within B4J.

I tried deepseek and got

Code: 
The TimeoutException you're encountering indicates that your application is unable to acquire a resource (such as a database connection) from the resource pool within the specified timeout period. This is a common issue when working with connection pools like C3P0 (which is what com.mchange.v2.resourcepool refers to).

Here are some steps to troubleshoot and resolve this issue:
1. Check Database Availability
    Ensure that your database is up and running.
    Verify that the database is accessible from the application server (e.g., check network connectivity, firewall rules, etc.).
    Test the database connection using a tool like telnet or a database client.

2. Increase Connection Pool Timeout

The default timeout for acquiring a connection might be too short. You can increase the checkoutTimeout in your C3P0 configuration:
properties

c3p0.checkoutTimeout=30000 # 30 seconds

This setting defines how long the pool will wait for a connection before throwing a TimeoutException.
3. Review Connection Pool Configuration

Ensure that your connection pool settings are appropriate for your application's load. Key settings to check:

    maxPoolSize: The maximum number of connections in the pool.

    minPoolSize: The minimum number of connections to keep in the pool.

    initialPoolSize: The number of connections to create when the pool is initialized.

    maxIdleTime: The maximum time a connection can remain idle before being closed.

Example configuration:
properties
Copy

c3p0.maxPoolSize=50
c3p0.minPoolSize=5
c3p0.initialPoolSize=10
c3p0.maxIdleTime=300 # 5 minutes

4. Check for Connection Leaks

If connections are not being properly returned to the pool, it can lead to exhaustion. To detect leaks:

    Enable unreturnedConnectionTimeout and debugUnreturnedConnectionStackTraces in your C3P0 configuration:
    properties

    c3p0.unreturnedConnectionTimeout=300 # 5 minutes
    c3p0.debugUnreturnedConnectionStackTraces=true

    Review your code to ensure that all database connections, statements, and result sets are properly closed in a finally block.

5. Monitor Resource Usage

    Check the current number of active connections in the pool. If it's close to the maxPoolSize, you may need to increase it.

    Use monitoring tools (e.g., C3P0's built-in JMX support or a database monitoring tool) to track connection usage and identify bottlenecks.

6. Optimize Database Queries

Slow queries can tie up connections for longer than necessary, leading to connection pool exhaustion. Ensure that your queries are optimized and that appropriate indexes are in place.
7. Restart the Application

If the connection pool is in a bad state, restarting the application can help reset the pool and clear any lingering issues.
8. Upgrade C3P0

If you're using an older version of C3P0, consider upgrading to the latest version, as it may include bug fixes and performance improvements.
Example C3P0 Configuration

Here’s an example of a complete C3P0 configuration:
properties

c3p0.maxPoolSize=50
c3p0.minPoolSize=5
c3p0.initialPoolSize=10
c3p0.maxIdleTime=300
c3p0.checkoutTimeout=30000
c3p0.unreturnedConnectionTimeout=300
c3p0.debugUnreturnedConnectionStackTraces=true

9. Consider Alternative Connection Pools

If you continue to face issues, consider switching to a different connection pool, such as HikariCP, which is known for its performance and reliability.

Connection leak would seem a possibility, i.e. connection being opened, but not closed, however the jRDC handler closes the connection at the end of the handler viz:

Code: 
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
    Try
        con = Main.rdcConnector1.GetConnection
        If method = "query2" Then
            q = ExecuteQuery2(con, in, resp)
#if VERSION1
        Else if method = "query" Then
            in = cs.WrapInputStream(in, "gzip")
            q = ExecuteQuery(con, in, resp)
        Else if method = "batch" Then
            in = cs.WrapInputStream(in, "gzip")
            q = ExecuteBatch(con, in, resp)
#end if
        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
    Log($"Command: ${q}, took: ${DateTime.Now - start}ms, client=${req.RemoteAddress}"$)
End Sub

Where to go from here?
 

Attachments

Upvote 0
Additional:


I tried different settings for the pool : viz

Code: 
        If config.ContainsKey("PoolSize") Then
            If IsNumber(config.Get("PoolSize")) Then
                Dim poolSize As Int = config.Get("PoolSize")
                Dim jo As JavaObject = pool
                jo.RunMethod("setMaxPoolSize", Array(poolSize))
                jo.RunMethod("setInitialPoolSize", Array(20))
                jo.RunMethod("setMaxConnectionAge", Array(3600))
                jo.RunMethod("setMaxIdleTime", Array(3600 * 2))
'                jo.RunMethod("setAcquireRetryAttempts", Array(5))
                jo.RunMethod("setCheckoutTimeout", Array(3000))
'                jo.RunMethod("setMaxIdleTimeExcessConnections", Array(3000))
            End If
        End If

using these settings I was able to get 50 record-sets.


The pool size max was 50.

I had a look at the MySQL performance monitoring:

Resources for each user:


Snag_1f3c8c93.png



Note the jRDC user and the number of open connections. When the number of open connections reached the max pool size and BOOOM! the jRDC server errors out.

The number of connections starts at the minimum set at (20) and once 20 records are obtained it starts to increment on each record-set retrieve.

So it seems to me the code calling the pool is not giving up its connections when it has finished with them and so they accumulate to the limit.

I tried making the idle timeout 1 second hoping the pool would close these connections off, but it didn't. The number of connections kept increasing.

The c# program that is putting the data in to the tables has a constant number of connections (5)

So I don't think that playing around with the C3PO parameters is a fix.

Where to from here. Do I really have to go back to sqLite after all of my work on mySQL?
 
Upvote 0
I am not expert on this.
My understanding is opening database connection is expensive in term of resources.
Why do you need to make so many queries per milliseconds?
 
Upvote 0
rgarnett1955 said:
sql.getDailyData5min
Click to expand...
I see your sql query contains sub queries and complex calculation of aggregate functions which may need time to process. It may takes more time to complete.
Try modify to use database views, may improve the performance.
 
Upvote 0
rgarnett1955 said:
Additional:


I tried different settings for the pool : viz

Code: 
        If config.ContainsKey("PoolSize") Then
            If IsNumber(config.Get("PoolSize")) Then
                Dim poolSize As Int = config.Get("PoolSize")
                Dim jo As JavaObject = pool
                jo.RunMethod("setMaxPoolSize", Array(poolSize))
                jo.RunMethod("setInitialPoolSize", Array(20))
                jo.RunMethod("setMaxConnectionAge", Array(3600))
                jo.RunMethod("setMaxIdleTime", Array(3600 * 2))
'                jo.RunMethod("setAcquireRetryAttempts", Array(5))
                jo.RunMethod("setCheckoutTimeout", Array(3000))
'                jo.RunMethod("setMaxIdleTimeExcessConnections", Array(3000))
            End If
        End If

using these settings I was able to get 50 record-sets.


The pool size max was 50.

I had a look at the MySQL performance monitoring:

Resources for each user:


View attachment 161417


Note the jRDC user and the number of open connections. When the number of open connections reached the max pool size and BOOOM! the jRDC server errors out.

The number of connections starts at the minimum set at (20) and once 20 records are obtained it starts to increment on each record-set retrieve.

So it seems to me the code calling the pool is not giving up its connections when it has finished with them and so they accumulate to the limit.

I tried making the idle timeout 1 second hoping the pool would close these connections off, but it didn't. The number of connections kept increasing.

The c# program that is putting the data in to the tables has a constant number of connections (5)

So I don't think that playing around with the C3PO parameters is a fix.

Where to from here. Do I really have to go back to sqLite after all of my work on mySQL?
Click to expand...

aeric said:
I am not expert on this.
My understanding is opening database connection is expensive in term of resources.
Why do you need to make so many queries per milliseconds?
Click to expand...
I am making a very simple query, five rows of 4 values every 60 seconds. The query only takes 35 to 500 milliseconds to return the data.

The database is only idling at this transaction rate.

I have used jRDC with sqLite and have had no problems.

The first query always seems to be the longest response time at around 530 ms. I'm not sure why, but this is tiny
aeric said:
I see your sql query contains sub queries and complex calculation of aggregate functions which may need time to process. It may takes more time to complete.
Try modify to use database views, may improve the performance.
Click to expand...
I am testing without the aggregate queries. The query I am using is simple select ordering on the DataTimeStamp which is indexed, so very fast.

SQL: 
sql.getGHTempsAndBattery
SELECT
    UTC_DateTime,
    InsideTempDegC,
    OutsideTemperatureDegC,
    BatteryVolts,
    UTC_RecordAdded
FROM tbl_process_analog
ORDER BY UTC_DataTimeStamp DESC
LIMIT 5;
 
Upvote 0
EnriqueGonzalez said:
I don't think this will work, 5 connections could serve hundreds of users

If you are using vanilla jrdc2 then the issue must be on MySQL side that is not closing the connections successfully, may be a setting.

Connections get lost but it seems you are on a lan. So thats neither an option
Click to expand...
The c# data server that is inserting records into the two tables is far more complex as it inserts then reads the data back to check it has been inserted correctly. So that is one insert query and one select query on all fields per record inserted and there is no problem with the MySQL server. I am using a different connection string over TCP/IP, "localhost" but that should not make any difference.
 
Upvote 0
ok i found the problem, you will see it ni RDCConnetor:110-111

the connection is being created twice. one on line 110 and one on line 111, line 110 is lost on ram opened and thats why you are chocking the app

change
before:
B4X: 
Public Sub GetConnection As SQL
    If DebugQueries Then LoadSQLCommands(LoadConfigMap)
    If UsePool Then
        con = pool.GetConnection
        Return pool.GetConnection
    Else
        Return SQLite
    End If
End Sub

after:

B4X: 
Public Sub GetConnection As SQL
    If DebugQueries Then LoadSQLCommands(LoadConfigMap)
    If UsePool Then
        Return pool.GetConnection
    Else
        Return SQLite
    End If
End Sub
 
Upvote 1
EnriqueGonzalez said:
ok i found the problem, you will see it ni RDCConnetor:110-111

the connection is being created twice. one on line 110 and one on line 111, line 110 is lost on ram opened and thats why you are chocking the app

change
before:
B4X: 
Public Sub GetConnection As SQL
    If DebugQueries Then LoadSQLCommands(LoadConfigMap)
    If UsePool Then
        con = pool.GetConnection
        Return pool.GetConnection
    Else
        Return SQLite
    End If
End Sub

after:

B4X: 
Public Sub GetConnection As SQL
    If DebugQueries Then LoadSQLCommands(LoadConfigMap)
    If UsePool Then
        Return pool.GetConnection
    Else
        Return SQLite
    End If
End Sub
Click to expand...

Yes that fixed it.

I remember how that extra line got in. I was having a lot of trouble getting a connection and I was fiddling around trying to get it to work. I must have had a "seniors moment" and I added it in then forgot about it.

So thank you very much, you are a star!

I knew it had to be a connection leak, but I don't understand how the jRDC system works behind the scenes so I was looking at the handler not the connector. I am not a Java person, I'm a bog standard "C" embedded programmer so all this stuff is a mystery and being nearly seventy I don't have the inclination to start on Java as I would have to live to 100.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

aeric
  • Article Article
Share My Creation jRDC2 server with login session
Replies
2
Views
3K
aeric
aeric
aeric
  • Article Article
Share My Creation [Project Template] [B4X] jRDC2 Client (B4A, B4i, B4J)
Replies
3
Views
4K
aeric
aeric
aeric
  • Article Article
Share My Creation [Project Template] jRDC2 Server
Replies
3
Views
4K
aeric
aeric
aeric
  • Article Article
Share My Creation jRDC2 server authentication + console client
Replies
2
Views
3K
aeric
aeric
OliverA
  • Article Article
B4J Tutorial jRDC2 client example (using modded jRDC2)
Replies
10
Views
13K
Diceman
Diceman
Back
Top