Date: 2017-10-19 12:58:54 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.27.5 (Jul2017-SP1)
CC: @njnes

Last updated: 2017-12-14 14:46:05 +0100

Comment 25764

Date: 2017-10-19 12:58:54 +0200
From: Martin van Dinther <<martin.van.dinther>>

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

Dropping a system schema such as profiler or json is possible.
System schemas shouldn't be allowed to be dropped, as they are created and needed by the system to operate correctly.

Note: schema "bam" is marked as being a system schema, but is in fact not a real system schema, as it is not needed by the system. It is an application specific schema. An optional extension to the system.

Reproducible: Always

Steps to Reproduce:

1. Start mserver5 (MonetDB 5 server v11.27.8)
2. Start mclient
3. Execute SQL:
   select name, authorization, owner from sys.schemas where system and name NOT IN ('bam') order by name;
   set schema profiler;
   select current_schema;
   drop schema profiler;
   set schema json;
   select current_schema;
   drop schema profiler restrict;
   select name, authorization, owner from sys.schemas where system and name NOT IN ('bam') order by name;
   set schema profiler;
   set schema tmp;
   select current_schema;
   drop schema json restrict;
   select name, authorization, owner from sys.schemas where system and name NOT IN ('bam') order by name;
   set schema json;
   set schema sys;
   select current_schema;
   drop schema tmp restrict;
   set schema tmp;
   drop schema sys restrict;
   select name, authorization, owner from sys.schemas where system and name NOT IN ('bam') order by name;

Actual Results:

bash-4.4$ mclient -p41000
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.27.8 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>select name, authorization, owner from sys.schemas where system and name NOT IN ('bam') order by name;
+----------+---------------+-------+
| name | authorization | owner |
+==========+===============+=======+
| json | 3 | 3 |
| profiler | 3 | 3 |
| sys | 2 | 3 |
| tmp | 2 | 3 |
+----------+---------------+-------+
4 tuples (2.709ms)
sql>set schema profiler;
auto commit mode: on
sql>select current_schema;
+----------+
| L2 |
+==========+
| profiler |
+----------+
1 tuple (1.115ms)
sql>drop schema profiler;
DROP SCHEMA: cannot drop current schema
sql>set schema json;
auto commit mode: on
sql>select current_schema;
+------+
| L2 |
+======+
| json |
+------+
1 tuple (1.054ms)
sql>drop schema profiler restrict;
operation successful (11.575ms)
sql>select name, authorization, owner from sys.schemas where system and name NOT IN ('bam') order by name;
+------+---------------+-------+
| name | authorization | owner |
+======+===============+=======+
| json | 3 | 3 |
| sys | 2 | 3 |
| tmp | 2 | 3 |
+------+---------------+-------+
3 tuples (2.634ms)
sql>set schema profiler;
Schema (profiler) missing
sql>set schema tmp;
auto commit mode: on
sql>select current_schema;
+------+
| L2 |
+======+
| tmp |
+------+
1 tuple (1.009ms)
sql>drop schema json restrict;
operation successful (14.841ms)
sql>select name, authorization, owner from sys.schemas where system and name NOT IN ('bam') order by name;
+------+---------------+-------+
| name | authorization | owner |
+======+===============+=======+
| sys | 2 | 3 |
| tmp | 2 | 3 |
+------+---------------+-------+
2 tuples (2.732ms)
sql>set schema json;
Schema (json) missing
sql>set schema sys;
auto commit mode: on
sql>select current_schema;
+------+
| L2 |
+======+
| sys |
+------+
1 tuple (0.991ms)
sql>drop schema tmp restrict;
DROP SCHEMA: access denied for 'tmp'
sql>set schema tmp;
auto commit mode: on
sql>drop schema sys restrict;
DROP SCHEMA: access denied for 'sys'
sql>select name, authorization, owner from sys.schemas where system and name NOT IN ('bam') order by name;
+------+---------------+-------+
| name | authorization | owner |
+======+===============+=======+
| sys | 2 | 3 |
| tmp | 2 | 3 |
+------+---------------+-------+
2 tuples (2.595ms)
sql>

Expected Results:

Dropping schema profiler or json should return an error, just like the case when trying to drop schema sys or tmp.

Note: The keyword restrict in "drop schema profiler restrict" is recognised but NOT enforced. See sentence:
The drop_action option is supported for compatibility with the SQL standard, however it is not implemented yet.
on https://www.monetdb.org/Documentation/SQLreference/Schema

Comment 25769

Date: 2017-10-19 20:04:04 +0200
From: MonetDB Mercurial Repository <>

Changeset fc1d11e132eb made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

partial fix of bug #6436 (ie do not overwrite the argument list of a function description)
fix bug #6437, ie do not drop systems schemas (later we should remove the system flag from the bam schema)

Comment 25857

Date: 2017-11-09 14:22:14 +0100
From: MonetDB Mercurial Repository <>

Changeset d4c059ab34ba made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Add missing test for bug #6437

Comment 25858

Date: 2017-11-09 16:15:29 +0100
From: MonetDB Mercurial Repository <>

Changeset 0c634ffa3285 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Moved test drop_system_schema.Bug-6437 to be the first test.
Strangly the test output is differently when it is run as first test or standalone.
The fix for bug #6437 is not yet 100% robust/complete.
Also added a test to check that the system schemas: sys, tmp, json and profiler
exist after test drop_system_schema.Bug-6437 has run (and the server is restarted).

Comment 25861

Date: 2017-11-09 18:22:29 +0100
From: Martin van Dinther <<martin.van.dinther>>

ReOpened this issue as it appears that when a server is started for a new database the first time, the problem is still reproducable.
This occurs by running the test drop_system_schema.Bug-6437 as the first test.
Only if the server is shutdown and than restarted the behavior is correct.

Comment 25867

Date: 2017-11-12 14:35:35 +0100
From: @njnes

added a trigger on the update of the system table schemas. This resets the internal c-structures to the values stored in the db.

Comment 25868

Date: 2017-11-12 14:37:36 +0100
From: MonetDB Mercurial Repository <>

Changeset b72a963da690 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

fixed bug #6437, ie added trigger to update the in memory c-structures, which
are used to (disallow) dropping of these system schemas.