AV Error inside Method-based services using transactions...

Luzcka · 2013-11-06 17:16:10

Hi!!!!!

In the past I asked about transactions inside Method-based services (http://synopse.info/forum/viewtopic.php?id=1427)
and got the following code:

function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; const Params: array of const; aExpanded: Boolean = True): RawJSON;
var
  Res: ISQLDBRows;
  Conn: TSQLDBConnection;
begin
  if fProps=nil then
    raise Exception.Create('Objeto database não associado.');
  Conn := fProps.ThreadSafeConnection;
  Conn.Connect;
  try
    Conn.StartTransaction;
    try
      res := fProps.Execute(aSQL, Params);
      if res=nil then
        result := ''
      else
        result := res.FetchAllAsJSON(aExpanded);
      Conn.Commit;
    except
      Conn.Rollback;
    end;
  finally
    Conn.Disconnect;
  end;
end;

This code works on my computer, but when deploy on a basic amazon server it causes an AV error... this error is intermittent.

{
"ErrorCode":500,
"ErrorText":"Exception EAccessViolation: Access violation at address 0040C45C in module 'ServidorTeste.exe'. Read of address 00200055"
}

I created a test application to try to reproduce the error, and this actually happened due to the Path structure
(containing spaces and other characters). On the server I created a small folder structure with no special characters,
but the problem persists.

It occurs after the final 'end;' but is caused by 'conn.Disconnect;' as if this line is extracted (or all with 'conn')
the error no longer exists.

On my local computer the error happens in the following function (remember, in my experiment is because the path):

function TSQLPropInfoRTTICurrency.CompareValue(Item1,Item2: TObject; CaseInsensitive: boolean): PtrInt;

Any idea is welcome...

Thanks in advance.

ab · 2013-11-06 19:09:37

Enable logging to provide the stack trace (with source code line numbers) of the exception.
(you need to provide the .map file together with the .exe)

Which external DB backend are your using?
Could you try with a SQLite3 DB first?

Ensure you have the latest "unstable" 1.18 revision of the framework source.

Luzcka · 2013-11-07 16:04:21

Hi Arnaud!

I'm using the latest version (downloaded today Leaf: [baf1521dc9]), changed the TSQLLog.Family to use Level: = [sllException, sllExceptionOS, sllMemory, sllStackTrace]; and generated the .MAP file. How can I send this data to you? I tried to email via the forum, but allows only 64KB of text.

I'm using Firebird-Zeos.

Tkz.

Last edited by Luzcka (2013-11-07 16:05:37)

Luzcka · 2013-11-08 10:47:11

Hi Arnaud!

Yesterday I sent to you by email the data connection info for FTP, there can access the necessary files: Log, Map, Exe and etc.

Thanks

ab · 2013-11-08 10:57:29

I was not able to connect... due to our corporate proxy policy...

I will try this weekend from home.

Or you can use email "webcontact01 at synopse dot info" as alternative.

Luzcka · 2013-11-08 14:07:44

Hi Arnaud!

I just send the files to the webcontact01 email.

Thanks in advance!

mpv · 2013-11-09 21:38:24

Luzcka, in your code you use Conn.Connect; / Conn.Disconnect for every call. Establish connection is hard process for DB server. You do non need disconnect every time.
IMHO this code is much better:

function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; const Params: array of const; aExpanded: Boolean = True): RawJSON;
var
  Res: ISQLDBRows;
  Conn: TSQLDBConnection;
begin
  if fProps=nil then
    raise Exception.Create('Objeto database não associado.');
  Conn := fProps.ThreadSafeConnection;
  if not Conn.IsConnected then 
    Conn.Connect;

    Conn.StartTransaction;
    try
      res := fProps.Execute(aSQL, Params);
      if res=nil then
        result := ''
      else
        result := res.FetchAllAsJSON(aExpanded);
      Conn.Commit;
    except
      Conn.Rollback;
    end;
end;

fProps internally hold connection pool.
This is not AV reason, just for better performance...

ab · 2013-11-09 22:35:06

I'm not sure if it is in the Zeos part of the mORMot part...
Do you use the latest Zeos 7.x version (we are using the one from SVN)?

Your code sounds incorrect to me.
Your are creating the connection and releasing it BEFORE your Res: ISQLDBRows variable is released.

Remember that when you use an interface variable, there is an hidden try...finally block generated by the compiler (see the SAD pdf about it).
In reality, your code is executed as such:

function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
var
  Res: ISQLDBRows;
  Conn: TSQLDBConnection;
begin

  Res := nil;            // ADDED BY COMPILER
  try                 // ADDED BY COMPILER

  if fProps=nil then
    raise Exception.Create('Objeto database não associado.');
  conn := fProps.ThreadSafeConnection;
  Conn.Connect;
  try
    Conn.StartTransaction;
    try
      res := fProps.ExecuteInlined(aSQL, aExpectResults);
      if res=nil then
        result := ''
      else
        result := res.FetchAllAsJSON(aExpanded);
      conn.Commit;
    except
      conn.Rollback;
    end;
  finally
    conn.Disconnect;
  end;

  finally         // ADDED BY COMPILER
     Res := nil;   // ADDED BY COMPILER
   end;         // ADDED BY COMPILER
end;

So your ISQLDBRows instance will be freed AFTER its connection - I suspect this is what is triggerring your AV...

You NEED to manually release the Res variable:

....
    try
      res := fProps.ExecuteInlined(aSQL, aExpectResults);
      if res=nil then
        result := ''
      else
      try
        result := res.FetchAllAsJSON(aExpanded);
      finally
         res := nil; // RELEASE the ISQLDBRows variable
      end;
....

See sample "16 - Execute SQL via services" as reference.

And do not create a transaction for one-line SQL statement - it does not make sense.
Our classes should be on auto-commit mode by default.

mORMot Open Source

#1 2013-11-06 17:16:10

AV Error inside Method-based services using transactions...

#2 2013-11-06 19:09:37

Re: AV Error inside Method-based services using transactions...

#3 2013-11-07 16:04:21

Re: AV Error inside Method-based services using transactions...

#4 2013-11-08 10:47:11

Re: AV Error inside Method-based services using transactions...

#5 2013-11-08 10:57:29

Re: AV Error inside Method-based services using transactions...

#6 2013-11-08 14:07:44

Re: AV Error inside Method-based services using transactions...

#7 2013-11-09 21:38:24

Re: AV Error inside Method-based services using transactions...

#8 2013-11-09 22:35:06

Re: AV Error inside Method-based services using transactions...

Board footer