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

Cannot alter the domain after migrating from older versions [CORE3101] #3479

Closed
firebird-automations opened this issue Aug 8, 2010 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @dyemanov

Votes: 3

Test case:

-- with Firebird 1.5 or 2.0
create domain state smallint;
create table tab (col state, check (col in (0, 1, 2)));
commit;

-- backup / restore

-- with Firebird 2.5
alter domain state set default 0;
commit;

-- ERROR
action cancelled by trigger (1) to preserve data integrity.
Cannot update trigger used by a CHECK Constraint.

The problem is that MET_change_fields() schedules the validation for dependent procedures/triggers. The CHECK constraint has two underlying triggers depending on the domain. During validation, RDB$VALID_BLR is going to be set to TRUE. But its prior value is NULL, as this field didn't exist in ODS 11.0. System trigger RDB$TRIGGER_22 considers that being a meaningful change and throws an exception (its purpose is to disallow modifications of check constraints).

I can think of a few possible solutions:

a) don't validate system triggers (thus not maintaining RDB$VALID_BLR for them)
b) remove comparison for RDB$VALID_BLR from RDB$TRIGGER_22 making this field irrelevant for the check
c) always reset the RDB$VALID_BLR flag during restore (don't preserve its backed up value)

Commits: f42c9bd 68fbad9

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

description: Test case:

-- with Firebird 1.5 or 2.0
create domain state smallint;
create table tab (col state, check (col in (0, 1, 2)));
commit;

-- backup / restore

-- with Firebird 2.1 2.5
alter domain state set default 0;
commit;

-- ERROR
action cancelled by trigger (1) to preserve data integrity.
Cannot update trigger used by a CHECK Constraint.

The problem is that MET_change_fields() schedules the validation for dependent procedures/triggers. The CHECK constraint has two underlying triggers depending on the domain. During validation, RDB$VALID_BLR is going to be set to TRUE. But its prior value is NULL, as this field didn't exist in ODS 11.0. System trigger RDB$TRIGGER_22 considers that being a meaningful change and throws an exception (its purpose is to disallow modifications of check constraints).

I can think of a number of a few possible solutions:

a) don't validate system triggers (thus not maintaining RDB$VALID_BLR for them)
b) remove comparison for RDB$VALID_BLR from RDB$TRIGGER_22 making this field irrelevant for the check
c) always reset the RDB$VALID_BLR flag during restore (don't preserve its backed up value)

=>

Test case:

-- with Firebird 1.5 or 2.0
create domain state smallint;
create table tab (col state, check (col in (0, 1, 2)));
commit;

-- backup / restore

-- with Firebird 2.5
alter domain state set default 0;
commit;

-- ERROR
action cancelled by trigger (1) to preserve data integrity.
Cannot update trigger used by a CHECK Constraint.

The problem is that MET_change_fields() schedules the validation for dependent procedures/triggers. The CHECK constraint has two underlying triggers depending on the domain. During validation, RDB$VALID_BLR is going to be set to TRUE. But its prior value is NULL, as this field didn't exist in ODS 11.0. System trigger RDB$TRIGGER_22 considers that being a meaningful change and throws an exception (its purpose is to disallow modifications of check constraints).

I can think of a number of a few possible solutions:

a) don't validate system triggers (thus not maintaining RDB$VALID_BLR for them)
b) remove comparison for RDB$VALID_BLR from RDB$TRIGGER_22 making this field irrelevant for the check
c) always reset the RDB$VALID_BLR flag during restore (don't preserve its backed up value)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

description: Test case:

-- with Firebird 1.5 or 2.0
create domain state smallint;
create table tab (col state, check (col in (0, 1, 2)));
commit;

-- backup / restore

-- with Firebird 2.5
alter domain state set default 0;
commit;

-- ERROR
action cancelled by trigger (1) to preserve data integrity.
Cannot update trigger used by a CHECK Constraint.

The problem is that MET_change_fields() schedules the validation for dependent procedures/triggers. The CHECK constraint has two underlying triggers depending on the domain. During validation, RDB$VALID_BLR is going to be set to TRUE. But its prior value is NULL, as this field didn't exist in ODS 11.0. System trigger RDB$TRIGGER_22 considers that being a meaningful change and throws an exception (its purpose is to disallow modifications of check constraints).

I can think of a number of a few possible solutions:

a) don't validate system triggers (thus not maintaining RDB$VALID_BLR for them)
b) remove comparison for RDB$VALID_BLR from RDB$TRIGGER_22 making this field irrelevant for the check
c) always reset the RDB$VALID_BLR flag during restore (don't preserve its backed up value)

=>

Test case:

-- with Firebird 1.5 or 2.0
create domain state smallint;
create table tab (col state, check (col in (0, 1, 2)));
commit;

-- backup / restore

-- with Firebird 2.5
alter domain state set default 0;
commit;

-- ERROR
action cancelled by trigger (1) to preserve data integrity.
Cannot update trigger used by a CHECK Constraint.

The problem is that MET_change_fields() schedules the validation for dependent procedures/triggers. The CHECK constraint has two underlying triggers depending on the domain. During validation, RDB$VALID_BLR is going to be set to TRUE. But its prior value is NULL, as this field didn't exist in ODS 11.0. System trigger RDB$TRIGGER_22 considers that being a meaningful change and throws an exception (its purpose is to disallow modifications of check constraints).

I can think of a few possible solutions:

a) don't validate system triggers (thus not maintaining RDB$VALID_BLR for them)
b) remove comparison for RDB$VALID_BLR from RDB$TRIGGER_22 making this field irrelevant for the check
c) always reset the RDB$VALID_BLR flag during restore (don't preserve its backed up value)

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

d) Ignore field RDB$VALID_BLR and require all object to be valid on commit (if not - refuse to commit).

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

What would the implications be if the source of all triggers where deleted as a "source security" measure by the database admin, on the possible solutions?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It has nothing to do with the source code. Only BLR is validated.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Dmitry Y,

Without source how can BLR be "validated"? (Looking for an education on BLR)

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

BLR is being parsed and compiled into the internal execution tree format (and probably optimized as well). If any change in the dependencies makes this process to fail for whatever reason, then BLR is considered invalid.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.0 [ 10221 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment