B4J Question Mysql and SSL

micro

Well-Known Member
Licensed User
Longtime User
....from italian forum.

Hi to all
I started again to make some attempt yesterday, but I still have some doubt.
Saying again:
on the Aruba Linux Cloud where I have already installed 'mysql', yesterday I installed the certificates using this procedure (till the input of an utent) and till here everything is fine.
In fact now I have in the 'mysql' a ssl folder with
ca-cert.pem
server-cert.pem
server-key.pem

I put in my.cnf the right paths and I have enabled the log and here I find a problem, because for ssl the path of the certificates is wrong but the path are those indeed.
However I will watcj it later.

The part I cannot understand is the setting of the Pc with which I connect to the cloud.
When I worked without ssl (they are not critical data) I had:
B4X:
Try
        pool.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://ip_cloud:3306/name_db", "user", "pass")
        Dim jo As JavaObject = pool
        jo.RunMethod("setMaxIdleTime", Array As Object(3600))
        jo.RunMethod("setMaxPoolSize", Array As Object(200))
        jo.RunMethod("setCheckoutTimeout", Array As Object(3500))
        NotificaShow("Info", "Connessione database Ok!", "info", 2000)
        connessocloud = True
        Inizializza
        CaricaArchivi
        LoadColumn
    Catch As Exception
        NotificaShow("Errore", "Errore connessione server mysql!", "errore", 2000)
        connessocloud = False
    End Try

and so far everything is ok because it worked well.
Now that I'm going to use ssl (I installed on win OpenSSl32) the string should become:
B4X:
pool.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://ip_cloud:3306/name_db?&useSSL=true&serverSSlCert=C:\Program Files (x86)\OpenSSL-Win32\certs\ca-cert.pem", "user", "pass")
It's correct?
Or do I have to do something else?
Because it does not work.
Thanks
 

micro

Well-Known Member
Licensed User
Longtime User
Remove the Try / Catch block and post the full error message.
Thanks for the reply

With this:
pool.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://ip_cloud:3306/name_db", "user", "pass")
all ok, work fine (user without the use ssl option)

With this:
pool.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://ip_cloud:3306/name_db?&useSSL=true&serverSSlCert=C:\Program Files (x86)\OpenSSL-Win32\certs\ca-cert.pem", "user", "pass")
User with use ssl option

B4X:
Waiting for debugger to connect...
Program started.
giu 09, 2019 11:11:55 AM com.mchange.v2.log.MLog
INFORMAZIONI: MLog clients using java 1.4+ standard logging.
giu 09, 2019 11:11:55 AM com.mchange.v2.c3p0.C3P0Registry
INFORMAZIONI: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
giu 09, 2019 11:11:55 AM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
INFORMAZIONI: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 3500, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge15ba3giewdcipqhkl|1126091, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge15ba3giewdcipqhkl|1126091, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://80.211.187.141:3306/AttivitaClaudio?useSSL=true&ServerSslCert=C:\Program Files (x86)\OpenSSL-Win32\certs\ca-cert.pem, maxAdm...
giu 09, 2019 11:12:28 AM com.mchange.v2.resourcepool.BasicResourcePool
AVVERTENZA: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@13f35a7 -- 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:
java.sql.SQLException: null,  message from server: "Host '93-43-182-236.ip93.fastwebnet.it' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1042)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.GeneratedConstructorAccessor14.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
    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)
giu 09, 2019 11:12:28 AM com.mchange.v2.resourcepool.BasicResourcePool
AVVERTENZA: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@16ec123 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
giu 09, 2019 11:12:28 AM com.mchange.v2.resourcepool.BasicResourcePool
AVVERTENZA: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@13227ac -- 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:
java.sql.SQLException: null,  message from server: "Host '93-43-182-236.ip93.fastwebnet.it' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1042)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.GeneratedConstructorAccessor14.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
    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)
giu 09, 2019 11:12:28 AM com.mchange.v2.resourcepool.BasicResourcePool
AVVERTENZA: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@16ec123 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
giu 09, 2019 11:12:28 AM com.mchange.v2.resourcepool.BasicResourcePool
AVVERTENZA: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@112c496 -- 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:
java.sql.SQLException: null,  message from server: "Host '93-43-182-236.ip93.fastwebnet.it' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1042)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.GeneratedConstructorAccessor14.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
    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)
giu 09, 2019 11:12:28 AM com.mchange.v2.resourcepool.BasicResourcePool
AVVERTENZA: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@16ec123 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.

Why aren't you using a certified certificate from one of the free SSL providers? It will be simpler.
Where i can download this free certified and how use it on mysql server side (cloud) and remote pc side?
And the connection string how would it become?
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
AVVERTENZA: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@13f35a7 -- 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:
java.sql.SQLException: null, message from server: "Host '93-43-182-236.ip93.fastwebnet.it' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
 
Upvote 0

micro

Well-Known Member
Licensed User
Longtime User
The server firewall has blocked you. You must first remove this blocking
do you mean server (cloud) where mysql is installed?
I don't think there is an active firewall and then why if the connection is without ssl it does not block?
I use different port for other uses and have never been blocked.
Perhaps blocked is meant because it receives the string (with use ssl) incorrectly and mysql does not respond in the right time
because it does not recognize the string?
Or not?

Thanks Erel and DonManfredd
 
Upvote 0
Top