Dave,

<< I take your point and if there was any doubt about the application being
improperly closed I would certainly implement it The application is not
being closed improperly, it is still running announcing the details of the
error. >>

Again, that's not what I'm saying.  What I'm saying is that it is possible
that the BLOB file is updated by the application, the application is shut
down improperly or experiences interrupted network access, restarted, and
*then* the error occurs when the BLOB file is updated again by the
application.  Unless your logging shows that the application is continuously
updating data without any gaps, then there is the possibility that there is
an interruption that may cause an issue.  That interruption may be an
application issue, network issue, or whatever.

Do you have a copy of a table that is showing such an error ?  If so, please
email it to me and I'll take a look.  I can usually tell what is causing the
corruption by looking at it with a hex editor.

--
Tim Young
Elevate Software
www.elevatesoft.com

> Again, that's not what I'm saying.  What I'm saying is that it is
> possible that the BLOB file is updated by the application, the
> application is shut down improperly or experiences interrupted
> network access, restarted, and then the error occurs when the BLOB
> file is updated again by the application.  Unless your logging shows
> that the application is continuously updating data without any gaps,
> then there is the possibility that there is an interruption that may
> cause an issue.  That interruption may be an application issue,
> network issue, or whatever.

Hi Tim

OK, I'm with you now.

The scenario you describe is certainly possible although I'd be
surprised if it happened as often as we are getting corruption.

Would I be right in thinking that improper shutdown and/or interrupted
network would only be an issue if the table was being edited at the
time?

I will look into adding the logging you suggest and I'll send you a
corrupted table via the binaries group - I'll have to try and track
down a fairly small one.

Regards
Dave

Dave,

<< Would I be right in thinking that improper shutdown and/or interrupted
network would only be an issue if the table was being edited at the
time? >>

If there is more than one user accessing a given table, then yes.  With
opportunistic locking via the SMB protocol, Windows can cache data locally
if only one user is accessing a file or files, so it is possible to get some
serious corruption under this scenario, even long after an
insert/edit/delete occurred.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Dave,
>
> << Would I be right in thinking that improper shutdown and/or
> interrupted network would only be an issue if the table was being
> edited at the time? >>
>
> If there is more than one user accessing a given table, then yes.
> With opportunistic locking via the SMB protocol, Windows can cache
> data locally if only one user is accessing a file or files, so it is
> possible to get some serious corruption under this scenario, even
> long after an insert/edit/delete occurred.

Hi Tim

Just to make sure I understand you correctly, in the scenario you
describe above, the problem could occur when a single instance of the
application has the file open and is incorrectly shutdown, possibly
long after the insert/edit/delete, because windows has cached the
updates and they thus never make it into the file?

Can this risk be minimized by regular calls to
TDBISAMTable.FlushBuffers? Having asked that, I'm sure I've read
somewhere that commiting a transaction calls FlushBuffers internally
somewhere.

Having written the previous paragraph I realised that although the
insertions are handled within transactions which are strictly committed
within a try/finally, the deletions are not.  Deletions simply use a
TDBISAMQuery (delete from imageocrtext where bla bla bla).  Does such a
query create its own transaction internally if one is not explicitly
used and if so, it presumably must call Commit on that transaction
which would, in turn, call FlushBuffers somewhere? If not, should I be
calling explicitly calling FlushBuffers after ExecSQL?

But returning to your earlier offer, I now have an example of a
corrupted file for you to take a look at.  You can download from:
http://www.reddoorsoftware.co.uk/LN-KKI.zip (273MB)

In the root of that zip you'll find the log file from running Verify in
DBSYS.  The table in question is called (imaginatively
imageocrtext.

Regards
Dave

Dave,

<< Just to make sure I understand you correctly, in the scenario you
describe above, the problem could occur when a single instance of the
application has the file open and is incorrectly shutdown, possibly long
after the insert/edit/delete, because windows has cached the updates and
they thus never make it into the file? >>

Correct.

<< Can this risk be minimized by regular calls to TDBISAMTable.FlushBuffers?
>>

Yes, that will help.

<< Having asked that, I'm sure I've read somewhere that commiting a
transaction calls FlushBuffers internally somewhere. >>

Yes, the TDBISAMDatabase.Commit method will, by default, force a flush to
disk.

<< Having written the previous paragraph I realised that although the
insertions are handled within transactions which are strictly committed
within a try/finally, the deletions are not.  Deletions simply use a
TDBISAMQuery (delete from imageocrtext where bla bla bla).  Does such a
query create its own transaction internally if one is not explicitly used
and if so, it presumably must call Commit on that transaction which would,
in turn, call FlushBuffers somewhere? >>

A DELETE execution will use an implicit transaction, but it does not execute
a flush during the Commit for performance reasons.  If you want the flush,
then wrap the DELETE execution in an explicit transaction.

<< But returning to your earlier offer, I now have an example of a corrupted
file for you to take a look at.  You can download from: >>

Thanks.  Could you email me the password for that table ?

--
Tim Young
Elevate Software
www.elevatesoft.com

> A DELETE execution will use an implicit transaction, but it does not
> execute a flush during the Commit for performance reasons.  If you
> want the flush, then wrap the DELETE execution in an explicit
> transaction.

Aha!  This is is encouraging, the application (as it was) does lots of
deletes which are not wrapped in transactions and there is no explicit
buffer flushing going on either.  I am in the process of reworking that
part of the application so it does its work on a local copy to
eliminate network issues and unwanted access by other processes - I
will wrap the deletions in a transaction while I'm about it.

>
> << But returning to your earlier offer, I now have an example of a
> corrupted file for you to take a look at.  You can download from: >>
>
> Thanks.  Could you email me the password for that table ?

Dohh! Password emailed.

Thanks
Dave

Hi guys

I have been using the following procedure and it deal with the situation
described. It can be used for INSERT, UPDATE and DELETE operations.

procedure PerformSql(cSql: String);
var cCmd: String;
   InTransaction: Boolean;
begin
    if empty(cSql) then begin
       exit;
    end;

    InTransaction := True;
    if not D.Database.InTransaction then begin
       InTransaction := False;
       D.Transaction;
    end;

    with D.Query do begin
       DatabaseName := DBName;
       Sql.Clear;
       Sql.Add(cSql);
       ExecSql;
       Close;
    end;

    if not InTransaction then begin
       D.Commit;
    end;
end;

Dave,

The BLOB offset errors are due to invalid BLOB signatures for certain
records.  The BLOB signature is simply an integer that is attached to each
record's BLOB fields, and then also attached to the first block of each
linked-list of BLOB blocks.  Whenever DBISAM tries to read a BLOB, it
verifies that the two signatures match.  If they don't, then DBISAM assumes
that something went wrong and the record is out of synch with the BLOB.

I've examined the signatures that are mismatched, and they are often several
hundred signatures apart (with the record having the "older" signature), so
that indicates that the BLOB blocks were updated and made it to disk, but
not the record.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Dave,
>
> The BLOB offset errors are due to invalid BLOB signatures for certain
> records.  The BLOB signature is simply an integer that is attached to
> each record's BLOB fields, and then also attached to the first block
> of each linked-list of BLOB blocks.  Whenever DBISAM tries to read a
> BLOB, it verifies that the two signatures match.  If they don't, then
> DBISAM assumes that something went wrong and the record is out of
> synch with the BLOB.
>
> I've examined the signatures that are mismatched, and they are often
> several hundred signatures apart (with the record having the "older"
> signature), so that indicates that the BLOB blocks were updated and
> made it to disk, but not the record.

Hi Tim

Thanks for that, I'm not sure I'm any the wiser unless this means that
the corruption definitely occurred during an update - as opposed to an
insert or delete?

Regards
Dave

Dave,

<< Thanks for that, I'm not sure I'm any the wiser unless this means that
the corruption definitely occurred during an update - as opposed to an
insert or delete? >>

It could have happened with an insert also due to record recycling.

--
Tim Young
Elevate Software
www.elevatesoft.com