Known Causes of Corruption in InterBase Databases
Abstract: Known Causes of Corruption in InterBase Databases
by Craig Stuntz
InterBase is, in general, a very stable database server and corruption is rare. When corruption does occur, then, it is important to determine the cause of the corruption, because it indicates that something is wrong with the installation and it is important to fix it. Database corruption should not be a routine occurance, even if a customer neglects to maintain the DB.
It is a good idea to run Database Validation or command-line gfix from time to time so that if corruption does occur it can be caught early, before the damage to database is too severe to repair.
As an aid to diagnosing source of the problem, the table below lists known causes of InterBase database corruption, along with suggestions for how to avoid each type of corruption in the future. To repair a corrupt database, use command-line gfix with the -mend switch, or Database Validation in IBConsole. If that fails, restore from a recent backup or post a question to the borland.public.interbase.general newsgroup.
| Cause | IB Versions | How To Prevent |
|---|---|---|
| Changing metadata while database in use | All? | This one is a rumor. I've never seen a reproduceable test case of this. IB should handle this without a problem. But more than one person has claimed this was a problem, and there's no reason to do this on a routine basis, so I'm including it in the list. |
| Connecting to DB during restore | All? | This is simply a special case of "Changing metadata while database in use," above. IB doesn't seem to prevent you from connecting to a DB during a restore. You should ensure that nobody can connect to the DB until the restore is complete. |
| Connection strings mismatched | Fixed in 6? | A connection string consists of two parts: a server name and a filename with path. The server name is optional -- it can be omitted for a local connection. The filename portion must be identical for all clients. On most OSs, it is possible to use two different strings to express the same filename/path. For example, on Windows C:\DIRNAME\FILENAME.GDB and C:DIRNAME\FILENAME.GDB are the same (note the missing backslash in the second). On unix(es) you can have a hard link to a file. It is very important that all clients connect to the same database using the same connect string. Otherwise InterBase server may think it is connecting each client to different databases and may try to update the "databases" in conflicting ways. This may be fixed in IB 6 but should always be avoided. |
| Copying the database file(s) while in use | All | There are really two separate issues here. If you copy the database file(s) while the server is active, there is a very good chance that the copies produced may be corrupt, especially if forced writes is off. You should use gbak or IBConsole backup and restore to produce a non-corrupt copy of the database while it is in use. Please note that a DB file can be "in use" even if no users are connected to it, as long as the server is active (the server could be doing a sweep, for example). A second issue is that some backup software attempts to get an exclusive lock on files it is backing up. If it gets an exclusive lock on the InterBase database, it can cause the InterBase server to be unable to perform important operations. If forced writes are off, it can actually cause corruption of the source file, as well as the copy! Again, only InterBase gbak or IBConsole's backup (or any other program which uses the InterBase services API to perform the backup) should be used to backup an InterBase database. You can use your regular backup software to backup the completed GBK, if you want. |
| Database files too large in IB 5 or earlier. | Through version 5 | If a single database file (not to be confused with the size of the database in total) exceeds 4 GB on Windows or 2 GB on unix, the database will be corrupted. IB 6.0.1.6 and 6.0.2.0 display an error message instead of corrupting the database, and IB 6.5 and later allow the use of larger files without errors or corruption. For IB 5.6 and earlier, add files to the database using ALTER DATABASE ADD FILE... when any single file in the DB begins to grow close to these limits. Guard against this carefully; damage from this bug can be difficult to impossible to fix. |
| Deleting the current row in an UPDATE trigger | Fixed in 7.1 | There is no good reason to do this. It's an error. But if the trigger calls a procedure which updates the table, which calls a procedure, which deletes the row.... Mistakes happen. If any combination of events causes the current row to be deleted within the context of an UPDATE trigger on that table, the table's data will be corrupted. Gfix cannot repair this damage. |
| Forced writes OFF | All | This is not a cause of corruption per se, but rather a failure to protect against other causes of corruption. With forced writes ON, InterBase will always try to keep the database on disk in a stable state. With forced writes OFF, InterBase will cache writes and write to disk in storage (rather than stability) order, improving performance, but increasing the risk of corruption. Turn forced writes ON unless you have a lot of confidence in the stability of your hardware, your OS, and your InterBase use. |
| Hardware glitches | All | If the disk drive holding the database dies, the DB dies with it. The best defense against this is to use a RAID array to hold the DB -- not only do you get protection from hardware failure with zero downtime, but you get a nice speed boost as well. Of course, you should still backup the DB regularly. If server power is interrupted, the effects are more or less the same as a server crash (see below). Use of an uninterruptable power supply is strongly recommended; this allows the server to be shut down gracefully in the event of a power failure. Faulty memory could result in IB writing bad data to the DB, in rare cases. |
| Mismatched client and server versions | All | This doesn't cause corruption directly, but it can crash the server, which can cause corruption (see "Server Crashes" below). In particular, the combination of IB 6+ client, IB 5.6 server, and version 5.2 of the BDE driver for InterBase (sqlint32.dll) will crash the IB server when a query uses a date parameter. |
| Server crashes | All | If the server crashes while writing to disk, the DB can be corrupted. If forced writes are turned off and the server crashes before all changes have been written to disk the DB will probably be corrupted. If the InterBase server crashes, the InterBase Guardian will immediately restart it, so you may not even notice the crash. Check the interbase.log file to determine if the server has crashed. Turning on forced writes significantly reduces the chances that a server crash will corrupt a database. |
| Too many generators in IB 5 or earlier. | Through version 5, mostly, but to a lesser extent all versions | If using version 5 or earlier, do not create more than around 248 generators per KB of DB page size. If you need to create more generators, increase the DB page size first. Most of what causes this bug is fixed in 6.0.1.6 and later, but it can surface even in versions 6.0.1.6-7 if you use ISQL (instead of IBConsole or a client application) to create the generators, and the limit in this case is about 115 generators per KB of DB page size. To be on the safe side, expand the DB page size when you need a large number of generators. The symptom of this type of corruption is that one or more generators will return wildly incorrect values, but the corruption can affect other parts of the DB, as well. |
| Too many transactions. | All | You must backup and restore the database before the "next transaction" statistic from the gstat -h or database statistics in IBConsole reaches a certain critical number, just over 131000000 transactions per KB of DB page size. This is a rather large number, but it's possible to get there if you have a very active DB with a small page size which is never restored from backup. |
| Transporting database without transportable backup | All | Never move an InterBase database from one operating system to another by copying the database file(s). Always make a transportable backup, and restore on the new OS. In at least some cases (Novell to Windows, for example), failing to do so will produce a DB which is unreadable by the InterBase server on the new OS. |
| Use of IB 4 DBs (ODS 8) with IB 6 Open Edition | 6.x Open Edition | IB 6 Open Edition may not prevent you from connecting to an On Disk Structure (ODS) 8 (IB 4) DB. Doing so can corrupt the DB. IB 6 commercial editions do prevent this. In general, any InterBase server version supports connecting to DBs created by the previous version, but not older DBs. However, when upgrading server versions it's always a good idea to make a transportable backup under the old IB server version, do a test restore to ensure that you have a good backup, upgrade the server, and then restore under the new server version. This ensures that the database ODS is up-to-date. |
| Use of IB 5.5 | WI-V5.5 | The automatic sweep in InterBase 5.5 can corrupt your DBs; this is fixed in 5.6 and later. An update to 5.61 is available for "free" (you pay shipping/handling) from Borland. While you're waiting for it to arrive, set the sweep interval to zero (see Operations Guide) and manually sweep the database at night. You can restore the sweep interval to its former value after 5.61 is installed. |
| Use of IB 6 prior to 6.0.1.6 | 6.0.0.27 - 6.0.1.0 | Versions of IB 6 prior to 6.0.1.6 are pre-release. There are several bugs which can corrupt your databases, so these versions (I won't call them releases) should not be used under any circumstances. Borland certified (commercial) releases of InterBase 6 have never had these bugs. If using IB Open Edition, update to 6.0.1.6 or later as soon as possible. |
| Use of IB 7.0 and certain operations in a stored procedure. | 7.0 - 7.1 SP 1 | If you do certain certain operations in a procedure the server can corrupt the database (see QC #5376). This is fixed in InterBase 7.1 SP 2, which is a free upgrade for anyone with a 7.0 license. |
| Use of IB 7.1 prior to SP 2 (7.1.0.189), in a few cases | 7.1 without service packs, 7.1 SP 1 | InterBase 7.1 SP 2 resolves bugs present in 7.1 without service packs and InterBase 7.1 SP 1. These issues are described in more detail in QualityCentral. Essentially, these bugs may affect you if you:
Also fixed is a bug which can cause gfix / Database Validation to incorrectly report corruption. |
| Use of NCOPY on Netware | Novell versions | See this article. |
Thanks to Alexey Kovyazin, Bjørge Sæther, Thomas Steinmaurer, and Quinn Wildman for feedback on this article.
Copyright © 2002 by Craig Stuntz. All rights reserved.
Archive of https://support.embarcadero.com/article/35503