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:
Start mserver5 (MonetDB 5 server v11.27.8)
Start mclient
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
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)
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.
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:
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:
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:
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:
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:
The text was updated successfully, but these errors were encountered: