Capture moment of reconnection - does not work

[atendimentobed]

Hi,

My scenario has Zeos 8, Mysql Server 5.0 and Delphi 11.2.

Imagine the following scenario:
I run my application (Delphi + Zeos) on a remote workstation and a connection id 2 (SHOW PROCESSLIST) is generated on the MySQL server.
Later I run the command "KILL 2" on the MySQL server killing this connection.
If I try to use my application, ZEOS automatically reconnects to the MySQL server, creating a connection with id 3 for example.

That's ok, but I want to capture the moment when that reconnection happens. I've already tried to put a ShowMessage('Here') in the BeforeReconnect and BeforeConnect event of the ZConnection component, however the message is not triggered.

Does anyone know where I can capture this moment of reconnection?

Thx

[marsupilami]

Hello atendimentobed,

I am not sure, if this is done by Zeos. if it is done by Zeos, the current spec would say that the OnConnectionLoss event od TZConnection triggers in that moment. But honestly I wonder wether this might be a function of libmysql.

With best regards,

Jan

[marsupilami]

There it is. I found this in the MySQL 5.0 Reference Manual:

To connect to the server, call mysql_init() to initialize a connection handler, then call mysql_real_connect() with that handler (along with other information such as the host name, user name, and password). Upon connection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure) to a value of 1 in versions of the API older than 5.0.3, or 0 in newer versions. A value of 1 for this flag indicates that if a statement cannot be performed because of a lost connection, to try reconnecting to the server before giving up. As of MySQL 5.0.13, you can use the MYSQL_OPT_RECONNECT option to mysql_options() to control reconnection behavior. When you are done with the connection, call mysql_close() to terminate it.

So it depends on your version of libmysql.dll, if I understand this correctly.

[atendimentobed]

Hi marsupilami,

thx...

Can I capture by Delphi the moment of this reconnection?

[marsupilami]

No - as far as I know, you cannot. libmysql doesn't tell us whan that happens. You could try to explicitly set MYSQL_OPT_RECONNECT to false / 0. Then you should get an error and can do the reconnect yourself using Zeos.

[atendimentobed]

No - as far as I know, you cannot. libmysql doesn't tell us whan that happens. You could try to explicitly set MYSQL_OPT_RECONNECT to false / 0. Then you should get an error and can do the reconnect yourself using Zeos.

Hi marsupilami,

thx...

Where can I explicitly set MYSQL_OPT_RECONNECT to false/0???

[marsupilami]

Something like this could work:

Code: Select all

  ZConnection.Properties.Add('MYSQL_OPT_RECONNECT=false')

[atendimentobed]

Something like this could work:

Code: Select all

  ZConnection.Properties.Add('MYSQL_OPT_RECONNECT=false')

Hi marsupilami,

Good idea, but it didn't work.

If the DLL is handling the reconnection, it simply ignored the property passed to the ZConnection.

[atendimentobed]

I forgot to mention that also test the value "MYSQL_OPT_RECONNECT=0"

[atendimentobed]

I discovered an "interesting" behavior:

Whith ZConnection1.Properties.Add('MYSQL_OPT_RECONNECT=0'), if I kill the MySQL process with the program open and try to open a screen with a grid, an exception is generated before opening the screen, but then the reconnection occurs and the items are displayed.



But with ZConnection1.Properties.Add('MYSQL_OPT_RECONNECT=1'), If I kill the MySQL process with the program open and try to open a screen with a grid, it simply reconnects before opening the screen without generating any exception and loads the data in the grid.

In both it reconnects, but only in the first it generates an exception before.

[marsupilami]

The first behavior with the EZDatabaseConnectionLostError is the expected behavior. Maybe you want to upgrade your client and see what happens. You could test the Mariadb Connector/C.

[aehimself]

If I understand your issue correctly, it is not going to happen. The client side is not getting notified that a connection was dropped, it always realizes it the next time some action is ran against the server; e.g. opening a new dataset, refreshing contents, etc.
The closest you can get is to have a regular ZConnection.Ping performed with a TTimer which will also fail if the connection was lost.

[atendimentobed]

The first behavior with the EZDatabaseConnectionLostError is the expected behavior. Maybe you want to upgrade your client and see what happens. You could test the Mariadb Connector/C.

Hi,

Yes, the first behavior is really what I expected too, but when I enabled the option ZConnection1.Properties.Add('MYSQL_OPT_RECONNECT=1') it simply stopped giving rejection. I'll try to perform tests with more modern MySQL clients and post the results here.

[atendimentobed]

If I understand your issue correctly, it is not going to happen. The client side is not getting notified that a connection was dropped, it always realizes it the next time some action is ran against the server; e.g. opening a new dataset, refreshing contents, etc.
The closest you can get is to have a regular ZConnection.Ping performed with a TTimer which will also fail if the connection was lost.

Hi,

As I said after killing the connection, despite the exception the reconnection is done by someone else, and the program connects to the database again.

Too bad it's not possible to capture this exact moment in Delphi, it would solve my problem :-)

Anyway thanks for the help.