enLibrary
"Cannot commit index" or how to pump data from partially restored (inconsistent) database
Alexey Kovyazin, last update 30-March-2014
After fixing corruptions it's a often to see "Cannot commit index" error at restore step. In Firebird 2.0 and later this comes as a warning and database will be not brought on-line; in Firebird 1.0-1.5 and InterBase this occurs as an very unpleasant error which stop restoring process (it's impressive to see this error in the end of 200Gb database restore).
The reason of "Cannot commit index.." error is obvious: corruption affects database referential integrity, and records disappear in magic (from the server point of view) way.
For example, you have a table "Customers" and associated table "Orders" referenced by Foreign key. Without corruption it's not possible to violate Foreign key and delete record from Customers without prior deleting all orders which refers to particular customer. Corruption affects database file at low-level and kills records directly. What a nice surprise for server to see unresolved links to master records from foreign key records.Here I need to add a tip about restoring process: engine restores all tables with non-activated indices (it makes filling of tables with records much faster), and after that starts to activate (i.e., build) indices one by one. It means that all data are in database already when restore process starts indices creation step and you see "Cannot commit index.." error.
Well, the first wish when you see "Cannot commit index" error is to drop appropriate Foreign key constraint in order to allow restore to finish. The question is what we are going to do with inconsistent, but successfully restored database: dropped foreign key can be important part of business logic.
There are 2 approaches here: recreate missed data or drop inconsistent data. Let's see what it means on Customers-Orders example above.
Recreation of missed data
So, you have decided that you need to recreate missed Customers for inconsistent Orders. In this way you need to determine lost IDs. Usually I use the SQL query like this (I think it will be easy to adopt it to appropriate):
SELECT O.Customer_ID FROM ORDERS O WHERE NOT EXISTS ( SELECT C.Customer_ID FROM CUSTOMERS C WHERE O.Customer_ID=C.Customer_ID)After that you can manually insert records with list of missed primary keys, and run backup/restore again.
Pumping of consistent data
If you think it's acceptable to lose inconsistent records, you can pump all data from partially restored database to the empty database and just skip inconsistent records, using the following approach:
1) Download free tool IBDataPump http://www.clevercomponents.com/demo/datapump/IBPump.zip2) Create only metadata database from backup of repaired database (after IBFirstAID) using command
gbak -c -m -user SYSDBA -pass masterkey Disk:\Path\backup.fbk Disk:\Path\fresh.fdb
3) Run IBDataPump and set partially repaired database as Source, and fresh empty database as target
4) Click on next tabs, click appropriate buttons and a 3st tab click "Pump". Wait for completing (it can be long process, to get some idea you can check the growth dynamics of target database size).
5) As result you'll have fresh.fdb database with consistent foreign keys relationships - it's fully ok.
If database has loop in with foreign keys, IBDataPump will warn you and give a list of such constraints - one or more of them should be dropped before pumping will be available.
Of course, there were some cases when pumping require special approach, and if you'll face such situation, please contact our support.