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

Metadata change does not commit until all users are disconnected [CORE1289] #1710

Closed
firebird-automations opened this issue May 30, 2007 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Eduardo Gamboa (bestrafender)

Is duplicated by CORE1208

When the definition of a field is changed from NOT NULL to NULL in order to accept null values, the change does not take effect until all users disconnect from the database even though the transaction in wich the change was defined has been commited.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

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

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is duplicated by CORE1208 [ CORE1208 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Eduardo, how did you change NOT NULL column to be NULL?

@firebird-automations
Copy link
Collaborator Author

Commented by: Eduardo Gamboa (bestrafender)

I use IBExpert Personal. It generates a SQL statement like this:
update RDB$RELATION_FIELDS set
RDB$NULL_FLAG = NULL
where (RDB$FIELD_NAME = 'CAMPO1') and
(RDB$RELATION_NAME = 'TEST_NULLS')

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

As soon as you (or IBExpert) does direct update of system tables, some "unexpected" things may happen. Changing nullability of a column to NOT NULL is relatively safe, but reattach required is normal case for it.

@firebird-automations
Copy link
Collaborator Author

Commented by: Eduardo Gamboa (bestrafender)

Is there a way to change the nullability of a column without updating directly to system tables?

@firebird-automations
Copy link
Collaborator Author

Commented by: @paulvink

Yes. If you create a not null constraint and you think you might want to drop it later, give it a name. That is, instead of just "NOT NULL", specify "CONSTRAINT ID_REQ NOT NULL" (ID_REQ is just an example name of course).

You can then drop the constraint later with "ALTER TABLE MYTABLE DROP CONSTRAINT ID_REQ".

If you don't name the constraint explicitly, Firebird assigns a name. Unfortunately, you can't retrieve this name with "SHOW TABLE MYTABLE" (unlike auto-assigned check constraint names - I think this deserves a request for improvement ;-))

You can dig up the name with this query however (this doesn't alter system tables, it just consults them):

select rc.rdb$constraint_name
from rdb$relation_constraints rc
join rdb$check_constraints cc
on rc.rdb$constraint_name = cc.rdb$constraint_name
where rc.rdb$constraint_type = 'NOT NULL'
and rc.rdb$relation_name = '<TableName>'
and cc.rdb$trigger_name = '<FieldName>'

Yes, some of the field names are illogical, but this IS correct. Be sure to UPPERCASE the table and field name if they were created case-insensitively. Otherwise, match the case exactly but don't surround them with double-quotes! And don't include the <>.

Once you have the name, you can drop the constraint as shown above.

Notice that you ALWAYS must close all connections to the database and reconnect before you can insert nulls in the altered column!

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

This is a very old issue (inherited from InterBase) and it has been fixed in v2.1 only. Actually, it duplicates CORE866.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 12233 ] => Firebird [ 15591 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

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

No branches or pull requests

1 participant