Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 15 total |
Autoinc rollover |
Thu, Apr 11 2013 10:35 AM | Permanent Link |
Arthur Williams Zarksoft | 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent Link |
Arthur Williams Zarksoft | >>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 PM | Permanent Link |
Raul 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 PM | Permanent Link |
Raul 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 ). 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 AM | Permanent Link |
Arthur Williams Zarksoft | 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 ). 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |