Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 15 total
Thread Autoinc rollover
Thu, Apr 11 2013 10:35 AMPermanent Link

Arthur Williams

Zarksoft

Avatar

When a Autoinc field rolls over into a negative integer, is there any fallout like an exception, or does DBISAM just accept negative integers fine as index values ?
Thu, Apr 11 2013 11:25 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Arthur

>When a Autoinc field rolls over into a negative integer, is there any fallout like an exception, or does DBISAM just accept negative integers fine as index values ?

That's a two part question. To answer the second part first DBISAM is quite happy with negative integers as part of an index. No reason why it shouldn't be.

I have (eg for mailboxes to differentiate between standard and user defined ones) created a record where the autoinc is set to a negative value and DBISAM is happy with this even when its the primary index.

What I have no idea about is what happens at the rollover point.

Looking at the manual

<<Integer fields contain 32-bit, signed, integers and are stored internally as such.>>

this will apply to autoincs as well. That's an awfully big number (2,147,483,647) and I suspect that if you reach the rollover point you're going to have other problems first.

On the basis that all Tim's doing is adding 1 to an integer in Delphi its interesting.. Try this:

procedure TForm1.Button1Click(Sender: TObject);
var
xx:integer;
begin
xx:=maxint;
xx:=xx+1;
showmessage(inttostr(xx));
end;

The answer is -2147483648


Roy Lambert [Team Elevate]
Thu, Apr 11 2013 11:51 AMPermanent Link

Arthur Williams

Zarksoft

Avatar

>>Roy Lambert wrote:

>>Arthur

>>What I have no idea about is what happens at the rollover point.

I ask because this other piece of sh-t DB I have has options to either reset the autoinc to zero, some number, or just roll over. I'm assuming DBI just rolls over, but that's just a guess on my part, hence the question.

>>Looking at the manual

<<Integer fields contain 32-bit, signed, integers and are stored internally as such.>>

>>this will apply to autoincs as well. That's an awfully big number (2,147,483,647) and I suspect that if you reach >>the rollover point you're going to have other problems first.

That might be true if you started from zero. If you start at 2,147,483,000 you would encounter rollover fairly quickly.
Thu, Apr 11 2013 6:43 PMPermanent Link

Raul

Team Elevate Team Elevate

On 4/11/2013 10:35 AM, Arthur Williams wrote:
> When a Autoinc field rolls over into a negative integer, is there any fallout like an exception, or does DBISAM just accept negative integers fine as index values ?


I don't think it does - I just did a quick test on sample table (1
autoinc reset to last autoinc of 2147483640 and one text field).

When i added some records (in dbsys) then it actually rolled over to 1
after 2147483647

Raul
Thu, Apr 11 2013 6:52 PMPermanent Link

Raul

Team Elevate Team Elevate


On 4/11/2013 6:43 PM, Raul wrote:
> When i added some records (in dbsys) then it actually rolled over to 1
> after 2147483647

On further look there might be a bug in the autoinc rollover as it gets
stuck on 1 - adding more record resulted in 1 in the new autoinc columns.


here's the sql to create the table
CREATE TABLE IF NOT EXISTS "autoinctest"
(
   "MyID" AUTOINC,
   "MyText" VARCHAR(10),
PRIMARY KEY ("RecordID") COMPRESS NONE
LAST AUTOINC 2147483640
);

if you do the primary key on the MyID column then after rollover insert
fails for 2 insert (since there is already valur 1 in the field)

Using 4.35 b2

Raul
Fri, Apr 12 2013 4:16 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Arthur

>>>this will apply to autoincs as well. That's an awfully big number (2,147,483,647) and I suspect that if you reach >>the rollover point you're going to have other problems first.
>
>That might be true if you started from zero. If you start at 2,147,483,000 you would encounter rollover fairly quickly.

True, but thinking about it further, and reading Raul's post I think there is also a conceptual problem.

What you're asking for is more of a fill in the gap type of thing rather than an autoinc. There is a major potential for performance problems if autoinc wraps and tries to fill in the gaps.

Consider this scenario:

You start at 2,147,483,000 with most of the earlier numbers filled but some deleted. Lets be wicked and say there is a decent gap between 1,000,000,000 and 2,000,000,000. So you rollover and the LASTAUTOINC goes back to 1. Whichever poor sod does the next insert has a bit of a wait as the system keeps on finding the autoinc has already been taken.

There is also a conceptual problem with the fact that record 10 may be a lot later entry than record 2,147,483,000 so still useful as a unique id but you've lost the sequence information that is generally implicit with an autoinc.

If you genuinely need this, and you can't tidy up the database you need to implement some sort of free number store approach or switch to GUID or some alternative strategy.

Roy Lambert [Team Elevate]
Fri, Apr 12 2013 6:05 AMPermanent Link

Matthew Jones

I think if I had such a database, I'd manually create a replacement field for the
current autoinc, which can handle the holes and the wrap etc. I'd use another new
field for a new auto-inc. Auto_inc is handy for a quick reference for updates, but
I never use them as part of the core data. The advantage is that when you hit such
a limit, you can do a big update and "move" them all down while maintaining
integrity and not fighting the database. (Unless of course you actually have that
much data!)

/Matthew Jones/
Fri, Apr 12 2013 7:58 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Raul,

<< On further look there might be a bug in the autoinc rollover as it gets
stuck on 1 - adding more record resulted in 1 in the new autoinc columns. >>

Confirmed, and logged as an incident report (thanks for the submitted
report, BTW Smile).

A fix will be in the next 4.35 Build 3.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com
Fri, Apr 12 2013 8:23 AMPermanent Link

Arthur Williams

Zarksoft

Avatar

I'm guessing from your reply then that when the Autoinc rolls over, it's supposed to reset to 1 rather than go negative, and the bug is that it sticks at 1. Or do you mean the bug is that it doesn't go negative and and instead jumps back to 1.

>>
"Tim Young [Elevate Software]" wrote:

Raul,

<< On further look there might be a bug in the autoinc rollover as it gets
stuck on 1 - adding more record resulted in 1 in the new autoinc columns. >>

Confirmed, and logged as an incident report (thanks for the submitted
report, BTW Smile).

A fix will be in the next 4.35 Build 3.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com
Mon, Apr 15 2013 8:13 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Arthur,

<< I'm guessing from your reply then that when the Autoinc rolls over, it's
supposed to reset to 1 rather than go negative, and the bug is that it
sticks at 1. Or do you mean the bug is that it doesn't go negative and and
instead jumps back to 1. >>

The bug is that it sticks at 1 after the rollover.

Tim Young
Elevate Software
www.elevatesoft.com
Page 1 of 2Next Page »
Jump to Page:  1 2
Image