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
Comments
Modified by: Sean Leyne (seanleyne)status: Open [ 1 ] => Resolved [ 5 ] resolution: Duplicate [ 3 ] |
Commented by: @AlexPeshkoff Eduardo, how did you change NOT NULL column to be NULL? |
Commented by: Eduardo Gamboa (bestrafender) I use IBExpert Personal. It generates a SQL statement like this: |
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. |
Commented by: Eduardo Gamboa (bestrafender) Is there a way to change the nullability of a column without updating directly to system tables? |
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 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! |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pcisarWorkflow: jira [ 12233 ] => Firebird [ 15591 ] |
Modified by: @pavel-zotovQA Status: No test |
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.
The text was updated successfully, but these errors were encountered: