Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Restore fails if one stored procedure is incorrect [CORE4340] #4662

Open
firebird-issue-importer opened this issue Feb 14, 2014 · 5 comments
Open

Comments

@firebird-issue-importer

Submitted by: Robert Gilland (robert.gilland_basx.com.au)

Votes: 2

GBAK restore will fail if one procedure is incorrect, this means backups that were thought to be good cannot be used.
Also grave possibility of complete loss of data.

I recommend any metadata that cannot be committed be "skipped" during the restore.
Thus allowing the database to be usable after the restore.

The "skipped" metadata should be identified in the restore logs.

I just spent an entire day looking for an incorrect procedure!

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 14, 2014

Commented by: @dyemanov

What do you mean by incorrect procedure? What was the gbak error message? Can you provide an example? What FB version do you use?

A restored database is either consistent or not. Skipping some metadata is not a good behavior, at least by default. This is especially important when we consider interdependencies, i.e. one skipped procedure may require to skip a dozen of dependent ones. Besides that, I foresee imlementation issues in this approach. Metadata objects are restored in a single transaction for a reason and it's surely impossible to change commit into a partial rollback. Restoring metadata in many separate transactions has its own drawbacks and is likely to introduce another issues.

As for complete data loss, this is impossible. When restore fails due to incorrect metadata (whatever it might mean), all tables along with all their records are already restored and can be accessed in the newly created database. Also, a sane admin should always check the resulting code of gbak to avoid backups "thought to be good".

I can consider this ticket as a possible request for improvement, but not as a bug.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 17, 2014

Commented by: Robert Gilland (robert.gilland_basx.com.au)

gbak error message

invalid request BLR at offset 640
Input parameter mismatch for procedure PROC_PRCH
exiting before completion due to errors.

using firebird 2.5.2 latest release

we got no errors during backup. ( using database workbench for backup ).

so when this happens to us should we?
1. run a new backup over the failed restore database.
2, restore again from the new backup.

From memory we have tried this approach and it has landed us in further hot water.
Like an unstable database system. I can't remember the details of the instability.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 17, 2014

Commented by: Sean Leyne (seanleyne)

Robert,

Not sure how to read your last comment, are you looking to:
(a) prevent it from affecting another backup, or
(b) solve this problem with the existing restore

If (a) then I would make a notional change to the SP logic, in the source db, to force the engine to recompile it. The next backup should not have any errors.

If (b) then you would need to:
(i) hopefully you have a copy of the live database or the database metadata, from which you would create a new empty database
(ii) use a data copy/move tool to move the rows from the failed restore to the new empty database.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 17, 2014

Commented by: Robert Gilland (robert.gilland_basx.com.au)

well both really.

1. Fortunately I have been able to find the bad psql and resolve it then create a second good backup file. However, next time I might not be so lucky.
2. I am simply putting proposal forward for firebird to elegantly recover from this problem.

recovering by data/copy move tools is not an option, as almost everything in our database is foreign key constrained.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 18, 2014

Commented by: Sean Leyne (seanleyne)

Some of the copy/move tools are designed to process rows based on FK constraints or to drop the FKs and then add them in a cleanup step...

But your point is well made, that restore should handle some failures better.

In this case, the only solution I can think of is to restore an empty SP initially (with parameters only) and then later restore the full BLR... (much like the indexes definitions are restored but activated later in the restore). This approach is feasible but ... a little ugly.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant