I leave systems operating in fairly tough environments, most of my clients are based in Africa and power outages are common, often daily. Usually systems are used by 5 - 20 people, and each one shares a common application framework but different data-structures. Systems have UPS's fitted to keep power on during the first few minutes of an outage, but clients can't afford full back-up generators etc., so the power can easily go off unexpectedly.

DBISAM does a good job of not falling over, but from time to time I get a particular problem. I have attached a table which is not exactly corrupted, but which no longer accepts data posted to it via an INSERT statement. The error which comes back is to do with AUTO INC fields already having had that ID used.

I am pretty sure that the problem occurs when all or part of the network fails due to power outage and the application in part way through a "POST" / "FLUSHBUFFERS" call.

I don't know enough about file structures of DBISAM to really examine this table, but I would really like to know what has gone wrong with it! ... and ideally how I might rewrite code to stop it from happening or mitigate the problem.

Anyone out there curious enough to have a look at what has happened to it & get back to me?

Thanks in advance.

Adam


Just had a quick look and whilst I can't give you a definitive answer I can say

1. Good news - repair fixed the problem

2. It looks as though the index had been updated, but the power went before the datafile was updated, and (as a wild guess) before the autoinc information in the header was updated. You then try and add another record, according to the info in the header the next autoinc is (say) 1001. DBISAM adds this to the index and bombs because there's already 1001 in there.

I have no idea what to suggest since an SQL insert should use an engine generated transaction anyway

Roy Lambert [Team Elevate]

Roy,

<< I have no idea what to suggest since an SQL insert should use an engine
generated transaction anyway >>

Only for an INSERT INTO..SELECT FROM - a singleton insert works just like
any other singleton insert.  Plus, the issue is most likely occurring during
the commit (write to disk) of the changes.  One file is getting updated, but
not the other.

--
Tim Young
Elevate Software
www.elevatesoft.com

Adam,

<< I am pretty sure that the problem occurs when all or part of the network
fails due to power outage and the application in part way through a "POST" /
"FLUSHBUFFERS" call. >>

Post/FlushBuffers is the correct way to handle this.  Sometimes the timing
is just such that the Post is interrupted during the write stage, with one
file updated but not the other.

One thing you may want to do, however, is add logic to your application to
handle duplicate key errors like this on tables with autoinc primary
indexes, and then display a more useful error message to the user, and/or
initiate a repair on the table.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sorry to have posted my problem & then disappeared off the face of the Earth ... I was in the middle of Ghana bumping around in a truck, trying to sell a man a database!

Thanks for these comments. I didn't have the technical skills to check out what was wrong, but what you have said sounds about right.

I have put a simple trap on this error, using the error code for Duplicate Primary Key & I am now crossing my fingers ... the problem only occurs every year or so at one of my various sites, so it is hard to get enough information about it to fix it! What is good is that it is possible to cue a RepairTable call within the application and fix the problem.

As a secondary question: If I was to transfer these systems to an ELEVATE backend would the problem be better, worse or the same?

Adam,

<< I was in the middle of Ghana bumping around in a truck, trying to sell a
man a database >>

Cool, you probably just missed the Amazing Race folks.

<< As a secondary question: If I was to transfer these systems to an ELEVATE
backend would the problem be better, worse or the same? >>

You'll have a more clear indicator when a write doesn't complete on a table,
but you'll still end up having to repair the table.  However, the 2.04
REPAIR TABLE and VERIFY TABLE statements are very fast now, and it is very
easy to verify a table since it can be done on-line while other users are
using the database (and the table).

--
Tim Young
Elevate Software
www.elevatesoft.com