Issue Details (XML | Word | Printable)

Key: CORE-4771
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Vladimir Arkhipov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Parameter for restore to ignore some dependency and validity errors

Created: 23/Apr/15 02:16 PM   Updated: 04/Jul/15 05:00 PM
Component/s: None
Affects Version/s: 3.0 Beta 1, 2.5.4
Fix Version/s: None


 Description  « Hide
For example, you have database and make regular backups but don't make regular control restore (sure, it is bad)
In database there is small bug in unused stored procedure: this procedure uses paramer1 in second procedure, which was dropped.

And after damage of real database you try to restore backup and get the dependency error, you get unrestorable backup (I don't know simple way to fix it).
Will be useful to make restore with some paramater (like IGNORE_ERRORS) which, for example, automatically comment the body of procedure with dependency errors.

And in addition, there is gbak paramater "no_validity". Will be useful to have ability to ignore only wrong conditions, not all.
After restore you can simple fix one problem in one place, but with "no_validity" you get inefficient database.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 23/Apr/15 04:37 PM
Please confirm which FB version you are running, the scenario described was once possible but the ability to "corrupt" SP should already have been fixed. The current "Affects Version/s" value is therefore suspect.

The "no_validity" restore switch/option is limited in scope (by design) to the enforcement/validation of foreign key constraints.

The reason that the SP does not restore is that the intermediate BLR which is used by the engine is recompiled from SP sources during the restore. The fundamental definition of an SP requires that BLR be present, so 'skipping' the SP is not possible. There could be other SPs, triggers, computed columns which are dependent on the bad SP, so the SP must exist for the entire validity of the database schema.

Vladimir Arkhipov added a comment - 25/Apr/15 09:40 AM
I checked this behavior on firebird Firebird 3.0 Beta1 and earlier versions (Firebird 2.1, Firebird 2.5).

For example:

create or alter procedure TEST1
returns (
    OUT1 integer,
    OUT2 integer
)
as
begin
  /* Procedure Text */
  suspend;
end;

create or alter procedure TEST2
returns (
    PARAM1 integer,
    PARAM2 integer)
as
begin
  select out1, out2 from test1 into param1, param2;
  suspend;
end;

Then I remove OUT2 parameter from procedure TEST1:

create or alter procedure TEST1
returns (
    OUT1 integer
)
as
begin
  /* Procedure Text */
  suspend;
end;

Compilation of changed TEST1 is successful and this is normal, potential blocking on this stage is very bad and difficult to pre resolve dependencies.
But if I forget to fix procedure TEST2 and make backup and restore, I get following error on restore procedure TEST2: "invalid request BLR at offset ... column OUT2 is not defined in procedure TEST1".

>>> Reply to: The reason that the SP does not restore is that the intermediate BLR which is used by the engine is recompiled from SP sources during the restore. The fundamental definition of an SP requires that BLR be present, so 'skipping' the SP is not possible.

As I wrote I suggest, for example, automatically comment the body of procedure (and preserve declaration of input and output parameters), not "skipping SP". So all dependencies on this procedure will be saved.


Sean Leyne added a comment - 29/May/15 07:44 PM
There are several issues/limitation with the proposed approach:

- it is possible for a developer to clear the procedure text/source while maintaining the compiled BLR, so there would be no source text to comment-out. Assigning dummy/commented source would effectively 'destroy' the SP logic.

- simply commenting out the source is not enough, if the procedure is SELECTable, then the commented version would need to contain an SP body which contains a SUSPEND statement. There was talk to adding a field/attribute to the RDB$Procedure metadata to tag selectable procedures, by I don't recall if that was implemented. If not, determining if the SP is selectable would require considerable effort.


I think that this case should be replaced by a solution to the source/real problem -- evaluate SP dependencies when changes to parameters are made and prevent invalid changes being committed.

Adriano dos Santos Fernandes added a comment - 29/May/15 07:56 PM
Engine knows it's in "restore mode" and may ignore whatever needed. It can just store invalid BLR and produce a so bad database like the original one.

I do think this option should exist. It should not be default, however.

Vladimir Arkhipov added a comment - 03/Jun/15 11:25 AM
>>> Sean Leyne: I think that this case should be replaced by a solution to the source/real problem -- evaluate SP dependencies when changes to parameters are made and prevent invalid changes being committed.

It is not very convenient solution because in this case I have to comment all existing calls of procedure TEST1 previously (in TEST2 and so on). Then change parameters in TEST1 and uncomment and fix all calls of TEST1 with new parameters. At present I can fix dependent procedures only once.

>>> Adriano dos Santos Fernandes: I do think this option should exist. It should not be default, however.
Yes, as I wrote in description I suggest exactly the optional (not default) parameter in gbak for restore corrupted backups.

Sean Leyne added a comment - 04/Jul/15 05:00 PM
It seems that the only solution would be to support the notion of "invalidating" dependent SPs based on changes to the SP parameters.

This would allow for SP parameters to be changed without having to perform an extensive amount of DDL to "comment out" the dependant SPs. It would, however, introduce new issues relating to how and when (the larger question) the SPs would be re-validated, and what would be the outcome of a failure of the re-validation.

{Interestingly, the concept of "invalidating" schema object was discussed as a potential solution to the old problem of gbak restoring a database which contained cyclical references}