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
Do you want to access the data as string, html, json or raw bytes with B4A, B4i, B4J or display as web page? :)
 
Upvote 0
I need to query 1 field of the table as a string.
I then send this string back to the remote device with:
B4X: Copy 
resp.Write("GOTCHA")
Where "GOTCHA" is the result of the query.

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

Client:
B4X: Copy 
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

Upvote 1
I am getting the following error on the server.
It relates to "Too Many Connections":

B4X: Copy 
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
Declan said:
I am getting the following error on the server.
It relates to "Too Many Connections":

B4X: Copy 
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
Declan said:
Where in my code do I put:
B4X: Copy 
Dim jo As JavaObject = pool
jo.RunMethod("setMaxPoolSize", Array(100))
Click to expand...
You can put it after initialize the Pool.

B4X: Copy 
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
aeric said:
You can put it after initialize the Pool.

B4X: Copy 
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
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: Copy 
'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
Upvote 0
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
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
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
Code: Copy 
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.

Similar Threads

D
  • Question Question
B4J Question B4j jserver
Replies
9
Views
1K
davelew1s
D
Magma
  • Question Question
B4J Question Dockers and JServer apps
Replies
6
Views
2K
aeric
aeric
C
  • Article Article
B4J Library jServer & jWebSocketClient using Jetty 11.0.26
Replies
0
Views
2K
Chris2
C
aeric
  • Article Article
Share My Creation [B4J] [MySQL] [API] Server (Key + Token) and [B4X] User Login Apps
2 3
Replies
45
Views
51K
aeric
aeric
udg
  • Question Question
B4J Question [solved] Jserver+HTML+JS
Replies
11
Views
8K
udg
udg
Back
Top