Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Firebird corrupt table, how to fix !!!!

Posted on 2007-11-23
23
Medium Priority
?
11,079 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 4
  • +1
23 Comments
 
LVL 19

Accepted Solution

by:
Gary Benade earned 336 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 332 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 332 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

730 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