Problems with multi-thread application

[JasonBourne]

Hello guys >.< !

Well, after trying everything with no success, I resolved to request some help here.

I don't know why, but no matter what library I use for the thread pool, no matter how I execute the querys in database, Im still getting the same errors. Seems the querys of one thread blends with others. Check this error log:

Code: Select all

-----------------------
function: procCountById()   <--the function. Ok!
Arg value: 55  <--parameter received. Ok!
MyQuery: SELECT COUNT(*) FROM clients WHERE ID = 55   <--MyQuery = string variable, in the next line of code I will execute this query!. Ok!
Error Message: SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dbname.clients LIKE '%'' at line 1 <--Fail. The executed query isn't the same query of MyQuery variable.
------------------------

-----------------------------------------------
Function:      UpdateClientProcCount()   <--the function. Ok!
Arg Value:     185      <--parameter received. Ok!
MyQuery:       UPDATE clients SET NumProcs = 2 WHERE ID = 185    <--MyQuery = string variable, in the next line of code I will execute this query!. Ok!
Error Message: SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'D = 185' at line 1 <--Same error. The query just broke here, I don't know why
-----------------------------------------------

I'm using the OmniThreadLibrary for the thread pool, and the connection pool unit coded by <I Don't remeber who created, lol>. Im creating 10 threads in each pool (the thread pool and the connection pool (so, each thread have his own TZConnection)). In the first app, the connection pool was returning TZConnection object, so I changed the pool to return TMyDataBase object. Nothing has changed, Im still get the same errors.

Here is the sample function:

Code: Select all

procedure TMyDataBase.SetProcessFlag(ProcessID: Integer; Flag: Integer = PROC_OK);
var
	Query: TZQuery;
	eMsg: string;
begin
	try
      //Probably, you are thinking: Why you're creating one TZQuery for each procedure ? Well, Doesn't matter if I create one TZQuery in private section of my object, or create one in each procedure/function, this erros still happens.
		Query:=TZQuery.Create(nil);
		Query.Connection:=ZCon;

      //Using Format or ParamByName, the error still occur.     ---
		MyQuery:=Format('UPDATE process SET Stats = 1, Flag = %d WHERE ID = %d', [Flag, ProcessID]);
		Query.SQL.Add(MyQuery);

               //Im getting this errors using transaction with ExecuteDirect too, I have no ideia how to solve this...
		Query.ExecSQL;

		Query.Free;
	except
		On E:Exception do
		begin
			eMsg:=' '+sLineBreak;
			eMsg:=eMsg + '-----------------------------------------------'+sLineBreak;
			eMsg:=eMsg + 'Function:      SetProcessFlag()'+sLineBreak;
			eMsg:=eMsg + 'Arg Value:     '+IntToStr(ProcessID)+' -- '+IntToStr(Flag)+sLineBreak;
			eMsg:=eMsg + 'MyQuery:       '+MyQuery+sLineBreak;
			eMsg:=eMsg + 'Error Message: '+E.Message+sLineBreak;
			eMsg:=eMsg + '-----------------------------------------------';
			debugWrite(eMsg);
		end;
	end;
end;

Im using Delphi XE6 with Zeos 7.2 beta. I will build a simple test case and post here, I really need to solve this problem .

Sorry for the English ^^.

[RaThek]

I would do this like this:

Code: Select all

      //Using Format or ParamByName, the error still occur.     ---
      //MyQuery:=Format('UPDATE process SET Stats = 1, Flag = %d WHERE ID = %d', [Flag, ProcessID]);
      //Query.SQL.Add(MyQuery);
      Query.SQL.Text := 'UPDATE process SET Stats = 1, Flag = :flag WHERE ID = :id';
      Query.ParamByName('flag').AsInteger := Flag; //guessing Flag is integer
      Query.ParamByName('id').AsInteger := ProcessID;
      Query.ExecSQL;

When you use Query.SQL.Add you're adding new text to one that there is already. Is it what you wanted it to be?

[JasonBourne]

I would do this like this:

Code: Select all

      //Using Format or ParamByName, the error still occur.     ---
      //MyQuery:=Format('UPDATE process SET Stats = 1, Flag = %d WHERE ID = %d', [Flag, ProcessID]);
      //Query.SQL.Add(MyQuery);
      Query.SQL.Text := 'UPDATE process SET Stats = 1, Flag = :flag WHERE ID = :id';
      Query.ParamByName('flag').AsInteger := Flag; //guessing Flag is integer
      Query.ParamByName('id').AsInteger := ProcessID;
      Query.ExecSQL;

When you use Query.SQL.Add you're adding new text to one that there is already. Is it what you wanted it to be?

Doesn't work. Still getting the same errors. I got 4 errors when I tried to insert 350 values in database (in the test case). I will upload the test case here (no exe, and I didn't included the OmniThread library). Pretty small test, just one database with one table (sql file included).

[EgonHugeist]

Have no XE6, sorry. Thougth treads need a syncronise call?

function: procCountById() <--the function. Ok!
Arg value: 55 <--parameter received. Ok!
MyQuery: SELECT COUNT(*) FROM clients WHERE ID = 55 <--MyQuery = string variable, in the next line of code I will execute this query!. Ok!
Error Message: SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dbname.clients LIKE '%'' at line 1 <--Fail. The executed query isn't the same query of MyQuery variable.

Your comment is true. Zeos adds the default schema(this happens i think). Have to look deeper if i can.

[EgonHugeist]

Ok i did check it a while.

As i sad: you're running asynchronus threads. The problem happens everywhere where an interface is created from the asynced connection-component. Most issues happen because of wrong string tokens.
There is no stable fix for this behavior except creating one connection per thread.
I couldn't find a stable issue while running the threads. Everything was completely randomly..

Note: long times i've an idea in my mind to create connection from existing handles. So no reconnect would be done. But this is music of future..

Edit: Playing a while tracking the problems seems to be possible...

[EgonHugeist]

Forget about it.

Sorry SF.net is temporarily in static offline mode. So actually i can't commit my patch.

So i've attached a dif

[miab3]

@JasonBourne, @EgonHugeist,

SVN r3471:
Now works for me without error.

Michal

[jjturbo]

Hi,

i also have the problem with an exception when using a multi-thread application.
I am using Delphi XE7 and ZeosLib 7.2beta.
I have written a small application, a form with a timer, and a simple thread object to get a second thread.

Both threads are using an own TZConnection. But when both threads are polling to a small table a get an exception like this:

Code: Select all

'SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''FFROLagesSELECT * FROM Lagerbestand'' at line 1', 0, nil, nil, False

or like this:

Code: Select all

'SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Fosfz'' at line 1'

or like this:

Code: Select all

'SQL Error: Table ''hc2466.jbs_z'' doesn''t exist'

or like this:

Code: Select all

'SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SLCT*  aebsand'#$D#$A'SELECT * FROM Lagerbestand'' at line 1'

This is the first unit:

Code: Select all

unit Unit1;

interface


uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.ExtCtrls, Vcl.StdCtrls, ZConnection, ZDataSet, UnitTestThread;

const
   WM_MsgVomSPSThread     = wm_App + 402;


type
  TForm2 = class(TForm)
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    Timer1: TTimer;
    procedure Timer1Timer(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure CheckBox2Click(Sender: TObject);
  private
    procedure OnMsg_VomSPSThread(var Message: TMessage); message WM_MsgVomSPSThread;
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form2      :TForm2;
  myDataBase :TZConnection;
  myZQuery   :TZQuery;
  TestThread :TTestThread;

implementation

{$R *.dfm}




procedure TForm2.OnMsg_VomSPSThread(var Message: TMessage);
var s        :String;
    i        :Integer;
    MyColor  :TColor;
begin
  CheckBox2.Caption := IntToStr(DateTimeToTimeStamp(Now).Time) + ': ' + IntToStr(Message.WParam) + ' / ' + IntToStr(Message.LParam);
end;





procedure ExecSQL_ZQuery(MyQuery:TZQuery;SQLText:String);
begin

  MyQuery.Active := false;
  MyQuery.SQL.Text := SQLText;

  TRY
    MyQuery.Active := True;
  EXCEPT
    on E: Exception do begin
      ShowMessage('SQL Error !!' + #13 + 'SQL String = ' + SQLText + E.Message + E.ClassName);
    end;
  END;

end;



procedure TForm2.CheckBox2Click(Sender: TObject);
begin
  TestThread.Start := CheckBox2.Checked;
end;



procedure TForm2.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  TestThread.Terminate;
  CheckBox1.Enabled := false;
  CheckBox2.Enabled := false;

  myZQuery.Close;
  myZQuery.Free;

  myDataBase.Disconnect;
  myDataBase.Free;

  TestThread.WaitFor;
  Sleep(300);
end;





procedure TForm2.FormCreate(Sender: TObject);
begin
  myDataBase                            := TZConnection.Create(nil);
  myDataBase.LoginPrompt                := false;
  myDataBase.User                       := 'thread_RFZ';
  myDataBase.AutoCommit                 := True;
  myDataBase.Password                   := '1Veltins1';
  myDataBase.HostName                   := '127.0.0.1';
  myDataBase.Database                   := 'HC2466';
  myDataBase.Protocol                   := 'mysql';
  myDataBase.Connect;

  myZQuery                              := TZQuery.Create(nil);
  myZQuery.Connection                   := myDataBase;


  TestThread                            := TTestThread.Create(false);
end;







procedure TForm2.Timer1Timer(Sender: TObject);
var SQLText      :String;
begin
  Timer1.Enabled := false;

  if CheckBox1.Checked then begin
    SQLText := 'SELECT * FROM jobs_rfz';
    ExecSQL_ZQuery(myZQuery,PChar(SQLText));
    Caption := IntToStr(DateTimeToTimeStamp(Now).Time) + ': ' + IntToStr(myZQuery.Recordcount);
  end;

  Timer1.Enabled := True;
end;







end.

and this is the second one:

Code: Select all

unit UnitTestThread;

interface

uses
  System.Classes, System.sysUtils, ZConnection, ZDataSet, Dialogs, Windows, Forms;

type
  TTestThread = class(TThread)
  private
    myDataBase :TZConnection;
    myZQuery   :TZQuery;
    { Private-Deklarationen }
  protected
    procedure Execute; override;
  public
    Start   :Boolean;
  end;

implementation

uses Unit1;


var     ErrText :String;

{ TTestThread }




procedure ExecSQL_ZQuery(MyQuery:TZQuery;SQLText:String);
begin

    MyQuery.Active := false;
    MyQuery.SQL.Text := SQLText;

    TRY
      MyQuery.Active := True;
    EXCEPT
      on E: Exception do begin
        ErrText := 'SQL Error !!' + #13 + 'SQL String = ' + SQLText + E.Message + E.ClassName;
      end;
    END;

end;





procedure TTestThread.Execute;
var SQLText      :String;
begin
  myDataBase                            := TZConnection.Create(nil);
  myDataBase.LoginPrompt                := false;
  myDataBase.User                       := 'thread_QTW';
  myDataBase.AutoCommit                 := True;
  myDataBase.Password                   := '1Veltins1';
  myDataBase.HostName                   := '127.0.0.1';
  myDataBase.Database                   := 'HC2466';
  myDataBase.Protocol                   := 'mysql';
  myDataBase.Connect;

  myZQuery                              := TZQuery.Create(nil);
  myZQuery.Connection                   := myDataBase;
  Start                                 := false;



  while not Terminated do begin
    if Start then begin
      SQLText := 'SELECT * FROM Lagerbestand';
      ExecSQL_ZQuery(myZQuery,PChar(SQLText));
      PostMessage(Application.MainForm.Handle,WM_MsgVomSPSThread,999,myZQuery.Recordcount);
    end;
    Sleep(20);
  end;




  myZQuery.Close;
  myZQuery.Free;

  myDataBase.Disconnect;
  myDataBase.Free;

end;



end.

I hope you can help me?

With best regards, Oliver

[EgonHugeist]

Hi jjturbo,

as i wrote, the bug is fixed on our \testing-7.2 branch in our SVN repository. If you update your Zeos from SVN with TortoiseSVN f.e. you'll be a lucky man. Otherwise see my attached file "Thread_Safe_Token.patch" in my previous reply and apply the changes manually.