Recent

Author Topic: Keep alive MySQL connection in a console application  (Read 1335 times)

Libra07

  • New member
  • *
  • Posts: 7
Keep alive MySQL connection in a console application
« on: April 30, 2020, 06:01:24 pm »
We have a console FreePascal application that works with a MySQL database. It connects to the remote server from a client-side machine. Reads and writes data. Everything works fine, stable due to Transaction handling, but if they don’t work from the database for a few minutes, it will disconnect and we have to reconnect.

I’ve already set up 2 things to reconnect faster:
mysql_options () -> MYSQL_OPT_RECONNECT = 1
mysql_options () -> MYSQL_OPT_CONNECT_TIMEOUT = 1

So it got a lot better, but not perfect. Continuous disconnection, reconnection. I thought this could be solved by keeping the MySQL connection alive.

Under Lazarus, there is TTimer, which is very easy to use. For example, it would be a good idea to send a mysql_ping() to the server every 10 minutes. Unfortunately, this is not so easy for console applications.
I read and looked at some documentation and downloaded some source code samples to understand how it works. If I understood correctly, this can be solved here with TFPTimer.

The following small program prints a dot every 1 second and the timer prints the time every 5 seconds. Its operation is pretty much understandable.
The problem is that I can't embed this in the console app because if I understand CheckSynchronize() should be started all the time and users are doing something, reading or writing data in one of the menu items. I can't put this CheckSynchronize() call everywhere.

How can this be solved? Or is there any other way to keep my MySQL connection alive?

I attach the console timer app to better understand the problem.

Thanks for the help!
« Last Edit: April 30, 2020, 06:13:24 pm by Libra07 »

Zvoni

  • Sr. Member
  • ****
  • Posts: 410
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

Libra07

  • New member
  • *
  • Posts: 7
Re: Keep alive MySQL connection in a console application
« Reply #2 on: May 15, 2020, 06:18:26 pm »
Thanks for the reply! I checked the value of the variable:
select @@wait_timeout;         => 43200

43200 sec = 720 min = 12 hours
However, if I do not write to or read from the database, the connection to the server will be lost after about 20-30 minutes.
« Last Edit: May 25, 2020, 09:48:41 am by Libra07 »

Libra07

  • New member
  • *
  • Posts: 7
Re: Keep alive MySQL connection in a console application
« Reply #3 on: May 27, 2020, 03:47:56 pm »
Zvoni, you were right! I even tested everything a bit and read on the MySQL website as well.

I displayed 2 types of timeouts:
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
SHOW SESSION VARIABLES LIKE 'wait_timeout';

Modifying the global should be super privileged, but the session is just fine for me:
SET @@SESSION.wait_timeout: = 43200;

Now everything works so I don’t need constant pinging.
Thank you so much Zvoni!

 

TinyPortal © 2005-2018