Firebird corrupt table, how to fix !!!!

Good day Experts

One of my tables in firebird 2 database are corrupt, I have tried to backup the databse but I get the following error messages.

Failed: Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements.
Internal gds software consistency check (cannot find record back version (291), file: vio.cpp line: 3591).
 gds_$receive failed.

Please how can I fix this table, and how can I prevent this in the future.
I think the corruption occurred when I deleted all the records from the table from Delphi.

thanx
henryreynoldsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Gary BenadeConnect With a Mentor Commented:
Steps to fix database:

1) always work with a copy of the database in case further damage occurs. Working with a copy also gives you exclusive access which is required to
perform the following operations:
2) gfix -v -f database.gdb
3) if previous step reports corruption: gfix -m -i database.gdb
4) repeat step 2 to see if the corruption was fixed

If it isn't fixed follow these steps to salvage data:

1) create a new database with the same metadata structure:
A) gbak -m will backup only the metadata.  It can then be restored to create a
new database.
B) isql -x -a will extract the metadata to a script file.
isql -i scriptfile will then create the new database.
2) copy the data to the new database:
A) Delphi's datapump can be used to copy data from all tables to a new database.
B) use isql to perform a table by table copy of the data.

If you're on a windows system make sure forced writes are on in the firebird.conf file, also do a disk check to make sure there is nothing wrong with the disk itself.
0
 
Nick UpsonConnect With a Mentor Principal Operations EngineerCommented:
Which version are you using, none of them should allow this to occur
0
 
DeesyConnect With a Mentor Commented:
As another solution i used from time to time. manual but sometimes it works well
find table wich is corrupt. use interbase datapump to copy all other tables into empty db. then try to locate "bad record" in broken table. i use select * from broken_table order by id query and then fetch everything on by one. before the bad record you probably will receive a message kind of "can't fetch it" right id for last fetching record then try to findnext good id with query like: select * from broken_table where id > 123 order by id. so at the end of manipulation you probably will get one or few records wich are damaged. so you can use datapump with condition to skip  damaged records. sometime it handy to delete indecies from the damaged table. and sometimes even broken table can be completelly pumped into blank database.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Nick UpsonPrincipal Operations EngineerCommented:
to expand slightly I know you said firebird 2 but we are now up to 2.0.4 I think, there is also available the beta test versions for 2.1
0
 
henryreynoldsAuthor Commented:
Morning Experts

Sorry for replying only now, I was out of office yesterday.

I am using version :2.0.1.12855.

This is my second time it happens just after a delete everything from the table from Delphi.

Please experts what should I not DO to create this problem.

I did also check that force writes are on.

thanx
0
 
Gary BenadeCommented:
You shouldnt be able to damage the database by applying DDL or DML, so I recommend you upgrade to 2.03 as soon as possible, there were many bug fixes since 2.01.
0
 
Nick UpsonPrincipal Operations EngineerCommented:
yes, upgrade asap
0
 
henryreynoldsAuthor Commented:
Hi Experts

What else can I do from my side and in Delphi to prevent table corruption ?

0
 
DeesyCommented:
It should not be any corruption at all from delphi site or isql... db should respect any kind of clients and work properly. have a look OS for lost clasters, ups, bad blocks on hdd  and other diagnostic. server could be a problem.
0
 
henryreynoldsAuthor Commented:
would a corruption occur when the power is unstable sometimes ?  
0
 
henryreynoldsAuthor Commented:
Is there any firebird tools to help fix table corruption ?
0
 
Gary BenadeCommented:
Yes, power failures will corrupt data, suggest a UPS asap. The cli tools that come with firebird are all you need, follow the steps I suggested in my first post.
0
 
DeesyCommented:
yes. if your server was power off durng power cut, db can be corrupted. so that is quite important to make your server runniing properly.

most usefull tolls (in my practice):
gfix - to do some fixes of database
gbak - to make a backup, sometime even corrupted db can be back up (let say the index is corrupted)
ibpump - tool to transfer data between databases (from corrupted to new)
ibexpert - handy ui tool
0
 
henryreynoldsAuthor Commented:
At this stage I am using EMS firebird sql manager or Flameroby to perform backups and all my table creations, procedures creations , is this OK
0
 
DeesyCommented:
i would suggest use command-line tools above. you can define additional parameters (probably you can do it as well with gui tools) but no gui for gfix - afaik
0
 
henryreynoldsAuthor Commented:
I have never used the command line  tools, where is good examples or docs on command line usages.
0
 
DeesyCommented:
help should be available when you run command tool without parameters or -? -help. and it really easy to call:

backup:
gbak -b -v -user <username> -pass <password> <from_db> <to_db_bak>

restore:
gbak -r -v -user <username> -pass <password> <from_db_bak> <to_db>

-v - show detail info to screen
if you have transaction problems (when you connect to db even to read data, fb start read/write transaction) you can specify -mode read_only for the backup


almost the same for gfix. usefull parameter is -m

but remember as it was mention above: work always on copy of your db. to make copy properly do next:
1. shut down firebird
2. make copy of db file - put into safe place
3. start fb again

if any problem after you play recover/restore db:
1. shutdown fb
2. copy from safe place copy of your db
3. start fb again

hope that will help
0
 
Nick UpsonPrincipal Operations EngineerCommented:
remember that a file-copy of the database is very likely to be corrupt so don't treat that as a backup
0
 
DeesyCommented:
if firebird service is shutdown no problem to make a copy of the file
0
 
henryreynoldsAuthor Commented:
Are you guys only using the command line tools when you work with your database ?
0
 
DeesyCommented:
gfix & gbak - dest to be used command line. all other stuff - your choice ;-)
0
 
DeesyCommented:
gfix & gbak - best* to be used command line. all other stuff - your choice ;-)
0
 
henryreynoldsAuthor Commented:
Hi Experts

Thank you all for helping me, I think it is just fair to split the points between
Deesy
NickUpson
hobbit72

I hope you guys will agree.

Thank you very very much
0
All Courses

From novice to tech pro — start learning today.