Board index » delphi » SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS

SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS

Hi,

I'm experiencing a serious problem with auto-increment fields.  I've
used auto-increment fields for many of the tables in the database.
Problem is that I'm now getting a "Key Violation" error - which I know
is caused by a corrupted auto-increment key value - it must be resetting
the auto-increment counter or something.  The only way I can correct
this is to delete the whole table and then to recreate it with the data
- which is a major undertaking (in fact I'd go so far as to say that
this is unreasonable).  The database is being accessed over the network
and all systems are configured to use: \\server\db as the net directory
and the database directory.  I have also set LOCAL SHARE to true on all
machines.  I've installed BDE 4.51.  Is there anything else I can do?
I'm considering rewriting the application to use a SQL server database
at the moment.  But I don't want to have to do this.

The system is running with 3 client PCs at the moment but the update
interval is very frequent.

Please help!!!

Cheers,
Kevin.

Please use kevi...@usa.net as reply address as well as posting to the
newsgroup.

 

Re:SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS


First, before you recreate the entire table, try deleting the last couple of
records, reindexing the table ( we use TUtility from Out and About ), then
adding the records back in.  We've found that the counter is usually only
short by a few records.

Second, restructure the database to not use AutoInc fields.  They have been
a constant source of headaches for us.  Generate the incrementing integer
yourself from a key table, and you'll be much happier.

If you do move to an SQL Server implementation, I suggest that you still
avoid the "identity" fields in your tables, as I've heard of similar
problems with them.

Hope this helps.

Quote
Kevin Berry wrote in message <34DF8441.A86D6...@webhost.co.za>...
>Hi,

>I'm experiencing a serious problem with auto-increment fields.  I've
>used auto-increment fields for many of the tables in the database.
>Problem is that I'm now getting a "Key Violation" error - which I know
>is caused by a corrupted auto-increment key value - it must be resetting
>the auto-increment counter or something.  The only way I can correct
>this is to delete the whole table and then to recreate it with the data
>- which is a major undertaking (in fact I'd go so far as to say that
>this is unreasonable).  The database is being accessed over the network
>and all systems are configured to use: \\server\db as the net directory
>and the database directory.  I have also set LOCAL SHARE to true on all
>machines.  I've installed BDE 4.51.  Is there anything else I can do?
>I'm considering rewriting the application to use a SQL server database
>at the moment.  But I don't want to have to do this.

>The system is running with 3 client PCs at the moment but the update
>interval is very frequent.

Re:SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS


I believe you can cure this by restructuring the table. Change the AutoInc
to an Integer and save it, then change it back to an AutoInc. That should
reset the counter properly.
--
Tim Borman
tbor...@nospam.prophase.com
please reply to this newsgroup
-----------------------------------------------------

Quote
Kevin Berry wrote in message <34DF8441.A86D6...@webhost.co.za>...
>Hi,

>I'm experiencing a serious problem with auto-increment fields.  I've
>used auto-increment fields for many of the tables in the database.
>Problem is that I'm now getting a "Key Violation" error - which I know
>is caused by a corrupted auto-increment key value - it must be resetting
>the auto-increment counter or something.  The only way I can correct
>this is to delete the whole table and then to recreate it with the data
>- which is a major undertaking (in fact I'd go so far as to say that
>this is unreasonable).  The database is being accessed over the network
>and all systems are configured to use: \\server\db as the net directory
>and the database directory.  I have also set LOCAL SHARE to true on all
>machines.  I've installed BDE 4.51.  Is there anything else I can do?
>I'm considering rewriting the application to use a SQL server database
>at the moment.  But I don't want to have to do this.

>The system is running with 3 client PCs at the moment but the update
>interval is very frequent.

>Please help!!!

>Cheers,
>Kevin.

>Please use kevi...@usa.net as reply address as well as posting to the
>newsgroup.

Re:SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS


Hi Tim,
You're perfectly correct.
I have met the very same problem before and cured it by what you suggest.
Q: Is there any way to know that the counter is damaged without actually
performing
    insert and getting KeyViol error? The tutility reports the table is OK,
which is wrong.
--
Roman
KRE...@mbox.cesnet.cz
(please remove STOPSPAM. in header]

Quote
Tim Borman wrote in message <6bnp20$d...@forums.borland.com>...
>I believe you can cure this by restructuring the table. Change the AutoInc
>to an Integer and save it, then change it back to an AutoInc. That should
>reset the counter properly.
>--
>Tim Borman
>tbor...@nospam.prophase.com
>please reply to this newsgroup
>-----------------------------------------------------

Re:SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS


This is what I've found too - TUTILITY says everything is just fantastic!!!!
:-(

Quote
Roman Krejci wrote in message <6bnsf3$d...@forums.borland.com>...
>Hi Tim,
>You're perfectly correct.
>I have met the very same problem before and cured it by what you suggest.
>Q: Is there any way to know that the counter is damaged without actually
>performing
>    insert and getting KeyViol error? The tutility reports the table is OK,
>which is wrong.
>--
>Roman
>KRE...@mbox.cesnet.cz
>(please remove STOPSPAM. in header]

>Tim Borman wrote in message <6bnp20$d...@forums.borland.com>...
>>I believe you can cure this by restructuring the table. Change the AutoInc
>>to an Integer and save it, then change it back to an AutoInc. That should
>>reset the counter properly.
>>--
>>Tim Borman
>>tbor...@nospam.prophase.com
>>please reply to this newsgroup
>>-----------------------------------------------------

Re:SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS


Why isn't there an actual solution to this?  What causes it and why haven't
Borland done something about this.  What good are auto-increment fields if
you can use them as keys???

My problem is that the tables are becoming corrupted daily and it isn't
feasible to have to try and fix them every day.  The users have said they
are not switching off their machines either, so that can't be causing it.

Quote
Tim Borman wrote in message <6bnp20$d...@forums.borland.com>...
>I believe you can cure this by restructuring the table. Change the AutoInc
>to an Integer and save it, then change it back to an AutoInc. That should
>reset the counter properly.
>--
>Tim Borman
>tbor...@nospam.prophase.com
>please reply to this newsgroup
>-----------------------------------------------------
>Kevin Berry wrote in message <34DF8441.A86D6...@webhost.co.za>...
>>Hi,

>>I'm experiencing a serious problem with auto-increment fields.  I've
>>used auto-increment fields for many of the tables in the database.
>>Problem is that I'm now getting a "Key Violation" error - which I know
>>is caused by a corrupted auto-increment key value - it must be resetting
>>the auto-increment counter or something.  The only way I can correct
>>this is to delete the whole table and then to recreate it with the data
>>- which is a major undertaking (in fact I'd go so far as to say that
>>this is unreasonable).  The database is being accessed over the network
>>and all systems are configured to use: \\server\db as the net directory
>>and the database directory.  I have also set LOCAL SHARE to true on all
>>machines.  I've installed BDE 4.51.  Is there anything else I can do?
>>I'm considering rewriting the application to use a SQL server database
>>at the moment.  But I don't want to have to do this.

>>The system is running with 3 client PCs at the moment but the update
>>interval is very frequent.

>>Please help!!!

>>Cheers,
>>Kevin.

>>Please use kevi...@usa.net as reply address as well as posting to the
>>newsgroup.

Re:SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS


Quote
Kevin wrote:

> Why isn't there an actual solution to this?  What causes it and why haven't
> Borland done something about this.  What good are auto-increment fields if
> you can use them as keys???

> My problem is that the tables are becoming corrupted daily and it isn't
> feasible to have to try and fix them every day.  The users have said they
> are not switching off their machines either, so that can't be causing it.

> Tim Borman wrote in message <6bnp20$d...@forums.borland.com>...
> >I believe you can cure this by restructuring the table. Change the AutoInc
> >to an Integer and save it, then change it back to an AutoInc. That should
> >reset the counter properly.
> >--
> >Tim Borman
> >tbor...@nospam.prophase.com
> >please reply to this newsgroup
> >-----------------------------------------------------
> >Kevin Berry wrote in message <34DF8441.A86D6...@webhost.co.za>...
> >>Hi,

> >>I'm experiencing a serious problem with auto-increment fields.  I've
> >>used auto-increment fields for many of the tables in the database.
> >>Problem is that I'm now getting a "Key Violation" error - which I know
> >>is caused by a corrupted auto-increment key value - it must be resetting
> >>the auto-increment counter or something.  The only way I can correct
> >>this is to delete the whole table and then to recreate it with the data
> >>- which is a major undertaking (in fact I'd go so far as to say that
> >>this is unreasonable).  The database is being accessed over the network
> >>and all systems are configured to use: \\server\db as the net directory
> >>and the database directory.  I have also set LOCAL SHARE to true on all
> >>machines.  I've installed BDE 4.51.  Is there anything else I can do?
> >>I'm considering rewriting the application to use a SQL server database
> >>at the moment.  But I don't want to have to do this.

> >>The system is running with 3 client PCs at the moment but the update
> >>interval is very frequent.

> >>Please help!!!

> >>Cheers,
> >>Kevin.

> >>Please use kevi...@usa.net as reply address as well as posting to the
> >>newsgroup.

Hello,
  We (Borland) has not fixed it because we do not have an example of
this happening in simple cases.  If anyone can narrow down the problem,
please fill out a bug report at:
http://www.borland.com/devsupport/bugs/bug_reports.html

  Thanks,
Scott
--
BDE Support:
 http://www.borland.com/devsupport/bde
Delphi Support:
 http://www.borland.com/devsupport/delphi
Common Delphi and BDE Questions and Answers:
 http://www.borland.com/devsupport/delphi/qanda/delphi3qa.html

Re:SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS


Make sure you do not have one of the buggy versions of VREDIR.VXD.
See document 90 at www.borland.com/devsupport/sqllinks/sqlnoteidx.html
for details.  If you are running on an NT server you might also want
to turn opportunistic locking off and see if that helps.

Bill

(Sorry but TeamB cannot answer support questions received via email.)
(To send me email for any other reason remove .nospam from my address.)

Re:SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS


Quote
"Kevin" <kevi...@usa.net> wrote:
>My problem is that the tables are becoming corrupted daily and it isn't
>feasible to have to try and fix them every day.  The users have said they
>are not switching off their machines either, so that can't be causing it.

I don't know anything about this particular problem, but I do have
experience that you can't count on the fact that users aren't turning
off their computer just because they say so.  Years ago with a
different database product I had a client that was constantly getting
corrupted data.  They swore they weren't turning off the machine
without exiting the program, and in fact I sat there for a day and my
partner for another day watching them correctly operate the program,
with no sign of data corruption.  At the end of the second day my
partner was there across the room from the PC talking to me on the
phone.  I had him call across the room "Hey Janet, I have to go to
another client so we're done for the day" and her hand went straight
for the power switch.  So what clients say they do and actually do do
when you're watching may not be the same as what they do when no one
is watching.

-------------------------------------
Larry Lustig
http://www.pipeline.com/~nyguide/
NY-DC 2 Day Tours, NY-PHILADELPHIA 1 Day Tour,
NY-Shopping Outlet 1 Day Tour.

Larry's Opinionated Guide to New York City
(for Cheapskates): The information you need
from a real New Yorker.  Available at Travel
Bookstores, or from the author
(email for information).

Re:SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS


Hi Scott,
I am unable at this moment to determine the reproducible steps that would
lead to the above mentioned corruption, but I have the corrupted table.
Is that what you need?
Anyway, there's a workaround-namely the double
restructuring, but one would need to know what table is corrupted without
actually
going through the insert/keyviol scenario. The reason for this is that
the end users just report they are unable to perform some high level task.
I cannot ask them to determine what table caused it and chose the
table from the list of tables in some repair utility-absolutely no way.
I also do not want to restructure all tables-this is a time consuming
operation and would require a specialized repair utility for just
this one kind of corruption. (After some 6 months after the first release
of my product it turned out that this type of corruption is the second most
frequent just following the famous Index out of date)
My questions are:
Is there a new version of tutility going to be released that would
detect this (and repair this as well)? Or
is there some TI available over the Net that deals with the intrinsic
details of autoinc generator in Paradox tables?
TIA
--
Roman
KRE...@mbox.cesnet.cz
(please remove STOPSPAM. in header]

Quote
Scott Frolich [Borland] wrote in message <34DF87AE.3...@corp.borland.com>...
>> >.......
>Hello,
>  We (Borland) has not fixed it because we do not have an example of
>this happening in simple cases.  If anyone can narrow down the problem,
>please fill out a bug report at:
>http://www.borland.com/devsupport/bugs/bug_reports.html

>  Thanks,
>Scott
>--
>BDE Support:
> http://www.borland.com/devsupport/bde
>Delphi Support:
> http://www.borland.com/devsupport/delphi
>Common Delphi and BDE Questions and Answers:
> http://www.borland.com/devsupport/delphi/qanda/delphi3qa.html

Re:SERIOUS PROBLEM WITH AUTO-INCREMENT KEY FIELDS


A suggestion:  a pair of api calls to get and set the next autoinc value for
a field would allow developers to test for and fix problems as well as
answering the common request to be able to initialize the autoinc value of a
new table. Or maybe this is already possible if you know the api well
enough?
--
Tim Borman
tbor...@nospam.prophase.com
please reply to this newsgroup
-----------------------------------------------------

Quote
Scott Frolich [Borland] wrote in message <34DF87AE.3...@corp.borland.com>...
>Kevin wrote:

>> Why isn't there an actual solution to this?  What causes it and why
haven't
>> Borland done something about this.  What good are auto-increment fields
if
>> you can use them as keys???

>> My problem is that the tables are becoming corrupted daily and it isn't
>> feasible to have to try and fix them every day.  The users have said they
>> are not switching off their machines either, so that can't be causing it.

>> Tim Borman wrote in message <6bnp20$d...@forums.borland.com>...
>> >I believe you can cure this by restructuring the table. Change the
AutoInc
>> >to an Integer and save it, then change it back to an AutoInc. That
should
>> >reset the counter properly.
>> >--
>> >Tim Borman
>> >tbor...@nospam.prophase.com
>> >please reply to this newsgroup
>> >-----------------------------------------------------
>> >Kevin Berry wrote in message <34DF8441.A86D6...@webhost.co.za>...
>> >>Hi,

>> >>I'm experiencing a serious problem with auto-increment fields.  I've
>> >>used auto-increment fields for many of the tables in the database.
>> >>Problem is that I'm now getting a "Key Violation" error - which I know
>> >>is caused by a corrupted auto-increment key value - it must be
resetting
>> >>the auto-increment counter or something.  The only way I can correct
>> >>this is to delete the whole table and then to recreate it with the data
>> >>- which is a major undertaking (in fact I'd go so far as to say that
>> >>this is unreasonable).  The database is being accessed over the network
>> >>and all systems are configured to use: \\server\db as the net directory
>> >>and the database directory.  I have also set LOCAL SHARE to true on all
>> >>machines.  I've installed BDE 4.51.  Is there anything else I can do?
>> >>I'm considering rewriting the application to use a SQL server database
>> >>at the moment.  But I don't want to have to do this.

>> >>The system is running with 3 client PCs at the moment but the update
>> >>interval is very frequent.

>> >>Please help!!!

>> >>Cheers,
>> >>Kevin.

>> >>Please use kevi...@usa.net as reply address as well as posting to the
>> >>newsgroup.

>Hello,
>  We (Borland) has not fixed it because we do not have an example of
>this happening in simple cases.  If anyone can narrow down the problem,
>please fill out a bug report at:
>http://www.borland.com/devsupport/bugs/bug_reports.html

>  Thanks,
>Scott
>--
>BDE Support:
> http://www.borland.com/devsupport/bde
>Delphi Support:
> http://www.borland.com/devsupport/delphi
>Common Delphi and BDE Questions and Answers:
> http://www.borland.com/devsupport/delphi/qanda/delphi3qa.html

Other Threads