parameter in the beginning of sql query and exception: zeos or mormot?

dcoun · 2022-10-12 14:51:31

Running the following

DBsrvPool.GetDBsrv.Orm.OneFieldValue(TOrmapimans,'RowID','am=:('+inttostr(am)+'):')

Sometimes, I get sometimes the following error:

Exception: 20221011 18040212  ) EXC   EZSQLException {Message:"
SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version 
for the right syntax to use near '13100000113select ID from apimans where am=? limit 1' at line 1 \r\nCode: 1064 
SQL: select ID from apimans where am=? limit 1"} [] at 01ccb696 
ZDbcMySql.pas TZMySQLConnection.HandleErrorOrWarning (1271)  
ZDbcMySql.pas TZMySQLConnection.HandleErrorOrWarning (1271) 
ZDbcMySqlStatement.pas ExecuteEmulated (1039) 
ZDbcMySqlStatement.pas TZAbstractMySQLPreparedStatement.ExecuteQueryPrepared (1051) 
mormot.db.sql.zeos.pas TSqlDBZeosStatement.ExecutePrepared (1179) 
mormot.db.sql.pas TSqlDBStatement.ExecutePreparedAndFetchAllAsJson (6664) 
mormot.orm.sql.pas TRestStorageExternal.EngineList (1380) 
mormot.orm.server.pas TRestOrmServer.EngineList (1073) 
mormot.orm.rest.pas TRestOrm.ExecuteList (1675) 
mormot.orm.rest.pas TRestOrm.MultiFieldValue (976)

As you can notice the parameter is in the beginning of the sql query. Is it Mormot's fault or Zeos's?

I am using Mormot2, Delphi 11.2 and Zeoslib 8.0-patches from github

Last edited by dcoun (2022-10-12 14:56:31)

ab · 2022-10-12 16:36:29

The log states SQL: select ID from apimans where am=? limit 1" in the 3rd line, which seems correct.

Try
1. to add some logs to see what is actually executed.
2. to add some critical section lock/unlock around the ORM call to see if there is a multi-thread problem.
3. to ask also from help on the Zeos support forum.

dcoun · 2022-10-12 16:44:27

ab wrote:

The log states SQL: select ID from apimans where am=? limit 1" in the 3rd line, which seems correct.
Try
1. to add some logs to see what is actually executed.
2. to add some critical section lock/unlock around the ORM call to see if there is a multi-thread problem.
3. to ask also from help on the Zeos support forum.

can you help me a bit about the 2?
It runs in threads. What is the orm calls what should be protected more with a critical section? Should I use the orm.Writelock/orm.writeunlock for each Insert/update/delete sql command?
Thank you in advace

ab · 2022-10-12 17:22:45

Try to reproduce the problem with a minimal example.
For instance, run DBsrvPool.GetDBsrv.Orm.OneFieldValue in a loop, in one or several threads.

Then if you are able to reproduce the problem not with a single thread, but several, use Orm.WriteLock/WriteUnLock around the OneFieldValue() call.

dcoun · 2022-10-22 09:31:09

Finally Orm.WriteLock/WriteUnLock when used in other parts of my program when multiple threads call add in the same table probably the same record (same IDValue). I also defined the ZEOS_DISABLE_POOLED directive in zeoslib
Just wondering why the following lock that exists in Orm.InternalAdd does not help and I have to use Orm.WriteLock/WriteUnLock

  fRest.AcquireExecution[execOrmWrite].Safe.Lock;
  try
    // may be within a batch in another thread -> use execOrmWrite lock
    result := EngineAdd(t, json); // will call static if necessary
  finally
    fRest.AcquireExecution[execOrmWrite].Safe.UnLock;
  end;

I am using Mariadb with

AcquireExecutionMode[execOrmGet]:=amBackgroundThread;
AcquireExecutionMode[execOrmWrite]:=amBackgroundThread;

ab · 2022-10-22 10:50:46

AcquireExecutionMode[] is for remote REST access, not direct IRestOrm access.

OneFieldValue() does not use the WriteLock.

dcoun · 2022-10-22 16:40:08

ab wrote:

AcquireExecutionMode[] is for remote REST access, not direct IRestOrm access.

Ok I will remove it. I noticed that it is needed if TSQLDBZEOSConnectionProperties.ConnectionTimeOutMinutes is set.

// - warning: no connection shall still be used on the background (e.g. in
// multi-threaded applications), or some unexpected issues may occur - for
// instance, ensure that your mORMot ORM server runs all its statements in
// blocking mode for both read and write:
// ! aServer.AcquireExecutionMode[execOrmGet] := am***;
// ! aServer.AcquireExecutionMode[execOrmWrite] := am***;
// here, safe blocking am*** modes are any mode but amUnlocked, i.e. either
// amLocked, amBackgroundThread or amMainThread
property ConnectionTimeOutMinutes: cardinal
read GetConnectionTimeOutMinutes write SetConnectionTimeOutMinutes;

ab wrote:

OneFieldValue() does not use the WriteLock.

Sure, I have not this error the last two days since I put a WriteLock in an other place of my program where multiple threads could try at the same time to add in an other table the same row info.
Looking in orm.add I noticed that it has a lock, why a WriteLock for an orm.add stopped this error?

Last edited by dcoun (2022-10-22 16:43:52)

dcoun · 2022-10-24 06:30:48

Problem found again today. From logs it happens after mormot2 enters zeos' pas units, after exceptions to other sql queries and probably due to the sqlwriter that creates the query for zeos.
Reported to zeos forum and waiting approval. To be honest, I feel uncertain using zeos for the future.
I transformed most insert queries to Trestbatch where insert ignore is available. Anyway time penalty with memberexists is the same.

ab · 2022-10-24 16:37:22

From TSqlDBStatement.ExecutePreparedAndFetchAllAsJson ?

dcoun · 2022-10-24 16:46:53

ab wrote:

From TSqlDBStatement.ExecutePreparedAndFetchAllAsJson ?

In TSqlDBZeosStatement.ExecutePrepared from mormot.db.sql.zeos the fStatement.GetSQL returns the correct query:

select pid as ID from table where am=? limit 1

Last edited by dcoun (2022-10-24 16:57:40)

dcoun · 2023-04-06 15:18:17

I have not found a solution to that problem and even trying different approaches it still seldom happens and it is very difficult to reproduce it.
From logging I notice that before this problem starts there are log entries with access violation like the following:
Exception OS: 20230406 13312317 & EXCOS EAccessViolation (c0000005) [Main] at 70f7b162 ZDbcMySqlStatement.pas TZAbstractMySQLPreparedStatement.InternalRealPrepare (1441) ZDbcMySqlStatement.pas TZAbstractMySQLPreparedStatement.Prepare (697) ZDbcMySqlStatement.pas TZAbstractMySQLPreparedStatement.ExecuteQueryPrepared (1048) mormot.db.sql.zeos.pas TSqlDBZeosStatement.ExecutePrepared (1179) mormot.db.sql.pas TSqlDBStatement.ExecutePreparedAndFetchAllAsJson (6682) mormot.orm.sql.pas TRestStorageExternal.EngineList (1403) mormot.orm.server.pas TRestOrmServer.EngineList (1116) mormot.orm.rest.pas TRestOrm.ExecuteJson (1716) mormot.orm.rest.pas TRestOrm.ExecuteList (1699) mormot.orm.rest.pas TRestOrm.MultiFieldValue (983) mormot.orm.rest.pas TRestOrm.OneFieldValue (891)
Looking in zeos's ZDbcMySqlStatement.pas the line 1441 is the last line the following:

procedure TZAbstractMySQLPreparedStatement.InternalRealPrepare;
var I: Integer;
begin
  if (FMYSQL_STMT = nil) then
    FMYSQL_STMT := FPlainDriver.mysql_stmt_init(FPMYSQL^);

Can FPMYSQL, which is the database connection, be invalid after a pool refreshing or a timeout or something like that?

PS this an old thread that should be moved to mormot2

Last edited by dcoun (2023-04-06 15:22:31)

ab · 2023-04-06 16:42:51

Did you try to ask it on the Zeos forum?

Since the AV is triggered in the ZDBC source code, they may help more.

mORMot Open Source

#1 2022-10-12 14:51:31

parameter in the beginning of sql query and exception: zeos or mormot?

#2 2022-10-12 16:36:29

Re: parameter in the beginning of sql query and exception: zeos or mormot?

#3 2022-10-12 16:44:27

Re: parameter in the beginning of sql query and exception: zeos or mormot?

#4 2022-10-12 17:22:45

Re: parameter in the beginning of sql query and exception: zeos or mormot?

#5 2022-10-22 09:31:09

Re: parameter in the beginning of sql query and exception: zeos or mormot?

#6 2022-10-22 10:50:46

Re: parameter in the beginning of sql query and exception: zeos or mormot?

#7 2022-10-22 16:40:08

Re: parameter in the beginning of sql query and exception: zeos or mormot?

#8 2022-10-24 06:30:48

Re: parameter in the beginning of sql query and exception: zeos or mormot?

#9 2022-10-24 16:37:22

Re: parameter in the beginning of sql query and exception: zeos or mormot?

#10 2022-10-24 16:46:53

Re: parameter in the beginning of sql query and exception: zeos or mormot?

#11 2023-04-06 15:18:17

Re: parameter in the beginning of sql query and exception: zeos or mormot?

#12 2023-04-06 16:42:51

Re: parameter in the beginning of sql query and exception: zeos or mormot?

Board footer