#1 2016-09-07 20:53:12

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'?

Offline

#2 2016-09-08 08:32:17

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,713
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.

Offline

#3 2016-09-08 18:25:42

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?

Offline

#4 2020-03-12 01:55:45

doosy
Member
Registered: 2018-09-20
Posts: 2

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

ab wrote:

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!

Offline

#5 2020-03-12 08:09:23

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,713
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.

Offline

#6 2020-03-13 06:33:34

doosy
Member
Registered: 2018-09-20
Posts: 2

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

thx!

Offline

Board footer

Powered by FluxBB