Programming => Databases => Topic started by: jollytall on May 31, 2019, 08:52:47 am

Title: MYSQL access from threads
Post by: jollytall on May 31, 2019, 08:52:47 am
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!

Code: Pascal  [Select]
  1. unit Unit1;
  3. {$mode objfpc}{$H+}
  5. interface
  7. uses
  8.   CThreads, Classes, SysUtils, Forms, Controls, StdCtrls,  mySQL57conn, sqldb, sqldblib;
  10. type
  11.   TForm1 = class(TForm)
  12.     Button1: TButton;
  13.     procedure Button1Click(Sender: TObject);
  14.   end;
  15. var
  16.   Form1: TForm1;
  18. implementation
  19. {$R *.lfm}
  20. var
  21.   GMYSQLConnection:tMYSQL57Connection;
  22. //  SQLDBLibraryLoader1:TSQLDBLibraryLoader;
  24. type
  25.   tMyThread = class(tThread)
  26.     private
  27.       FSQLTransaction:          tSQLTransaction;
  28.       FSQLQuery:                tSQLQuery;
  29.     public
  30.       constructor Create;
  31.       procedure Execute; override;
  32.     end;
  33. constructor tMyThread.Create;
  34.   begin
  35.   inherited Create(false);
  36.   FSQLTransaction:=tSQLTransaction.Create(GMYSQLConnection);
  37.   FSQLTransaction.Database:=GMYSQLConnection;
  38.   FSQLQuery:=tSQLQuery.Create(GMYSQLConnection);
  39.   FSQLQuery.DataBase:=GMYSQLConnection;
  40.   FSQLQuery.Transaction:=FSQLTransaction;
  41.   end;
  42. procedure tMYThread.Execute;
  43.   begin
  44.   FSQLQuery.SQL.Text:='select * from tablename';
  45.   FSQLQuery.Open;
  46.   end;
  48. procedure TForm1.Button1Click(Sender: TObject);
  49.   var
  50.     i:integer;
  51.   begin
  52.   (*
  53.   SQLDBLibraryLoader1:=TSQLDBLibraryLoader.Create(nil);
  54.   SQLDBLibraryLoader1.ConnectionType:='MySQL 5.7';
  55.   SQLDBLibraryLoader1.LibraryName:='';
  56.   SQLDBLibraryLoader1.Enabled := true;
  57.   SQLDBLibraryLoader1.LoadLibrary;
  58.   *)
  59.   GMYSQLConnection:=tMYSQL57Connection.Create(nil);
  60.   with GMYSQLConnection do
  61.     begin
  62.     DataBaseName:='dbname';
  63.     HostName:='localhost';
  64.     Port:=3306;
  65.     UserName:='user';
  66.     Password:='';
  67.     Connected:=true;
  68.     end;
  69.   for i:=1 to 1 do
  70.     begin
  71.     with tMyThread.Create do
  72.       Start;
  73.     end;
  74.   end;
  76. end.
Title: Re: MYSQL access from threads
Post by: sash on May 31, 2019, 09:45:04 pm
I'm not that familiar with mysql client implementation, but I believe a non-shared (i.e. separate instance per each thread) TSQLConnection is much safer option.

If you're on Linux, please check if cthreads are used.
Title: Re: MYSQL access from threads
Post by: john horst on June 01, 2019, 04:56:29 am
This code looks odd. I don't use MySQL but it seems like the flow is.

Connect to DB -> then in threads try a bunch of the same transactions at once??? -> seems like only one would complete and the rest would roll back / error. That's my take on a transaction.

It seems to me you would have to wait for transaction to complete before doing the next.
Title: Re: MYSQL access from threads
Post by: mangakissa on June 01, 2019, 10:43:05 am
Always create a new connection when using threads
Title: Re: MYSQL access from threads
Post by: jollytall on June 02, 2019, 08:43:14 am
Oddly enough I tried the 1 connection/thread logic earlier and that failed too. But now it works  :).
What is also strange that in an earlier project I used the shared connection logic and there it did not cause any problem.
Anyway, the good news is that it seems to work now.
Title: Re: MYSQL access from threads
Post by: jollytall on June 19, 2019, 10:05:33 pm
I am still straggling with this. It works, but at random times crashes with a SIGSEGV when trying to commit the transaction. I guess it is because the DB access library is not thread safe. I want to use a library for MySQL. What I see in the compiled project is that my program uses /usr/lib/x86_64-linux-gnu/
The smaller problem is why not the what is also available in the same library.
The bigger problem is why not the version, also present for both mariadb and mysql.
How can I change what library to link? How can I make it thread safe?

Thanks in advance
Title: Re: MYSQL access from threads
Post by: jollytall on June 22, 2019, 09:16:43 pm
PascalDragon solved it:,45806.msg324520.html#msg324520