B4J Question jServer and MySQL

D

Declan

Well-Known Member
Licensed User
Longtime User
I have a jServer running - thanks to Erel for amazing support.
I receive the POST request from my remote device and am able to reply from jServer back to the device.
I must now query the MySQL database table for a value.
I have looked at the jServer example (with MySQL), but cannot get my head around the MySQL connection.

Can anyone point be in the direction of a simple jServer / MySQL query?
 
Sort by date Sort by votes
D

Declan

Well-Known Member
Licensed User
Longtime User
I need to query 1 field of the table as a string.
I then send this string back to the remote device with:
B4X: 
resp.Write("GOTCHA")
Where "GOTCHA" is the result of the query.

In a previous version, not jServer, I used RDC with:
B4X: 
sql.get_tracking=SELECT tracking FROM devices WHERE deviceid=?
 
Upvote 0
aeric

aeric

Expert
Licensed User
Longtime User
Here is a minimal example.

Client:
B4X: 
Sub PostData
    Try
        Dim j As HttpJob
        j.Initialize("", Me)
        j.PostString("http://localhost:8080", "device_id=1")
        Wait For (j) JobDone(j As HttpJob)
        If j.Success Then
            Log( j.GetString )
        Else
            Log( j.ErrorMessage )
        End If
    Catch
        Log(LastException.Message)
    End Try
    j.Release
End Sub
 

Attachments

  • jServer.zip
    1.3 KB · Views: 143
Upvote 1
D

Declan

Well-Known Member
Licensed User
Longtime User
I am getting the following error on the server.
It relates to "Too Many Connections":

B4X: 
Tue Jul 18 05:07:28 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:30 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:30 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:30 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:31 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2023-07-18 05:07:31.786:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#1: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@6eb646cd -- 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 source rejected establishment of connection,  message from server: "Too many connections"
    at jdk.internal.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1031)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at jdk.internal.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2023-07-18 05:07:31.786:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#1: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@7bc10d84 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#2: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@3a1d7f27 -- 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 source rejected establishment of connection,  message from server: "Too many connections"
    at jdk.internal.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1031)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at jdk.internal.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#2: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@7bc10d84 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#0: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@67fb0c0 -- 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 source rejected establishment of connection,  message from server: "Too many connections"
    at jdk.internal.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1031)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at jdk.internal.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#0: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@7bc10d84 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
 
Upvote 0
aeric

aeric

Expert
Licensed User
Longtime User
Declan said:
I am getting the following error on the server.
It relates to "Too Many Connections":

B4X: 
Tue Jul 18 05:07:28 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:30 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:30 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:30 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:31 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2023-07-18 05:07:31.786:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#1: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@6eb646cd -- 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 source rejected establishment of connection,  message from server: "Too many connections"
    at jdk.internal.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1031)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at jdk.internal.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2023-07-18 05:07:31.786:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#1: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@7bc10d84 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#2: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@3a1d7f27 -- 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 source rejected establishment of connection,  message from server: "Too many connections"
    at jdk.internal.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1031)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at jdk.internal.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#2: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@7bc10d84 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#0: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@67fb0c0 -- 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 source rejected establishment of connection,  message from server: "Too many connections"
    at jdk.internal.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1031)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at jdk.internal.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#0: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@7bc10d84 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
Click to expand...

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

Hi, Following this thread : http://www.b4x.com/android/forum/threads/sql-error-operation-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...
www.b4x.com www.b4x.com

I usually set x to 100
 
Last edited:
Upvote 0
aeric

aeric

Expert
Licensed User
Longtime User
Declan said:
Where in my code do I put:
B4X: 
Dim jo As JavaObject = pool
jo.RunMethod("setMaxPoolSize", Array(100))
Click to expand...
You can put it after initialize the Pool.

B4X: 
Private Sub OpenConnection
    Pool.Initialize("com.mysql.jdbc.Driver", _
    "jdbc:mysql://localhost/test?characterEncoding=utf8", _
    "root", _
    "password")
    Dim jo As JavaObject = Pool
    jo.RunMethod("setMaxPoolSize", Array(100))
End Sub
 
Upvote 0
D

Declan

Well-Known Member
Licensed User
Longtime User
aeric said:
You can put it after initialize the Pool.

B4X: 
Private Sub OpenConnection
    Pool.Initialize("com.mysql.jdbc.Driver", _
    "jdbc:mysql://localhost/test?characterEncoding=utf8", _
    "root", _
    "password")
    Dim jo As JavaObject = Pool
    jo.RunMethod("setMaxPoolSize", Array(100))
End Sub
Click to expand...
Thanks, that seems to have done the trick.
I will run it for a while and see how it goes.
 
Upvote 0
D

Declan

Well-Known Member
Licensed User
Longtime User
Unfortunately, it runs for a while on the server, then gives me the "Too many connections" error.
I have even increased the "setMaxPoolSize" to 1000.

My Code:
B4X: 
'Handler class
Sub Class_Globals
    Private SQL As SQL
    Private Pool As ConnectionPool
    Private Request As ServletRequest
    Private Response As ServletResponse
End Sub

Public Sub Initialize
    OpenConnection
End Sub

Sub Handle (req As ServletRequest, resp As ServletResponse)
    Request = req
    Response = resp
    
    Dim input() As Byte = Bit.InputStreamToBytes(req.InputStream)
    Dim msg As String = BytesToString(input, 0, input.Length, "utf8")
    
    Log("Got: " & msg)
    
    Dim myDeviceID As String
    
    If msg.IndexOf("TRACK") > -1 Then
        Log("Got Track")
        myDeviceID = msg.SubString(msg.Length - 5)
        Log ("Tracking ID: " & myDeviceID)
    End If
    
    
    Try
        SQL = GetConnection
        Dim device_id As String = myDeviceID
'        Log( device_id )
        Dim strSQL As String = "SELECT tracking FROM devices WHERE deviceid=?"
        Dim Res As ResultSet = SQL.ExecQuery2(strSQL, Array As String(device_id))
        If Res.NextRow Then
            Response.Write( Res.GetString2(0) )
        End If
        Res.Close
    Catch
        Log(LastException)
    End Try
    If SQL <> Null And SQL.IsInitialized Then SQL.Close
End Sub

Private Sub OpenConnection
    Pool.Initialize("com.mysql.jdbc.Driver", _
    "jdbc:mysql://localhost/invictus?characterEncoding=utf8", _
    "root", _
    "Declan2014!@#$%")
    
    Dim jo As JavaObject = Pool
    jo.RunMethod("setMaxPoolSize", Array(1000))
End Sub

Public Sub GetConnection As SQL
    Return Pool.GetConnection
End Sub
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
Upvote 0
aeric

aeric

Expert
Licensed User
Longtime User
Sorry because I was creating a quick demo.
I am not clear what is the actual situation like how many connections are creating to the server.
Like mentioned by OliverA, study the code for jRDC2.
For my case, I am usually using my own Web API Template.
 
Upvote 0
aeric

aeric

Expert
Licensed User
Longtime User
Declan said:
Dim input() As Byte = Bit.InputStreamToBytes(req.InputStream) Dim msg As String = BytesToString(input, 0, input.Length, "utf8") Log("Got: " & msg) Dim myDeviceID As String If msg.IndexOf("TRACK") > -1 Then Log("Got Track") myDeviceID = msg.SubString(msg.Length - 5) Log ("Tracking ID: " & myDeviceID) End If
Click to expand...
By the way, why are you sending bytes? Why not use PostString?
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
Declan said:
still confused
Click to expand...

The super easy way would be to place both OpenConnection and GetConnection into Main. In Main, call OpenConnection once. In your handler code do
B4X: 
SQL = Main.GetConnection
to retrieve a connection from the pool.

Later on, if you want to, you can place the relevant pool code into another class (as jRDC2 does) or code module.
 
Upvote 0
You must log in or register to reply here.
Top