Hi Mark,
Below is details of database properties
Database header page information:
Version: WI-V2.5.4.26856 Firebird 2.5
Flags 0
Checksum 12345
Generation 941170
Page size 4096
ODS version 11.2
Oldest transaction 941159
Oldest active 941160
Oldest snapshot 941160
Next transaction 941161
Bumped transaction 1
Sequence number 0
Next attachment ID 56
Implementation ID 26
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Attributes force write
Variable header data:
Sweep interval: 20000
Allocated pages: 603479
*END*
On the affected table that is transaction table(‘trnTable’), I am using
triggers to generate ID’s. Below I am pasting queries for generating the unique
IDs
Trigger that calls generator is fired on insertion:
CREATE TRIGGER 'BI_ID' FOR ‘trnTable’
ACTIVE BEFORE
INSERT
POSITION 0
AS
BEGIN
IF (NEW._ID IS NULL) THEN
NEW._ID = GEN_ID(_ID_GEN, 1);
END
Generator:
CREATE GENERATOR _ID_GEN;
SET GENERATOR _ID_GEN TO 16594028
Thank you.
With Regards,
Joje T. George
From: firebird-support@yahoogroups.com
[mailto:firebird-supp...@yahoogroups..com]
Sent: 02 April 2018 09:27 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Auto-increment generating old ID's for insertion
On 2-4-2018 14:11, 'Joje' j...@codework-solutions.com
<mailto:j...@codework-solutions.com> [firebird-support]
wrote:
> I have issue where auto-increment of ID column is generating older ID’s
> that is already used in table.
>
> Currently, my transaction table has around 16 million rows and *ID of
> last inserted row is 16,594,027*. While the *generator ID its
> 16,593,965*. As you can see the generator is generating ID’s that is
> already present in table which causes *violation of PRIMARY or UNIQUE
> KEY constraint "INTEG_105"*error on that table.
>
> I managed to fix this issue, recently I am facing it frequently on this
> transaction table, majority of the tables had number of recor ds are
> more than 2 million or there is connectivity issues with DB.
>
> Now, I am wondering is this related to Firebird DB or is there any other
> issue.
Which version of Firebird are you using (full version please)?
Do you have code that generates its own IDs? Do you have code that tries
to manipulate the value using `GEN_ID(<seq_name>, -1)` (or another
negative value), `ALTER SEQUENCE seq_name RESTART WITH new_val` or `SET
GENERATOR seq_name TO new_val`? Do you allow your code to specify its
own IDs instead of using the sequence?
It could be a bug in Firebird, but we would really need to see a
reproducible case.
Mark
--
Mark Rotteveel
