Reference giulia Mon, 02/24/2020 - 11:43

Here you will find some man pages for MonetDB applications

MonetDB Client Applications

MonetDB Client Applications giulia Mon, 02/24/2020 - 15:03


mclient sjoerd Tue, 04/26/2011 - 12:34


mclient − the MonetDB command-line tool


mclient [ options ] [ file or database [ file ... ] ]
mclient −−help


MonetDB is a database management system that is developed from a main-memory perspective with use of a fully decomposed storage model, automatic index management, extensibility of data types and search accelerators, and an SQL front end.

Mclient is the command-line interface to the MonetDB server.

If the −−statement=query (−s query) option is given, the query is executed. If any files are listed after the options, queries are read from the files and executed. The special filename refers to standard input. Note that if there is both a −−statement option and filename arguments, the query given with −−statement is executed first. If no −−statement option is given and no files are specified on the command line, mclient reads queries from standard input.

When reading from standard input, if standard input is a terminal or if the −−interactive (−i) option is given, mclient interprets lines starting with \ (backslash) specially. See the section BACKSLASH COMMANDS below.

Before mclient starts parsing command line options, it reads a configuration file. If the environment variable DOTMONETDBFILE is set and not empty, it reads the file pointed to by that variable. If set but empty, no configuration file is read. If unset, mclient first looks for a file .monetdb in the current working directory, and if that doesn’t exist, it looks for a file monetdb in the XDG configuration directory ($XDG_CONFIG_HOME which defaults to $HOME/.config if not set), and finally for a file .monetdb in the current user’s home directory. This file can contain defaults for the flags user, password, language, database, save_history, format, host, port, and width. For example, an entry in a .monetdb file that sets the default language for mclient to mal looks like this: language=mal. To disable reading the .monetdb file, set the variable DOTMONETDBFILE to the empty string in the environment.


General Options

Print usage information and exit.

−−version (−v)

Print version information and exit.

−−encoding=encoding (−E encoding)

Specify the character encoding of the input. The option applies to both the standard input of mclient and to the argument of the −−statement (−s) option but not to the contents of files specified on the command line (except for which refers to standard input) or files specified using the \< command (those must be encoded using UTF-8). The default encoding is taken from the locale.

−−language=language (−l language)

Specify the query language. The following languages are recognized: mal and sql. A unique prefix suffices. When the −−language option is omitted, the default of sql is assumed.

−−database=database (−d database)

Specify the name or URI of the database to connect to. The −d can be omitted if an equally named file does not exist in the current directory. As such, the first non-option argument will be interpreted as database to connect to if the argument does not exist as file. Valid URIs are as returned by ‘monetdb discover‘, see monetdb(1), and look like mapi:monetdb://hostname:port/database.

−−host=hostname (−h hostname)

Specify the name of the host on which the server runs (default: localhost). When the argument starts with a forward slash (/), host is assumed to be the directory where the UNIX sockets are stored for platforms where these are supported.

−−port=portnr (−p portnr)

Specify the portnumber of the server (default: 50000).

−−interactive (−i)

When reading from standard input, interpret lines starting with \ (backslash) specially. See the section BACKSLASH COMMANDS below.

−−timer=timermode (−t timermode)

The timer command controls the format of the time reported for queries. The default mode is none which turns off timing reporting. The timer mode clock reports the client-side wall-clock time ("clk") in a human-friendly format. The timer mode performance reports client-side wall-clock time ("clk") as well as detailed server-side timings, all in milliseconds (ms): the time to parse the SQL query, optimize the logical relational plan and create the initial physical (MAL) plan ("sql"); the time to optimize the physical (MAL) plan ("opt"); the time to execute the physical (MAL) plan ("run"). All timings are reported on stderr.
that the client-measured wall-clock time is reported per query only when options −−interactive or −−echo are used, because only then does mclient send individual lines (statements) of the SQL script to the server. Otherwise, when mclient sends the SQL script in large(r) batch(es), only the total wall-clock time per batch is measured and reported. The server-measured detailed performance timings are always measured and reported per query.

−−user=user (−u user)

Specify the user to connect as. If this flag is absent, the client will ask for a user name, unless a default was found in the .monetdb or $DOTMONETDBFILE file.

−−format=format (−f format)

Specify the output format. The possible values are sql, expanded, x, csv, tab, raw, xml, trash, and rowcount. csv is comma-separated values; tab is tab-separated values; raw is no special formatting (data is dumped the way the server sends it to the client); sql is a pretty format which is meant for human consumption where columns are clearly shown; expanded and x are synonyms and are another pretty format meant for human consumption where column values are printed in full and below each other; xml is a valid (in the XML sense) document; trash does not render any output, enabling performance measurements free of any output rendering/serialization costs; and rowcount is a variation on trash where only the number of affected rows is printed. In addition to plain csv, two other forms are possible. csv=c uses c as column separator; csv+c uses c as column separator and produces a single header line in addition to the data.

−−echo (−e)

Echo the query. Note that using this option slows down processing.

−−history (−H)

If compiled with the readline(3) library, load and save the command line history (default off).

−−log=logfile (−L logfile)

Save client/server interaction in the specified file.

−−statement=stmt (−s stmt)

Execute the specified query. The query is run before any queries from files specified on the command line are run.

−−timezone (−z)

Do not tell the client’s timezone to the server.

−−Xdebug (−X)

Trace network interaction between mclient and the server.

−−pager=cmd (−| cmd)

Send query output through the specified cmd. One cmd is started for each query. Note that the | will have to be quoted or else the shell will interpret it.

SQL Options
nullstr (−n nullstr)

Set the string to be used as NULL representation when using the sql, csv, or tab output formats. If not used, NULL values are represented by the string "null" in the sql output format, and as the empty string in the csv and tab output formats. Note that an argument is required, so in order to use the empty string, use −n "" (with the space) or −−null=.

−−autocommit (−a)

Switch autocommit mode off. By default, autocommit mode is on.

−−allow−remote (−R)

Allow remote content (URLs) in the COPY INTO table FROM file ON CLIENT ... query. Remote content is retrieved by mclient.

−−rows=nr (−r nr)

If specified, query results will be paged by an internal pager at the specified number of lines.

−−width=nr (−w nr)

Specify the width of the screen. The default is the (initial) width of the terminal.

−−dump (−D)

Create an SQL dump.

−−inserts (−N)

Use INSERT INTO statements instead of COPY INTO + CSV values when dumping the data of a table. This option can be used when trying to load data from MonetDB into another database, or when e.g. JDBC applications are used to reload the dump.


General Commands


Show a help message explaining the backslash commands.


Exit mclient.

\< file

Read input from the named file.

\> file

Write output to the named file. If no file is specified, write to standard output.

\| command

Pipe output to the given command. Each query is piped to a new invocation of the command. If no command is given, revert to writing output to standard output.


Show the readline(3) history.

\L file

Log client/server interaction in the given file. If no file is specified, stop logging information.


Trace what mclient is doing. This is mostly for debugging purposes.


Echo the query in SQL formatting mode.

\f format

Use the specified format mode to format the output. Possible modes the same as for the −−format (−f) option.

\w width

Set the maximum page width for rendering in the sql formatting mode. If width is −1, the page width is unlimited, when width is 0, use the terminal width. If width is greater than 0, use the given width.

\r rows

Use an internal pager using rows per page. If rows is −1, stop using the internal pager.

SQL Commands


Dump the complete database. This is equivalent to using the program msqldump(1).

\D table

Dump the given table.


Alias for \dvt.


List database objects of the given type. Multiple type specifiers can be used at the same time. The specifiers S, t, v, s, f and n stand for System, table, view, sequence, function and schema respectively. Note that S simply switches on viewing system catalog objects, which is orthogonal to the other specifiers.

\d[Stvsfn]+ object

Describe the given object in the database using SQL statements that reconstruct the object. The same specifiers as above can be used, following the same rules. When no specifiers are given, vt is assumed. The object can be given with or without a schema, separated by a dot. The object name can contain the wildcard characters * and _ that represent zero or more, and exactly one character respectively. An object name is converted to lowercase, unless the object name is quoted by double quotes ("). Examples of this, are e.g. *.mytable, tabletype*, or "myschema.FOO". Note that wildcard characters do not work in quoted objects. Quoting follows SQL quoting rules. Arbitrary parts can be quoted, and two quotes following each other in a quoted string represent the quote itself.


Enable auto commit mode.


Disable auto commit mode.


Efficiently import data from a CSV (comma-separated values) file into a table. The file must be readable by the server. $file is the absolute path name of the file, $table is the name of the table, $db is the name of the database.

mclient −d $db −s "COPY INTO $table FROM ’$file’ USING DELIMITERS ’,’,E’\\n’,’\"’"

Efficiently import data from a CSV file into a table when the file is to be read by mclient (e.g. the server has no access to the file). $file is the (absolute or relative) path name of the file, $table is the name of the table, $db is the name of the database.

mclient −d $db −s "COPY INTO $table FROM STDIN USING DELIMITERS ’,’,E’\\n’,’\"’" − < $file

Note that in this latter case, if a count of records is supplied, it should be at least as large as the number of records actually present in the CSV file. This, because otherwise the remainder of the file will be interpreted as SQL queries.

Another, easier method to have the client read the file content is as follows:

mclient −d $db −s "COPY INTO $table FROM ’$file’ ON CLIENT USING DELIMITERS ’,’,E’\\n’,\"’"

In this case the value of $file can be a path name relative to the directory in which mclient was started. If, in addition, the option −−allow−remote is passed to mclient, the $file in the above query can also be a URL. It then has to have the form schema://string, e.g.,

See for more information about the COPY INTO query.


msqldump(1), mserver5(1)

mclient --help

mclient --help mk Sun, 03/28/2010 - 23:14

The mclient program is the universal command-line tool that implements the MAPI protocol for client-server interaction with MonetDB.
On a Windows platform it can be started using start->MonetDB->MonetDB SQL Client. Alternatively, you can use the command window to start mclient.exe. Be aware that your environment variables are properly set to find the libraries of interest.
On a Linux platform it provides readline functionality, which greatly improves user interaction. A history can be maintained to ease interaction over multiple sessions.

The default setting is geared at establishing a guest connection to a MonetDB SQL database server at a default server running on the localhost. The -h hostname specifies on which machine the MonetDB server is running. If you communicate with a MonetDB server on the same machine, it can be omitted. The default TCP port used is 50000. If this port happens to be in use on the server machine (which generally is only the case if you run two MonetDB servers on it), you will have to use the -p port do define the port to which the mserver is listening. Otherwise, it may also be omitted. If there is more than one mserver running, you must also specify the database name -d database. In this case, if your port is set to the wrong database, the connection will always be redirected to the correct one. Note that the default port (and other default options) can be set in the server configuration file.

shell>mclient --help
Usage: mclient [ options ] [ file or database [ file ... ] ]

Options are:
 -h hostname | --host=hostname    host or UNIX domain socket to connect to
 -p portnr   | --port=portnr      port to connect to
 -u user     | --user=user        user id
 -d database | --database=database  database to connect to (may be URI)
 -e          | --echo             echo the query
 -E charset  | --encoding=charset specify encoding (character set) of the terminal
 -f kind     | --format=kind      specify output format {csv,tab,raw,sql,xml,trash,rowcount}
 -H          | --history          load/save cmdline history (default off)
 -i          | --interactive      interpret `\' commands on stdin
 -t          | --timer=format     use time formatting {none,clock,performance} (none is default)
 -l language | --language=lang    {sql,mal}
 -L logfile  | --log=logfile      save client/server interaction
 -s stmt     | --statement=stmt   run single statement
 -X          | --Xdebug           trace mapi network interaction
 -z          | --timezone         do not tell server our timezone
 -| cmd      | --pager=cmd        for pagination
 -v          | --version          show version information and exit
 -?          | --help             show this usage message

SQL specific opions 
 -n nullstr  | --null=nullstr     change NULL representation for sql, csv and tab output modes
 -a          | --autocommit       turn off autocommit mode
 -R          | --allow-remote     allow remote content
 -r nr       | --rows=nr          for pagination
 -w nr       | --width=nr         for pagination
 -D          | --dump             create an SQL dump
 -N          | --inserts          use INSERT INTO statements when dumping
The file argument can be - for stdin

Within the context of each query language there are more options. They can be shown using the command \? or using the commandline.

For SQL there are several knobs to tune for a better rendering of result tables (\w).

shell>mclient -d demo
Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2020)
Database: MonetDB v11.39.5 (Oct2020), 'demo'
Type \q to quit, \? for a list of available commands
auto commit mode: on
\?       - show this message
\<file   - read input from file
\>file   - save response in file, or stdout if no file is given
\|cmd    - pipe result to process, or stop when no command is given
\history - show the readline history
\help    - synopsis of the SQL syntax
\D table - dumps the table, or the complete database if none given.
\d[Stvsfn]+ [obj] - list database objects, or describe if obj given
\A       - enable auto commit
\a       - disable auto commit
\e       - echo the query in sql formatting mode
\t       - set the timer {none,clock,performance} (none is default)
\f       - format using renderer {csv,tab,raw,sql,xml,trash,rowcount,expanded}
\w#      - set maximal page width (-1=unlimited, 0=terminal width, >0=limit to num)
\r#      - set maximum rows per page (-1=raw)
\L file  - save client-server interaction
\X       - trace mclient code
\q       - terminate session and quit mclient


msqldump sjoerd Tue, 09/20/2011 - 14:45


msqldump − dump a MonetDB/SQL database


msqldump [ options ] [ dbname ]


MonetDB is a database management system that is developed from a main-memory perspective with use of a fully decomposed storage model, automatic index management, extensibility of data types and search accelerators, and an SQL front end.

Msqldump is the program to dump an MonetDB/SQL database. The dump can be used to populate a new MonetDB/SQL database.

Before msqldump starts parsing command line options, it reads a configuration file. If the environment variable DOTMONETDBFILE is set and not empty, it reads the file pointed to by that variable. If set but empty, no configuration file is read. If unset, msqldump first looks for a file .monetdb in the current working directory, and if that doesn’t exist, it looks for a file monetdb in the XDG configuration directory ($XDG_CONFIG_HOME which defaults to $HOME/.config if not set), and finally for a file .monetdb in the current user’s home directory. This file can contain defaults for the flags user, password, host, and port. To disable reading the .monetdb file, set the variable DOTMONETDBFILE to the empty string in the environment.


−−help (−?)

Print usage information and exit.

−−database=database (−d database)

Specify the name of the database to connect to. The −d can be omitted if it is the last option.

−−host=hostname (−h hostname)

Specify the name of the host on which the server runs (default: localhost).

−−port=portnr (−p portnr)

Specify the portnumber of the server (default: 50000).

−−user=user (−u user)

Specify the user to connect as. If this flag is absent, the client will ask for a user name.

−−describe (−D)

Only dump the database schema.

−−inserts (−N)

When dumping the table data, use INSERT INTO statements, rather than COPY INTO + CSV values. INSERT INTO statements are more portable, and necessary when the load of the dump is processed by e.g. a JDBC application.

−−noescape (−e)

When dumping the table data, use the NO ESCAPE option on the COPY INTO query.

−−functions (−f)

Only dump functions definitions.

−−table=table (−t table)

Only dump the specified table.

−−quiet (−q)

Don’t print the welcome message.

−−Xdebug (−X)

Trace network interaction between mclient and the server.


mclient(1), mserver5(1)


jdbcclient mk Sun, 03/28/2010 - 23:15

The JdbcClient program is a textual command line program (inspired by mclient) for interactieve SQL processing and querying written completely in Java using the JDBC API and the MonetDB JDBC driver. It is available as a single small jar file jdbcclient.jre8.jar downloadable from MonetDB Java Download Area. The jar file already includes the MonetDB JDBC driver, which is used to communicate to a MonetDB server, so no further jar files (or classpath settings) are needed.

The JdbcClient program supports startup options. To view them simply type in a shell the command:

     java -jar jdbcclient.jre8.jar --help

This will show following usage information:

Usage java -jar jdbcclient.jre8.jar
		[-h host[:port]] [-p port] [-f file] [-u user]
		[-l language] [-d database] [-e] [-D [table]] [-X<opt>]
		| [--help] | [--version]
or using long option equivalents --host --port --file --user --language
--database --echo --dump.
Arguments may be written directly after the option like -p50000.

If no host and port are given, localhost and 50000 are assumed.
An .monetdb file may exist in the user's home directory.  This file can contain
preferences to use each time JdbcClient is started.  Options given on the
command line override the preferences file.  The .monetdb file syntax is
<option>=<value> where option is one of the options host, port, file, mode
debug, or password.  Note that the last one is perilous and therefore not
available as command line option.
If no input file is given using the -f flag, an interactive session is
started on the terminal.

-h --host     The hostname of the host that runs the MonetDB database.  A port
              number can be supplied by use of a colon, i.e. -h somehost:12345.
-p --port     The port number to connect to.
-f --file     A file name to use either for reading or writing.  The file will
              be used for writing when dump mode is used (-D --dump).  In read
              mode, the file can also be an URL pointing to a plain text file
              that is optionally gzip compressed.
-u --user     The username to use when connecting to the database.
-d --database Try to connect to the given database (only makes sense if
              connecting to monetdbd).
-l --language Use the given language, defaults to 'sql'.
--help        This help screen.
--version     Display driver version and exit.
-e --echo     Also outputs the contents of the input file, if any.
-q --quiet    Suppress printing the welcome header.
-D --dump     Dumps the given table(s), or the complete database if none given.
-Xoutput      The output mode when dumping.  Default is sql, xml may be used for
              an experimental XML output.
-Xhash        Use the given hash algorithm during challenge response. Supported
              algorithm names: SHA512, SHA384, SHA256 and SHA1.
-Xdebug       Writes a transmission log to disk for debugging purposes. If a
              file name is given, it is used, otherwise a file called
              monet<timestamp>.log is created.  A given file never be
              overwritten; instead a unique variation of the file is used.
-Xbatching    Indicates that a batch should be used instead of direct
              communication with the server for each statement.  If a number is
              given, it is used as batch size.  i.e. 8000 would execute the
              contents on the batch after each 8000 statements read.  Batching
              can greatly speedup the process of restoring a database dump.


Using the JdbcClient program

It is easy to start it (assuming you have a MonetDB/SQL server running) from a shell, for example:

% java -jar jdbcclient.jre8.jar -p50000 -ddemo -umonetdb

Welcome to the MonetDB interactive JDBC terminal!
JDBC Driver: MonetDB Native Driver v3.0 (Liberica 20210219 based on MCL v1.19)
Database Server: MonetDB v11.39.13
Current Schema: sys
Type \q to quit (you can also use: quit or exit), \? or \h for a list of available commands
auto commit mode: on

As the password cannot be provided as startup option, you will be asked to enter it after the password: prompt.

Tip: if you do not want to enter the password each time, use a .monetdb file (which contains the user and password settings) similar as for mclient.

If the authentication or connection fails, observe the printed error messages from jdbcclient (and/or the merovingian logs) for clues.

After a successful connection the sql> prompt is shown, allowing you to enter any SQL query or backslash commands and execute it by using the enter-key. You can use multiple lines to enter the SQL query. To execute it enter the ; character after the SQL query, and press the enter-key. For example:

sql> select * from table_types
more> order by 2;
| table_type_id | table_type_name        |
|            20 | GLOBAL TEMPORARY TABLE |
|            30 | LOCAL TEMPORARY TABLE  |
|             3 | MERGE TABLE            |
|             5 | REMOTE TABLE           |
|             6 | REPLICA TABLE          |
|             4 | STREAM TABLE           |
|            10 | SYSTEM TABLE           |
|            11 | SYSTEM VIEW            |
|             0 | TABLE                  |
|             1 | VIEW                   |
10 rows
Elapsed Time: 4 ms

To view a list of available backslash commands enter \? after the sql> prompt and the enter-key.

sql> \?
Available commands:
\q       quits this program (you can also use: quit or exit)
\d       list available user tables and views in current schema
\dS      list available system tables and views in sys schema
\d <obj> describes the given table or view
\l<uri>  executes the contents of the given file or URL
\i<uri>  batch executes the inserts from the given file or URL
\vsci    validate sql system catalog integrity
\vsi <schema>  validate integrity of data in the given schema
\vdbi    validate integrity of data in all user schemas in the database
\? or \h this help screen

Use \q to quit the program. Alternatively you may use quit or exit commands.

The following commands allow you to quickly validate data integrity of: system catalog tables or tables in one schema or all user schemas in the database:
  \vsi  schema_name
The current validations include:
- Primary Key uniqueness
- Primary Key column(s) being NOT NULL (currently only for \vsci)
- Unique constraint uniqueness
- Foreign Key referential integrity
- Column NOT NULL constraint
- Char(n), varchar(n), clob(n), blob(n), json(n) and url(n) max length constraint

It is advised to run \vsci command before and after an upgrade of MonetDB server to check if the system catalog tables are consistent.
Use \vsi my_schema to validate data in all tables of a specific schema.
Use \vdbi to validate integrity of user data in all user schemas in the database.
Note: It can take a long time to complete, depending on your number of user schemas, tables, columns and rows.
Warning: The integrity validation functionality is still beta, so you can get false errors reported.
If you encounter any please let us know via


Note: The JDBC protocol does not support the SQL: DEBUG <query> ; or TRACE <query> ; commands functionality. Use the mclient program for those instead.

Note: the JdbcClient program is provided as a simple tool for performing connectivity and interactive tests using integrated MonetDB JDBC driver.
It is *not* recommended for production environments, the mclient program should be used instead.


MonetDB Server Applications

MonetDB Server Applications giulia Mon, 02/24/2020 - 15:17

Note: on MS Windows the server programs monetdb and monetdbd are not available. Only mserver5 is available to start a MonetDB server. See installed batch files: M5server.bat and MSQLserver.bat (which calls M5server.bat).


monetdb sjoerd Tue, 04/26/2011 - 12:33


monetdb − control a MonetDB Database Server instance


monetdb [ monetdb_options ] command [ command_options ] [ command_args ]


Monetdb allows an administrator of the MonetDB Database Server to perform various operations on the databases in the server. It relies on monetdbd(1) running in the background for all operations.


Monetdb_options affect all commands and control the general behavior of monetdb.


Suppresses all standard progress messages, only writing output to stderr if an error occurred.

−h hostname

Connect to hostname instead of attempting a connection over the local UNIX socket. This allows monetdb to connect to a remote monetdbd(1). The use of this option requires −P (see below). If hostname starts with a forward slash (/), hostname is assumed to be the directory where the UNIX sockets are stored. In that case, the −P option is not allowed.

−p port

Connects to the given portnumber instead of the default (50000). Requires −h to be given as option too.

−P passphrase

Specifies the passphrase necessary to login to a remote monetdbd(1). This option requires −h to be given as well. A bad passphrase causes monetdb to fail to login, and hence fail to perform any remote action.


Show version, equal to monetdb version.


The commands for the monetdb utility are create, destroy, lock, release, status, start, stop, kill, profilerstart, profilerstop, snapshot, set, get, inherit, discover, help, and version. The commands facilitate adding, removing, maintaining, starting and stopping a database inside the MonetDB Database Server.

For all commands, database arguments can be glob-like expressions. This allows to do wildcard matches. For details on the syntax, see EXPRESSIONS.
[−m pattern] [−p password] database [database ...]

Initializes a new database in the MonetDB Database Server. A database created with this command makes it available under its database name, but not yet for use by clients, as the database is put into maintenance mode. This allows the database administrator to perform initialization steps before releasing it to users, unless the −p argument is supplied. See also monetdb lock. The name of the database must match the expression [A−Za−z0−9−_]+.

With the −m flag, instead of creating a database, a multiplex-funnel is created. See section MULTIPLEX-FUNNEL in monetdbd(1). The pattern argument is not fully the same as a pattern for connecting or discovery. Each parallel target for the multiplex-funnel is given as username+password@pattern sequence, separated by commas. Here the pattern is an ordinary pattern as would be used for connecting to a database, and can hence also be just the name of a database.

−p password

The −p flag allows to create a database with the given password for the monetdb user. Since this protects the database from being accessed via well-known credentials, the created database is not locked after creation. This way, a new database can be created and used right away using the password supplied.

destroy [−f] database [database ...]

Removes the given database, including all its data and logfiles. Once destroy has completed, all data is lost. Be careful when using this command.


By default, a confirmation question is asked, however the −f option, when provided, suppresses this question and removal is executed right away. Note that without this option you cannot destroy a running database, bring it down first using the stop command.

lock database [database ...]

Puts the given database in maintenance mode. A database under maintenance can only be connected to by an administrator account (by default the monetdb account). A database which is under maintenance is not started automatically by monetdbd(1), the MonetDB Database Server, when clients request for it. Use the release command to bring the database back for normal usage. To start a database which is under maintenance for administrator access, the start command can be used.

release database [database ...]

Brings back a database from maintenance mode. A released database is available again for normal use by any client, and is started on demand. Use the lock command to take a database under maintenance.

status [−lc] [−s states] [database ...]

Shows the state of the given database, or, when none given, all known databases. Three modes control the level of detail in the displayed output. By default a condensed one-line output per database format is used. This output resembles pretty much the output of various xxxstat programs, and is ideal for quickly gaining an overview of the system state. The output is divided into four columns, name, state, health, and remarks. The state column contains two characters that identify the state of the database, based on Booting (starting up), Running, Stopped, Crashed and Locked (under maintenance). This is followed by the uptime when running. The health column contains the percentage of successful starts and stops, followed by the average uptime. The remarks column can contain arbitrary information about the database state, but usually contains the URI the database can be connected to.


The −c flag shows the most used properties of a database. This includes the state of the database (running, crashed, stopped), whether it is under maintenance or not, the crash averages and uptime statistics. The crash average is the number of times the database has crashed over the last 1, 15 or 30 starts. The lower the average, the healthier the database is.


Triggered by the −l flag, a long listing is used. This listing spans many rows with on each row one property and its value separated by a colon (:). The long listing includes all information that is available.


The −s flag controls which databases are being shown, matching their state. The required argument to this flag can be a combination of any of the following characters. Note that the order in which they are put also controls the order in which the databases are printed. b, r, s, c, and l are used to print a starting up (booting), started (running), stopped, crashed and locked database respectively. The default order which is used when the −s flag is absent, is rbscl.

start [−a] database [database ...]
[−a] database [database ...]
[−a] database [database ...]

Starts, stops or kills the given database, or, when −a is supplied, all known databases. The kill command immediately terminates the database by sending the SIGKILL signal. Any data that hasn’t been committed will be lost. This command should only be used as last resort for a database that doesn’t respond any more. It is more common to use the stop command to stop a database. This will first attempt to stop the database, waiting for exittimeout seconds and if that fails, kill the database. When using the start command, monetdb(1) will output diagnostic messages if the requested action failed. When encountering an error, one should always consult the logfile of monetdbd(1) for more details. For the kill command a diagnostic message indicating the database has crashed is always emitted, due to the nature of that command. Note that in combination with −a the return code of monetdb(1) indicates failure if one of the databases had a failure, even though the operation on other databases was successful.

profilerstart database [database ...]
database [database ...]

Starts or stops the collection of profiling logs for the given database. The property profilerlogpath must be set for the given database, and it should point to a directory where the logs will be gathered. The filenames of the logs have the format: proflog_<database>_YYYY-MM-DD_HH-MM-SS.json where the last part is the date and time when the collection started. Please note that a file recording the pid of the profiler is written in the log directory, therefore each database needs to have a different profilerlogpath value.

monetdb snapshot write dbname

Takes a snapshot of the given database and writes it to stdout.

monetdb snapshot create [-t targetfile] dbname [dbname..]

Takes a snapshot of the given databases. Here, dbname can be either the name of a single database or a pattern such as staging* indicating multiple databases to snapshot. Unless -t is given, the snapshots are written to files named <snapshotdir>/<dbname>_<YYYY><MM><DD>T<HH><MM>UTC<snapshotcompression> where snapshotdir is a monetdbd setting that has to be configured explicitly using monetdbd set and snapshotcompression is another monetdbd setting which defaults to .tar.lz4 or .tar. If -t is given, only a single database can be snapshotted and the snapshot is written to targetfile, a file on the server which must be somewhere under snapshotdir but which does not have to follow any particular naming convention.

monetdb snapshot list [dbname..]

Lists the snapshots for the given databases, or all databases if none is given, showing the snapshot id, the time the snapshot was taken and the (compressed) size of the snapshot file. Only snapshots following the naming convention described under monetdb snapshot create are listed. The snapshot id is of the form dbname@tag where the tags are numbers starting at 1 for the most recent snapshot of a database, 2 for the next most recent, etc. For clarity, the first snapshot for each database shows the full snapshot id (dbname@1) and older snapshots for the same database are listed just as @2, @3, etc.

monetdb snapshot restore [-f] snapshotid [dbname]

Restores a database from the given snapshot, where snapshotid is either a path on the server or name@tag as listed by monetdb snapshot list. The optional dbname argument sets the name of the newly created database. It can be omitted unless snapshotid is a full path. When -f is given, no confirmation is asked when overwriting an existing database.

monetdb snapshot destroy [-f] name@tag..

Delete the listed snapshots from the snapshotdir directory. When -f is given, no confirmation is asked.

monetdb snapshot destroy [-f] -r N dbname..

Delete all but the N latest snapshots for the given databases. Again, dbname can be a pattern such as staging* or even * to work on all snapshotted databases. When -f is given, no confirmation is asked.

get <all | property[,property[,..]]> [database ...]

Prints the requested properties, or all known properties, for the given database. For each property its source and value are printed. Source indicates where the current value comes from, e.g. the configuration file, or a local override.

set property=value database [database ...]

Sets property to value for the given database. For a list of properties, run monetdb get all. Most properties require the database to be stopped when set.

Defines if and how the database is being announced to other monetdbds or not. If not set to yes or no the database is simply announced or not. Using a string, called tag the database is shared using that tag, allowing for more sophisticated usage. For information about the tag format and use, see section REMOTE DATABASES in the monetdbd(1) manpage. Note that this property can be set for a running database, and that a change takes immediate effect in the network.


Defines how many worker threads the server should use to perform main processing. Normally, this number equals the number of available CPU cores in the system. Reducing this number forces the server to use less parallelism when executing queries, or none at all if set to 1.


Each server operates with a given optimizer pipeline. While the default usually is the best setting, for some experimental uses the pipeline can be changed. See the mserver5(1) manpage for available pipelines. Changing this setting is discouraged at all times.


Defines if the database has to be started in readonly mode. Updates are rejected in this mode, and the server employs some read-only optimizations that can lead to improved performance.


Sets the maximum amount of clients that can connect to this database at the same time. Setting this to a high value is discouraged. A multiplex-funnel may be more performant, see MULTIPLEX-FUNNEL below.


Defines how the server interprets literal strings. See the mserver5(1) manpage for more details.

inherit property database [database ...]

Like set, but unsets the database-local value, and reverts to inherit from the default again.

discover [expression]

Returns a list of remote monetdbds and database URIs that were discovered by monetdbd(1). All databases listed can be connected to via the local MonetDB Database Server as if it were local databases using their database name. The connection is redirected or proxied based on configuration settings. If expression is given, only those discovered databases are returned for which their URI matches the expression. The expression syntax is described in the section EXPRESSIONS. Next to database URIs the hostnames and ports for monetdbds that allow to be controlled remotely can be found in the discover list masked with an asterisk. These entries can easily be filtered out using an expression (e.g. "mapi:monetdb:*") if desired. The control entries come in handy when one wants to get an overview of available monetdbds in e.g. a local cluster. Note that for monetdbd to announce its control port, the mero_controlport setting for that monetdbd must be enabled in the configuration file.


help [command]

Shows general help, or short help for a given command.



Shows the version of the monetdb utility.


For various options, typically database names, expressions can be used. These expressions are limited shell-globbing like, where the * in any position is expanded to an arbitrary string. The * can occur multiple times in the expression, allowing for more advanced matches. Note that the empty string also matches the *, hence "de*mo" can return "demo" as match. To match the literal ’*’ character, one has to escape it using a backslash, e.g. "\*".


The monetdb utility returns exit code 0 if it successfully performed the requested command. An error caused by user input or database state is indicated by exit code 1. If an internal error in the utility occurs, exit code 2 is returned.


monetdbd(1), mserver5(1)


monetdbd sjoerd Tue, 04/26/2011 - 12:31


monetdbd − the MonetDB Database Server daemon


monetdbd command [ command_args ] dbfarm


Monetdbd is the MonetDB Database Server daemon. The program is mainly meant to be used as daemon, but it also allows to setup and change the configuration of a dbfarm. The use of monetdbd is either as user-oriented way to configure, start and stop a database farm, or to be started from a startup script, such as from systemd(1) on Linux systems, as part of a system startup.

Monetdbd is the system formerly known as merovingian. It was renamed to monetdbd since the name merovingian proved to be confusing to most regular end-users. Internally, monetdbd uses the name merovingian at many places for historical reasons.

A monetdbd instance manages one local cluster of databases, which is a directory in the system, referred to as the dbfarm. The dbfarm location must always be given as argument to monetdbd.

Within its local cluster monetdbd takes care of starting up databases when necessary, and stopping them either upon request via monetdb(1) or when being shut down. Client database connections are made against monetdbd initially which redirects or proxies the client to the appropriate database process, started on the fly when necessary.

When started, monetdbd runs by default in the background, sending log messages to merovingian.log, until being sent a stop, terminate or interrupt signal, possibly using the stop command of monetdbd.

monetdbd uses a neighbor discovery scheme to detect other monetdbd processes running in the local network. Databases from those remote instances are made available to a locally connecting client. Remote databases never override local databases, and their availability is controlled by the remote monetdbd process. See also the sharing capabilities of monetdb(1) and the REMOTE DATABASES section below.


The commands for monetdbd are create, start, stop, get, set, version, and help. The commands facilitate initializing a dbfarm, starting and stopping the MonetDB Database Server, and retrieving or setting options.

Initializes a new database farm, such that a MonetDB Database Server can be started on that location. All necessary directories are attempted to be created, and an initial properties file is created in the directory itself. dbfarm must be a location addressable in the local filesystem hierarchy.

start [−n] dbfarm

Starts monetdbd, the MonetDB Database Server, on the given dbfarm. When the −n flag is given, monetdbd will not fork into the background, but instead remain attached to the calling environment, until given a stop signal.

stop dbfarm

Sends a stop signal to the monetdbd process responsible for the given dbfarm. If the exittimeout value is non-zero (see below), all mserver processes will be sent a termination signal. The current process will wait for the manager process to terminate, but sends a SIGKILL signal if waiting takes longer than 5 seconds more than the value of exittimeout. If exittimeout is negative, both monetdbd processes involved will wait until the servers have all terminated and no extra signals are sent.

get <all | property[,property[,..]]> dbfarm

Prints the requested properties, or all known properties, for the given dbfarm. For each property, its value is printed. Some properties are virtual, and given for information purposes only, they cannot be modified using the set command.

set property=value dbfarm

Sets property to value for the given database. For a list of properties, run monetdbd get all. Some properties require a restart of the MonetDB Database Server in order to take effect. The set command, will however always write the property, and tell the running monetdbd to reload the properties file (if running). For an explanation of the properties, see the CONFIGURATION section below.


Monetdbd reads its properties from the .merovingian_properties file inside the dbfarm. This file is created by the create command. This file is not meant to be edited manually, instead it should be updated using the set command. The following properties can be set:

This property points to the file where all log messages are written to. It is relative to the dbfarm directory, but can be absolute to point to e.g. another medium. Changing this property takes effect immediately at runtime.


Monetdbd stores the process ID of the background server in the file pointed to by this property. The same rules apply as for the logfile property.


For faster access, monetdbd uses UNIX domain sockets for its control mechanism and regular database connections. The sockets are placed as files in the filesystem hierarchy. The sockdir property controls in which directory they are placed. In general this setting should not be changed.


This property specifies which TCP port monetdbd should listen to for connection requests. Defaults to 50000.


This property specifies an address that is allowed to connect to the server. The following possibilities exist:

The server only listens on the IPv4 and IPv6 loopback interface (if available). This is the default.

The server only listens on the IPv4 loopback interface.


The server only listens on the IPv6 loopback interface.


The server listens on all available IPv4 and IPv6 interfaces.

The server listens on all available IPv4 interfaces.


The server listens on all available IPv6 interfaces.


The server listens on the interface designated by hostname which is looked up using the normal hostname lookup facilities.


For remote management of monetdbd, the control property specifies whether or not to enable remote management. Note that for remote management, a passphrase is required, see below. It defaults to false for security reasons. Changing this property takes effect immediately at runtime.


To control monetdbd from a remote machine, a passphrase is necessary, to be given to monetdb(1). The passphrase can be either given as hashed value prefixed by the hash type in curly braces (e.g. {SHA512}xxx...) or as plain text value which will be hashed automatically. Note that the only hash accepted is the one specified at configure time, which is SHA512. Changing this property takes effect immediately at runtime.


This property specifies where the monetdb snapshot create command stores its snapshots. There is no default value. If unset, no snapshots can be created.


This property specifies whether database snapshots created by monetdb snapshot create are compressed, and if so, using which algorithm. The default value is .tar.lz4 if LZ4 compression is available, or .tar otherwise. Other supported values are, if available, .tar.gz, .tar.xz, and .tar.bz2. These more efficient but much slower compression algorithms are usually not recommended for backups of large databases because it would take enormous amounts of time.


Specifies whether neighbor discovery is to be enabled using UDP broadcasts or not. The broadcasts are done on the same portnumber as the port setting.


Monetdbd publishes locally available databases to others periodically. The interval used here, defined in seconds, depends on the time-to-live of the databases before they need to get refreshed. The default is 600 seconds (10 minutes), which should keep traffic in your network fairly low. Additions and removals are processed immediately regardless of this timeout. If you are in a network environment where physical network links disappear often, you may want to decrease this value to more quickly remove no longer reachable databases.


If monetdbd stops a running mserver, for example in response to a monetdb stop command or when monetdbd itself is shutting down, it first sends the mserver a SIGTERM signal to trigger a graceful shutdown. If that takes more than exittimeout seconds, SIGKILL is sent as a last resort. This may cause (partial) data loss in that transactions may not be able to complete (all completed transactions, whether reported back or not, should be save). A negative time-out value value means the second signal (SIGKILL) will not be sent, but instead all mserver processes will be waited for. A time-out value of 0 means no mservers will be shut down, and hence they will continue to run after monetdbd has shut down. Note that this particular configuration is extremely inconvenient. The default time-out is 60 seconds. If your databases are rather large and you find your databases consistently being killed by monetdbd upon shutdown, you may want to increase this time-out. Changing this property takes effect immediately at runtime.


monetdbd has two ways in which it can "attach" a connecting client to the target database. The first method, redirect, uses a redirect sent to the client with the responsible mserver process. The second method, proxy, proxies the client to the mserver over monetdbd. While redirect is more efficient, it requires the connecting client to be able to connect to the mserver. In many settings this may be undesirable or even impossible, since a wide range of open ports and routing are necessary for this. In such case the proxy technique of monetdbd is a good solution, which also allows a monetdbd instance on the border of a network to serve requests to nodes in the local (unreachable) network. Note that for local databases, the proxy method uses a UNIX domain socket feature to pass file-descriptors to the local mserver. This effectively is as efficient as the redirect approach, but still hides away the mservers properly behind monetdbd. Hence, in practice it is only relevant for connections to remote databases to use redirects instead of proxies. Changing this property takes effect immediately at runtime.


The neighbor discovery capabilities of monetdbd allow a user to contact a remote database transparently, as if it were a local database. By default, all local databases are announced in the network, such that neighbors can pick them up to make them available for their local users. This feature can be disabled globally, or on database level. For the latter, the monetdb(1) utility can be used to change the share property of a database.

While neighbor discovery in itself is sufficient to locate a database in a cluster, it is limited in expressiveness. For instance, database names are assumed to be unique throughout the entire system. This means local databases overshadow remote ones, and duplicate remote entries cannot be distinguished. To compensate for this, monetdbd allows to adds a tag to each database that is being shared. This tag is sent in addition to the database name, and only understood by other monetdbds.

Tags are arbitrary ASCII-strings matching the pattern [A−Za−z0−9./]+. There are no assumed semantics in the tag, which allows for multiple approaches when using the tag. The tag is always used in combination with the database name. For this, the ‘/’ character is used as separator, which hence suggests the user to use that character as separator for multilevel tags. Monetdbd allows common path globbing using ‘*’ on tags, which allows for many use-cases. Consider for instance the following three databases with their tag:


A default match has implicit ‘/*’ added to the search, making more generic search strings match more specific ones. Hence, a connect with database dbX is the same as dbX/* and hence matches dbX/master/tableQ. Similar, a database connect for */master matches the same database as before. Note that the implicit ‘/*’ is not added if that would cause no matches, such as for */master/tableQ which would return all masters for tableQ, which in our hypothetical example is only dbX. In contrast, a database connect for */slave/tableQ matches with either dbY or dbZ. Monetdbd returns the two options to the client in a round-robin fashion, such that subsequent connects for the same pattern result in a load-balanced connect to either of both databases.

With tags in use, one can possibly make distinction between databases, if setup like that. The previous example could hence also be setup like this:


Connecting to tableQ/slave would now return either of both databases even though they are not unique (apart from the host they are located on, which is not shown in the example). While being confusing for humans, for monetdbd it is the same situation as in the previous example. However, because globbing allows to make things easier to understand, tags for both slaves could be changed to slaveX or slave/X and use the necessary pattern to match them. It is up to the user to decide how to use the tags.


Monetdbd implements multiplex-funnel capabilities. As the name suggests two techniques are combined, the multiplexer and the funnel.

The funnel capability limits the access to the database to one client at a time. That is, if multiple clients connect to the funnel, their queries will be serialized such that they are executed one after the other. An effect of this approach is that clients no longer have an exclusive channel to the database, which means that individual queries from one client may have been interleaved with queries from others. This most notably makes SQL transaction blocks unreliable with a funnel. The funnel, hence, is meant to scale down a large amount of clients that perform short-running (read-only) queries, as typically seen in web-based query loads.

When a funnel is defined to use multiple databases, the funnel adds a multiplexer to its query channel. A multiplex-funnel sends each query to all of the defined databases. This behavior can be quite confusing at first, but proves to be useful in typical sharding configurations, where in particular simple selection queries have to be performed on each of the shards. The multiplexer combines the answers from all defined databases in one single answer that it sends back to the client. However, this combining is without any smart logic, that is, the multiplexer does not evaluate the query it is running, but just combines all answers it receives from the databases. This results in e.g. as many return tuples for a SELECT COUNT(*) query, as there are databases defined.

Due to the two above mentioned characteristics, a multiplex-funnel has some limitations. As mentioned before, transactions over multiple queries are likely not to result in the desired behavior. This is due to each query to the funnel is required to be self-contained. Further, since for each query, the results from multiple servers have to be combined into one, that query must only return a single response, i.e. multi-statement queries are most likely causing the funnel to respond with an error, or return garbled results. Last, the size of each query is limited to currently about 80K. While this size should be sufficient for most queries, it is likely not enough for e.g. COPY INTO statements. Apart from the data transfer implications, such statements should not be used with the funnel, as the results will be undefined due to the limited query buffer. Applications using the funnel should aim for short and single-statement queries that require no transactions.

See the create command in the monetdb(1) man-page for details on how to setup a multiplex-funnel.


Monetdbd acts upon a number of signals as is common for a daemon.

Any of these signals make monetdbd enter the shutdown sequence. This sequence involves cleanly shutting down listener sockets, shutting down all started databases and finally terminating itself.


When this signal is received by monetdbd it will reopen the logfile as pointed to by the logfile setting. Before it reopens the logfile, it will re-read the properties file from the dbfarm, which might result in opening a different file to continue logging.


Monetdbd returns exit code 0 if it was able to successfully perform the requested action, e.g. start, stop, etc. When an error occurs during the action, that prevents monetdbd from successfully performing the action, the exit code 1 is returned.


monetdb(1), mserver5(1)


mserver5 sjoerd Tue, 04/26/2011 - 12:35


mserver5 − the MonetDB server version 5


mserver5 [ options ]


Mserver5 is the current MonetDB server that performs all processing on request of clients for a certain database.

Note that while mserver5 is the process that does the actual work, it is usually more common to start, monitor and connect to the mserver5 process through monetdbd(1).

This man-page describes the options that mserver5 understands. It is intended for people who really need to work with mserver5 itself. In regular cases, the programs monetdbd(1) and monetdb(1) control the many options, and allow to adjust them to appropriate values where sensible. For normal usage, it is preferred to apply any configuration through these programs.


When the build-time configuration did not disable this, a mserver5 process presents the user with a console prompt. On this prompt, MAL commands can be executed. The architecture is setup to handle multiple streams of requests. The first thread started represents the server, which is the console prompt, reading from standard input and writing to standard output.

The server thread started remains in existence until all other threads die. The server is stopped by sending it a termination signal (SIGINT, SIGTERM, SIGQUIT).


Mserver5 can be started with options as arguments.

Path where mserver5 should find a database. Shorthand for option gdk_dbpath. Default value: /home/sjoerd/.scratch/install/Monet-Jul2021/var/monetdb5/dbfarm/demo.


Path where mserver5 should store transient data. Default value is the value of the −−dbpath option. If the value of path is in-memory, transient data is not stored on disk at all but kept in memory at all times.


File name for the trace log file for mserver5. Default value is the file mdbtrace.log inside the directory specified with the −−dbpath option.


Config file to read options from. This file can contain all options as can be set with the −−set flag. See CONFIG FILE FORMAT.


Allow only a single user at a time.


The database is opened in read-only mode.

−−set option=value

Set individual configuration option. For possible options, see PARAMETERS sections.


Print list of options.


Print version and compile configuration.


Run mserver5 in-memory. No data will be written to disk. The name of the database that a client can connect to is in-memory.


Set debug level. This is mostly for debugging purposes. The value is an integer, which can be (a bit-wise OR of):
= THRDMASK = thread-specific debug output
= CHECKMASK = property enforcing on new BATs
= PROPMASK = property checking on all values:
tells about wrongly set properties
= IOMASK = major IO activity
= BATMASK = BAT handling
= PARMASK = Thread management
= TMMASK = Transaction management
= TEMMASK = Locks and Triggers
= PERFMASK = BBP Performance (?)
= DELTAMASK = Delta debugging (?)
= LOADMASK = Module loading
= ACCELMASK = Accelerator debugging
= ALGOMASK = show low level algorithm chosen
= NOSYNCMASK = disable forcefully synchronizing files
to disk. If this flag is set, there is
no guarantee that the database remains
consistent after a crash. DO NOT USE
(unless you really don’t care about your
= DEADBEEFMASK = disable "cleaning" of freed memory
in GDKfree() which only happens in a
debug build (i.e. with assertions
enabled) e.g., for performance
= ALLOCMASK = exhaustive GDK malloc & free tracing
for debugging (GDK developers, only)
= OPTMASK = trace the actions, decisions and
effects of MAL optimizers
= HEAPMASK = trace/debug HEAPextend;
used only for development & debugging
= FORCEMITOMASK = forcefully activate mitosis even on
small tables, i.e., split small tables
in as many (tiny) pieces as there are
cores (threads) available;
this allows us to test mitosis
functionality without requiring large
data sets (— at the expense of a
potentially significant interpretation
overhead for unnecessarily large plans);
used only for development & testing;
set automatically by
The value together with the = sign is optional. If not specified, it defaults to 1. In the short form −d, the value, if present, must immediately (i.e. without space) follow the option. The values of multiple instances of this flag are OR-ed together.


Equivalent to −−debug=(ALGOMASK).


Equivalent to −−debug=(FORCEMITOMASK | NOSYNCMASK).


Equivalent to −−debug=(HEAPMASK).


Equivalent to −−debug=(IOMASK | PERFMASK).


Equivalent to −−debug=(ALLOCMASK).


Equivalent to −−debug=(LOADMASK).


Equivalent to −−debug=(DEADBEEFMASK).


Equivalent to −−debug=(CHECKMASK | PROPMASK | BATMASK).


Equivalent to −−debug=(THRDMASK | PARMASK).


Equivalent to −−debug=(TMMASK | DELTAMASK | TEMMASK).


Read an unencrypted password from standard input and use it to set the password for the monetdb administrator user, initialize the database, and exit. If the database was already initialized, the administrator password is not changed. This option is used by monetdbd(1) when creating a new database with an administrator password and should not be used otherwise.


Mserver5 instructs the GDK kernel through the MAL (MonetDB Assembler Language) language. MonetDB 5 contains an extensive optimiser framework to transform MAL plans into more optimal or functional (e.g. distributed) plans. These parameters control behaviour on the MAL level.

The authorisation tables inside mserver5 can be encrypted with a key, such that reading the BATs does not directly disclose any credentials. The monet_vault_key setting points to a file that stores a secret key to unlock the password vault. It can contain anything. The file is read up to the first null-byte (’\0’), hence it can be padded to any length with trailing null-bytes to obfuscate the key length. Generating a key can be done for example by using a tool such as pwgen and adding a few of the passwords generated. Make sure not to choose a too small key. Note that on absence of a vault key file, some default key is used to encrypt the authorisation tables. Changing this setting (effectively changing the key) for an existing database makes that database unusable as noone is any longer able to login. If you use monetdbd(1), a per-database vault key is set.


Controls how many client slots are allocated for clients to connect. This settings limits the maximum number of connected clients at the same time. Note that MonetDB is not designed to handle massive amounts of connected clients. The funnel capability from monetdbd(1) might be a more suitable solution for such workloads. Default 64.


The name of the UNIX domain socket file on which the server will listen for connections. If the name contains the substring ${PORT}, that part will be replaced by the decimal representation of the TCP/IP port (option mapi_port). This is especially useful if the port was specified as 0. Note, there is usually a severe system-imposed length limitation on the name of the file.


The TCP/IP port number on which the server will listen for connections. This is only used if the value of the mapi_listenaddr option is not equal to none. Default 50000. If the value is 0, the server will use a so called ephemeral port, i.e. one that is assigned by the system. After successfully starting to listen to a port, the value of the port can be retrieved from the file .conn inside the database (−−dbpath) directory.


The TCP/IP interface on which the server will listen for connections. Possibilites are:

The server listens only on the IPv4 and IPv6 loopback interface. This is the default.

The server listens only on the IPv4 loopback interface.


The server listens only on the IPv6 loopback interface.


The server listens on all IPv4 and IPv6 interfaces.

The server listens on all IPv4 interfaces.


The server listens on all IPv6 interfaces.


The server will not listen on any TCP/IP interface (you need to use the UNIX domain socket interface).


The server will listen on the interface designated by hostname which is looked up using the normal hostname lookup facilities.


The SQL component of MonetDB 5 runs on top of the MAL environment. It has its own SQL-level specific settings.

Enable debugging using a mask. This option should normally be disabled (0). Default: 0.


The default SQL optimizer pipeline can be set per server. See the optpipe setting in monetdb(1) when using monetdbd. During SQL initialization, the optimizer pipeline is checked against the dependency information maintained in the optimizer library to ensure there are no conflicts and at least the pre-requisite optimizers are used. The setting of sql_optimizer can be either the list of optimizers to run. Default: default_pipe.
The following are possible pipes to use:

The minimal pipeline necessary by the server to operate correctly. minimal_pipe=inline,remap,deadcode,multiplex,generator,profiler,candidates,garbageCollector


The default pipeline contains the mitosis-mergetable-reorder optimizers, aimed at large tables and improved access locality. default_pipe=inline,remap,costModel,coercions,aliases,evaluate,emptybind,deadcode,pushselect,aliases,mitosis,mergetable,aliases,constants,commonTerms,projectionpath,deadcode,reorder,matpack,dataflow,querylog,multiplex,generator,profiler,candidates,postfix,deadcode,wlc,garbageCollector


The no_mitosis pipeline is identical to the default pipeline, except that optimizer mitosis is omitted. It is used mainly to make some tests work deterministically, and to check/debug whether "unexpected" problems are related to mitosis (and/or mergetable). no_mitosis_pipe=inline,remap,costModel,coercions,aliases,evaluate,emptybind,deadcode,pushselect,aliases,mergetable,aliases,constants,commonTerms,projectionpath,deadcode,reorder,matpack,dataflow,querylog,multiplex,generator,profiler,candidates,postfix,deadcode,wlc,garbageCollector


The sequential pipeline is identical to the default pipeline, except that optimizers mitosis & dataflow are omitted. It is use mainly to make some tests work deterministically, i.e., avoid ambigious output, by avoiding parallelism. sequential_pipe=inline,remap,costModel,coercions,aliases,evaluate,emptybind,deadcode,pushselect,aliases,mergetable,aliases,constants,commonTerms,projectionpath,deadcode,reorder,matpack,querylog,multiplex,generator,profiler,candidates,postfix,deadcode,wlc,garbageCollector


Enable embedded Python. This means Python code can be called from SQL. The value is true or 3 for embedded Python 3. Note that by enabling embedded Python, users of the server are allowed to execute arbitrary Python code, and are therefore able to read and modify all data that the server process has access to.


Enable embedded R. This means R code can be called from SQL. Note that by enabling embedded R, users of the server are allowed to execute arbitrary R code, and are therefore able to read and modify all data that the server process has access to.


Enable embedded C. This means C code can be called from SQL. The C code will first be compiled and then executed. This means a C compiler must be available. Note also that by enabling embedded C, users of the server are allowed to execute arbitrary C code, and are therefore able to read and modify all data that the server process has access to. In addition, if the C code causes a crash, all bets are off.


The boolean option raw_strings controls how the sql scanner interprets string literals. If the value is false then strings are interpreted as if they were delimited with E-quotes, that is strings are interpreted as C strings and backslash characters are needed to escape special characters. If the value is true then strings are interpreted as if they were delimited with R-quotes, that is all characters are interpreted literally. Single quote characters need to be doubled inside strings. The default value is false.


The configuration file readable by mserver5 consists of parameters of the form "name=value". The file is line-based, each newline-terminated line represents either a comment or a parameter. Only the first equals sign in a parameter is significant. Whitespace before or after the first equals sign is not stripped. Trailing whitespace in a parameter value is retained verbatim. Any line beginning with a hash (#) is ignored, as are lines containing only whitespace. The values following the equals sign in parameters are all a string where quotes are not needed, and if written be part of the string.


monetdbd(1), monetdb(1), mclient(1)

Readonly Database

Readonly Database mk Mon, 03/02/2020 - 11:15

When working in a regular mode, the query processing spans the basic column storage and the data changed by transactions (inserts, updates, and deletes). For instance, the inserted data, kept in a separate delta structure, are added to the column before other operations in the plan. In a 'read-only mode' modifying transactions are not allowed and the query processing spans only over the basic column storage. One of the consequences is that query execution may become faster.

The readonly mode is administered with a boolean variable, which  can be set by the system administrator using the monetdb tool:

shell> monetdb set readonly=yes <mydatabasename>

It ensures that all tables are accessed in read only mode.  Moreover, the database is protected against any catalog change, the user can not even create a temporary table for keeping an intermediate result around.

The alternative route is to deploy SQL schemas in combination with user authentication and access control grants to selectively allow users access to the database. See the corresponding description in the SQL manual.