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

AV in engine when preparing query against dropping table [CORE3557] #3913

Closed
firebird-issue-importer opened this issue Jul 16, 2011 · 18 comments
Closed

Comments

@firebird-issue-importer

Submitted by: @hvlad

Block progress on CORE3579

Attachments:
core_isql.zip

Commits: 0aede9b ed6d43f 34e13e6 13c6c85 7833f5e 0ed1fad d760408 6522004 f414686 ea661b3 757ea1e

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 16, 2011

Commented by: @hvlad

To reproduce:

1. Prepare database and disconnect

firebird\bin>isql
create database 'core-3557.fdb';
create table t (id int);
exit;

2. First session: drop table and do not commit transaction
firebird\bin>isql core-3557.fdb
set autoddl off;
drop table t;

3. Second session: run query against dropping table
firebird\bin>isql core-3557.fdb
set autoddl off;
commit;
insert into t values (1);

Statement failed, SQLSTATE = 08006
connection lost to database

Firebird crashed

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 16, 2011

Commented by: @hvlad

Another test case (requires FB 2.5 or higher)

1.
firebird\bin>isql
create database 'core-3557.fdb';
create table t (id int);
exit;

2.
firebird\bin>isql CORE3557.FDB
set term ^;
execute block as
begin
execute statement 'drop table t';
in autonomous transaction do
execute statement ('insert into t values (1)');
end
^

Statement failed, SQLSTATE = 08006
connection lost to database

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 16, 2011

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 16, 2011

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.1 [ 10333 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 2.0.7 [ 10390 ]

Fix Version: 2.1.5 [ 10420 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 1, 2011

Commented by: @samofatov

The bugfix makes it impossible to drop certain tables with computed fields. Test case was sent privately to Vlad Horsun.

I believe this bugfix also is the cause of CORE3579 regression.

The reason of the problem is that Vlad's code calls MET_scan_relation when relation fields are already deleted.
This fails and DROP TABLE statement fails with it.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 1, 2011

Modified by: @samofatov

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 7, 2011

Commented by: @hvlad

Additional fix is committed. Please check and confirm if it is ok.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 8, 2011

Commented by: @dmitry-starodubov

New fix solves the problem. Thank you.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 8, 2011

Modified by: @hvlad

Link: This issue block progress on CORE3579 [ CORE3579 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 8, 2011

Modified by: @hvlad

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.2 [ 10450 ]

Fix Version: 2.5.1 [ 10333 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 12, 2012

Commented by: @dyemanov

A new regression is found because of this change. Simple test case:

create view v as
select t4.rdb$relation_name
from rdb$relations t1, rdb$relations t2, rdb$relations t3, rdb$relations t4;

-- reconnect

alter view v as
select 0 as rdb$relation_name from rdb$database;

-- no commit here

drop view v;

-- the engine crashes

When MET_scan_relation() is called during DROP, we have the new field list and view BLR out of sync with the RDB$RUNTIME which is modified at the commit time only and thus preserving the original structure.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 12, 2012

Modified by: @dyemanov

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 18, 2012

Commented by: @pavel-zotov

Today isql produced coredump with some of scripts that performed "bulk update" of metadata.
Connect was made via -ch utf8.
Two variant of backtrace in attach (for /opt/firebird/bin/isql and .../bin/.debug/isql.debug).

HTH

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 18, 2012

Modified by: @pavel-zotov

Attachment: core_isql.zip [ 12132 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 25, 2012

Modified by: @dyemanov

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 4, 2012

Commented by: @pavel-zotov

Another sample:

DDL:
#⁠#⁠#⁠#⁠
create or alter view v as select 1 id from rdb$database;
commit;
set term ^; create or alter procedure p as begin end^
set term ;^
commit;
--------
recreate table doc(doc_id int);
-----
set term ^;
create or alter procedure p(a_doc_id integer)
returns (o_value numeric(15,4)) as
begin
o_value = 1;
suspend;
end;
^set term ;^
commit;
--------
create or alter view v as
select
d.doc_id,
cast(sp.o_value as double precision) n
--sp.o_value
from doc d
left join p(1) sp on 1=1;
commit;
quit;

Then let's try to drop both view and procedure using common script:

set autoddl off;
set bail on;
commit;

create or alter view v as select
0 as doc_id
, 0 as o_value
from rdb$database;
----
set term ^;
create or alter procedure p(a_doc_id integer)
returns (o_value numeric(15,4))
AS
begin
suspend;
end;
^set term ;^
----
set term ^;
execute block as begin
execute statement 'drop view v;';
when any do begin end
end^
set term ;^
----
set term ^;
execute block as begin
execute statement 'drop procedure p;';
when any do begin end
end^
set term ;^

The message about dependencies appear instead of successful deletion:
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-cannot delete
-PROCEDURE P
-there are 1 dependencies
After line 31 in file t0_run.sql

If we change the view:

create or alter view v as
select
d.doc_id,
sp.o_value n -- *DELETE* cast from this field
from doc d
left join p(1) sp on 1=1;
commit;

-- no errors occured.
If we create view without join table and SP - no errors also.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 29, 2014

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 29, 2015

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