B4J Question sql.SQLException: "Too many connections" with ConnectionPool library

jmon

Well-Known Member
Licensed User
Longtime User
Hi,

Following this thread : http://www.b4x.com/android/forum/th...ion-not-allowed-after-resultset-closed.42516/

I have changed all my SQL queries and SQL connection to the Pool from ConnectionPool method. I now run into this problem:
Jul 07, 2014 11:03:19 AM com.mchange.v2.resourcepool.BasicResourcePool$Scattered
AcquireTask run
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1186
11a -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to
acquire a needed new resource, we failed to succeed more than the maximum number
of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data sourc
e rejected establishment of connection, message from server: "Too many connecti
ons"
at sun.reflect.GeneratedConstructorAccessor20.newInstance(Unknown Source
)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1114)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2493)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2
526)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2311)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor18.newInstance(Unknown Source
)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java
:347)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManag
erDataSource.java:146)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnecti
on(WrapperConnectionPoolDataSource.java:195)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnecti
on(WrapperConnectionPoolDataSource.java:184)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionRe
sourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResource
Pool.java:1086)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPe
ndingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourc
ePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.ru
n(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:648)

Jul 07, 2014 11:03:19 AM com.mchange.v2.resourcepool.BasicResourcePool forceKill
Acquires
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicR
esourcePool@7a84ce is interrupting all Threads waiting on a resource to check ou
t. Will try again in response to new client requests.
calendarupdate._requestasync (java line: 222)
java.sql.SQLException: An attempt by a client to checkout a Connection has timed
out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne
ction(C3P0PooledConnectionPool.java:687)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(A
bstractPoolBackedDataSource.java:140)
at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPo
ol.java:41)
at com.jmontserrat.postchatclient.calendarupdate._requestasync(calendaru
pdate.java:222)
at com.jmontserrat.postchatclient.main._appstart(main.java:463)
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:93)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at com.jmontserrat.postchatclient.main.start(main.java:35)
at com.sun.javafx.application.LauncherImpl$8.run(Unknown Source)
at com.sun.javafx.application.PlatformImpl$7.run(Unknown Source)
at com.sun.javafx.application.PlatformImpl$6$1.run(Unknown Source)
at com.sun.javafx.application.PlatformImpl$6$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl$6.run(Unknown Source)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(Unknown Source)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.access$300(Unknown Source)
at com.sun.glass.ui.win.WinApplication$4$1.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out whil
e waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePo
ol@7a84ce -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicRes
ourcePool.java:1416)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(
BasicResourcePool.java:606)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicR
esourcePool.java:526)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConn
ectionInUse(C3P0PooledConnectionPool.java:755)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne
ction(C3P0PooledConnectionPool.java:682)
... 22 more

My application runs with about 30 users. As you can see from the log, there are 2 errors:
B4X:
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1186
11a -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to
acquire a needed new resource, we failed to succeed more than the maximum number
of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data sourc
e rejected establishment of connection,  message from server: "Too many connecti
ons"

And

B4X:
java.sql.SQLException: An attempt by a client to checkout a Connection has timed
out.

Is it caused by the pool library? Am I not closing the connections properly?

Thanks
Jmon
 

jmon

Well-Known Member
Licensed User
Longtime User
Yes, I am for each query.

But I have a question. For the async queries, I close the connections the way you shown me in this thread : http://www.b4x.com/android/forum/th...ion-not-allowed-after-resultset-closed.42516/, but how about the non async queries is this ok:
B4X:
Dim sq As SQL = Pool.GetConnection
MYUNIQUEID = sq.ExecQuerySingleResult("SELECT id FROM user WHERE nick = '" & MYUSERNAME & "';")
sq.Close

[EDIT]
Do I need to close the sq.close before the crsr.close or after?

Thanks
 
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
Yes, I am for each query.

o_O NO! Arghhh, I messed up on one of them, instead of calling
B4X:
Dim sq As SQL = sender
in the QueryComplete event I was calling:
B4X:
Dim sq As SQL = Main.Pool.GetConnection

I found the error after reviewing my code. Thanks a lot, and sorry for wasting your time!

Regards
jmon
 
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
[UPDATE] After implementing correctly the Pool Library, my application was running fine. Sometimes I would get a crash "Too Many Connections".

Jul 07, 2014 11:02:46 AM com.mchange.v2.resourcepool.BasicResourcePool$Scattered
AcquireTask run
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@2d31
0e -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to a
cquire a needed new resource, we failed to succeed more than the maximum number
of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data sourc
e rejected establishment of connection, message from server: "Too many connecti
ons"
at sun.reflect.GeneratedConstructorAccessor20.newInstance(Unknown Source
)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1114)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2493)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2
526)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2311)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor18.newInstance(Unknown Source
)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java
:347)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManag
erDataSource.java:146)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnecti
on(WrapperConnectionPoolDataSource.java:195)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnecti
on(WrapperConnectionPoolDataSource.java:184)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionRe
sourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResource
Pool.java:1086)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPe
ndingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourc
ePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.ru
n(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:648)

Jul 07, 2014 11:02:46 AM com.mchange.v2.resourcepool.BasicResourcePool forceKill
Acquires
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicR
esourcePool@7a84ce is interrupting all Threads waiting on a resource to check ou
t. Will try again in response to new client requests.
User: node UID: 219

Jul 07, 2014 11:03:19 AM com.mchange.v2.resourcepool.BasicResourcePool$Scattered
AcquireTask run
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1186
11a -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to
acquire a needed new resource, we failed to succeed more than the maximum number
of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data sourc
e rejected establishment of connection, message from server: "Too many connecti
ons"
at sun.reflect.GeneratedConstructorAccessor20.newInstance(Unknown Source
)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1114)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2493)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2
526)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2311)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor18.newInstance(Unknown Source
)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java
:347)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManag
erDataSource.java:146)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnecti
on(WrapperConnectionPoolDataSource.java:195)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnecti
on(WrapperConnectionPoolDataSource.java:184)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionRe
sourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResource
Pool.java:1086)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPe
ndingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourc
ePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.ru
n(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:648)

Jul 07, 2014 11:03:19 AM com.mchange.v2.resourcepool.BasicResourcePool forceKill
Acquires
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicR
esourcePool@7a84ce is interrupting all Threads waiting on a resource to check ou
t. Will try again in response to new client requests.
calendarupdate._requestasync (java line: 222)
java.sql.SQLException: An attempt by a client to checkout a Connection has timed
out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne
ction(C3P0PooledConnectionPool.java:687)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(A
bstractPoolBackedDataSource.java:140)
at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPo
ol.java:41)
at com.jmontserrat.postchatclient.calendarupdate._requestasync(calendaru
pdate.java:222)
at com.jmontserrat.postchatclient.main._appstart(main.java:463)
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:93)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at com.jmontserrat.postchatclient.main.start(main.java:35)
at com.sun.javafx.application.LauncherImpl$8.run(Unknown Source)
at com.sun.javafx.application.PlatformImpl$7.run(Unknown Source)
at com.sun.javafx.application.PlatformImpl$6$1.run(Unknown Source)
at com.sun.javafx.application.PlatformImpl$6$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl$6.run(Unknown Source)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(Unknown Source)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.access$300(Unknown Source)
at com.sun.glass.ui.win.WinApplication$4$1.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out whil
e waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePo
ol@7a84ce -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicRes
ourcePool.java:1416)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(
BasicResourcePool.java:606)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicR
esourcePool.java:526)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConn
ectionInUse(C3P0PooledConnectionPool.java:755)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne
ction(C3P0PooledConnectionPool.java:682)
... 22 more


C:\Program Files (x86)\Java\jre8\bin>"C:\Program Files (x86)\Java\jre8\bin\java.
exe" -jar "C:\Program Files\Postchat2\PostChat.exe"
Program started.

Jul 07, 2014 11:03:33 AM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource g
etPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acqu
ireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCo
mmitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> fa
lse, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connection
TesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceN
ame -> 1hge10x9345qns9t9601z|14c3e4f, debugUnreturnedConnectionStackTraces -> fa
lse, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLoca
tion -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge1
0x9345qns9t9601z|14c3e4f, idleConnectionTestPeriod -> 600, initialPoolSize -> 3,
jdbcUrl -> jdbc:mysql://xxxxxxxxxx/intranet?characterEncoding=utf8, ma
xAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdl
eTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxStateme
ntsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQue
ry -> null, properties -> {user=******, password=******}, propertyCycle -> 0, st
atementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, test
ConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides ->
{}, usesTraditionalReflectiveProxies -> false ]
calendarpopup._updateeventtime (java line: 697)
java.sql.SQLException: An attempt by a client to checkout a Connection has timed
out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne
ction(C3P0PooledConnectionPool.java:687)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(A
bstractPoolBackedDataSource.java:140)
at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPo
ol.java:41)
at com.xxx.xxxx.calendarpopup._updateeventtime(calenda
rpopup.java:697)
at com.xxx.xxxx.calendarpopup._initialize(calendarpopu
p.java:492)
at com.xxx.xxxx.jcalendar._initialize(jcalendar.java:2
67)
at com.xxx.xxxx.pagecalendar._initialize(pagecalendar.
java:170)
at com.xxx.xxxx.main._appstart(main.java:320)
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:93)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at com.jmontserrat.postchatclient.main.start(main.java:35)
at com.sun.javafx.application.LauncherImpl$8.run(Unknown Source)
at com.sun.javafx.application.PlatformImpl$7.run(Unknown Source)
at com.sun.javafx.application.PlatformImpl$6$1.run(Unknown Source)
at com.sun.javafx.application.PlatformImpl$6$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl$6.run(Unknown Source)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(Unknown Source)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.access$300(Unknown Source)
at com.sun.glass.ui.win.WinApplication$4$1.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out whil
e waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePo
ol@6ba066 -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicRes
ourcePool.java:1416)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(
BasicResourcePool.java:606)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicR
esourcePool.java:526)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConn
ectionInUse(C3P0PooledConnectionPool.java:755)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne
ction(C3P0PooledConnectionPool.java:682)
... 25 more


C:\Program Files (x86)\Java\jre8\bin>

My application runs on 28 computers and some android phones. Each client will on startup query ~10 times the MySQL 5.5 Database.

This page recommends to set the max_connections to a value higher than the max connection value from the pool library:
If you are using connection pool on the application side, max_connections should be bigger than max connections on the pool side. Connection pooling is also a good alternative if you are expecting a high number of connections.

How to know the max connection from the pool library? I dont't see it in the error report? I have set it to 300 in MySQL, and that seems to fix the problem for now.

Thanks
Jmon.
 
Upvote 0

alienhunter

Active Member
Licensed User
Longtime User
[UPDATE] After implementing correctly the Pool Library, my application was running fine. Sometimes I would get a crash "Too Many Connections".



My application runs on 28 computers and some android phones. Each client will on startup query ~10 times the MySQL 5.5 Database.

This page recommends to set the max_connections to a value higher than the max connection value from the pool library:


How to know the max connection from the pool library? I dont't see it in the error report? I have set it to 300 in MySQL, and that seems to fix the problem for now.

Thanks
Jmon.

Hi ,
now I got the same error but with about 80 computers and tablets "too many connections" , did erel's recommended code helped ?
thanks

AH
 
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
Upvote 0

alienhunter

Active Member
Licensed User
Longtime User
Check that all your connections and cursors are closed properly each time you call them. You can try increasing the connection limit in Mysql: https://major.io/2007/01/24/increase-mysql-connection-limit/

In my case, with 40 users, I had to increase it to 800, because I have many concurrent connections opened at the same time.

thanks I increased the connections to 300 , checked all cursors , still there are "sleeping" ones from the tablet's ( i might missed a couple of cursors or something else )
for too long so I decreased the the sleep value to 14400 in mysql so it kicks it out if still open ,
not pretty but it works ;) due to advanced laziness:eek: ... reading old code ....
 
Upvote 0

balarii

New Member
Licensed User
Longtime User
For MySQL / MariaDB you can kill the connections in sleep state, like this:

Attention!!! Connect with an user which have the permission to KILL CONNECTION (like root).


B4X:
Sub Process_Globals
  '-----------------------------------
  ' MariaDB Connection
  Private poolRemote As ConnectionPool
  Private db_server As String = "localhost"
  Private db_name As String = "test"
  Private db_user As String = "root"
  Private db_password As String = "test"
  Private SleepMaxValue As Int = 90

  Private Timer1 As Timer
End Sub

Sub AppStart (Args() As String)
  Dim jSQL As String = "jdbc:mysql://{server}/{db}?characterEncoding=utf8"
  jSQL = jSQL.Replace("{server}", db_server)
  jSQL = jSQL.Replace("{db}", db_name)
  poolRemote.Initialize("com.mysql.jdbc.Driver", jSQL, db_user, db_password)

  '!!! Very important, choose a big value for the timer, otherwise you will be banned by the MySQL server
  Timer1.Initialize("Timer1", SleepMaxValue * 1000)
  Timer1.Enabled = True

  ' . . .
End Sub

Private Sub Timer1_Tick
  KillConnections(SleepMaxValue, db_name)
End Sub

'-----------------------------------------------------
Sub KillConnections(timeout As Int, dbName As String)
  Try
    Dim sql As String = ""
    sql = "SELECT GROUP_CONCAT(CONCAT('KILL CONNECTION ',ID,';') SEPARATOR ' ') KillConnections " & CRLF
    sql = sql & "FROM information_schema.processlist " & CRLF
    sql = sql & "WHERE `Time`>{0} " & CRLF
    sql = sql & "AND `DB`='{1}' " & CRLF
    sql = sql & "AND `Command`='Sleep' " & CRLF
    sql = sql & "AND `User`<>'system user' " & CRLF
    sql = sql.Replace("{0}", timeout)
    sql = sql.Replace("{1}", dbName)

    Log("KillConnections: " & "Try to kill Sleep connections")
    Private sqlTmp As SQL = poolRemote.GetConnection()
    sqlTmp.ExecQueryAsync("SQLKillConnections", sql, Null)
    ' ------>
    'continue in SQLKillConnections_QueryComplete
  Catch
    sqlTmp.Close()
    Log("KillConnections Error: " & LastException)
  End Try
End Sub

Sub SQLKillConnections_QueryComplete(Success As Boolean, Crsr As ResultSet)
  Try
    'get the connection
    Dim sqlTmp As SQL = Sender

    If Success Then
      'read server response
      Dim sqlCmd_ As String = ""
      Do While Crsr.NextRow()
        sqlCmd_ = Crsr.GetString2(0)
        Log("SQLKillConnections_QueryComplete: " & sqlCmd_)
      Loop
      Crsr.Close()
      sqlTmp.Close()

      '-------------------------------------
      'execute kill connections, one by one
      Dim sql As String = sqlCmd_
      If sql <> Null Then
        Private sqlTmp1 As SQL = poolRemote.GetConnection()

        Dim strSplit() As String = Regex.Split(";", sql)
        For Each s_ As String In strSplit
          If s_.Trim() <> "" Then
            sqlTmp1.AddNonQueryToBatch(s_, Null)
          End If
        Next
        sqlTmp1.ExecNonQueryBatch("SQLKillConnectionsResult")
        ' ------>
        'continue in SQLKillConnectionsResult_NonQueryComplete
      End If
    Else
      sqlTmp1.Close()
      sqlTmp.Close()
    End If
  Catch
    sqlTmp1.Close()
    sqlTmp.Close()
    Log("SQLKillConnections_QueryComplete Error: " & LastException)
  End Try
End Sub

Sub SQLKillConnectionsResult_NonQueryComplete(Success As Boolean)
  Try
    'get the connection
    Dim sqlTmp As SQL = Sender

    Log("SQLKillConnectionsResult_NonQueryComplete: " & Success)
    If Success Then
      Log("SQLKillConnectionsResult_NonQueryComplete: " & "All sleep connections are closed")
    Else
      Log("SQLKillConnectionsResult_NonQueryComplete: " & LastException)
    End If

    sqlTmp.Close()
  Catch
    sqlTmp.Close()
    Log("SQLSendToServer_NonQueryComplete Error: " & LastException)
  End Try
End Sub
 
Last edited:
Upvote 0
Top