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

jmon

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
Click to expand...

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
 
Sort by date Sort by votes
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

jmon

Well-Known Member
Licensed User
Longtime User
jmon said:
Yes, I am for each query.
Click to expand...

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

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>
Click to expand...

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.
Click to expand...

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

alienhunter

Active Member
Licensed User
Longtime User
jmon said:
[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.
Click to expand...

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

jmon

Well-Known Member
Licensed User
Longtime User
Upvote 0
alienhunter

alienhunter

Active Member
Licensed User
Longtime User
jmon said:
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.
Click to expand...

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
jmon

jmon

Well-Known Member
Licensed User
Longtime User
Upvote 0
B

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
You must log in or register to reply here.
Top