Solved

Firebird corrupt table, how to fix !!!!

Posted on 2007-11-23
23
9,373 Views
Last Modified: 2013-12-09
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
0
Comment
Question by:henryreynolds
  • 8
  • 8
  • 4
  • +1
23 Comments
 
LVL 19

Accepted Solution

by:
Gary Benade earned 84 total points
ID: 20342238
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
 
LVL 19

Assisted Solution

by:NickUpson
NickUpson earned 83 total points
ID: 20345453
Which version are you using, none of them should allow this to occur
0
 
LVL 1

Assisted Solution

by:Deesy
Deesy earned 83 total points
ID: 20349005
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
 
LVL 19

Expert Comment

by:NickUpson
ID: 20349177
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
 

Author Comment

by:henryreynolds
ID: 20356042
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
 
LVL 19

Expert Comment

by:Gary Benade
ID: 20356128
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
 
LVL 19

Expert Comment

by:NickUpson
ID: 20356727
yes, upgrade asap
0
 

Author Comment

by:henryreynolds
ID: 20357014
Hi Experts

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

0
 
LVL 1

Expert Comment

by:Deesy
ID: 20357044
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
 

Author Comment

by:henryreynolds
ID: 20357128
would a corruption occur when the power is unstable sometimes ?  
0
 

Author Comment

by:henryreynolds
ID: 20357152
Is there any firebird tools to help fix table corruption ?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Expert Comment

by:Gary Benade
ID: 20357168
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
 
LVL 1

Expert Comment

by:Deesy
ID: 20357172
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
 

Author Comment

by:henryreynolds
ID: 20357203
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
 
LVL 1

Expert Comment

by:Deesy
ID: 20357276
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
 

Author Comment

by:henryreynolds
ID: 20357297
I have never used the command line  tools, where is good examples or docs on command line usages.
0
 
LVL 1

Expert Comment

by:Deesy
ID: 20357359
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
 
LVL 19

Expert Comment

by:NickUpson
ID: 20357482
remember that a file-copy of the database is very likely to be corrupt so don't treat that as a backup
0
 
LVL 1

Expert Comment

by:Deesy
ID: 20357581
if firebird service is shutdown no problem to make a copy of the file
0
 

Author Comment

by:henryreynolds
ID: 20357640
Are you guys only using the command line tools when you work with your database ?
0
 
LVL 1

Expert Comment

by:Deesy
ID: 20357682
gfix & gbak - dest to be used command line. all other stuff - your choice ;-)
0
 
LVL 1

Expert Comment

by:Deesy
ID: 20357685
gfix & gbak - best* to be used command line. all other stuff - your choice ;-)
0
 

Author Comment

by:henryreynolds
ID: 20372047
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now