mORMot Open Source

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Duplicate insert when unique key causes Commands out of Sync in Zeos

Using zeos and mysql, I am having the following problem:
If an insert to a table happens to have a duplicate key with existing record,
Exception: 20230406 14135223  " EXC   EZSQLException {Message:"SQL Error: Duplicate entry '1007806' for key 'PRIMARY' \r\nCode: 1062 SQL: insert into apiorder (ID,blabla,blabla) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"} [] at 01608282 ZDbcMySql.pas TZMySQLConnection.HandleErrorOrWarning (1267)  ZDbcMySql.pas TZMySQLConnection.HandleErrorOrWarning (1267) ZDbcMySqlStatement.pas TZAbstractMySQLPreparedStatement.ExecuteUpdatePrepared (1150) mormot.db.sql.zeos.pas TSqlDBZeosStatement.ExecutePrepared (1204) mormot.orm.sql.pas TRestStorageExternal.ExecuteFromJson (2144) mormot.orm.sql.pas TRestStorageExternal.EngineAdd (1272) mormot.orm.rest.pas TRestOrm.Add (1754)

after that, in every insert zeos responds:
Exception: 20230406 14135223  " EXC   EZSQLException {Message:"SQL Error: Commands out of sync; you can't run this command now \r\nCode: 2014 SQL: insert into apiorder (ID,blabla) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"} [] at 01608282 ZDbcMySql.pas TZMySQLConnection.HandleErrorOrWarning (1267)  ZDbcMySql.pas TZMySQLConnection.HandleErrorOrWarning (1267) ZDbcMySqlStatement.pas TZAbstractMySQLPreparedStatement.FlushPendingResults (1227) ZDbcMySqlStatement.pas TZAbstractMySQLPreparedStatement.Prepare (693) ZDbcMySqlStatement.pas TZAbstractMySQLPreparedStatement.ExecuteUpdatePrepared (1123) mormot.db.sql.zeos.pas TSqlDBZeosStatement.ExecutePrepared (1204) mormot.orm.sql.pas TRestStorageExternal.ExecuteFromJson (2144) mormot.orm.sql.pas TRestStorageExternal.EngineAdd (1272) mormot.orm.rest.pas TRestOrm.Add (1754)

How to overcome this problem?
Sometimes, even memberexists does not prevent it, even with writelock.
Thank you in advance

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Duplicate insert when unique key causes Commands out of Sync in Zeos

Sounds like a DB cache / connnection consistency problem...

If a WriteLock does not help, perhaps keeping a list in memory may help, instead of relying on the DB.
Another pattern, often used in such context, is to pre-allocate the IDs, then rely on this list in pascal code to ensure they are unique.

How do you generate the ID?
There are several ways of generating them in the ORM. The local-side incremental ID is thread-safe, and should not generate such issues.
Or since MySQL does not support a sequence, relying on AUTO_INCREMENT may help.

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: Duplicate insert when unique key causes Commands out of Sync in Zeos

The ID is copied from an other api service. The problem is not only produced by the main ID, it can be produced from other unique keys also.
Usually it does not happen. The problem is that if it happens the only way is to restart all the service.
Using firedac I havent managed to log such errors but firedac is unusable with rawjson and it was not possible to test it in the same way.

ps I will try to check it with firedac.

Last edited by dcoun (2023-04-06 17:19:40)