#1 2022-06-20 07:02:37

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

TRestBatch does not use Replace sql commands?

I am trying to have a TRestBatch to enter 10000 records in a table at server side. Connection is with Zeos 8.0-patches (from GitHub) to Mariadb using Delphi 11.1/win11

ba:=TRestBatch.Createnorest(DBsrvPool.GetDBsrv.Model, Tormapiobjs, 3000, [boInsertOrReplace,boExtendedJson]);
for n:=0 to 10000 do ba.Add(dr.objs[n],True,true);
BatchSend(DBsrvPool.GetDBsrv.Orm);

If a record with the same primary id exists I am getting an error in ZDBcMySql, line 1263:
First chance exception at $7504F192. Exception class EZSQLException with message
'SQL Error: Duplicate entry '24' for key 'PRIMARY'
Code: 1062 SQL: insert into apiobjs (ID,ecode,bcode,galid,cnameonly) values (?,?,?,?,?)'.
Process mormottesting.exe (21148)

I expected a replace instead of insert, Mariadb & mysql support REPLACE.
Should I configure something and I did not?

Thank you in advance

Offline

#2 2022-06-20 13:39:56

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: TRestBatch does not use Replace sql commands?

EncodeAsSqlPrepared() should indeed use "insert or replace into" SQL statement.

It was currently only supported for SQlite3, not for external databases.

Please try https://github.com/synopse/mORMot2/commit/1ab7f9ba
and https://github.com/synopse/mORMot2/commit/677e26a6

Offline

#3 2022-06-20 20:40:18

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

It is not solved
mormot.orm.sql, line 1071 Why the BatchOptions argument is empty?

                 SQL := JsonDecodedPrepareToSql(Decode, ExternalFields, Types,
                    Occasion, [], {array=}true);

That will result in line 2129 to have again Batchoptions empty

 result := EncodeAsSqlPrepared(Decoder, fTableName, Occasion,
    fStoredClassMapping^.RowIDFieldName, BatchOptions);

and EncodeInsertPrefix will not add a replace into

Thank a lot for your work

Offline

#4 2022-06-21 07:21:24

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: TRestBatch does not use Replace sql commands?

You are right.

Perhaps https://github.com/synopse/mORMot2/commit/8494c5fb would help.

Offline

#5 2022-06-21 07:38:50

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

Now it works. Thank you a lot @ab

Offline

#6 2022-06-27 16:31:50

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

An other bug I noticed using BatchSend is the following:
if EngineAddUseSelectMaxID for a table is enabled then in every record/orm item a select max is issued resulting to the same RowID for all new items in the batch

Offline

#7 2022-06-27 20:43:16

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: TRestBatch does not use Replace sql commands?

In which context?

Offline

#8 2022-06-28 05:24:24

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

it happens using the above code, but with

ba.Add(dr.objs[n],True,false)

I am setting EngineAddUseSelectMaxID  with the following:

((DBsrv.Orm as TRestOrmServer).StaticTable[Tormapiobjs] as TRestStorageExternal).EngineAddUseSelectMaxID:=true;

where DBsrv is a TRestServerDB

Offline

#9 2022-06-28 06:55:38

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: TRestBatch does not use Replace sql commands?

You set forceID=true as first parameter, which bypass the generator and send the value of ba.ID.

Offline

#10 2022-06-28 06:58:17

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

I am using the following from mormot.orm.core line 4447:

   function Add(Value: TOrm; SendData: boolean; ForceID: boolean = false;
      const CustomFields: TFieldBits = []; DoNotAutoComputeFields: boolean = false): integer;

SendData=true ForceID=false

Offline

#11 2022-06-28 07:26:02

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: TRestBatch does not use Replace sql commands?

Normally, TRestStorageExternal.EngineAdd should call EngineLockedNextID.

Could you try to debug a little and find out why not?

Offline

#12 2022-06-28 19:18:45

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

Difficult day today
TRestStorageExternal.EngineAdd does not find an ID so it requests a new from EngineLockedNextID
The problem is that in line 970 from mormot.orm.sql, if EngineAddUseSelectMaxID=true, it is always requested from DB, even fEngineLockedMaxID>0 and even if it is a batch of records for the same table

 if (fEngineLockedMaxID = 0) or
     EngineAddUseSelectMaxID then
    RetrieveFromDB;
  inc(fEngineLockedMaxID);

As the previous record was not yet inserted in the database the fEngineLockedMaxID is getting again again the same value

Last edited by dcoun (2022-06-28 19:21:20)

Offline

#13 2022-06-28 19:44:12

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: TRestBatch does not use Replace sql commands?

I don't see how this problem may be resolved from the framework itself.
EngineAddUseSelectMaxID is clearly not compatible with a Batch with no ID.

What you can do is to insert the IDs as part of the Batch definitions by setting ForceID with a pre-computed ID from the DB.

Offline

#14 2022-06-28 19:44:18

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

Thank you @ab, no problem for that.

An other question I have is the following:
Lets use a scenario like that in https://synopse.info/forum/viewtopic.php?id=5788
multiple TREstServerDB like in TRestServerDBObjArray = array of TRestServerDB;
each client calls an interface and the interface is getting randomly one TREstServerDB instance to communicate.
if EngineAddUseSelectMaxID=false, each TRestServerDB will have its own fEngineLockedMaxID
when inserting new records using a randomly selected TRestServerDB instance, how the fEngineLockedMaxID is shared between them, without a select max(ID) query?

Last edited by dcoun (2022-06-28 19:45:08)

Offline

#15 2022-06-28 19:46:47

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: TRestBatch does not use Replace sql commands?

It was never designed to have several TRestServerDB on the same DB.
You need to use a single TRestServerDB per DB and the internal sequences, which are tied to a TRestServer instance.

What you could still do is to use e.g. a TSynUniqueIdentifier generator and pre-fill the ID.
Or use a centralized single service with a single TRestServerDB and its single ID sequence. This last option is IMHO the preferred.

Offline

#16 2022-06-28 19:47:42

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

Thank you a lot @ab
I was thinking that in an array only TRestServerDBObjArray[0] will process the inserts and above [0] will do the read process

Last edited by dcoun (2022-06-28 19:50:04)

Offline

#17 2022-08-11 06:07:20

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

With the latest commits boInsertOrReplace does not work again
The following code gives again only "insert' in the sql query

ba:=TRestBatch.Createnorest(srv.model,TOrmApiitem,100,[boInsertOrReplace,boExtendedJson]); 
item:=TOrmApiitem.Create; 
.....
item.IDValue:=insu[n].id;
ba.Add(item,True,true); 
srv.Orm.BatchSend(ba);
20220811 06034303  ! ERROR 	mormot.orm.server.TRestOrmServerBatchSend(0c8b82d0) EngineBatchSend json=138 B count=1 errors=1 post=1 simple=0 hex=0 hexid=0 put=0 delete=0 8.03s 0/s 
20220811 06034262  ! EXC   	EZSQLException {Message:"SQL Error: Duplicate entry '28415238' for key 'PRIMARY' \r\nCode: 1062 SQL: insert into apiitem (ID,pid,tam,melos,amman,ama,emaes,lastactive,rered,updDate,validstart,validend,act) values (?,?,?,?,?,?,?,?,?,?,?,?,?)"} [Main] at f8a3c5  
20220811 06033502  ! DB    	mormot.db.sql.zeos.TSqlDBZeosStatement(0c628280) Prepare 318us insert into apiitem (ID,pid,tam,melos,amman,ama,emaes,lastactive,rered,updDate,validstart,validend,act) values (?,?,?,?,?,?,?,?,?,?,?,?,?)
20220811 06033500  !  +    mormot.orm.server.TRestOrmServerBatchSend(0c8b82d0).EngineBatchSend TOrmApiitem inlen=138
20220811 06033461  ! trace mormot.rest.sqlite3.TRestServerDB(063d7bc0) BatchSend {"TRestBatch(0c718938)":{Table:20346408,Count:1,SizeBytes:137}}

Offline

#18 2022-08-11 06:34:50

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: TRestBatch does not use Replace sql commands?

It does not feel like a regression to me. But a good plain bug. hmm
My guess is that it never worked with a single item in the batch.

Please check https://github.com/synopse/mORMot2/commit/bfc8e3ef

Offline

#19 2022-08-11 07:30:02

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

It is an update function and it can have zero to 500 items....
Thank you a lot @ab!!!

Offline

#20 2022-08-11 07:49:59

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

I can confirm that for 2 or more items it works, but still not for one. I am using the commit 2.0.3913.
Just use the above code with any kind of Ormitem

Last edited by dcoun (2022-08-11 07:50:30)

Offline

#21 2022-08-12 09:07:27

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: TRestBatch does not use Replace sql commands?

Offline

#22 2022-08-12 09:20:37

dcoun
Member
From: Crete, Greece
Registered: 2020-02-18
Posts: 392

Re: TRestBatch does not use Replace sql commands?

Now It works. Thanks a lot @ab

Offline

Board footer

Powered by FluxBB