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

mclient accepts table with repeated constraint which causes crash on insert #3820

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2015-10-09 04:35:33 +0200
From: Doug Service <>
To: SQL devs <>
Version: 11.21.5 (Jul2015)
CC: n848dg

Last updated: 2015-11-03 10:18:37 +0100

Comment 21323

Date: 2015-10-09 04:35:33 +0200
From: Doug Service <>

User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:41.0) Gecko/20100101 Firefox/41.0
Build Identifier:

It is possible to create a table using mclient which has a constraint repeated as in the sample schema provided. Performing an insert on the table will then crash the server with a SIGSEGV. See the following SQL script. Note that if one dumps the invalid database with msqldump and then tries to reload the database into an empty database using mclient, the repeated constraint will be flagged as an error.

CREATE TABLE exchange (
pk_symbol VARCHAR(16) NOT NULL,
name VARCHAR(128) NOT NULL,
CONSTRAINT exchange_pk PRIMARY KEY (pk_symbol)
);

CREATE TABLE entity (
pk_uuid UUID NOT NULL,
name VARCHAR(128) NOT NULL,
industry VARCHAR(64),
category VARCHAR(64),
subcategory VARCHAR(64),
CONSTRAINT entity_pk PRIMARY KEY (pk_uuid)
);

CREATE TABLE instrument (
pk_uuid UUID NOT NULL,
symbol VARCHAR(32) NOT NULL,
fk_exchange VARCHAR(16) NOT NULL,
fk_entity_uuid UUID NOT NULL,
CONSTRAINT instrument_pk PRIMARY KEY (pk_uuid),
-- INCORRECT DOUBLE CONSTRAINT
CONSTRAINT instrument_exchange_fk FOREIGN KEY (fk_exchange) REFERENCES exchange (pk_symbol),
CONSTRAINT instrument_exchange_fk FOREIGN KEY (fk_exchange) REFERENCES exchange (pk_symbol),
CONSTRAINT instrument_entity_fk FOREIGN KEY (fk_entity_uuid) REFERENCES entity (pk_uuid)
);

INSERT INTO exchange (pk_symbol, name) VALUES ('EXCH', 'The exchange');
INSERT INTO entity (pk_uuid, name, industry, category, subcategory) VALUES ('52aaa24f-c9cb-4421-9501-33be20d80dcc', 'NAME', 'XXX', 'YYY', 'ZZZ');

--Crash the server with SIGSEGV
INSERT INTO instrument (pk_uuid, symbol, fk_exchange, fk_entity_uuid) VALUES ('24a2a880-451c-4401-94af-93715ad0baf6', 'ABC', 'EXCH', '52aaa24f-c9cb-4421-9501-33be20d80dcc');

Reproducible: Always

Steps to Reproduce:

1.Load the provided schema into a new database and the server will crash with a SIGSEGV on the last insert statement.

Actual Results:

8233 2015-10-08 15:09:51 MSG dbtst[21580]: loading sql script: 80_udf.sql
8234 2015-10-08 15:09:51 MSG dbtst[21580]: loading sql script: 80_udf_hge.sql
8235 2015-10-08 15:09:51 MSG dbtst[21580]: loading sql script: 90_generator.sql
8236 2015-10-08 15:09:51 MSG dbtst[21580]: loading sql script: 90_generator_hge.sql
8237 2015-10-08 15:09:51 MSG dbtst[21580]: loading sql script: 99_system.sql
8238 2015-10-08 15:22:17 MSG merovingian[796]: database 'dbtst' (21580) was killed by signal SIGSEGV

Expected Results:

mclient should stop processing and emit an error message when it sees the repeated constraint.

dougs@quant:~$ mserver5 --version
MonetDB 5 server v11.21.5 "Jul2015" (64-bit, 64-bit oids, 128-bit integers)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 16.3GiB available memory, 12 available cpu cores
Libraries:
libpcre: 8.35 2014-04-04 (compiled with 8.35)
openssl: OpenSSL 1.0.1f 6 Jan 2014 (compiled with OpenSSL 1.0.1f 6 Jan 2014)
libxml2: 2.9.2 (compiled with 2.9.2)
Compiled by: root@dev.monetdb.org (x86_64-pc-linux-gnu)
Compilation: gcc -O3 -fomit-frame-pointer -pipe -g -D_FORTIFY_SOURCE=2
Linking : /usr/bin/ld -m elf_x86_64

Comment 21333

Date: 2015-10-14 13:09:23 +0200
From: MonetDB Mercurial Repository <>

Changeset abb0465976b8 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=abb0465976b8

Changeset description:

Check whether foreign key was already added.
This should fix bug #3820 by making the precondition for the crash
impossible.

Comment 21334

Date: 2015-10-14 13:10:07 +0200
From: @sjoerdmullender

Niels, can you check my fix?

Comment 21449

Date: 2015-11-03 10:18:37 +0100
From: @sjoerdmullender

Jul2015 SP1 has been released.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant