ZeosDBO & multithreading safely

[PetyaMaster]

Hello Zeos Folks,

I want to know the community's general opinion about ZeosDBO (6.6.6 stable) and multiple threads.
I have several threads each with its own ZConnection and ZQuery created dynamically, and have static ZConnection and ZQuery components put on the main form also.
After several hours of flawless running once the ZQuery on the static ZConnection throws an Invalid pointer operation on an Open; call, then some time later again (or List index out of bounds (4)), and things start to get worse and worse, tons of AccessViolations, until the app crashes with EOutOfMemory. All coming from Zeos. It looks like the static ZQuery or ZConnection has been freed abnormally, or its address being changed somewhere out of my sight (I hope not the last ... ).

Code: Select all

TMyZQuery	= class(TZQuery)
  private
    procedure BeforeOpenHandler(DataSet: TDataSet);
  public
    constructor Create(AOwner: TComponent); override;
    procedure ExecSQL; override;
  end;

constructor TMyZQuery.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  BeforeOpen:=BeforeOpenHandler;
end;

procedure TMyZQuery.ExecSQL;
begin
  if Assigned(BeforeOpen) then BeforeOpen(Self);
  if IniParams.Database.ProtectQueries then
  try
     DatabaseRWCriticalSection.Enter;
     inherited ExecSQL;
  finally
     DatabaseRWCriticalSection.Leave;
  end
  else inherited ExecSQL;
end;

{$B-}
procedure TMyZQuery.BeforeOpenHandler(DataSet: TDataSet);
begin
  if IniParams.Log.Database and (DataSet.ClassName='TMyZQuery') then FileLog(DatabaseAll,Linearize((DataSet as TZQuery).SQL.Text));
end;

Code: Select all

procedure TGPRSConn.FTPLoadCommands;
var c,h: integer; FTPMode: TFTPMode; QueryText: string; Query: TMyZQuery; t: Double;
begin
  h:=0; t:=Now;
  try
  Query:=TMyZQuery.Create(MainModule);
  with Query do try
    h:=1;
    Connection:=MainModule.ZConnection2;
    h:=2;
    SQL.Add('SELECT FSZGCommandId,Command FROM FSZGCommands WHERE (FszgId=' + str(id) + ') ');
    SQL.Add('AND (SentDate IS NULL) AND (ExecuteDate < ' + NowDBFunc + ') ORDER BY IssueDate ASC ;');
    h:=3; t:=Now;
    Open;
    h:=4;
    if IniParams.GPRS.CollectSQL then SqlHistory.Add(FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',Now) + #9 + Linearize(SQL.Text));
    if IniParams.Log.GPRSSQL then FileLog(GPRSSQL,Linearize(SQL.Text));
    if RecordCount > 0 then begin
       First;
       FSZGCommandId:=FindField('FSZGCommandId').AsInteger;
       Send(FindField('Command').AsString);
       h:=9;
       UpdateFSZGEntry(FSZGCommandId,UpdStart,0,'Command was sent.');
    end

    else begin
       h:=10;
       Close;
       SQL.Clear;
       SQL.Add('SELECT UzenetId,Prioritas,Uzenet FROM Uzenetek WHERE (Fszg=' + str(id) + ') ');
       SQL.Add('AND (UzenetTipusId=0) AND (Kezbesites IS NULL) AND (Kuldes < ' + NowDBFunc + ') AND (StatuszJelentes<>2) ');
       SQL.Add('ORDER BY UzenetId DESC ;');
       h:=11; t:=Now;
       Open;
       h:=12;
       if IniParams.GPRS.CollectSQL then SqlHistory.Add(FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',Now) + #9 + Linearize(SQL.Text));
       if IniParams.Log.GPRSSQL then FileLog(GPRSSQL,Linearize(SQL.Text));
       h:=20;
       if RecordCount > 0 then begin
          First;
          h:=30;
          with IniParams.GPRS do case MessageFormat of
            0 : begin
                  h:=40;
                  Send('MESS ' + FieldByName('Uzenet').AsString);
                end;
            1 : while not Eof do begin
                  UzenetId:=FieldByName('UzenetId').AsInteger;
                  h:=50;
                  Send('MESSAGE ' + str(UzenetId) + ' ' + FieldByName('Prioritas').AsString + ' ' + FieldByName('Uzenet').AsString);
                  Next;
                end;
            else FileLog(Errors,'Wrong value in IniParams.GPRS.MessageFormat : ' + str(MessageFormat));
          end;
          h:=60;
          UpdateUzenetEntry(FieldByName('UzenetId').AsInteger,1);
       end

       else if IniParams.Local.FTPCommandSyntax = 0 then begin
          h:=70;
          Close;
          SQL.Clear;
          SQL.Add('SELECT Command,DIR_S,DIR_C,FileName,IssuerIP,FTPCommandId FROM FTPCommands WHERE (FszgId=' + str(id) + ') AND ((Command=1) OR (Command=2)) ');
          SQL.Add('AND (ExecuteDate < ' + NowDBFunc + ') ');
          SQL.Add('AND (DateDiff(day,ExecuteDate,' + NowDBFunc + ') <= ' + str(IniParams.GPRS.FTPCommandsExpiration) + ') ');
          SQL.Add('AND ((StartDate IS NOT NULL) OR (FinishDate IS NULL)) ');
          SQL.Add('AND ( (FinishDate IS NULL) '); // added 2010.04.02.
          case IniParams.GPRS.FTPRetries of
            OnNoInfo     : SQL.Add('OR ((Status = 21) AND (Retries < ' + str(IniParams.GPRS.FTPRetryCount) + ')) ) ');
            UntilSuccess : SQL.Add('OR ((Status NOT IN (20,22)) AND (Retries < ' + str(IniParams.GPRS.FTPRetryCount) + ')) ) ');
            else SQL.Add(' ) ');
          end;
          SQL.Add('ORDER BY IssueDate ASC ;');
          h:=71; t:=Now;
          Open;
          h:=72;
          if IniParams.GPRS.CollectSQL then SqlHistory.Add(FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',Now) + #9 + Linearize(SQL.Text));
          if IniParams.Log.GPRSSQL then FileLog(GPRSSQL,Linearize(SQL.Text));
          if RecordCount > 0 then begin
             First;
             h:=73;
             repeat
               FTPMode:=ftpNoMode;
               c:=FindField('Command').AsInteger;
               case c of
                 1: FTPMode:=ftpPut;
                 2: FTPMode:=ftpGet;
                 else FileLog(Errors,'Unexpected command code in GPRSConn.FTPLoadCommands : ' + str(c));
               end;
               FTPAdd(FTPMode,FindField('DIR_S').AsString,
                              FindField('DIR_C').AsString,
                              FindField('FileName').AsString,
                              FindField('IssuerIP').AsString,
                              FindField('FTPCommandId').AsInteger);
               Next;
             until Eof;
             h:=75;
          end;
       end
       else if ExportId = 0 then begin
          h:=80;
          Close;
          SQL.Clear;
          SQL.Add('SELECT ExportId,FileName FROM NapiFeladatExportok WHERE (FSzg=' + str(id) + ') ');
          SQL.Add('AND (Aktiv=1) AND (Statusz<>99) AND (CAST(Letrehozas AS Integer) = CAST(GETDATE() AS integer)) ');
          SQL.Add('ORDER BY Sorrend ASC,Datum ASC');
          h:=81; t:=Now;
          Open;
          h:=82;
          if IniParams.GPRS.CollectSQL then SqlHistory.Add(FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',Now) + #9 + Linearize(SQL.Text));
          if IniParams.Log.GPRSSQL then FileLog(GPRSSQL,SQL.Text);
          h:=90;
          if RecordCount > 0 then begin
             First;
             h:=100;
             c:=FindField('ExportId').AsInteger;
             Send('DOWNLOAD ' + str(c) + ' ' + FindField('FileName').AsString);
             h:=110;
             BusDataRequester:=nil;
             ExportId:=c;
             ExportTime:=Now;
             h:=120;
             Close;
             SQL.Clear;
             try
               DatablocksCriticalSection.Enter;
               DatablocksSQL.Add('UPDATE NapiFeladatExportok SET GpsComVette=GETDATE(),Statusz=9,Retries=Retries+1,Message=''' +
                                 IniFile.ReadString('LocalReplyCodes','009','') + ''' WHERE ExportId=' + str(c));
             finally
               DatablocksCriticalSection.Leave;
             end;
{             SQL.Add('UPDATE NapiFeladatExportok SET GpsComVette=GETDATE(),Statusz=9,Retries=Retries+1,Message=''' +
                     IniFile.ReadString('LocalReplyCodes','009','') + ''' WHERE ExportId=' + str(c));
             h:=130;
             ExecSQL;}
             h:=140;
             if IniParams.GPRS.CollectSQL then SqlHistory.Add(FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',Now) + #9 + Linearize(SQL.Text));
             if IniParams.Log.GPRSSQL then FileLog(GPRSSQL,SQL.Text);
             h:=150;
          end;
       end;
    end;
    h:=160;

  finally
    QueryText:=Query.SQL.Text;
    //if IniParams.Log.Database then FileLog(DatabaseAll,QueryText);
    Query.Free;
  end;
  except
    on E: EDatabaseError do FileLog(DatabaseErrors,QueryText + E.Message);
    on E: Exception do
       if pos('General SQL Server error: Check messages from the SQL Server',E.Message)>0 then
          FileLog(DatabaseErrors,QueryText + E.Message)
       else FileLog(Exceptions,E.ClassName + ' in GPRSConn.FTPLoadCommands pos ' + str(h) +
		    FormatDateTime(', hh:nn:ss.zzz',Now-t) + ' after Open;' + EOL + E.Message);
  end;
end;

Exception logs :

Code: Select all

2011-08-15 15:05:01.375 
  EListError in GPRSConn.FTPLoadCommands pos 71, 00:00:00.016 after Open;
  List index out of bounds (4)
2011-08-15 15:22:00.703 
  EInvalidPointer in GPRSConn.FTPLoadCommands pos 71, 00:00:00.015 after Open;
  Invalid pointer operation
2011-08-15 19:57:11.781 
  EListError in GPRSConn.FTPLoadCommands pos 11, 00:00:00.000 after Open;
  List index out of bounds (5)
2011-08-16 05:55:11.313 
  EInvalidPointer in GPRSConn.FTPLoadCommands pos 71, 00:00:00.016 after Open;
  Invalid pointer operation
2011-08-16 09:27:11.156 
  EInvalidPointer in GPRSConn.FTPLoadCommands pos 11, 00:00:00.000 after Open;
  Invalid pointer operation
2011-08-16 11:48:11.734 
  EInvalidPointer in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Invalid pointer operation
2011-08-16 11:48:11.953 
  EInvalidPointer in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Invalid pointer operation
2011-08-16 11:48:12.453 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 44626948
2011-08-16 11:48:12.688 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.016 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 546C714F
2011-08-16 11:48:12.953 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 546C714F
2011-08-16 11:48:13.188 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65526461
2011-08-16 11:48:13.406 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65526461
2011-08-16 11:48:13.641 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:13.875 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:14.109 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6962656E
2011-08-16 11:48:14.328 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6962656E
2011-08-16 11:48:14.531 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:14.750 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:14.984 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65526461
2011-08-16 11:48:15.188 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65526461
2011-08-16 11:48:15.422 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:15.656 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:15.891 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6962656E
2011-08-16 11:48:16.094 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6962656E
2011-08-16 11:48:16.297 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:16.531 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:16.766 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65526461
2011-08-16 11:48:16.984 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65526461
2011-08-16 11:48:17.203 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:17.438 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:17.672 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6962656E
2011-08-16 11:48:17.891 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6962656E
2011-08-16 11:48:18.125 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:18.359 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:48:18.594 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65526461
2011-08-16 11:48:18.828 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65526461
2011-08-16 11:49:11.156 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6620746B
2011-08-16 11:49:11.922 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 0C2B420D
2011-08-16 11:49:12.156 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 7669724C
2011-08-16 11:49:12.391 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6E41656F
2011-08-16 11:49:12.625 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65537468
2011-08-16 11:49:12.859 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 7267746F
2011-08-16 11:49:13.094 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 69646F69
2011-08-16 11:49:13.313 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 62726570
2011-08-16 11:49:13.547 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 7267746F
2011-08-16 11:49:13.766 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53626948
2011-08-16 11:49:13.984 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65746948
2011-08-16 11:49:14.188 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6279535E
2011-08-16 11:49:14.438 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6962656E
2011-08-16 11:49:14.672 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65526568
2011-08-16 11:49:14.906 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 69772060
2011-08-16 11:49:15.125 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 654D6570
2011-08-16 11:49:15.344 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6962656E
2011-08-16 11:49:15.578 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 7267746F
2011-08-16 11:49:15.813 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65746170
2011-08-16 11:49:16.016 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6962656E
2011-08-16 11:49:16.250 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 74614470
2011-08-16 11:49:16.484 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 654D6568
2011-08-16 11:49:16.703 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 444F5270
2011-08-16 11:49:16.922 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 74614470
2011-08-16 11:49:17.156 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65746170
2011-08-16 11:49:17.391 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 654D6570
2011-08-16 11:49:17.625 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:49:17.859 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65526568
2011-08-16 11:49:18.094 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 4D626948
2011-08-16 11:49:18.313 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 7267746F
2011-08-16 11:49:18.531 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 5263696E
2011-08-16 11:49:18.750 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 54737461
2011-08-16 11:49:18.984 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65736E65
2011-08-16 11:49:19.188 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65537468
2011-08-16 11:49:19.406 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 7361626E
2011-08-16 11:49:19.641 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65526461
2011-08-16 11:49:19.875 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 7267746F
2011-08-16 11:49:20.109 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 72506E73
2011-08-16 11:49:20.344 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 61745368
2011-08-16 11:49:20.563 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 436C714F
2011-08-16 11:49:20.797 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 7267746F
2011-08-16 11:49:21.031 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65537468
2011-08-16 11:49:21.250 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 7361626E
2011-08-16 11:49:21.453 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53626948
2011-08-16 11:49:21.672 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53734D5E
2011-08-16 11:49:21.906 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 53657263
2011-08-16 11:49:22.141 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 61746E6B
2011-08-16 11:49:22.359 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 65746170
2011-08-16 11:49:22.594 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 61746E6B
2011-08-16 11:49:22.813 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 7361626E
2011-08-16 11:50:11.156 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163
2011-08-16 11:50:11.797 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163
2011-08-16 11:50:12.031 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163
2011-08-16 11:50:12.266 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163
2011-08-16 11:50:12.500 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163
2011-08-16 11:50:12.719 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163
2011-08-16 11:50:12.953 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163
2011-08-16 11:50:13.188 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163
2011-08-16 11:50:13.422 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163
2011-08-16 11:50:13.656 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163
2011-08-16 11:50:13.891 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163
2011-08-16 11:50:14.125 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163

... 

from now on all addresses are the same several times each second, until 12:04:22

...

2011-08-16 12:04:22.641 
  EAccessViolation in GPRSConn.FTPLoadCommands pos 3, 00:00:00.000 after Open;
  Access violation at address 00404650 in module 'GpsCom.exe'. Read of address 6C6C6163

Eurekalog call stack WITH ZEOS CODE LINE NUMBERS :

Code: Select all

Call Stack Information:
------------------------------------------------------------------------------------------------------
|Address |Module    |Unit                  |Class                      |Procedure/Method    |Line    |
------------------------------------------------------------------------------------------------------
|Running Thread: ID=308; Priority=0; Class=; [Main]                                                  |
|----------------------------------------------------------------------------------------------------|
|004ECDCA|GpsCom.exe|ZPlainDbLibDriver.pas |TZDBLibMSSQL7PlainDriver   |CheckError          |1209[22]|
|004FD0D7|GpsCom.exe|ZDbcDbLib.pas         |TZDBLibConnection          |CheckDBLibError     |401[2]  |
|004EFFD3|GpsCom.exe|ZDbcDbLibStatement.pas|TZDBLibStatement           |FetchResults        |296[24] |
|004F0173|GpsCom.exe|ZDbcDbLibStatement.pas|TZDBLibStatement           |ExecuteQuery        |329[5]  |
|004DDD8D|GpsCom.exe|ZDbcStatement.pas     |TZEmulatedPreparedStatement|ExecuteQuery        |1984[1] |
|004DDEB1|GpsCom.exe|ZDbcStatement.pas     |TZEmulatedPreparedStatement|ExecuteQueryPrepared|2025[1] |
|00596DCE|GpsCom.exe|ZAbstractRODataset.pas|TZAbstractRODataset        |CreateResultSet     |1591[25]|
|00596C20|GpsCom.exe|ZAbstractRODataset.pas|TZAbstractRODataset        |CreateResultSet     |1566[0] |
|00591480|GpsCom.exe|ZAbstractDataset.pas  |TZAbstractDataset          |CreateResultSet     |328[1]  |
|00596ECD|GpsCom.exe|ZAbstractRODataset.pas|TZAbstractRODataset        |InternalOpen        |1615[12]|
|0068D874|GpsCom.exe|GPRSUnit.pas          |TGPRSConn                  |FTPLoadCommands     |456[8]  |
|0068D794|GpsCom.exe|GPRSUnit.pas          |TGPRSConn                  |FTPLoadCommands     |448[0]  |
|0064F59C|GpsCom.exe|TimersUnit.pas        |TTimers                    |FTPTimerTimer       |477[3]  |
|77F4E431|user32.dll|                      |                           |DispatchMessageA    |        |
|77F4E427|user32.dll|                      |                           |DispatchMessageA    |        |
|0069D8AA|GpsCom.exe|GPSCom.dpr            |                           |                    |43[22]  |
------------------------------------------------------------------------------------------------------

(btw. how to widen the code window ?)

I suspect there are some global variables ZConnection or ZQuery uses that are not thread-safe. Or I lack some essential information that's Zeos specific and needed to run ZComponents multithreadedly safely.
I've already implemented TCriticalSection as I found it best.
These errors did not exist before I put Zeos in threads.

On the first sight I wonder the community's general suggestion about Zeos and threads, does anybody have experience pushing a DB server to its limits successfully this way ?

Thanks for any comments.

Cheers,
Peter


ps. I am on: Delphi 5; MSSQL 2005; Win2003 Server

[Wild_Pointer]

Hello, PetyaMaster,

when programming multithread application you must remember not to use TZConnection components from several threads. All connection and using it components should be used in single thread.
I see you are using mysql. I have no experience with it, but I have some using postgresql. I have written several programs using zeos 7 components in several threads. I works ok...

By the way - zeos 666 had some problems if you execute queries after the connection to server is broken. May this be the case?

[PetyaMaster]

Hi Wild_Pointer,

So you say that Zeos 6.x.x is completely incompatible with multithreading, but 7.x is ? You say this function is implemented in versions 7.x above ? Am I understanding it right ?

Well, I have decided to DO multithreading in my app, even if I found it's not possible with Zeos anyway. I have several reasons to do so. But before, if I find even one possible way to do it with Zeos, I prefer that one.
My company has bought me another lightweight database component (Devart SDAC) for another project, but I do not want to completely throw Zeos away, mainly cos it's a LOT work I wanna spare if possible, second cos Zeos has worked fine for me for at least 5 years now, so I have quite a bit of experience with it.

Btw. I hope the last stable version number was chosen without concerns to other possible associations - even in the form you wrote it without the points - I haven't noticed this before your post.
Did (all of) you know that Intel had not produce that speed of processor when they reached that point - I haven't found any other acceptable reasons for that. There were 466, 566, 667, 766 and 866 MHz Celerons. They left out that number intentionally.

Cheers,

Peter

ps. I'm on MSSQL, dunno how you found My.

[Wild_Pointer]

So you say that Zeos 6.x.x is completely incompatible with multithreading, but 7.x is ? You say this function is implemented in versions 7.x above ? Am I understanding it right ?

No, what I ment it has problems after connection loss (in the Postgresql part at least). This does not mean you can't use it for multithreading. What I wanted to say is that unstable network may be causing your problems (unless your server is localhost).

Well, I have decided to DO multithreading in my app, even if I found it's not possible with Zeos anyway. I have several reasons to do so. But before, if I find even one possible way to do it with Zeos, I prefer that one.

I'm sure you will succeed. Just see one more time if no connection object is used from 2 threads.

ps. I'm on MSSQL, dunno how you found My.

Sorry, seems that I have misread "TZDBLibMSSQL7PlainDriver" from your listing Anyway - I have no experience with Zeos + MSSQL....

Good luck!

[PetyaMaster]

Hi,

I'm on localhost.

I'm sure you will succeed. Just see one more time if no connection object is used from 2 threads.

No, I have separate 'dynamic' ZConnection objects created and connected in each thread before entering the main loops (I have 4 threads btw), all has only 1 instance of ZQuery using it from within the threads.
However, I needed to create a separate CriticalSection object for syncing the connections from the threads, cos when all threads were started at the same time, connection problems occurred. CriticalSectioning the connections eliminated these.
I also have one 'static' ZConnection on the main form, but that's not for a single ZQuery, this one may be fired multiple queries in paralell, but not from other threads, only from its own thread (which is the main thread).
Do you think this can cause my problems ?

Thanks, Peter

[Wild_Pointer]

PetyaMaster,

cos when all threads were started at the same time, connection problems occurred

This is strange. I think you should try to investigate why such things happen. It is very hard to find what is wrong when the program crashes once a week. If several connection objects in separate threads causes errors - start there to see what causes them. Do you use sql monitor? May it be a cause of problems?

Good luck!

[PetyaMaster]

It is very hard to find what is wrong when the program crashes once a week.

Right.
I have 20 different error log files, I created inspection points in code and write them out. See the last FileLog() call in the next code.

Code: Select all

    try
    //Query:=ZQuery.Create(MainModule);
    //with ZQuery.Create(MainModule) do
    with MainModule.ZQueryBlockInsert do try
      h:=10;
//      SQL.Clear;
      h:=11;
//      SQL.Add(temp1);
      h:=12;
      with Form1 do if Visible then Edit_Insert.Text:=temp1;
      h:=13;
      LogTime(51);
{      h:=14;
      t1:=Now;
      h:=15;
      DatabaseRWCriticalSection.Enter;
      h:=151;
      ExecSQL;
      h:=152;
      DatabaseRWCriticalSection.Leave;
      h:=16;
}     try
        h:=153;
        AdatblokkCriticalSection.Enter;
        h:=154;
        AdatblokkSQL.Add(temp1);
        h:=155;
      finally
        AdatblokkCriticalSection.Leave;
      end;
      h:=17;
      LogTime(52);
      h:=18;
      Result:=DSRAdatblokkOK;
      h:=19;
      //Close;
    finally
//      QueryText:=SQL.Text;
      QueryText:=temp1;
      if IniParams.GPRS.CollectSQL then Conn.SqlHistory.Add(FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',Now) + #9 + Linearize(QueryText));
      //if IniParams.Log.Database then FileLog(DatabaseAll,QueryText);
      if IniParams.Log.GPRSSQL then FileLog(Conn.GPRSSQL,Linearize(QueryText));
      //ZQuery1.SQL.Clear;
    end;
    except
      on E: EDatabaseError do begin
         FileLog(DatabaseErrors,QueryText + E.Message);
//         FileLog(BadBlocks,'Bad block from ' + str(Conn.id) + #9 + 'REASON : INSERT to Adatblokk failed' + EOL + Block + EOL);
         Exit;
      end;
      on E: Exception do
         if pos('General SQL Server error: Check messages from the SQL Server.',E.Message)>0 then begin
            FileLog(DatabaseErrors,QueryText + E.Message);
//            FileLog(BadBlocks,'Bad block from ' + str(Conn.id) + #9 + 'REASON : INSERT to Adatblokk failed' + EOL + Block + EOL);
            Exit;
         end
         else begin

           FileLog(Exceptions,E.ClassName + ' in SendData while writing table Adatblokk pos ' + str(h) + EOL + E.Message);

         end;
    end;

I cannot debug in realtime cos my app runs on remote servers. I see my app via RDP consoles.
I don't use sql monitor.

[Wild_Pointer]

hello,

is procedure TGPRSConn.FTPLoadCommands; executed from other than main thread ?

[PetyaMaster]

is procedure TGPRSConn.FTPLoadCommands; executed from other than main thread ?

No.

Do you want to see the threads full code (~5 pages in 1280x1024) ?
All 4 threads have same code, same class, but 4 instances created with different parameters.
There's no FTPLoadCommands; call in that code.

[Wild_Pointer]

hm...
there is not much I can think of then... If you send me your program source to private I will try to investigate the source this weekend.

Or maybe someone with experience of zeos + mssql will appear hier.

[PetyaMaster]

If you send me your program source

My program is the intellectual property of my company. I'd need their permission to do that in a whole.
But it's only an excuse, the real reason I'm not doing so is this wouldn't be workmanlike anyway, it would show me as being vocationally incompetent.

Yesterday I found a fix for my problem by myself, but don't wanna post it now to see what others say without any technical influences. I wonder the level of volunteerism here. I'll post my fix if it remains stable for a week if I get no other tips. Well, I will do so also if I get any

Thank you for your contribution, Wild_Pointer !
You made me really closer to the final solution of the problem.

[Wild_Pointer]

the real reason I'm not doing so is this wouldn't be workmanlike anyway

Respect.

Will wait for your fix.

Good Luck!

[PetyaMaster]

The following addition to MyZQuery solved the original problem.

Code: Select all

  TMyZQuery	= class(TZQuery)
  private
    procedure BeforeOpenHandler(DataSet: TDataSet);
  public
    constructor Create(AOwner: TComponent); override;
    procedure ExecSQL; override;
    procedure Open; // <- ADDED SOLUTION
  end;

procedure TMyZQuery.Open;
begin
  if IniParams.Database.ProtectQueries then
  try
     DatabaseRWCriticalSection.Enter;
     inherited Open;
  finally
     DatabaseRWCriticalSection.Leave;
  end
  else inherited Open;
end;

However, this way it seems Zeos is not stable while multithreading. And I told it in a polite way.
If I switch ProtectQueries off, the original situation returns.

Meanwhile on Eurekalog's forum I got another interesting advice HERE. It probably won't make anybody happy here, but I wanna link it for you to see this story in a whole. I haven't done that step yet, neither willing to do so until my app remains that stable as it is now, but it's just such a thing that can't hurt knowing about. Well, can't hurt me, at least. I'm really sorry for that, but it was not my opinion.

I must say the biggest THANK YOUs ever possible for those who are devoded to Zeos, and continue contributing to the project.

Wish you really the best future I can imagine. May Go(o)d('s) Luck be with Zeos

Cheers, Peter

[mdaems]

Peter,

This advice doesn't make me unhappy. If, after investing enough professional (=~paid) time on an open source project, a paid closed source component does solve the problem, as a professional you must use that closed source component. It's the only way you can have your bills paid.

Certainly when using MSSQL this seems like a problem that will not be solved easily in zeoslib. At the moment there's nobody really focussing on mssql. And from what I know about the zeoslib Mssql driver code... I wouldn't risk to say the driver code can be thread safe.

If it's easy to detect a failure and requery that could also be a solution. One more call once a week won't hurt. But I must admit : it's not really nice.

Mark

[PetyaMaster]

Dear Mark,

I must really say my best thank you for that kind of approach from you. I really appreciate your point of view. Your advice has just arrived at the very best time even possible !!! Two days before I started that procedure, cannot tell it momently, how long it will last. If you're interested, I'll post how I succeed.

I cannot promise I will be active here in this forum in the future.
But I can offer, if anybody finds me personally with help or advice needed with Zeos and MSSQL, I will do my best to help him/her out.

zeos.lib@mail.system.hu

Remove the first two dots and leave the last before sending.

Wish You and this project really the bests


Cheers, Peter