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 or drop procedure - "there are x dependencies." on FB30 [CORE6258] #6500

Open
firebird-automations opened this issue Feb 27, 2020 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Attila Molnár (e_pluribus_unum)

exception on commit trying ro ALTER or DROP a procedure.

create or alter procedure GL_USER (
CSAK_EHHEZ_A_PELDANYHOZ varchar(1))
returns (
ID integer,
UNEV varchar(30),
TNEV varchar(70),
GL_ROLE_ID integer,
EMAIL varchar(100))
AS
declare variable conn xszoveg;
declare variable pass xvar30;
declare variable gl_pldid integer;
begin
SELECT c.conn
FROM gl_getdbconn('Libra3s') c
INTO :conn;
IF (conn IS NULL)
THEN EXECUTE PROCEDURE gl_exception('gl_sql_run.conn.null');

SELECT rdb$get_context('USER_SESSION', 'PASSWORD_USER')
FROM dual
INTO :pass;
IF (pass IS NULL)
THEN EXECUTE PROCEDURE gl_exception('gl_sql_run.pass.null');

select ertek from xpref_olvas('GL_PLDID')
into gl_pldid;

for execute statement 'select http://xu.id, xu.unev, xu.tnev, xu.gl_role_id, xu.email from xuser xu ' ||
iif(:csak_ehhez_a_peldanyhoz is not distinct from 'I', 'where exists(select 1 from xuserpld up where up.xuser_id = http://xu.id and up.xpld_id = ' || :gl_pldid || ')', '')
on external :conn
as user current_user
role current_role
password :pass
into id, unev, tnev, :gl_role_id, :email do
begin
suspend;
end
end

drop procedure gl_user

select *
from rdb$dependencies d
where d.rdb$dependent_name in ('GL_USER', 'RDB$112355', 'RDB$112354', 'RDB$112356') or
d.rdb$depended_on_name in ('GL_USER', 'RDB$112355', 'RDB$112354', 'RDB$112356')

"Cannot commit transaction:
This operation is not defined for system tables.
unsuccessful metadata update.
cannot delete.
DOMAIN RDB$112355.
there are 2 dependencies."

The reported dependency is wrong, the procedure do not depend on the named domain.

I can send you the database.

This is a stopper for us, until this is not fixed, we cannot upgrade to FB30.

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Might be related to CORE4844?

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Also might be related to CORE6257?

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Are you committing between creating the SP and dropping it?

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

The procedure already exists with empty body, and the goal is to alter and fill the body with create or alter procedure GL_USER.
The ALTER fails with the "cannot delete"

I just tested with DROP and it also fails, but it is not part of our process.
Our process (database schema update) :
- create or alter evrey proc and view with empty body
- then alter them with body,
In this way we don't have to run create or alter commands in dependecy order.
This method works fine on FB25.

Previously a view was depended on this proc, but the view body is also "empty" currenty, that is why it may link to CORE6257.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

You have not answered my question... are you committing between *any/separate* changes to the SPs?

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Yes, there is a commit after every DDL command.

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Found a workaround, the proceure out parameters are defined with domains

returns (
ID XIDN,
UNEV XVAR30N,
TNEV XVAR70N,
GL_ROLE_ID XID,
EMAIL XVAR100)

@firebird-automations
Copy link
Collaborator Author

Modified by: Attila Molnár (e_pluribus_unum)

priority: Blocker [ 1 ] => Major [ 3 ]

@EPluribusUnum
Copy link

This is still an issue (Tested with FB30 and FB40)

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

2 participants