Keep alive MySQL connection in a console application
lazarus / 63-Database

----------------------------------------------------------------------
Libra07 · 2020-04-30 16:01

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!

----------------------------------------------------------------------
Zvoni · 2020-05-12 09:56

Server-System-Variable "wait_timeout"
https://dev.mysql.com/doc/refman/8.0/en/server-system-
variables.html#sysvar_wait_timeout

----------------------------------------------------------------------
Libra07 · 2020-05-15 16:18

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.

----------------------------------------------------------------------
Libra07 · 2020-05-27 13:47

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!