B4J Question [SOLVED]How to AutoReconnect to an MySQL server

[Peter Lewis]

Hi all

I have a server app running which goes between my MySQL and MQTT. After around 24 hours I get this error.

I Tried to use Ctrl-M to copy the debug text and paste it but it just goes away as it sees it as a key press and I cannot select the text, so I had to do a screenshot. This after I compiled it and put it on the server machine and run in debug mode.

I have looked under all the options on the SQL init string and cannot find anywhere that allows me to re-connect.

If you know where I can set this parameter - Autoreconnect = true would be a great help

Thank you

 

[OliverA]

Try one application that runs your connection pool against your one i stance of mysql server and lets go from there. If you do not properly get and release your SQL connections from the pool, you will exhaust your pool. Let's get one working and go from there. Again, everytime you call a getconnection to your pool, you're holding o to that connection until you close your sql object. If you acquire more connections than you release, you will exhaust your pool. Two solutions: 1) make sure you only hold on to a connection as long as is necessary, 2) increase pool size. The amount of time that the SQL server requires to respond will also affect the pool size.

 

[OliverA]

Have you checked resource usage of the vm’s while doing this? Initially I would have done everything on one VM, checked cpu/ram usage and gone from there

edit: corrected an autocorrect issue

 

[Peter Lewis]

Have you checked resource usage of the vm’s while doing this? Initially I would have done everything on one VM, checked cpu/ram usage and gone from there

edit: corrected an autocorrect issue

Resource usage is very low less than 20% in all areas

 

[OliverA]

Resource usage is very low less than 20% in all areas

Then start by putting mqtt, pool, sql server and anything else on one VM and go from there.

 

[Peter Lewis]

Have you checked resource usage of the vm’s while doing this? Initially I would have done everything on one VM, checked cpu/ram usage and gone from there

edit: corrected an autocorrect issue

Before I change it all to use 1 connection in the Pool. I ran it again with the Utilisation levels and they are very very low.

this screen shot was taken at the point of failure on the left hand server

 

[Peter Lewis]

Then start by putting mqtt, pool, sql server and anything else on one VM and go from there.

Ok , that will take some time to do so I will do it in the next couple of hours. I need to open up ports in the router as well as one of my MQTT servers is in Germany on my VPs there. All the other items are on my VM. I had the Linux VM running MQTT but I will move it to Windows Server 2019. I will have to do some mods on that server as I am running MS SQL there. Do you want me to run all the server apps also on the same machine ?

 

[OliverA]

If the mqtt is remote, leave it alone.

 

[Peter Lewis]

If the mqtt is remote, leave it alone.

one of the MQTT servers is local and the other one in Germany.
I am using 2 different ports 1883 and 1884. Speed from Germany to here is fast so I do not think that is the issue. I just installed the second Mosquitto on the windows server but I will not use it then. MS SQL is disabled now I am going to install MySQL

 

[OliverA]

MS SQL is disabled now I am going to install MySQL

You don’t have to do that. I (wrongly) assumed you’re using MySQL

Update: I guess you were using MySQL. So proceed (as if you need my permission ?). Btw, just because I’m the one responding does not make me right....

 

[Peter Lewis]

You don’t have to do that. I (wrongly) assumed you’re using MySQL

Update: I guess you were using MySQL. So proceed (as if you need my permission ?). Btw, just because I’m the one responding does not make me right....

LOL, your name has EXPERT under it . But I also respect anyone who takes the time to try and help.

We all only learn by bouncing ideas around. I am using MySQL for this program

 

[Peter Lewis]

You don’t have to do that. I (wrongly) assumed you’re using MySQL

Update: I guess you were using MySQL. So proceed (as if you need my permission ?). Btw, just because I’m the one responding does not make me right....

Ok,

I made a new VM server as the other Windows server had too much stuff on it.

I now have MySQL, MQTT Broker port 1884 and the 3 server programs running on that server This was running with 1 pool size and it crashed with the same message after around 20 loops. I did not count it.

Utilization is very low

I am going to increase the connection pool to 100 and see if that makes any difference. I also want to try 20 as that was one of the suggestions


UPDATE:

100, 20 and 1000 pool size also failed

Last UPDATE:

I put both MQTT Brokers on the same machine with the MySQL and the 3 server programs and put the pool back to 100 and it crashed after 5 loops

 

[OliverA]

This makes little sense. I've stress tested various pools without your issues. You may want to zip up your project and post it, if possible.

 

[Peter Lewis]

This makes little sense. I've stress tested various pools without your issues. You may want to zip up your project and post it, if possible.

I can post the server app that is giving the most problems. So you can look at it. thank you. If you find out the problem on that one , then it prob will be the same problem on the other one

 

[OliverA]

Try attached
1) Made connection pool a global variable and initialized it only once upon program start
2) Do not use JdbcSQL with B4J. Just use jSQL

 

[Peter Lewis]

Try attached
1) Made connection pool a global variable and initialized it only once upon program start
2) Do not use JdbcSQL with B4J. Just use jSQL

WOW !! that worked. It has been running for 30 minutes perfectly.

So I tried just changing just the SQL away from JbdcSQL on the second server program and that did crash as well , how every the First server program continued. So I changed it like your method and all is working. I did read on one of the posts if you are using 2 databases, one SQLite and SQL then use the JbdSQL and jSQL which I copied the framework from my main game app. However now I am redirecting everything through MQTT I do not have MySQL access on the main game app anymore.

That is a great framework for all my SQL projects in the future.

Thank you and I hope I can return the favor one day.

 

[OliverA]

one SQLite and SQL then use the JdbcSQL and jSQL which I copied the framework from my main game app.

That should only apply to Android/B4A (Note: JdbcSQL is not availabe for B4i), since B4A's default SQL library only allows for SQLite access. With B4J, you can open as many JDBC DB's as you want, including embedded SQLite(s), via jSQL (no other library needed).