B4J Question JBDC pool connections: how to use correctly ?

peacemaker

Expert
Licensed User
Longtime User
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 ?
 
Sort by date Sort by votes

peacemaker

Expert
Licensed User
Longtime User
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
Click to expand...

Whole log: 
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
 
Last edited:
Upvote 0
A

amykonio

Active Member
Licensed User
Longtime User
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.
 
Upvote 0
A

amykonio

Active Member
Licensed User
Longtime User
Also I connect using root, as I haven't set user or privileges for my testing schema.
Andreas.
 
Last edited:
Upvote 0
A

amykonio

Active Member
Licensed User
Longtime User
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.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
peacemaker said:
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 ?
Click to expand...
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
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
peacemaker said:
pool.IsInitialized is always = TRUE
Click to expand...
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.
 
Upvote 0

peacemaker

Expert
Licensed User
Longtime User
amykonio said:
pool.initialize will run successfully even if you give wrong credentials
Click to expand...
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.

OliverA said:
if you are storing large image files in the DB
Click to expand...
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.
 
Last edited:
Upvote 0
A

amykonio

Active Member
Licensed User
Longtime User
If you don't want to provide login information each time you run it the modify Sub Connect in Main as follows:
Hard coded login info...: 
'    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.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
peacemaker said:
at any password, login....any credentials
Click to expand...
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.
 
Upvote 1

peacemaker

Expert
Licensed User
Longtime User
OliverA said:
standard JDBC calls (w/o pooling)
Click to expand...
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.

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

OliverA

Expert
Licensed User
Longtime User
peacemaker said:
Any "mySQL = pool.GetConnection" hangs the app till the die with error.
Click to expand...
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.
 
Upvote 0
A

amykonio

Active Member
Licensed User
Longtime User
peacemaker said:
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.
Click to expand...
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.
Click to expand...
Andreas.
 
Upvote 0

peacemaker

Expert
Licensed User
Longtime User
OliverA said:
should not hang
Click to expand...
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.

amykonio said:
Don't understand what you mean here
Click to expand...
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 ?
 
Last edited:
Upvote 0

OliverA

Expert
Licensed User
Longtime User
peacemaker said:
BTW, how to be sure that remote connection to MySQL is OK ?
Click to expand...
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
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Upvote 0
A

amykonio

Active Member
Licensed User
Longtime User
2) Do a "SELECT 1" query first. If it works, you're good to go
Click to expand...
Or execute "select connection_id()" which will also return your session id (there are cases you will need that information, example for debugging)...
Andreas.
 
Upvote 0

Magma

Expert
Licensed User
Longtime User
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.
 
Upvote 0
A

amykonio

Active Member
Licensed User
Longtime User
I ve noticed you ve said that you are trying Andreas app from your pc... trying to connect at the same db...?
Click to expand...
No. He tries to connect to a mysql server that runs on his computer.
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…