B4J Question Application Freezes when connecting to Remote MySQL

[jmon]

Hello,

As the title says, my application freezes when I connect to a remote MySQL database. I know the reason: I didn't open the port to connect to my Remote DB, but I'm trying to find out why SQL freezes the app

I tried initially with ConnectionPool:

MySQL.Initialize("com.mysql.jdbc.Driver", _
        $"jdbc:mysql://${MySQLHost}:${MySQLPort}/${MySQLDB}?characterEncoding=utf8"$, MySQLUSR, MySQLPW)

'....
Remote = MySQL.GetConnection
'-> There I get an error message

Error when calling "MySQL.GetConnection"

Waiting for debugger to connect...
Program started.
Dec 29, 2016 11:23:32 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
Dec 29, 2016 11:23:32 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.2.1 [built 20-March-2013 11:16:28 +0000; debug? true; trace: 10]
Connecting To MySQL...
Synchronization started...
Dec 29, 2016 11:23:42 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge1459lkx337z1pvni9x|19a6113, debugUnreturnedConnectionStackTraces -> false, description ->...
Error: SynchronizeLocalGet_QueryComplete
com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@19fca40 -- timeout at awaitAvailable()
Synchronization started...
Error: SynchronizeLocalGet_QueryComplete
com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@19fca40 -- timeout at awaitAvailable()
Synchronization started...
Dec 29, 2016 11:25:05 PM com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask run
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1f45136 -- 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.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.GeneratedConstructorAccessor6.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:338)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2232)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
at sun.reflect.GeneratedConstructorAccessor2.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:345)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:589)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:213)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:297)
... 20 more
Dec 29, 2016 11:25:05 PM com.mchange.v2.resourcepool.BasicResourcePool forceKillAcquires
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@19fca40 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
Error: SynchronizeLocalGet_QueryComplete
com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.

Then I tried with SQL Async connection directly:

MySQL.InitializeAsync("MySQLConnect", "com.mysql.jdbc.Driver", _
       $"jdbc:mysql://${MySQLHost}:${MySQLPort}/${MySQLDB}?characterEncoding=utf8"$, MySQLUSR, MySQLPW)

But it just freezes there, without even getting to the "Ready" event. At least with the connectionPool, there seems to be a timeout.

Apart from not opening the port on my remote server, Am I doing anything wrong that causes the app to freeze?

Thank you
Jmon.

 

[giannimaione]

with my APP i use:
Sql.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://" & ipAddress & "/database_name",user,password)

 

[jmon]

hum strange, now I opened the port, InitializeAsync freezes my app but with ConnectionPool and Initialize2 it doesn't freeze...

 

[Erel]

You should always use ConnectionPool when connecting to remote databases.

 

[jmon]

Thank you.

I set up the connectionPool, and I have 1 question:

- Each time I ask for a connection from the pool, the app freezes for maybe half a second. I need to synchronize some data with a remote database every 5 minutes in 3 steps:
1) Upload local data to remote database
2) Download remote data (from table 1) to local database
3) Download remote data (from table 2) to local database

I know how to use the connectionPool on a LAN network and used it on many LAN projects, but never used it with remote DB.

How could I solve the small freeze?:

Request only 1 time the connection pool before synchronizing the 3 steps and closing it only after the 3 steps?:

Sub Process_Globals
    'sq is a global variable shared though the 3 steps
    'only close sq when sync is finished
    Dim sq as sql
End sub

Sub sync
    'get the connection 1 time before syncing the 3 steps
    sq = Pool.GetConnection
    SyncStep1
End Sub

Sub SyncStep1
    sq.execnonQuery()
    'upload data to remote
End sub

Sub SyncStep1_Finished
    SyncStep2
End sub

Sub SyncStep2
    sq.exequery()
    'upload data to remote
End sub

Sub SyncStep2_Finished
    SyncStep3
End sub

'....

Sub SyncFinished
    'close the connection after the 3 steps
    sq.Close
End sub

or is the only way to request the pool at each step? problem here is I get 3x small freeze

Sub sync
    SyncStep1
End Sub

Sub SyncStep1
    dim sq as sql = pool.getconnection
    sq.execnonQuery()
    'upload data to remote
    sq.close
End sub

'.... do the same for next steps

Thanks for your help.
Jmon

 

[Erel]

What is the output of:

Dim n As Long = DateTime.Now
sq = Pool.GetConnection
Log("1: " & (DateTime.Now - n))
SyncStep1
Log("2: " & (DateTime.Now - n))

 

[Harris]

I don't see where you are setting the pool size?

Here is the init from ABMaterial DBM module that sets up a pool for use... I set the size to 100 and it performs well.

Sub InitializeMySQL(jdbcUrl As String ,login As String, password As String, poolSize As Int)
    Log("init mysql")
    UsePool = True
    Try
       pool.Initialize("com.mysql.jdbc.Driver", jdbcUrl, login, password)
    Catch
       Log("Last Pool Init Except: "&LastException.Message)
    End Try

   ' change pool size...
    Dim jo As JavaObject = pool
    jo.RunMethod("setMaxPoolSize", Array(poolSize)) 
End Sub


Sub GetSQL() As SQL   ' I always use MySQL - so get a pool connection
    If UsePool Then
        Return pool.GetConnection     
    Else
        Return SQLite
    End If 
End Sub

Sub CloseSQL(mySQL As SQL)   ' release connection when done any operation...
    If UsePool Then
        mySQL.Close
    End If 
End Sub

 

[jmon]

What is the output of:

hi, this is the output, the first time I get a connection from the pool:
1: 4502
2: 4504

for the next times I request a connection from the pool:
1: 269
2: 271

It looks like the first time I request the connection, right after starting the program, it takes 4 seconds to get the connection. The subsequent times, it's faster, but I can still feel the small freeze. To visualize the freeze, I put a progressIndicator (Spinning) with an indeterminate value (-1) so it spins continuously. I can see the spinning animation freezing each time I get the pool connection.

I don't get this freeze when I don't use the pool...

I don't see where you are setting the pool size?

I'm not changing the default settings in my app. I just run with the defaults.

 

[Erel]

Here: https://www.b4x.com/android/forum/threads/updates-to-internal-libaries.48274/#post-475316

 

[jmon]

Here

Thank you, I will test that and report!

 

[jmon]

Hi, I updated jServer, but don't get the same as this example:

pool.GetConnectionAsync("pool")
...
Private Sub ConnectionReady (Success As Boolean, SQL As SQL)
If Success Then
  'work with connection
  SQL.Close 'don't forget to close!
End If
End Sub

I'm missing "SQL as SQL" in the event, when creating the event Using spacebar/tab autocomplete:

Also, I imagine there is an error in the example, should it be (adding "pool" before "Connection Ready":

pool.GetConnectionAsync("pool")
...
Private Sub pool_ConnectionReady (Success As Boolean, SQL As SQL)
If Success Then
  'work with connection
  SQL.Close 'don't forget to close!
End If
End Sub

 

[Erel]

The event belongs to ConnectionPool not SQL.

Also, I imagine there is an error in the example, should it be (adding "pool" before "Connection Ready":

You are correct. It is fixed.

 

[jmon]

Great, it works, thanks a lot!