Solved

Firebird corrupt table, how to fix !!!!

Posted on 2007-11-23
23
9,629 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
error 1.1 400 Bad request idhttp delphi 18 55
LDAP find expired users 8 73
Update in Sql 7 30
How to get sum of group by column and sum of total values 4 57
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!
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

863 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

25 Experts available now in Live!

Get 1:1 Help Now