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:
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:
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?
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:
Sever side (jRDC2) log:
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.140So 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:
jRDC2 Server Config File:
#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?
