Access violation after a Mysql disconnection

[zeltron73]

Hello,

This code produces an access violation when the second query is freed.

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  sei: TShellExecuteInfo;
begin
  TButton(Sender).Enabled := False;
  Query1.ParamByName('PrimaryKey').AsInteger := 1;
  Query1.Prepare;
  Query1.Open;

  Query2.ParamByName('PrimaryKey').AsInteger := 1;
  Query2.Prepare;
  Query2.Open;
  try
    // stops the Mysql service and wait
    FillChar(sei, SizeOf(ShellExecuteInfo), 0);
    sei.cbSize := SizeOf(ShellExecuteInfo);
    sei.Wnd := Handle;
    sei.fMask := SEE_MASK_NOCLOSEPROCESS;
    sei.lpVerb := 'open';
    sei.lpFile := 'NET.EXE';
    sei.lpParameters := 'STOP MYSQL';  // NET = synchronous command (unlike SC)
    sei.lpDirectory := PChar(GetCurrentDir);

    if ShellExecuteEx(@sei) then
    begin
      while (MsgWaitForMultipleObjects(1, sei.hProcess, False, 500, QS_ALLINPUT) <> WAIT_OBJECT_0) do
        Application.ProcessMessages;
      CloseHandle(sei.hProcess);

      // starts the Mysql service and wait
      sei.lpParameters := 'START MYSQL';
      if ShellExecuteEx(@sei) then
      begin
        while (MsgWaitForMultipleObjects(1, sei.hProcess, False, 500, QS_ALLINPUT) <> WAIT_OBJECT_0) do
          Application.ProcessMessages;
        CloseHandle(sei.hProcess);
      end;
    end
    else
      raise Exception.Create('Unable to restart MySQL service');

    // queries have been closed
    Query1.Open;
    Query2.Open;
  finally
    TButton(Sender).Enabled := True;
    try Connection.Free except end;  // raises an EMysqlConnection ('Lost connection')
    Query2.Free;  // OK
    Query1.Free;  // Access Violation
  end;
end;

By design, I cannot use a disconnected mode. So I have to take into account a potential loss of connection... but I get access violations when the tables are released.

Thanks for your help.

[zeltron73]

Hello,

If you execute this code, you can watch an assertion that failed with this message: EAssertionFailed (MyClasses.pas) line 4724

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
const
  QUERY_1 = 'SELECT t1.* FROM `Table1` t1 WHERE t1.PrimaryKey = :Key';
  QUERY_2 = 'SELECT t2.* FROM `Table2` t2 WHERE t2.PrimaryKey = :Key';
var
  sei: TShellExecuteInfo;
begin
  TButton(Sender).Enabled := False;
  Query1.SQL.Text := QUERY_1;
  Query1.ParamByName('Key').AsInteger := 1;
  Query1.Prepare;
  Query1.Open;

  Query2.SQL.Text := QUERY_2;
  Query2.ParamByName('Key').AsInteger := 1;
  Query2.Prepare;
  Query2.Open;
  try
    // stops the Mysql service and wait
    FillChar(sei, SizeOf(ShellExecuteInfo), 0);
    sei.cbSize := SizeOf(ShellExecuteInfo);
    sei.Wnd := Handle;
    sei.fMask := SEE_MASK_NOCLOSEPROCESS;
    sei.lpVerb := 'open';
    sei.lpFile := 'NET.EXE';
    sei.lpParameters := 'STOP MYSQL';  // NET = synchronous command (unlike SC)
    sei.lpDirectory := PChar(GetCurrentDir);

    if ShellExecuteEx(@sei) then
    begin
      while (MsgWaitForMultipleObjects(1, sei.hProcess, False, 500, QS_ALLINPUT) <> WAIT_OBJECT_0) do
        Application.ProcessMessages;
      CloseHandle(sei.hProcess);

      // starts the Mysql service and wait
      sei.lpParameters := 'START MYSQL';
      if ShellExecuteEx(@sei) then
      begin
        while (MsgWaitForMultipleObjects(1, sei.hProcess, False, 500, QS_ALLINPUT) <> WAIT_OBJECT_0) do
          Application.ProcessMessages;
        CloseHandle(sei.hProcess);
      end;
    end
    else
      raise Exception.Create('Unable to restart MySQL service');

    try Connection.Disconnect; except end;
    Query2.UnPrepare;
    Query2.Prepare;  // EAssertionFailed
    Query2.Open;

    Query1.UnPrepare;
    Query1.Prepare;
    Query1.Open;
  finally
    TButton(Sender).Enabled := True;
  end;
end;

I wish that could help...

Regards.

[AndreyZ]

You handle connection losses in the incorrect way. To resume lost connection, you should use the TMyConnection.OnConnectionLost event handler. The OnConnectionLost event handler is used to process fatal errors and perform failover. To make the OnConnectionLost event handler work, you should set the TMyConnection.Options.LocalFailover property to True. Note that to use the OnConnectionLost event handler, you should add the MemData unit to the USES clause of your unit. Here is an example of using the OnConnectionLost event handler:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  MyConnection1.Options.LocalFailover := True;
  MyConnection1.Open;
end;

procedure TForm1.MyConnection1ConnectionLost(Sender: TObject;
  Component: TComponent; ConnLostCause: TConnLostCause;
  var RetryMode: TRetryMode);
begin
  RetryMode := rmReconnectExecute;
end;

In this case, if connection was lost, MyDAC will try to reconnect and reexecute the abortive operation. For more information, please read the MyDAC documentation.

[zeltron73]

Thank you for your response, but if you test the piece of code I posted before, you should notice the error with or without the "LocalFailOver" property set (and in my case, the event is NEVER triggered)...

In fact, the error is due to the prepared requests; if I unprepare Query1 or Query2 before connection is lost, the error disappears. If both requests are prepared before connection is lost, you catch the EAssertionFailed exception which causes AV.

Thanks for your help.

Regards.

[AndreyZ]

I cannot reproduce the problem. Your code works without any problems. Please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com . Also please specify the following:
- the exact version of MyDAC. You can learn it from the About sheet of TMyConnection Editor;
- the exact version of your IDE;
- the exact version of MySQL server and client. You can learn it from the Info sheet of TMyConnection Editor.

[zeltron73]

Hello,

Can anybody reproduce the error with MyDAC 7.1.6 ?

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
const
  QUERY_1 = 'SELECT t1.* FROM (' +
    '(SELECT 1 AS Field1, "string1" AS Field2) UNION ' +
    '(SELECT 2, "string2")' +
    ') t1 WHERE t1.Field1 = :Key';

  QUERY_2 = 'SELECT t2.* FROM (' +
    '(SELECT 3 AS Field1, "string3" AS Field2) UNION ' +
    '(SELECT 4, "string4")' +
    ') t2 WHERE t2.Field1 = :Key';
var
  sei: TShellExecuteInfo;
  Connection: TMyConnection;
  Query1: TMyQuery;
  Query2: TMyQuery;
  Test: string;
begin
  TButton(Sender).Enabled := False;

  Connection := TMyConnection.Create(Self);
  Connection.Server := 'localhost';
  Connection.Username := 'root';
  Connection.Password := 'mysql';
  Connection.LoginPrompt := False;

  Query1 := TMyQuery.Create(Self);
  Query1.Connection := Connection;

  Query2 := TMyQuery.Create(Self);
  Query2.Connection := Connection;

  try
    Query1.SQL.Text := QUERY_1;
    Query1.ParamByName('Key').AsInteger := 1;
    Query1.Prepare;
    Query1.Open;

    Query2.SQL.Text := QUERY_2;
    Query2.ParamByName('Key').AsInteger := 3;
    Query2.Prepare;
    Query2.Open;

    Test := Query1.FieldByName('Field2').AsString;
    Test := Query2.FieldByName('Field2').AsString;

    // to simulate a Mysql connection failure
    // stops the Mysql service and wait by issuing "NET STOP/START MYSQL"
    FillChar(sei, SizeOf(ShellExecuteInfo), 0);
    sei.cbSize := SizeOf(ShellExecuteInfo);
    sei.Wnd := Handle;
    sei.fMask := SEE_MASK_NOCLOSEPROCESS;
    sei.lpVerb := 'open';
    sei.lpFile := 'NET.EXE';
    sei.lpParameters := 'STOP MYSQL';  // NET = synchronous command (unlike SC)
    sei.lpDirectory := PChar(GetCurrentDir);

    if ShellExecuteEx(@sei) then
    begin
      while (MsgWaitForMultipleObjects(1, sei.hProcess, False, 500, QS_ALLINPUT) <> WAIT_OBJECT_0) do
        Application.ProcessMessages;
      CloseHandle(sei.hProcess);

      // starts the Mysql service and wait
      sei.lpParameters := 'START MYSQL';
      if ShellExecuteEx(@sei) then
      begin
        while (MsgWaitForMultipleObjects(1, sei.hProcess, False, 500, QS_ALLINPUT) <> WAIT_OBJECT_0) do
          Application.ProcessMessages;
        CloseHandle(sei.hProcess);
      end;
    end
    else
      raise Exception.Create('Unable to restart MySQL service');

    try
      Query1.UnPrepare;
      Query2.UnPrepare;  // raises an EMySqlException (lost connection)
    except
      on E: Exception do
      begin
        MessageDlg(E.Message, mtError, [mbOK], 0);
        Connection.Disconnect;
      end;
    end;
    Query1.Prepare;
    Query1.Open;

    Query2.Prepare;    // EAssertionFailed => EAccessViolation
    Query2.Open;
  finally
    TButton(Sender).Enabled := True;
    Connection.Free;
    Query1.Free;
    Query2.Free;
  end;
end;

Thanks for your help...

[zeltron73]

The bug still persists with the version 7.2.7 for Delphi 5 on Windows XP (x86) and Windows 7 (x64) and MySQL 5.5.25 (x86 or x64)...
In fact, the error appears when "Query2.UnPrepare" is called, and when the EMysqlException is unwound... the cleanup code is perhaps not properly called. If you prevent the exception from being raised, the AV disappears !!!

Any suggestions ?

[AndreyZ]

Thank you for the information. We have reproduced the problem and investigation of the problem is in progress. As soon as we have any results we will let you know.

[AndreyZ]

We have fixed this problem. This fix will be included in the next MyDAC build.

[zeltron73]

Thanks a lot for your responsiveness.

[AndreyZ]

Feel free to contact us if you have any further questions about MyDAC.