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]

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]

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]

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

 

[peacemaker]

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]

 

[amykonio]

View attachment 146830

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

 

[OliverA]

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]

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]

Out of the box, the MySQL server

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

But, thanks, Magma:

 

[Magma]

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]

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]

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

 

[Magma]

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]

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]

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

ps aux | grep mysqld
ps aux | grep mysql

 

[Magma]

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]

Wait For is needed in a jServer handler

My app structure:

1. Several simple handlers with simple web-forms for settings
2. Several ABM-pages for Admin interface and data explorer interface
3. 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)
4. DButils2 module for MySQL DB and 2 SQLite DBs
5. And the Main module that contains timers for background loops.

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

 

[peacemaker]

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]

sudo ufw enable

Note: firewall blocks the b4j-bridge

 

[Magma]

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