B4J Question JBDC pool connections: how to use correctly ?

peacemaker · Oct 12, 2023

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 · Oct 13, 2023

My MySQL server is a separate Ubuntu 20.04 PC (let it be "server") in the same LAN together with my developer laptop.
My project app is a server non-UI app that i run at server.
Test app is the UI app (of @amykonio) that started at my laptop and trying to connect to MySQL of the server. And failed yet.

amykonio · Oct 13, 2023

peacemaker said:
My MySQL server is a separate Ubuntu 20.04 PC (let it be "server") in the same LAN together with my developer laptop.
My project app is a server non-UI app that i run at server.
Test app is the UI app (of @amykonio) that started at my laptop and trying to connect to MySQL of the server. And failed yet.

In that case, MySQL is set to accept connections from remote pc's? (@Magma was right).
Have you tried to connect to MySQL using for example workbench from your laptop?

amykonio · Oct 13, 2023

Also, maybe the user you are using is limited to connect only from localhost?

peacemaker · Oct 13, 2023

MySQL server is OK with Adminer, as i described here: https://www.b4x.com/android/forum/threads/sqlite-or-mysql-database.155180/
But Adminer is the PHP software installed also at the same server, and it works OK with the DB using the same project DB user.

No idea what remote software to use for remote connection to MySQL for sure...
Found the "workbench", thanks, trying.

peacemaker · Oct 13, 2023

amykonio · Oct 13, 2023

peacemaker said:
View attachment 146830

Try the other user you have because root should be set to connect only from localhost...

OliverA · Oct 13, 2023

Out of the box, the MySQL server and the root user are setup for local access only. To enable https://stackoverflow.com/questions/16287559/mysql-adding-user-for-remote-access

Magma · Oct 13, 2023

at ubuntu + mysql server:

sudo ufw enable
sudo ufw allow mysql

also...
Edit the "bind-address" directive in /etc/mysql/mysql.conf.d/mysqld.cnf:

bind-address = 127.0.0.1 ( The default. )
bind-address = XXX.XXX.XXX.XXX ( The ip address of your Public Net interface. )
bind-address = ZZZ.ZZZ.ZZZ.ZZZ ( The ip address of your Service Net interface. )
bind-address = 0.0.0.0 ( All ip addresses. )

restart and at the end set the right priveleges at the users...

peacemaker · Oct 13, 2023

OliverA said:
Out of the box, the MySQL server

... kills me already by tons of settings...

But, thanks, Magma:

Magma · Oct 13, 2023

Nice... now you must optimize the loops (wait for them, limit the time for them, return error or ok) and don't have them inside of resultset loops.. hope all will work fine...

Magma · Oct 13, 2023

After the Tests... you must protect the port from outside... and not only

you can see a solution made old days... but need to make it work at debian / ubuntu ...

[B4J] A solution for SQL Brute force Protection

Hi there, I want to share a tactic / a small guard for MySQL/MariaDB DataBases.. Well all we know that having a Database online and not in localhost is difficult to protect from brute force attacks / ransomware attacks... Well i ll give a little "pebble" to help others to think / different -...

www.b4x.com

peacemaker · Oct 13, 2023

Actually we just set up once more access way, but it's maybe not related the connections pool...

Magma · Oct 13, 2023

peacemaker said:
Actually we just set up once more access way, but it's maybe not related the connections pool...

If had windows at server side... will see if that using the same pool... (at taskmgr)

at debian.... i don;t remember how to check the proccess... to see the pools running...

OliverA · Oct 13, 2023

Magma said:
wait for them

Be careful. On a non jServer handler, yes, but in a jServer handler, do NOT use Wait For unless you also create a message loop. Since each handler runs in its own thread, Wait For should not be needed (other requests will be handled by other threads). Again, if Wait For is needed in a jServer handler, then please see: https://www.b4x.com/android/forum/threads/resumable-subs-wait-for-sleep-in-server-handlers.81833/

Magma · Oct 13, 2023

check... if list growing and growing... you have many connections... so something going wrong...

ps aux | grep mysqld
ps aux | grep mysql

Magma · Oct 13, 2023

Also... something else... will help you see what happens:

How to find MySQL process list and to kill those processes?

The MySQL database hangs, due to some queries. How can I find the processes and kill them?

stackoverflow.com

peacemaker · Oct 13, 2023

OliverA said:
Wait For is needed in a jServer handler

My app structure:

Several simple handlers with simple web-forms for settings
Several ABM-pages for Admin interface and data explorer interface
Separate classes for APIs (to control the Python sub-system for recognition and Web-API to work with external public server) and work with files ("black box" file system)
DButils2 module for MySQL DB and 2 SQLite DBs
And the Main module that contains timers for background loops.

Handlers are without WaitFor, but all other modules\classes with.

peacemaker · Oct 13, 2023

amykonio said:
my simple testing application

Thanks again for the test application - it, at last, started to work at my side ?

1000 records are being inserted by the connections pool during ... maybe 2 minutes !!!11 Interesting that in Release and Debug mode - almost the same SLOW speed.
But works without errors.
So, it looks like the pool is working...but very slow...but working

peacemaker · Oct 13, 2023

Magma said:
sudo ufw enable

Note: firewall blocks the b4j-bridge

Magma · Oct 13, 2023

sudo ufw allow 59812
sudo ufw allow 8937
sudo ufw allow 8938

If the debugger fails to connect to the remote machine then you should open the following ports for incoming connections:
59812, 8937 and 8938

B4J Question JBDC pool connections: how to use correctly ?

Expert

Expert

Active Member

Active Member

Expert

Expert

Active Member

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency