On Tue, Mar 24, 2015 at 11:42 AM, Tim Ward tdw@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

gbak: ERROR:validation error for column BOXNUMBER, value "*** null ***"
gbak: ERROR: warning -- record could not be restored
gbak:Exiting before completion due to errors

I'm glad you got most of your database back.  I think the IBSurgeon people have a tool that  will let you fix a backup file - maybe to the level of mucking with individual constraints. 

So, at some point prior to the backup someone had added this NOT NULL
column, and hadn't gone round setting the values in the column. Yes, well, there's then the question about how come you're allowed to get a database into such an illegal state in the first place, isn't there.

 Yup.  Although the core of Firebird has move forward, some parts still reflect decisions that were made more than a generation ago.  Specifically, Firebird doesn't (generally) validate constraints when they're added.  When computers were small and slow and processing time was precious, we felt that good programmers always validated constraints before they added them, so having the database revalidate was a waste of money and penalized the responsible to protect the lazy.

*** BUT *** this is a "you had one job" issue, isn't it?

The one and only job of a backup utility is to create a backup that can
be restored. If it doesn't do that it's failed. At its one and only job.

Perhaps you'd be happier with NBackup, which doesn't understand the database structure at all - it just backs up pages and, when asks, puts them back together at some previous state.   

Soooo many other approaches could have been taken.

Most of the utilities, including gbak, are properly layered on the database.  One of the original philosophies (besides not duplicating the work a responsible database programmer or administrator would do automatically) was that any function that one of the tools needed was probably something that an application would need sooner or later. Keeping tools properly layered meant that we couldn't invent a magic hook to fly us out of a corner we'd painted ourselves into. But it does limit some of magic that might be nice.

 

, that wouldn't have
lost my database for me, including but probably not limited to:

(1) During backup, fail if the backup file being created is one that it
won't be able to restore.

That would make the backup slower - there may be other ways a layered application can validate data it reads, but the one that comes to mind is simultaneously building a backup file and a new database.   

(2) During restore ... er, just restore it anyway? - in its previous
state the database was, strictly speaking, illegal[#], but it was, in
real life, working fine. If gbak took this option, with a warning, then
I'd be able to fix the data ... but as it is, I can't, because I can't
restore the database.

It would be relatively easy - well sort of - to skip offending records rather than just blowing off whole tables.  But a layered application can't store invalid data.  Just imagine the uses of a connection parameter that says "ignore all validation". 

Yes I did discover no_validity, with which the restore did create *a*
database, but it was a completely ****ing useless database as the NOT
NULL constraints appeared to have been dropped everywhere, and who knows
what other manglings had taken place (the documentation doesn't list
them explicitly, it just says "deletes validity constraints from
restored metadata").

The theory is that the constraints are all there, just inactive.  Yes, it's going to be a pita to find them, turn them on one at a time, and test them.  

 

Which means that gbak had a third option to get it
right:

(3) Provide a restore option that warns about validity checking errors,
rather than failing them, but doesn't actually delete the checks from
the database.

As above. I suppose gbak could catch errors, and work back somehow to the original constraint - not sure that's at all clear from the error codes - commit and maybe disconnect, then start a transaction, deactivate the constraint, send out a message, and continue.

So, please, how am I expected to get my database back?

I'm assuming that you've trashed the original database and can't start from there. If you can't afford to lose a table with an invalid constraint, you might try the IBSurgeon tool.  Or restore the database twice, once, metadata only with the constraints active and once with data but no constraints.  Then pump the data from the second to the first using one of the third party data pump tools.  

Or, follow the recommendation that you backup regularly and restore periodically just to be sure your backups are good.  Gbak isn't the only backup tool that's let me down over the decades.  And it's a lot more malleable than the disk backups I've fought with.

OK OK, so I don't
actually need the data in the one table causing the problem (there
aren't millions of foreign keys pointing at it from all over the shop),
so -o -v worked, only losing the data in the one table that I didn't
care about anyway, but that's only because I happen to be lucky with the
data structures, it won't be a solution in general.

Right. It would be great if gbak had a devoted core of developers to make it more resilient.  But open source developers tend to go for more high visibility stuff like new SQL, new architectures, new interfaces, or even new types of backup and not go back to old boring layered code.  Maybe the tool vendors might find a project there.

Anyway, congratulations on getting your data back.

Good luck,

Ann