B4J Question JBDC pool connections: how to use correctly ?

[peacemaker]

HI, All

If a B4J server app (works on a Host1 of a LAN) uses a big MySQL database on a dedicated Host0 (of this LAN), and also planned to make other apps on different hostsX (and connecting to the same Host0 db) - how correctly to use the connections pool to avoid errors ?

There are tons of subs inside the app where the DB is used - is a new connection to be opened at each sub and closed at sub finish ?

 

[peacemaker]

What default port of MySQL server should be used ?

Created table manually, connected to the server OK hmmm, how to be sure ?.

Any run - gives hang at any

mySQL = pool.GetConnection

WARNING: package com.sun.javafx.embed.swing.oldimpl not in javafx.swing
Waiting for debugger to connect...
Program started.
2023-10-13 14:58:14.935:INFO :cmvl.MLog:MLog-Init-Reporter: MLog clients using slf4j logging.
2023-10-13 14:58:15.239:INFO :cmvc.C3P0Registry:JavaFX Application Thread: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2023-10-13 14:59:34.263:INFO :cmvci.AbstractPoolBackedDataSource:JavaFX Application Thread: 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, contextClassLoaderSource -> caller, dataSourceName -> 2s76dfazbaermw2g45ih|1cb3dfb7, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2s76dfazbaermw2g45ih|1cb3dfb7, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://10.8.0.2:3306/stancontrol?allowPublicKeyRetrieval=false&connectTimeout=5000&socketTimeout=500000&maxIdleTime=300&requireSSL=false&useSSL=false&pooling=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]

Maybe my MySQL server manually installed under Ubuntu is ... crooked anyhow

 

[amykonio]

What is your port? Are you using MariaDB or MySQL? For me it works with 3306. But, check if jdbc I use is compatible with your server. I did the test using MySQL 8.0.29 and mysql-connector-java-8.0.23.jar (driverclass com.mysql.cj.jdbc.Driver). Maybe you have to do some changes...
Andreas.

 

[amykonio]

Also I connect using root, as I haven't set user or privileges for my testing schema.
Andreas.

 

[peacemaker]

SELECT VERSION()

VERSION()
8.0.34-0ubuntu0.20.04.1

Strange, but pool.IsInitialized is always = TRUE, at any password, login....any credentials

 

[amykonio]

Here it works fine. But have in mind: you must at first add data. Then you can run the test. During test the query that is executed is "select * from testdata". Dependig on the rows you added on the table, this query may take lot of time. Also it will be repeated by default 10 times (or what value you set instead of 10).
Yes, pool.initialize will run successfully even if you give wrong credentials. But, select and insert wont run. They will give a timeout, and finally your machine will be "banned" form MySQL server. If this happens, run command "TRUNCATE TABLE performance_schema.host_cache" from workbench to fix that.

As you may understand, it is important to provide correct credentials.

Andreas.

 

[OliverA]

There are tons of subs inside the app where the DB is used - is a new connection to be opened at each sub and closed at sub finish ?

In a pooled environment, it is recommended to use on pooled connection per sql statement or sets of sql statements that belong together. This is to avoid locking out pool connections from other threads. It's the whole purpose of having a pool. It is up to you to decide if you want to follow that advice. If you don't, you may run out of connections within the pool, which then will require you to increase the amount of connections the pool has to maintain. Hikari actually says less is more (depending on CPU count / thread count, etc) and 10 should work for many cases! But that only works if one is careful in getting and realeasing pooled connection. Please note, if you need to increase your pool size to something like 100 or even 200 as mentioned in this thread, you may be doing something wrong, or your environment is really not right for pooling. Do you really think the server is going to process 100+ connections at one time? If so, that must be some impressive hardware.

The really big issue here is that I think you got sidetracked with pooling. You were worried about the increase in size of your SQLite database. Did you do any research if that is really an issue? Also, if you are storing large image files in the DB, then it does not matter what DB you use, since many people propose that large BLOB files should be stored outside the DB, with just the linking information stored inside the DB. But again, that may not be your preference, and only testing can determine which route you want to take (and SQLite may still have been the right choice for YOUR case).

Here is a good link that talks about proper pool sizing: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

 

[OliverA]

pool.IsInitialized is always = TRUE

IsInitialized has nothing to do with the pool properly connecting/being properly configured. All what this tells you is that the Object variable named pool has been initialized. Also, C3P0 is lazy, and you will not know if the pool will connect to your DB until you use the first connection. At that point, if there are any issues, you will see some exception errors thrown.

 

[peacemaker]

pool.initialize will run successfully even if you give wrong credentials

Why to check it then...
Now i'm not sure that connected OK to my server from my PC where your UI example is running. Both are in one LAN...
Any "mySQL = pool.GetConnection" hangs the app till the death with error.

if you are storing large image files in the DB

No any files in the DB. Just huge DB with high load from one app, and in future - from several similar extra apps on the LAN.

 

[amykonio]

If you don't want to provide login information each time you run it the modify Sub Connect in Main as follows:

'    Dim loginfrm As Login
'   
'    loginfrm.Initialize(DBInfo)
    
    DBInfo.server = "Your server"
    DBInfo.port = 3306 'or you server port
    DBInfo.schema = "Your schema"
    DBInfo.username = "Your username"
    DBInfo.password = "Your password"

Andreas.

 

[OliverA]

at any password, login....any credentials

If you run into any connection issues using the ConnectionPool object, use standard JDBC calls (w/o pooling) to verify the SQL connection first. If plain JDBC can connect, then it is very likely that ConnectionPool will be able to connect. In B4X, that means is the SQL object/library to connect, and once that works, use the ConnectionPool object to connect.

 

[peacemaker]

standard JDBC calls (w/o pooling)

Now only this way is started to work errorlessly, when the parameters are set so, to the single public SQL object directly, without pool. Before it gave the error.

SQL.Initialize2("com.mysql.jdbc.Driver", $"jdbc:mysql://${DBLocation}/${DBname}?characterEncoding=utf8&allowPublicKeyRetrieval=false&autoReconnect=false&requireSSL=false&useSSL=false&pooling=false"$, DBUsername, DBPassword)

 

[OliverA]

Any "mySQL = pool.GetConnection" hangs the app till the die with error.

They should not hang, and what errors are you experiencing? If you need to do very long running, multiple SQL statements per call to your server, then just use and SQL object per call to that server that Initializes the connection privately for its own use only. You may have one of the edge cases that does not work properly with Pooling. It could also be that your app is perfect for pooling, but it is not properly set up to do so.

 

[amykonio]

Why to check it then...
Now i'm not sure that connected OK to my server from my PC where your UI example is running. Both are in one LAN...
Any "mySQL = pool.GetConnection" hangs the app till the die with error.

Something is wrong then...

Don't understand what you mean here:

No any files in the DB. Just huge DB with high load from one app, and in future - from several similar extra apps on the LAN.

Andreas.

 

[peacemaker]

should not hang

It's about the test app from @amykonio that running not at the server but at my PC.
And all these together make me to think that all the trouble - is in my MySQL server installation regarding the pool. Without it - it started to work, if to use "&pooling=false" option.

Don't understand what you mean here

It was reply not to you.

Thanks to all you, friends, for discussion !

p.s. BTW, how to be sure that remote connection to MySQL is OK ?

 

[peacemaker]

C3P0 is lazy

Is this some special installation of this pool software ?

 

[OliverA]

BTW, how to be sure that remote connection to MySQL is OK ?

1) Use a pool and set up it's keep-alive option ( ???)
2) Do a "SELECT 1" query first. If it works, you're good to go

 

[OliverA]

Is this some special installation of this pool software ?

That's what ABMaterial uses. It's actually part of jServer (if you use jServer, you have access to ConnectionPool). Someone else extracted it and it can be used standalone.

Note: The someone was @Erel: https://www.b4x.com/android/forum/t...onpool-extracted-from-jserver.107776/#content

 

[amykonio]

2) Do a "SELECT 1" query first. If it works, you're good to go

Or execute "select connection_id()" which will also return your session id (there are cases you will need that information, example for debugging)...
Andreas.

 

[Magma]

I ve noticed you ve said that you are trying Andreas app from your pc... trying to connect at the same db...?

It may be need to setup properly the firewall and ofcourse let sql server have outside connections not only local host... it is specific option.

 

[amykonio]

I ve noticed you ve said that you are trying Andreas app from your pc... trying to connect at the same db...?

No. He tries to connect to a mysql server that runs on his computer.