msqldump generates incorrect syntax ON UPDATE (null)

[monetdb-team]

Date: 2015-10-27 16:35:03 +0100
From: @yzchang
To: clients devs <>
Version: -- development
CC: @yzchang

Last updated: 2020-11-13 08:51:18 +0100

Comment 21412

Date: 2015-10-27 16:35:03 +0100
From: @yzchang

Run the following queries in mclient:

CREATE TABLE bar (name CHAR(8) PRIMARY KEY);
CREATE TABLE foo (id INTEGER, barname CHAR(8));
ALTER TABLE foo ADD CONSTRAINT "fname" FOREIGN KEY ("barname") REFERENCES bar ("name") ON DELETE CASCADE;
\D

The final '\D' command will produce output like:

-- msqldump dump database Tue Oct 27 16:33:41 2015
-- MonetDB v11.21.10 (unreleased), 'jul2015'
START TRANSACTION;
SET SCHEMA "sys";
CREATE TABLE "sys"."bar" (
"name" CHAR(8) NOT NULL,
CONSTRAINT "bar_name_pkey" PRIMARY KEY ("name")
);
CREATE TABLE "sys"."foo" (
"id" INTEGER,
"barname" CHAR(8)
);
ALTER TABLE "sys"."foo" ADD CONSTRAINT "fname" FOREIGN KEY ("barname") REFERENCES "sys"."bar" ("name") ON DELETE CASCADE ON UPDATE (null);
COMMIT;

At the end of this output, the "ON UPDATE (null)" is of incorrect syntax. It should be "ON UPDATE SET NULL".

Comment 21413

Date: 2015-10-27 16:38:17 +0100
From: MonetDB Mercurial Repository <>

Changeset 09e0acbef025 made by Jennie Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for Bug #3839

Comment 21414

Date: 2015-10-27 17:57:45 +0100
From: @yzchang

Ok, after some debugging, found why "ON UPDATE (null)" is printed: the "action" denoted in sys.keys for the column "fname" is 1:

sql>select * from sys.keys where name = 'fname';
+------+----------+------+-------+------+--------+
| id | table_id | type | name | rkey | action |
+======+==========+======+=======+======+========+
| 7380 | 7379 | 2 | fname | 7373 | 1 |
+------+----------+------+-------+------+--------+

Therefore, in dump_foreign_keys() (see dump.c), the value of the variable "action" is 1. Then, in the 'if' statement at line 380:

         if (action > 0 &&
             (on_update = (action >> 8) & 255) < NR_ACTIONS &&
             on_update != 2     /* RESTRICT -- default */)
             mnstr_printf(toConsole, " ON UPDATE %s",
                      actions[on_update]);

the conditions evaluate to TRUE, and on_update gets the value 0, which means the value of "actions[on_update]" is 0.
Hence, finally, a 'null' string is printed to "toConsole".

But I don't know if "action" contains correct value here, and what we should do in case of on_update == 0.

Comment 21415

Date: 2015-10-28 09:10:49 +0100
From: MonetDB Mercurial Repository <>

Changeset 191d9077e78b 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=191d9077e78b

Changeset description:

Fix for bug #3839.

Comment 21450

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

Jul2015 SP1 has been released.

Comment 21526

Date: 2015-11-16 15:59:51 +0100
From: MonetDB Mercurial Repository <>

Changeset b4182120820a made by Jennie Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Approve output of fixed Bug #3839

Comment 28262

Date: 2020-11-13 08:51:18 +0100
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=96e4df23b1d9

Changeset description:

Move test for bug #3839 to testdb.