mORMot Open Source

restneophyte

Member

Registered: 2014-11-18

Posts: 26

EZSQLException - Duplicate Entry for key 'PRIMARY' -SQLite3

Using the latest version of the Synopse mORMot Framework, I have successfully created a Delphi 10.1 Berlin ReST server.

Within my 'Model,' I have the following defined:

  TMyCustomerProducts = class(TSQLRecord)
    private
      fCustID: Integer;
      fProductName: RawUTF8;
    published
      /// ORM will create the table columns
      property CustID: Integer read fCustID write fCustID;
      property ProductName: RawUTF8 index 12 read fProductName write fProductName stored AS_UNIQUE;
  end;

When my 'Client' tries to add a new 'TMyCustomerProducts' record, sometimes the call to the 'Add' function fails:

var
  iNewID: Integer;
  iClient: TSQLRestClientHTTP;
  iMyCustomerProductRecord: TMyCustomerProducts;
begin
  ...

  iNewID := iClient.Add(iDesktopRegistrationRecord, True); // when it fails: iNewID = 0

  ...
end;

I checked the 'log' file and found the following entries:

20160907 16100446  * SQL   		TSQLDBZEOSStatement(0091E640) insert into tblMyCustomerProducts (ID,CustID,ProductName) values (6,2181,'Product #1')
20160907 16100446  * EXC   		EZSQLException ("SQL Error: Duplicate entry '6' for key 'PRIMARY'") at 0061550C  stack trace API 

Table 'tblMyCustomerProducts' was created by the Server itself. It's "sqlite3" definition is as follow:

CREATE TABLE `tblMyCustomerProducts` (
	`ID` BIGINT(20) NOT NULL,
	`CustID` INT(11) NOT NULL,
	`ProductName` VARCHAR(12) NOT NULL,
	PRIMARY KEY (`ID`),
	UNIQUE INDEX `ProductName` (`ProductName`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

How is it possible (what am I doing wrong) that I am getting a duplicate entry for key 'Primary'?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,261

Website

Re: EZSQLException - Duplicate Entry for key 'PRIMARY' -SQLite3

There is already a record with the ID=6.
I guess some items are created outside the ORM scope, and conflicts with the ID internal sequence maintained within TSQLRestServer.

restneophyte

Member

Registered: 2014-11-18

Posts: 26

Re: EZSQLException - Duplicate Entry for key 'PRIMARY' -SQLite3

You are correct ID=6 already exist. However, programmatically, no one outside of the ORM scope has access to the records. Nevertheless, I did find out that a colleague had manually deleted some records. Could that explain the corruption occurring with the ID internal sequence maintained within TSQLRestServer?

Is there a way to re-initialize the ID internal sequence?

doosy

Member

Registered: 2018-09-20

Posts: 2

Re: EZSQLException - Duplicate Entry for key 'PRIMARY' -SQLite3

There is already a record with the ID=6.
I guess some items are created outside the ORM scope, and conflicts with the ID internal sequence maintained within TSQLRestServer.

Can you tell me how to solve this problem?

for balance the load, when two servers using mORMot write the same table of the same database, will there be the problem of duplicate ID keys?

thx!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,261

Website

Re: EZSQLException - Duplicate Entry for key 'PRIMARY' -SQLite3

We designed TSynUniqueIdentifier for this purpose.
Check https://synopse.info/files/html/api-1.1 … RGENERATOR
Just compute the ID before the insertion, and they will be guaranteed to be unique.

doosy

Member

Registered: 2018-09-20

Posts: 2

Re: EZSQLException - Duplicate Entry for key 'PRIMARY' -SQLite3

thx!