I use Debian 9, Gnome 3.22.2, Lazarus 2.0.0RC3, FPC 3.0.4, MYSQL 5.7.26 on 64bits.
I have a program that tries to access an MYSQL DB from threads. The SQLQuery fails at various times with various error messages ("MySQL server has gone away", "Lost connection to MySQL serer during query", "Commands out of sync", "Field not found", "Cannot open a non-select statement" and even SIGPIPE, etc.) for the very same code running it multiple times.
It is a complex code, but made a minimal version showing the same problem. In the full version I tried to capture the errors with try...except, obviously closed the query, transaction, thread and all other good housekeeping. Nothing helped.
The current version uses a shared global Connection and all threads have their own Transaction and SQLQuery. I tried all different versions, giving e.g. all treads their own Connection.
I also read on the forum (did my homework), to do an SQLDBLibraryLoader step as well (commented in the current version). That did not help either.
If in the for... loop I make 1 thread then it works always well. If I make 2 or 3 it sometimes works, sometimes fails. As I increase the number of threads further it surely fails, but always with different errors.
What it tells me that something is not Thread Safe, but cannot figure out what. Oddly enough, the same set-up worked well in a previous project (older FPC, MYSQL, etc.).
Any help would be appreciated!
unit Unit1;
{$mode objfpc}{$H+}
interface
uses
CThreads, Classes, SysUtils, Forms, Controls, StdCtrls, mySQL57conn, sqldb, sqldblib;
type
TForm1 = class(TForm)
Button1: TButton;
procedure Button1Click(Sender: TObject);
end;
var
Form1: TForm1;
implementation
{$R *.lfm}
var
GMYSQLConnection:tMYSQL57Connection;
// SQLDBLibraryLoader1:TSQLDBLibraryLoader;
type
tMyThread = class(tThread)
private
FSQLTransaction: tSQLTransaction;
FSQLQuery: tSQLQuery;
public
constructor Create;
procedure Execute; override;
end;
constructor tMyThread.Create;
begin
inherited Create(false);
FSQLTransaction:=tSQLTransaction.Create(GMYSQLConnection);
FSQLTransaction.Database:=GMYSQLConnection;
FSQLQuery:=tSQLQuery.Create(GMYSQLConnection);
FSQLQuery.DataBase:=GMYSQLConnection;
FSQLQuery.Transaction:=FSQLTransaction;
end;
procedure tMYThread.Execute;
begin
FSQLQuery.SQL.Text:='select * from tablename';
FSQLQuery.Open;
end;
procedure TForm1.Button1Click(Sender: TObject);
var
i:integer;
begin
(*
SQLDBLibraryLoader1:=TSQLDBLibraryLoader.Create(nil);
SQLDBLibraryLoader1.ConnectionType:='MySQL 5.7';
SQLDBLibraryLoader1.LibraryName:='libmysqlclient.so';
SQLDBLibraryLoader1.Enabled := true;
SQLDBLibraryLoader1.LoadLibrary;
*)
GMYSQLConnection:=tMYSQL57Connection.Create(nil);
with GMYSQLConnection do
begin
DataBaseName:='dbname';
HostName:='localhost';
Port:=3306;
UserName:='user';
Password:='';
Connected:=true;
end;
for i:=1 to 1 do
begin
with tMyThread.Create do
Start;
end;
end;
end.