mORMot Open Source

wprocopio

Member

Registered: 2015-07-03

Posts: 2

Sessions problem.

I am new to the mormot framework, and I must use it becasuse it was used in a project that I am maintaning.
So I have started to do some tests and studing it.

The software open a local database connection (mysql) and execute some queries. These queries are executed in anonymus threads. Every time a thread is created a new session is opend and never closed. After half hour I received a mysql error, because the software used all available connections.

This is the source code I wrote to test the problem. I have used ZeosDB and Unidac components.

TSQLCountry = class(TSQLRecord)
  private
    fUpdated: TDateTime;
    fCreated: TDateTime;
    fName: RawUTF8;
  published
    property Updated: TDateTime read fUpdated write fUpdated;
    property Created: TDateTime read fCreated write fCreated;
    property Name: RawUTF8 read fName write fName;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  FZConnection := TSQLDBZEOSConnectionProperties.Create(TSQLDBZEOSConnectionProperties.URI(dMySQL, 'localhost:3306'),
  'testDB', 'root', 'password');
  //FZConnection.ThreadingMode := tmMainConnection;

  FZModel := TSQLModel.Create([TSQLCountry]);

  VirtualTableExternalRegister(fzModel, TSQLCountry, fzConnection, 'country');

  FZLocalRestServer := TSQLRestServerDB.Create(FZModel, SQLITE_MEMORY_DATABASE_NAME);
  FZLocalRestServer.CreateMissingTables;

  FZLocalClient := TSQLRestClientDB.Create(FZLocalRestServer);
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  fConnection := TSQLDBUniDACConnectionProperties.Create(TSQLDBUniDACConnectionProperties.URI((dMySQL, 'localhost:3306'),
  'testDB', 'root', 'password');
  //fConnection.ThreadingMode := tmMainConnection;

  fModel := TSQLModel.Create([TSQLCountry]);

  VirtualTableExternalRegister(fModel, TSQLCountry, fConnection, 'country');

  FLocalRestServer := TSQLRestServerDB.Create(fModel, SQLITE_MEMORY_DATABASE_NAME);
  FLocalRestServer.CreateMissingTables;

  FLocalClient := TSQLRestClientDB.Create(FLocalRestServer);
end;

procedure TForm1.ExecuteUNIDACThread;
var
  lCountryExt: TObjectList<TSQLCountry>;
  CountryExt: TSQLCountry;

begin
  lCountryExt := FLocalClient.RetrieveList<TSQLCountry>('ID>?', [StrToInt(Edit1.Text)]);
  if assigned(lCountryExt) then
  begin
    for CountryExt in lCountryExt do
      memo1.Lines.Add(CountryExt.Name + ' ' + FormatDateTime('dd/mm/yyyy', CountryExt.Created) + ' ' + FormatDateTime('dd/mm/yyyy', CountryExt.Updated));
    lCountryExt.Free;
  end;
end;

procedure TForm1.ExecuteZEOSThread;
var
  lCountryExt: TObjectList<TSQLCountry>;
  CountryExt: TSQLCountry;

begin
  lCountryExt := FZLocalClient.RetrieveList<TSQLCountry>('ID>?', [StrToInt(Edit1.Text)]);
  if assigned(lCountryExt) then
  begin
    for CountryExt in lCountryExt do
      memo1.Lines.Add(CountryExt.Name + ' ' + FormatDateTime('dd/mm/yyyy', CountryExt.Created) + ' ' + FormatDateTime('dd/mm/yyyy', CountryExt.Updated));
    lCountryExt.Free;
  end;
end;

procedure TForm1.Button6Click(Sender: TObject);
begin
  TThread.CreateAnonymousThread(ExecuteUNIDACThread).Start;
end;

procedure TForm1.Button8Click(Sender: TObject);
begin
  TThread.CreateAnonymousThread(ExecuteZeosThread).Start;
end;

To solve the problem, I have setted the ThreadingMode to tmMainConnection and with unidac it works (it opens just a single connection), with zeosdb it open 2 connections instead of one. My questions are:

- If I use a thread it does not close the session with the thread. Is it normal?
- Is it correct that using ZeosDB it opens 2 connections instead of one?

Like I have wrote I do not know mormot framework and maybe I have made some mistakes in my code... if so, could someone tell me how to write it correctly?

Best regards
Walter Procopio

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Sessions problem.

http://synopse.info/files/html/Synopse% … #TITLE_265

and simple search in the forum
http://synopse.info/forum/search.php?se … =268064079

In your case, use of anonymous thread is IMHO a wrong idea from the beginning, if its purpose is to let the VCL be responsive.
There are much cleaner ways.
See for instance http://synopse.info/files/html/Synopse% … #TITLE_273

wprocopio

Member

Registered: 2015-07-03

Posts: 2

Re: Sessions problem.

First of all sorry for my late to reply you. Thank you very much for the answer.

I found the right parameter to set in the link about thread settings. I have some doubts about the solution.
Without the right thread settings the framework open new sessions (it is better say with default settings), but why does it not close them when the thread finished? Usually who allocate the resouces take care also to dispose them. It is just to know... I do not know the framework architecture so why the developing team decide to follow a way instaed of another.

The second doubt is why using ZeosDB the software open 2 connections instead of one, is it correct a framework works in 2 different ways doing the same operation?

Best regards
Walter

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Sessions problem.

Sessions are per thread, and there is a thread pool with http.sys so it is fine.
Re-use of the same session from the thread pool would use much less resources, and leverage performance.

I suspect the connection is opened first in the main thread, at creation, then another connection is used in the dedicated single thread.
Nothing to worry about.