Formless application shutdown delay

[aehimself]

Hello,

I am experiencing a very strange issue. I have a formless (console) Delphi application with several worker threads. Each worker thread is creating a TZConnection in the constructor and calls the .Connect in the onExecute cycle if it is not connected. Destruction it is closing all tables, disconnecting the connection and then calls FreeAndNil on each.

Everything is working fine, but upon closing my application I have about 6 seconds of delay before it actually closes. I managed to track down, if the ZConnection is not connected the delay disappears.

My worker threads have no message pump, but having one makes no difference.

I found a similar note in Overbyte's TWSocket implementation (see Feb 14 1999 release note of OverbyteIcsWSocket.pas):
"Your message pump must set TWSocket.Terminated property to TRUE when your application terminates or you may experience long delays when closing your application."

During the destruction I simply call .Disconnect and then free the component. What am I missing here?

Any ideas?

Thanks!

Edit:
I am using Zeos 7.2.4-stable on Delphi 10.2.3. The delay does not appear with normal VCL form applications.

[aehimself]

Test application:

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, ZConnection, Classes;

Type
 TWorkerThread = Class(TThread)
 strict private
  _sqlconnection: TZConnection;
 public
  Constructor Create; Reintroduce;
  Destructor Destroy; Override;
  Procedure Execute; Override;
 End;

Var
 wt: TWorkerThread;

constructor TWorkerThread.Create;
begin
 inherited Create(True);
 _sqlconnection := TZConnection.Create(nil);
 _sqlconnection.HostName := 'sqlhost';
 _sqlconnection.Database := 'sqldb';
 _sqlconnection.User := 'sqluser';
 _sqlconnection.Password := 'sqlpassword';
 _sqlconnection.Protocol := 'mysql';
end;

destructor TWorkerThread.Destroy;
begin
 _sqlconnection.Disconnect;
 FreeAndNil(_sqlconnection);
 inherited;
end;

procedure TWorkerThread.Execute;
begin
 Repeat
  If Not _sqlconnection.Connected Then _sqlconnection.Connect;
  Sleep(100);
 Until Terminated;
end;

begin
 WriteLn('Creating thread...');
 wt := TWorkerThread.Create;
 WriteLn('Starting thread...');
 wt.Start;
 WriteLn('Now sleeping...');
 Sleep(10000);
 WriteLn('Terminating thread...');
 wt.Terminate;
 WriteLn('Waiting for thread to quit...');
 wt.WaitFor;
 WriteLn('Freeing thread...');
 FreeAndNil(wt);
 WriteLn('Done!');
end.

Edit:
- Tried moving creation of ZConnection to thread's execute method
- Tried moving destruction of ZConnection to thread's execute method
- Tried simply wt.Free instead of FreeAndNil
- Confirmed that commenting out the If Not […] line in thread's execute will NOT trigger the delay
- Doublechecked with step-by-step debugging that no "rogue" threads are opened which are not closed at application termination

Edit-edit:
- Replaced Sleep(10000) with a message pump function in the main thread:

Code: Select all

   If PeekMessage(msg, 0, 0, 0, 0) Then Begin
                                        GetMessage(msg, 0, 0, 0);
                                        TranslateMessage(msg);
                                        DispatchMessage(msg);
                                        End;

- Put a Halt(0) after WriteLn('Done');

Edit-edit-edit:
- Keeping the TWorkerThread definition and putting it on a VCL form's buttonclick handler produces the same delay:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
Var
 wt: TWorkerThread;
begin
 wt := TWorkerThread.Create;
 Self.Caption := 'Starting thread...';
 wt.Start;
 Self.Caption := 'Now sleeping...';
 Sleep(10000);
 Self.Caption := 'Terminating thread...';
 wt.Terminate;
 wt.WaitFor;
 Self.Caption := 'Freeing thread...';
 FreeAndNil(wt);
 Self.Caption := 'All done!';
 Halt(0);
end;

- However, using TZConnection itself on a button handler makes no delay:

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
Var
  _sqlconnection: TZConnection;
begin
 Self.Caption := 'Creating connection...';
 _sqlconnection := TZConnection.Create(nil);
 _sqlconnection.HostName := 'sqlhost';
 _sqlconnection.Database := 'sqldb';
 _sqlconnection.User := 'sqluser';
 _sqlconnection.Password := 'sqlpassword';
 _sqlconnection.Protocol := 'mysql';
 Self.Caption := 'Connecting...';
 _sqlconnection.Connect;
 Self.Caption := 'Now sleeping...';
 Sleep(10000);
 Self.Caption := 'Disconnecting...';
 _sqlconnection.Disconnect;
 FreeAndNil(_sqlconnection);
 Self.Caption := 'All done!';
 Halt(0);
end;

- Creating a thread with BeginThread also causes delay, so the issue is not with Delphi's TThread object:

Code: Select all

procedure createandconnect(Params: Pointer);
Var
  _sqlconnection: TZConnection;
Begin
 _sqlconnection := TZConnection.Create(nil);
 _sqlconnection.HostName := 'sqlhost';
 _sqlconnection.Database := 'sqldb';
 _sqlconnection.User := 'sqluser';
 _sqlconnection.Password := 'sqlpassword';
 _sqlconnection.Protocol := 'mysql';
 _sqlconnection.Connect;
 Sleep(10000);
 _sqlconnection.Disconnect;
 FreeAndNil(_sqlconnection);
End;

procedure TForm1.Button3Click(Sender: TObject);
Var
 th: THandle;
 ti: Cardinal;
begin
 Self.Caption := 'Starting thread...';
 th := BeginThread(nil, 0, Addr(createandconnect), nil, 0, ti);
 Self.Caption := 'Waiting for thread to finish...';
 WaitForSingleObject(th, INFINITE);
 Self.Caption := 'All done!';
 CloseHandle(th);
 Halt(0);
end;

[aehimself]

So I made some logging with Process Monitor when the delay occurs and found some really interesting things:

dcdelay.PNG

At 20:39:52 the worker thread (with Thread ID 10920, confirmed by checking on Delphi's side during the test) terminates. In theory the connection is already closed and the component is freed. However, milliseconds after the thread termination the actual disconnect can be seen and locks the application for ~5 seconds!!!
So I guess that this is NOT a Zeos issue, but rather a MySQL driver implementation problem. And since it's using WinSock, I can hear myself referring to the wrapper comment by Overbyte I quoted two posts above.

At the moment my 32-bit MySQL library is version 5.5.11.0 and the 64 bit one is 5.6.17.0, both producing the delay. I'll experiment a bit with different, possibly newer versions.

[aehimself]

And my assumption was more than correct.

It's a pain in the back to download the latest version of libmysql.dll from Oracle:
- Download the web installer, then select Other, select Connectors / C, 64 bit (it can not install both at the same time... )
- Next-Next-Finish, and then go to C:\Program Files\MySQL\Connector something and copy the libmysql.dll to C:\Windows
- Uninstall everything and redo the same, only installing the other architecture of the same connector
- Go to C:\Program Files (x86)\MySQL\Connector something and copy the libmysql.dll to C:\Windows\Syswow64
- Now uninstall everything to get rid of this Oracle abomination
…and I thought I write shitty code

So I can confirm that the shutdown delay no longer exists (neither in 32 and 64 bit Delphi application) with MySQL driver version 6.1.11.0 even in console application or using worker threads.

I'm sorry for initially accusing Zeos for Oracle's mistake!!!

[Fr0sT]

Wow... I wish everyone reported issues so thoroughly! My respects

[aehimself]

Well, I was already trying to find the solution for a day when I decided to ask the community. Had a feeling that this is not a trivial issue so if I'd like to get a solution I must go into details.
The rest is just the typical case of the rubber duck debugging, managed to pinpoint the weak link as I was describing my problem

Hope it will help others one day