At 10:41 AM 8/02/2006, robert_p_levy wrote:

>Hello,
>
>The system that I am troubleshooting corruption for consists of Delphi
>applications using IBObjects on multiple machines interacting with an
>remote Firebird server through tcp/ip over a wireless connection. We
>seem to have corruption fairly frequently and our working theory is that
>occasionally a firebird transaction will be interrupted midstream by a
>lapse of the wireless connection. Are we correct to assume this at
>least could cause corruption?

No, an interrupted transaction cannot cause corruption. Dispose of
that theory and look for the real cause, which could be one or more of:

1. The database being configured for aysnchronous writes (a.k.a
Forced Writes OFF). Check this BEFORE looking anywhere else. It is
the MOST common cause of corruption in IB and Firebird databases. It
would be suicidal if you've got users connected by wireless, even in
a wireless LAN. But your Subject suggests that the remote
connections are WAN -- or did you mean to write "Intranet"?

2. A dying hard disk. Perform full scans of your hard disk at 2-day
intervals, preferably after user activity has finished for the day,
but activity has been happening, i.e. it doesn't make sense to do it
after a period when nothing has been written to disk.

3. (Possibly) RF interference - packets that arrive at the server
with damage that isn't detected until next time something tries to
read what was laid down from those packets. (You're better off if the
wireless connection just dies before the transaction completes...)
Large blobs might be more prone to that kind of corruption since the
engine merely counts the bytes and packs them into segments, it
doesn't try to make sense of them.

Since the wireless traffic is coming in "off the street", don't
overlook the possibility that it's malicious.

>So based on that theory, we decided one way to solve the problem is to
>do the updates locally and then at some point all at once in a single
>transaction using a script, make the changes post to the server. This
>would minimize the chances of posting during a blip in service.

Doesn't make sense. It loses concurrency totally and a script
doesn't have any way to protect the server from untimely updates. We
use replication to do this kind of thing, the so-called "briefcase model".

>We are currently using the TIB_DSQL object and each DML operation is a
>new transaction. But if instead we used that to write to a local
>database and then later on let the program use a TIB_SCRIPT object to
>make all the same updates to the the db on the server it would be a
>single transaction. But could we potentially roll back a messed up
>transaction if we caught an exception that the tcp/ip connection fell
>through?

Not sure what you are asking here. Neither the client nor the server
knows what the network is doing. Both assume it "just
works". However, if a client tries to write to a dead connection, it
will return an exception the first time it encounters a
non-response. On the server side, the server will eventually
recognise that a connection is dead and will proceed to roll back its
transactions.

>Does anyone know if it is supposed to be possible to roll back a
>transaction that was unnaturally interrupted?

No, you can't attack a transaction that was started by one connection
by somehow accessing it from another. You have to let matters take
their course - the server will clean up in due course.

All that said, don't start breaking your architecture yet. Find the
*cause* of the corruption, since corrupting databases isn't what
IB/Fb are designed to do.

One supposes that someone at the site is responsible for the network,
including keeping it clear of interference and secure from
raiders. Make Forced Writes top priority, check hardware on the
server (a new disk is virtually cost-free, in comparison to the time
and headaches involved trying to find out whether or not it's still
in as-new condition.) All HDDs die in time - treat drives that
store databases as consumables: replace them regularly and, if they
are in a vulnerable environment (inadequate or no UPS protection),
make replacement the first thing you do if a lightning storm or some
other unplanned line trauma intrudes on power supplied to the
building. "If the lights go out, the hard drive comes out."

All this is pretty well off-topic for IBO, of course....except the
bit about using IB_Script as a replication device. IB_Script can
play a role in replication systems written in IBO but, on its own,
it's not a replication system.

Helen