Subject SV: [firebird-support] Firebird Embedded corruptions
Author Svein Erling Tysvær
>We have shipped Firebird Embedded bundled together with our product for a few years now and the system is currently in production at several thousand of our customer's sites.
>Currently we are using Firebird Embedded 2.5.1 with the latest .NET-driver and a stack consisting of Castle Active Record on top on NHibernate and the system is running on the
>latest versions of Windows.
>All is well and Firebird has served us good so far with the exception of database corruptions that gets reported from a new set of customers every week. For some of them it is
>possible to instruct the customer on how to repair the databases themselves, but some of the databases are unfortunately so heavily corrupted that they need to be sent to us
>for repairing (which is a tedious work that steals time from other tasks). Most of them corruptions are normally found in the tables that gets the most writes, but I guess that is
>only natural.
>We are now at the planning stage for the next major release of our product and we are thus rethinking if Firebird really is a good choice, because of this.
>Lots of effort has gone into solving this problem on our side, so I think the normal prerequisites has already been put into place (e.g using forced writes and so forth), but our
>system needs to be up and running 24x7, which means that it is not possible to schedule periodic backup/restore cycles and my personal theory is that Firebird embedded gets
>corrupted over time if you are not doing this regularly.
>So I have have a few questions that I would appreciate if someone could answer:
>1. Is it feasible to run Firebird Embedded 24x7 in a setup where there are no scheduled backup/restore cycles. If not, how often should this be performed to ensure that the
>database does not get corrupted.
>2. Most of our customers are not using a UPS. From my experiments I have not managed to create a corrupted database by turning of the power while doing a large set of
>writes (in a session running in VirtualBox). Could someone please confirm that this is indeed safe when you are running with synchronized writes turned on?
>3. Are there any operations on a live database that should be avoided to minimize the risk of corruptions?
>4. Just read a discussion about whether it is needed or not to call fb_shutdown to stop Firebird Embedded. Could this be the reason why we are getting corruptions? Should
>we change our service to perform this call when it is stopped?
>5. I have also seen discussions of turning of automatic sweeps of the database (and doing them manually instead). Is this a likely source of corruptions for our setup?
>Thanks in advance. Maybe are there no certain answers to my questions, but any pointers in the right direction would be very appreciated. Firebird has been a real workhorse
>for us and we would rather like to keep it.

Hei Jan!

The one thing I try to avoid, is running DDL (CREATE, ALTER, DROP <table|trigger|stored procedure) on a database in use. Maybe I'm overly careful, but not all too long ago, a colleague caused some problems when he did

ALTER MyTable DROP MyField;

while he simultaneously had another transaction having uncommitted changes to MyField in one record.

I think (but have no experience), that possible reasons for corruption could include file system backups of the database while it is in use (exclude the database file(s) from such backups, rather use gbak for the backup, and include the resulting file in the system backup), and possibly anti-viruses preventing Firebird from doing it's work (though I would expect this to result in the database being unaccessible, not corrupted).

Another thing that's only affecting Fb 2.5.1, is that this version has an error relating to compund indices (requiring backup/restore or rebuilding such indices if upgrading to 2.5.2). Though I doubt this error would cause data corruptions involving more than the index.

Others will be able to give you a more thorough answer, despite having used Firebird since it's inception (0.9.4), I've very little experience with corruptions (undoubtedly related to only working on a handful of databases with about 20 simultaneous users).