problem with foreign key constrain violation
Hi MonetDB, I think I'm really confused or I found a bug. Please have a look below. I'm expecting a 'aggregation_book_contact_id_fkey' violation, since that row is clearly missing. But I'm getting a 'aggregation_book.aggregation_book_publisher_id_fkey' violation. Is it me or is this error message wrong? sql>\d sys.aggregation_book CREATE TABLE "sys"."aggregation_book" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_6205", "isbn" VARCHAR(9) NOT NULL, "name" VARCHAR(255) NOT NULL, "pages" INTEGER NOT NULL, "rating" DOUBLE NOT NULL, "price" DECIMAL(6,2) NOT NULL, "contact_id" INTEGER NOT NULL, "publisher_id" INTEGER NOT NULL, "pubdate" DATE NOT NULL, CONSTRAINT "aggregation_book_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "aggregation_book_contact_id_fkey" FOREIGN KEY ("contact_id") REFERENCES "sys"."aggregation_author" ("id"), CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY ("publisher_id") REFERENCES "sys"."aggregation_publisher" ("id") ); sql>select * from aggregation_publisher; +------+--------+------------+ | id | name | num_awards | +======+========+============+ | 1 | Apress | 3 | +------+--------+------------+ 1 tuple (0.588ms) sql>select * from aggregation_author; +----+------+-----+ | id | name | age | +====+======+=====+ +----+------+-----+ 0 tuples (0.570ms) sql>INSERT INTO "aggregation_book" ("id", "isbn", "name", "pages", more>"rating", "price", "contact_id", "publisher_id", "pubdate") VALUES (1, more>'159059725', 'The Definitive Guide to Django: Web Development Done more>Right', 447, 4.5, '30.00', 1, 1, '2007-12-06'); INSERT INTO: FOREIGN KEY constraint 'aggregation_book.aggregation_book_publisher_id_fkey' violated -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
Hi MonetDB people, again I'm confused. Please have a look below. Maybe my SQL language knowledge is not sufficient, but I'm expecting one row for the last statement. Any idea? Btw, I'm using Feb2013-sp1. sql>\d auth_user CREATE TABLE "sys"."auth_user" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_5989", "password" VARCHAR(128) NOT NULL, "last_login" TIMESTAMP NOT NULL, "is_superuser" BOOLEAN NOT NULL, "username" VARCHAR(30) NOT NULL, "first_name" VARCHAR(30) NOT NULL, "last_name" VARCHAR(30) NOT NULL, "email" VARCHAR(75) NOT NULL, "is_staff" BOOLEAN NOT NULL, "is_active" BOOLEAN NOT NULL, "date_joined" TIMESTAMP NOT NULL, CONSTRAINT "auth_user_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "auth_user_username_unique" UNIQUE ("username") ); sql>SELECT id,username FROM auth_user; +------+----------+ | id | username | +======+==========+ | 8 | joe | +------+----------+ 1 tuple (1.183ms) sql>SELECT id,username FROM auth_user WHERE username='joe'; +------+----------+ | id | username | +======+==========+ | 8 | joe | | 8 | joe | +------+----------+ 2 tuples (0.907ms)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 2013-04-23 14:30, Gijs Molenaar wrote:
Hi MonetDB people,
again I'm confused. Please have a look below. Maybe my SQL language knowledge is not sufficient, but I'm expecting one row for the last statement. Any idea? Btw, I'm using Feb2013-sp1.
Can you try Feb2013-SP2 from http://dev.monetdb.org/downloads/testing/? I can't quickly reproduce the problem.
sql>\d auth_user CREATE TABLE "sys"."auth_user" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_5989", "password" VARCHAR(128) NOT NULL, "last_login" TIMESTAMP NOT NULL, "is_superuser" BOOLEAN NOT NULL, "username" VARCHAR(30) NOT NULL, "first_name" VARCHAR(30) NOT NULL, "last_name" VARCHAR(30) NOT NULL, "email" VARCHAR(75) NOT NULL, "is_staff" BOOLEAN NOT NULL, "is_active" BOOLEAN NOT NULL, "date_joined" TIMESTAMP NOT NULL, CONSTRAINT "auth_user_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "auth_user_username_unique" UNIQUE ("username") );
sql>SELECT id,username FROM auth_user; +------+----------+ | id | username | +======+==========+ | 8 | joe | +------+----------+ 1 tuple (1.183ms)
sql>SELECT id,username FROM auth_user WHERE username='joe'; +------+----------+ | id | username | +======+==========+ | 8 | joe | | 8 | joe | +------+----------+ 2 tuples (0.907ms)
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
- -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.13 (GNU/Linux) Comment: Using GnuPG with undefined - http://www.enigmail.net/ iQCVAwUBUXaIjj7g04AjvIQpAQKekAP9GkzgiIa/dmCDff1c/5vVUMh0/MTwxJzD DShIDBJXCo9us+xroNXhxKKPgXKrxu+7Kvc5gg1AfSjwYb1wdnbK97kK9tVdEkdV 3wAgCYCqE18HOQGMh2H9c59PfL1Lvw9x7CEkYUI1VoPk1oX75VYTHJL4ph6qIXBt rpeIjyP76Ps= =BkfI -----END PGP SIGNATURE-----
On 23/04/13 15:11, Sjoerd Mullender wrote:
On 2013-04-23 14:30, Gijs Molenaar wrote:
Hi MonetDB people,
again I'm confused. Please have a look below. Maybe my SQL language knowledge is not sufficient, but I'm expecting one row for the last statement. Any idea? Btw, I'm using Feb2013-sp1.
Can you try Feb2013-SP2 from http://dev.monetdb.org/downloads/testing/? I can't quickly reproduce the problem.
This did not solve the problem. The same as for the other issue, I can't replicate the problem in isolation also.
sql>\d auth_user CREATE TABLE "sys"."auth_user" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_5989", "password" VARCHAR(128) NOT NULL, "last_login" TIMESTAMP NOT NULL, "is_superuser" BOOLEAN NOT NULL, "username" VARCHAR(30) NOT NULL, "first_name" VARCHAR(30) NOT NULL, "last_name" VARCHAR(30) NOT NULL, "email" VARCHAR(75) NOT NULL, "is_staff" BOOLEAN NOT NULL, "is_active" BOOLEAN NOT NULL, "date_joined" TIMESTAMP NOT NULL, CONSTRAINT "auth_user_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "auth_user_username_unique" UNIQUE ("username") );
sql>SELECT id,username FROM auth_user; +------+----------+ | id | username | +======+==========+ | 8 | joe | +------+----------+ 1 tuple (1.183ms)
sql>SELECT id,username FROM auth_user WHERE username='joe'; +------+----------+ | id | username | +======+==========+ | 8 | joe | | 8 | joe | +------+----------+ 2 tuples (0.907ms)
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
Please file a bug report with all information / definitions / scripts / data required to reproduce the problem. Thanks! Stefan On Tue, Apr 23, 2013 at 12:51:08PM +0200, Gijs Molenaar wrote:
Hi MonetDB,
I think I'm really confused or I found a bug. Please have a look below. I'm expecting a 'aggregation_book_contact_id_fkey' violation, since that row is clearly missing. But I'm getting a 'aggregation_book.aggregation_book_publisher_id_fkey' violation. Is it me or is this error message wrong?
sql>\d sys.aggregation_book CREATE TABLE "sys"."aggregation_book" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_6205", "isbn" VARCHAR(9) NOT NULL, "name" VARCHAR(255) NOT NULL, "pages" INTEGER NOT NULL, "rating" DOUBLE NOT NULL, "price" DECIMAL(6,2) NOT NULL, "contact_id" INTEGER NOT NULL, "publisher_id" INTEGER NOT NULL, "pubdate" DATE NOT NULL, CONSTRAINT "aggregation_book_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "aggregation_book_contact_id_fkey" FOREIGN KEY ("contact_id") REFERENCES "sys"."aggregation_author" ("id"), CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY ("publisher_id") REFERENCES "sys"."aggregation_publisher" ("id") );
sql>select * from aggregation_publisher; +------+--------+------------+ | id | name | num_awards | +======+========+============+ | 1 | Apress | 3 | +------+--------+------------+ 1 tuple (0.588ms)
sql>select * from aggregation_author; +----+------+-----+ | id | name | age | +====+======+=====+ +----+------+-----+ 0 tuples (0.570ms)
sql>INSERT INTO "aggregation_book" ("id", "isbn", "name", "pages", more>"rating", "price", "contact_id", "publisher_id", "pubdate") VALUES (1, more>'159059725', 'The Definitive Guide to Django: Web Development Done more>Right', 447, 4.5, '30.00', 1, 1, '2007-12-06'); INSERT INTO: FOREIGN KEY constraint 'aggregation_book.aggregation_book_publisher_id_fkey' violated
-- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
Hi Stefan, I've tried, but it is a bit hard/currently impossible to reproduce these problem in isolation. I'm running the Django test suite with MonetDB as a backend, which does a huge amount of operations on the database. Then after a while this kind of problems start to pop up. I can give you instructions on how to run the django/djonet test suite if you want. - Gijs On 23/04/13 15:07, Stefan Manegold wrote:
Please file a bug report with all information / definitions / scripts / data required to reproduce the problem.
Thanks!
Stefan
On Tue, Apr 23, 2013 at 12:51:08PM +0200, Gijs Molenaar wrote:
Hi MonetDB,
I think I'm really confused or I found a bug. Please have a look below. I'm expecting a 'aggregation_book_contact_id_fkey' violation, since that row is clearly missing. But I'm getting a 'aggregation_book.aggregation_book_publisher_id_fkey' violation. Is it me or is this error message wrong?
sql>\d sys.aggregation_book CREATE TABLE "sys"."aggregation_book" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_6205", "isbn" VARCHAR(9) NOT NULL, "name" VARCHAR(255) NOT NULL, "pages" INTEGER NOT NULL, "rating" DOUBLE NOT NULL, "price" DECIMAL(6,2) NOT NULL, "contact_id" INTEGER NOT NULL, "publisher_id" INTEGER NOT NULL, "pubdate" DATE NOT NULL, CONSTRAINT "aggregation_book_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "aggregation_book_contact_id_fkey" FOREIGN KEY ("contact_id") REFERENCES "sys"."aggregation_author" ("id"), CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY ("publisher_id") REFERENCES "sys"."aggregation_publisher" ("id") );
sql>select * from aggregation_publisher; +------+--------+------------+ | id | name | num_awards | +======+========+============+ | 1 | Apress | 3 | +------+--------+------------+ 1 tuple (0.588ms)
sql>select * from aggregation_author; +----+------+-----+ | id | name | age | +====+======+=====+ +----+------+-----+ 0 tuples (0.570ms)
sql>INSERT INTO "aggregation_book" ("id", "isbn", "name", "pages", more>"rating", "price", "contact_id", "publisher_id", "pubdate") VALUES (1, more>'159059725', 'The Definitive Guide to Django: Web Development Done more>Right', 447, 4.5, '30.00', 1, 1, '2007-12-06'); INSERT INTO: FOREIGN KEY constraint 'aggregation_book.aggregation_book_publisher_id_fkey' violated
-- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
On Tue, Apr 23, 2013 at 04:43:53PM +0200, Gijs Molenaar wrote:
Hi Stefan,
I've tried, but it is a bit hard/currently impossible to reproduce these problem in isolation. I'm running the Django test suite with MonetDB as a backend, which does a huge amount of operations on the database. Then after a while this kind of problems start to pop up. I can give you instructions on how to run the django/djonet test suite if you want.
Gijs, Stefan I found a bug in concurrent use which may very well also be your problem. The easiest way to reproduce a problem (related to this bug), is to start a mclient and do a start transaction. Then run your scripts which give problems (should have updates in it). This will cause problems. Fix will be checked into Feb2013 today.. Niels
- Gijs
On 23/04/13 15:07, Stefan Manegold wrote:
Please file a bug report with all information / definitions / scripts / data required to reproduce the problem.
Thanks!
Stefan
On Tue, Apr 23, 2013 at 12:51:08PM +0200, Gijs Molenaar wrote:
Hi MonetDB,
I think I'm really confused or I found a bug. Please have a look below. I'm expecting a 'aggregation_book_contact_id_fkey' violation, since that row is clearly missing. But I'm getting a 'aggregation_book.aggregation_book_publisher_id_fkey' violation. Is it me or is this error message wrong?
sql>\d sys.aggregation_book CREATE TABLE "sys"."aggregation_book" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_6205", "isbn" VARCHAR(9) NOT NULL, "name" VARCHAR(255) NOT NULL, "pages" INTEGER NOT NULL, "rating" DOUBLE NOT NULL, "price" DECIMAL(6,2) NOT NULL, "contact_id" INTEGER NOT NULL, "publisher_id" INTEGER NOT NULL, "pubdate" DATE NOT NULL, CONSTRAINT "aggregation_book_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "aggregation_book_contact_id_fkey" FOREIGN KEY ("contact_id") REFERENCES "sys"."aggregation_author" ("id"), CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY ("publisher_id") REFERENCES "sys"."aggregation_publisher" ("id") );
sql>select * from aggregation_publisher; +------+--------+------------+ | id | name | num_awards | +======+========+============+ | 1 | Apress | 3 | +------+--------+------------+ 1 tuple (0.588ms)
sql>select * from aggregation_author; +----+------+-----+ | id | name | age | +====+======+=====+ +----+------+-----+ 0 tuples (0.570ms)
sql>INSERT INTO "aggregation_book" ("id", "isbn", "name", "pages", more>"rating", "price", "contact_id", "publisher_id", "pubdate") VALUES (1, more>'159059725', 'The Definitive Guide to Django: Web Development Done more>Right', 447, 4.5, '30.00', 1, 1, '2007-12-06'); INSERT INTO: FOREIGN KEY constraint 'aggregation_book.aggregation_book_publisher_id_fkey' violated
-- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
participants (4)
-
Gijs Molenaar -
Niels Nes -
Sjoerd Mullender -
Stefan Manegold