Commented by: Sean Leyne (seanleyne)

A system domain should not be used by "user" structures, they are only intended for use for/by system structures. The definition of the domain could be changed in a later ODS update.

A comparable/synonym user domain should be created.

Commented by: rudi feijo (rudibr)

Hello Sean,

I understand this is not a valid operation.

But what should be done if this happens to occur?

Sorry if this is the wrong channel to ask, but is there a workaround I could use to delete the procedure?

This error led one of our databases to become unusable. The procedure was created , cant be modifed or deleted, and is using characters not supported by the client's connection charset (which cannot be changed). This causes any connection to be refused by that client. (cannot transliterate).
Is there anyway to delete the procedure and avoid having to completely recreate the database?

Commented by: Sean Leyne (seanleyne)

1 - I might try to delete the appropriate entries from the RDB$ tables (after making a backup of the db ;-]), or
2 - see if other groups like IBSurgeon might be able to help, or
3 - use tool to extract schema to script, create new database without the offending SP and then use data pump tool to migrate the data

Commented by: rudi feijo (rudibr)

Thanks for the feedback.

I seem to have worked around it, even tough it seems like risky business, I will post it since I opened the ticket and I saw this same problem being posted a few years ago.

1. backed up db
2. created a user domain with the same definitions as the offending system domain (CREATE DOMAIN RDB_SOURCE AS BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS)
3. updated all objects using the system domain to use the new user domain (update RDB$RELATION_FIELDS set RDB$FIELD_SOURCE = 'RDB_SOURCE' where RDB$FIELD_SOURCE = 'RDB$SOURCE')
4. deleted the procedure.
5. now the original system domain isnt being used anywhere else, it seems to have been auto-deleted, I guess its proposital behavior.
6. created a user domain with same definition and same name as the system domain (CREATE DOMAIN RDB$SOURCE AS BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS)
7. updated all objects using the user domain to use the new user domain (update RDB$RELATION_FIELDS set RDB$FIELD_SOURCE = 'RDB$SOURCE' where RDB$FIELD_SOURCE = 'RDB_SOURCE')
8. deleted the temporary user domain RDB_SOURCE
9. updated RDB$FIELDS table to set the new user domain as a system domain (update RDB$FIELDS set RDB$SYSTEM_FLAG = 1 where RDB$FIELD_NAME = 'RDB$SOURCE')

Just for the record, I never have messed with system objects before, nor do I plan to in the future, or would advise anyone to do it too :p.

Commented by: Sean Leyne (seanleyne)

Rudi,

Re #⁠5, I am not aware of any "auto-delete" feature for domain, so I am surprised that it "disappeared"

Does this mean that the case can be closed, or is there an issue which should still be address (i.e. should not be able to create user SPs which use system domains?)

Commented by: @asfernandes

The problem is not present in trunk. I believe it was fixed in the DDL refactor, but may be something else.