Recent

Author Topic: MYSQL access from threads  (Read 828 times)

jollytall

  • New Member
  • *
  • Posts: 22
MYSQL access from threads
« 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;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   CThreads, Classes, SysUtils, Forms, Controls, StdCtrls,  mySQL57conn, sqldb, sqldblib;
  9.  
  10. type
  11.   TForm1 = class(TForm)
  12.     Button1: TButton;
  13.     procedure Button1Click(Sender: TObject);
  14.   end;
  15. var
  16.   Form1: TForm1;
  17.  
  18. implementation
  19. {$R *.lfm}
  20. var
  21.   GMYSQLConnection:tMYSQL57Connection;
  22. //  SQLDBLibraryLoader1:TSQLDBLibraryLoader;
  23.  
  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;
  47.  
  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:='libmysqlclient.so';
  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;
  75.  
  76. end.
  77.  

sash

  • Sr. Member
  • ****
  • Posts: 275
Re: MYSQL access from threads
« Reply #1 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.
Lazarus 2.0.6 FPC 3.0.4 x86_64-linux-gtk2 -- Ubuntu 19.10 XFCE

john horst

  • Jr. Member
  • **
  • Posts: 53
    • JHorst
Re: MYSQL access from threads
« Reply #2 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.
« Last Edit: June 01, 2019, 05:07:39 am by john horst »

mangakissa

  • Hero Member
  • *****
  • Posts: 943
Re: MYSQL access from threads
« Reply #3 on: June 01, 2019, 10:43:05 am »
Always create a new connection when using threads
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

jollytall

  • New Member
  • *
  • Posts: 22
Re: MYSQL access from threads
« Reply #4 on: June 02, 2019, 08:43:14 am »
Thanks.
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.

jollytall

  • New Member
  • *
  • Posts: 22
Re: MYSQL access from threads
« Reply #5 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/libmariadbclient.so.18.0.0.
The smaller problem is why not the libmysqlclient.so what is also available in the same library.
The bigger problem is why not the ..._r.so 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

jollytall

  • New Member
  • *
  • Posts: 22
Re: MYSQL access from threads
« Reply #6 on: June 22, 2019, 09:16:43 pm »