Subject Re: [firebird-support] Re: Database corruption (again) or what is wrong with Firebird.
Author Alexandre Benson Smith
HKlemt wrote:
> >From my point of view all current firebird versions are very stable,
> but there are some database repair jobs from time to time that we do
> for our customers (this year there were 2 corrupt databases until now,
> but both from one software company). Real reasons are not so easy to
> reproduce, but in one case it was a full harddisk on a windows server
> that was resetted at a very bad time.
> Since we know that customers like you have sometimes already a problem
> when one record is lost, we have implemented some internal structure
> inside the customer database, that creates a kind of transactionlog
> based on automatic generated triggers. Based on firebirds Events, they
> are transmitted to one or more server in almost real time right after
> the transaction is committed. We use our ibeblock scripting language
> to do the transfer, but any other language can be used, that is able
> to copy a record from one database to another and is able to react on
> Events.
> Some basics of this concept and a demo database is available here for
> in our public download area
> This technology is used in several customer projects, for example
> german press agency, which would have a very big problem when their
> Firebird Server becomes unavailable in the early afternoon, because it
> is the major source for a lot of german newspapers, who need the
> stories in time before starting their printing machines. So they
> decided to have a replicated server and as far as i know, it was used
> only once in the last 6 years, because of a ram error in the main
> server. We have also implemented this for other companies and it
> simplpy works and can be used for different tasks (clustering,
> online/offline replication, laptop replication, etc.). Internally it
> uses pure Firebird technology, but it has some requirements in the
> database model, but this can also be added on an existing database.
> btw: we found that sometimes it helps to rebuild a database completly
> and not only do a backup and restore, since in a backup/restore
> process, the objects are not recompiled, so sometimes you find invalid
> sourcecode in the database, even if it does its job based on the blr
> code. This operation can be done in ibexpert in two simple operations
> (extract metadata with data and blobs to a script and execute it
> again). This gives you a guarantee that the new database is created
> only based on valid SQL Statements of the current firebird version. if
> you like, you can do the same job with other tools, but you definitly
> need more time and a lot of workarounds.
> Holger

Hi Holger !

We had talked about this system on the last Firebird Developers Day in
Brazil, and I thought it interesting and simple to implement from what
we had talked on that night, but I had not looked into the real
implementation until today :)

I know it's just the skeleton of a replication system...

But in a quick overview I saw the following:

No handle of computed fields
Always updates full record, not just the changed columns
No way to avoid replication of columns maintained by triggers

I did a simple two way replication system a couple of months ago...
Those was the first problems I got when building it.

I need to adjust a lot of triggers that should not fire when the DML is
applied by the "replication user"

I did not realize how you handle the following situation:
First Replication Action: Insert a new record ID#1 (fails)
Second replication Action: Changes field "foo" on record ID#1 (that did
not exists since the insert fails)

Does the second action get logged as "done" ? If so, this will lead to
problems of mismatch data between databases (the same applies to delete
instead of update).

Perhaps was just a misunderstanding on my part, since I did not read all
the code carefully, if so, just forgive me.

Do you treat all the action as a single transaction ? or do you treat
each statement as a single transaction ?

I had some cases where a insert on a given table auto-populate other
tables by after insert triggers, the auto-populated tables got logged to
the replication log, as if it was a normal insert operation, the
triggers that populated those tables has provision to not fire if the
current_user is the "replication user".

Let's suppose we have TableA that has an after insert trigger that does
an insert on TableB

The replication log would have two records ("Insert into TableA..." and
"Insert into TableB")
When the replication utility runs it could fail to insert on tableA but
suceeds on inserting on TableB, how do you handle this ? Do you handle
both inserts in a single transaction ?

How do you aggregate distinct replication actions on a single
transaction in you model ?

I think those are my doubts for a first look on your system.

Thanks for sharing it with us !

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil