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

Dropping FK on GTT crashes server [CORE4212] #4537

Closed
firebird-automations opened this issue Sep 9, 2013 · 13 comments
Closed

Dropping FK on GTT crashes server [CORE4212] #4537

firebird-automations opened this issue Sep 9, 2013 · 13 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Andrei Kireev (andreik)

Relate to CORE4214

Here is a script which creates some permanent and global temporary tables:

============================================================

SET NAMES WIN1251;
SET SQL DIALECT 3;
CREATE DATABASE 'put_your_database_name'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET WIN1251;

CREATE DOMAIN dintkey
AS INTEGER NOT NULL
CHECK (VALUE > 0);

CREATE DOMAIN dtext255
AS VARCHAR(255) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

CREATE DOMAIN dtext20
AS VARCHAR(20) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

CREATE DOMAIN dboolean
AS SMALLINT
DEFAULT 0
CHECK ((VALUE IS NULL) OR (VALUE IN (0, 1)));

CREATE DOMAIN dboolean_notnull
AS SMALLINT
DEFAULT 0
NOT NULL
CHECK (VALUE IN (0, 1));

CREATE DOMAIN dblobtext80_1251
AS BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET win1251;

CREATE DOMAIN dname
AS VARCHAR(60) CHARACTER SET WIN1251 NOT NULL COLLATE PXW_CYRL;

CREATE DOMAIN dclassname
AS VARCHAR(40) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

CREATE DOMAIN dtext60
AS VARCHAR(60) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

CREATE DOMAIN dinteger_notnull
AS INTEGER NOT NULL;

CREATE DOMAIN dinteger
AS INTEGER;

CREATE DOMAIN dforeignkey
AS INTEGER;

CREATE TABLE at_namespace (
id dintkey,
name dtext255 NOT NULL UNIQUE,
caption dtext255,
filename dtext255,
filetimestamp TIMESTAMP,
version dtext20 DEFAULT '1.0.0.0' NOT NULL,
dbversion dtext20,
optional dboolean_notnull DEFAULT 0,
internal dboolean_notnull DEFAULT 1,
comment dblobtext80_1251,
settingruid VARCHAR(21),

CONSTRAINT at_pk_namespace PRIMARY KEY (id)
);

CREATE GLOBAL TEMPORARY TABLE at_namespace_file (
filename dtext255,
filetimestamp TIMESTAMP,
filesize dinteger,
name dtext255 NOT NULL UNIQUE,
caption dtext255,
version dtext20,
dbversion dtext20,
optional dboolean_notnull DEFAULT 0,
internal dboolean_notnull DEFAULT 1,
comment dblobtext80_1251,
xid dinteger,
dbid dinteger,

CONSTRAINT at_pk_namespace_file PRIMARY KEY (filename)
)
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE at_namespace_sync (
namespacekey dforeignkey,
filename dtext255,
operation CHAR(2) DEFAULT ' ' NOT NULL,

CONSTRAINT at_fk_namespace_sync_nsk
FOREIGN KEY (namespacekey) REFERENCES at_namespace (id),
CONSTRAINT at_fk_namespace_sync_fn
FOREIGN KEY (filename) REFERENCES at_namespace_file (filename)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT at_chk_namespace_sync_op
CHECK (operation IN (' ', '< ', '> ', '>>', '<<', '==', '=>', '<=', '! ', '? '))
)
ON COMMIT DELETE ROWS;

============================================================

Next statement will crash server:

alter table at_namespace_sync drop constraint at_fk_namespace_sync_nsk

Confirmed for embedded and client-server versions.

Commits: cf4e7ec f6ba5b8 FirebirdSQL/fbt-repository@cddcab0 FirebirdSQL/fbt-repository@3f9db85

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Foreign key constraint from GTT to permanent table should be prohibited.
So, real bug is that such constraint is created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

@vlad,

It seems reasonable that a GTT should have a dependency on a permanent table. (Which is not how your comment reads)

The reverse (a permanent table depending on GTT) or a GTT depending on another GTT, however, should most certainly be prevented.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

It is documented since introducing GTT in v2.1 that constraints between temporary and permanent tables is forbidden.
This requirement is per SQL standard.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Below is simple example of how to reproduce bug without creating forbidden things (which will be subject of another ticket):

SQL> create database 'core4212.fdb' default character set win1251;
SQL> create global temporary table t1 (id varchar(8) not null primary key);
SQL> create global temporary table t2 (id varchar(8));
SQL> alter table t2 add constraint t2_fk foreign key (id) references t1 (id);
SQL>
SQL> connect core4212.fdb;
Commit current transaction (y/n)?y
Committing.
Database: core4212.fdb
SQL> alter table t2 drop constraint t2_fk;
Statement failed, SQLSTATE = 08006
connection lost to database
Statement failed, SQLSTATE = 08006
Error writing data to the connection.

The key of the issue is:
a) foreign key constraint at GTT
b) at least one field in FK should be [VAR]CHAR with some international charset
c) the DROP CONSTRAINT statement should be issued in a such attachment where corresponding relation was never referenced and missed in metadata cache (therefore i do reconnect in example above)

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Link: This issue relate to CORE4214 [ CORE4214 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Fixed in v2.5.3 and v3.0 alpha 2

v2.1 contains same code but it seems not affected because of somewhat different metadata loading.
The patch could be backported if strongly needed.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.3 [ 10461 ]

Fix Version: 3.0 Alpha 2 [ 10560 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Version: 3.0 Alpha 1 [ 10331 ]

Version: 2.5.2 Update 1 [ 10521 ]

Version: 2.5.2 [ 10450 ]

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.5.3 [ 10461 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Clarification on SQL Standard:

The spec only allows permanent tables to reference permanent tables, and GTTs to reference GTTs.

So, GTT can have dependencies/references, but only to another GTT.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Just for info, there is simple workaround : make any DML query with detail GTT before attempt to drop FK.
It ensure that GTT's metadata is loaded by the engine.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

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