TRestBatch does not use Replace sql commands?

dcoun · 2022-06-20 07:02:37

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

ab · 2022-06-20 13:39:56

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

dcoun · 2022-06-20 20:40:18

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

ab · 2022-06-21 07:21:24

You are right.

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

dcoun · 2022-06-21 07:38:50

Now it works. Thank you a lot @ab

dcoun · 2022-06-27 16:31:50

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

ab · 2022-06-27 20:43:16

In which context?

dcoun · 2022-06-28 05:24:24

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

ab · 2022-06-28 06:55:38

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

dcoun · 2022-06-28 06:58:17

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

ab · 2022-06-28 07:26:02

Normally, TRestStorageExternal.EngineAdd should call EngineLockedNextID.

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

dcoun · 2022-06-28 19:18:45

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)

ab · 2022-06-28 19:44:12

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.

dcoun · 2022-06-28 19:44:18

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)

ab · 2022-06-28 19:46:47

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.

dcoun · 2022-06-28 19:47:42

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)

dcoun · 2022-08-11 06:07:20

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}}

ab · 2022-08-11 06:34:50

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

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

dcoun · 2022-08-11 07:30:02

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

dcoun · 2022-08-11 07:49:59

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)

ab · 2022-08-12 09:07:27

Please try with https://github.com/synopse/mORMot2/commit/3a9500a8

dcoun · 2022-08-12 09:20:37

Now It works. Thanks a lot @ab

mORMot Open Source

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

TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

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

Re: TRestBatch does not use Replace sql commands?

Board footer