Date: 2019-01-25 12:06:41 +0100
From: Henrique <>
To: SQL devs <>
Version: 11.31.13 (Aug2018-SP2)

Last updated: 2019-04-30 12:36:04 +0200

Comment 26851

Date: 2019-01-25 12:06:41 +0100
From: Henrique <>

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36 OPR/58.0.3135.47
Build Identifier:

At C3SL, we use a tool called HOTMapper (github.com/c3sl/hotmapper) to map open data and insert this mapped data into a MonetDB database. The problem is that if we install the MonetDB and enable the systemctl our script does not work, instead a error (Cannot open file 'XXXXXX': No such file or directory) appears. On the other hand, if we do not enable systemctl and manage our farm ourself, our script works.

Reproducible: Always

Steps to Reproduce:

1.Install MonetDB and enable systemctl
2.Try to insert a CSV file into the database using the COPY command
3.

Actual Results:

Cannot open file 'XXXXXX': No such file or directory

Expected Results:

I expected the data from the CSVB being imported to the database

Another way to reproduce the error is following the tutorial from the script we use (available at: github.com/c3sl/hotmapper).

The COPY we command we were using is:

COPY OFFSET 2 INTO localoferta_ens_superior("cod_local_oferta", "cod_ies", "cod_municipio", "nome_municipio", "cod_uf", "sigla_uf", "sede", "cod_curso_polo", "cod_curso", "nucleo_educacao_a_distancia", "universidade_aberta_do_brasil", "reitoria", "polo_de_apoio_presencial", "unidade_academica", "ano_censo") FROM '/home/henrique/hotmapper/open_data/DM_LOCAL_OFERTA_2012.CSV'("cod_local_oferta", "cod_ies", "cod_municipio", "nome_municipio", "cod_uf", "sigla_uf", "sede", "cod_curso_polo", "cod_curso", "nucleo_educacao_a_distancia", "universidade_aberta_do_brasil", "reitoria", "polo_de_apoio_presencial", "unidade_academica", "ano_censo") USING DELIMITERS '|', '\n', '"' NULL AS ''

Comment 26852

Date: 2019-01-25 14:20:47 +0100
From: @sjoerdmullender

Could it be a permission problem?

When you run MonetDB under control of systemd (systemctl), it runs as user "monetdb" and group "monetdb". If that user has no access to the file you're trying to read, the COPY INTO will fail.

Permissions can obviously be the read/write/execute permission bits, but also SELinux, if you're using that.

Comment 26854

Date: 2019-01-28 11:09:07 +0100
From: Henrique <>

Thank you for your reply. Well, I tried some scenarios with no success:

Distro used: Linux Mint 19.1 Tessa, so I guess I do not use SELinux

1. I tried change the permission (to 777) of the file
2. I tried change the permission (to 777) of all folders and files from my user
3. I tried change the ownership of the file to the user monetdb (the file's permission was 777)
4. I tried change the ownership of the file to the user monetdb (the file's permission was 777 and all folders of my home, including it, was 777)

And the same error occured: Cannot open file 'xxxx/yyyy/zzzz.csv': No such file or directory

Do you have any other sugestions to try to make it work with systemctl?

Comment 26855

Date: 2019-01-28 19:12:13 +0100
From: @sjoerdmullender

The only other thing I can think of right off the top of my head is: is the file accessible from outside MonetDB? Can you read the file if you copy-paste the name from the SQL query to a shell command line? Something like:
more /home/henrique/hotmapper/open_data/DM_LOCAL_OFERTA_2012.CSV

Comment 26856

Date: 2019-01-29 10:55:19 +0100
From: Henrique <>

Again, thanks for your reply. I just tested two scenarios based on your last comment.

In the first one, I run:

more /home/henrique/hotmapper/open_data/DM_LOCAL_OFERTA_2012.CSV

as the user henrique, and it worked.

Then, in the second scenario, I created a standart user, and logged in with this new account. Then I run the same command:

more /home/henrique/hotmapper/open_data/DM_LOCAL_OFERTA_2012.CSV

And I succesfully openned the file with this other user.

Do you have any other idea about how to deal with this problem?

Comment 26857

Date: 2019-01-29 12:12:07 +0100
From: Henrique <>

Additionally, I collected the log (/var/log/monetdb/merovingian.log) from MonetDB when I run the command:

2019-01-29 08:49:43 MSG discovery[1808]: listening for UDP messages on localhost:50000
2019-01-29 08:49:43 MSG control[1808]: accepting connections on UNIX domain socket /tmp/.s.merovingian.50000
2019-01-29 09:03:44 MSG merovingian[1808]: starting database 'demo', up min/avg/max: 1m/2h/19h, crash average: 0.00 0.00 0.00 (7-7=0)
2019-01-29 09:03:45 MSG demo[2540]: arguments: /usr/bin/mserver5 --dbpath=/var/monetdb5/dbfarm/demo --set merovingian_uri=mapi:monetdb://vm:50000/demo --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/monetdb5/dbfarm/demo/.mapi.sock --set monet_vault_key=/var/monetdb5/dbfarm/demo/.vaultkey --set gdk_nr_threads=3 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes
2019-01-29 09:03:45 MSG demo[2540]: !ERROR: GDKmove: cannot rename /var/monetdb5/dbfarm/demo/bat/BBP.dir to /var/monetdb5/dbfarm/demo/bat/BACKUP/BBP.dir
2019-01-29 09:03:45 MSG demo[2540]: !OS: Permission denied
2019-01-29 09:03:45 ERR demo[2540]: !FATAL: BBPinit: cannot properly prepare process bat/BACKUP. Please check whether your disk is full or write-protected
2019-01-29 09:03:49 MSG merovingian[1808]: database 'demo' (2540) has exited with exit status 1
2019-01-29 09:03:49 ERR control[1808]: !monetdbd: an internal error has occurred 'database 'demo' appears to shut itself down after starting, check monetdbd's logfile (/var/log/monetdb/merovingian.log) for possible hints'
2019-01-29 09:03:54 ERR merovingian[1808]: client error: database 'demo' appears to shut itself down after starting, check monetdbd's logfile (/var/log/monetdb/merovingian.log) for possible hints

Comment 26863

Date: 2019-01-31 10:22:17 +0100
From: @sjoerdmullender

The merovingian log indicates that you have permission problems with the database itself. Fix those first.

Comment 26865

Date: 2019-02-01 11:35:46 +0100
From: Henrique <>

I fixed the problem with the database's permission, but it did not solve the problem.

I collected an example:
So, if I want to add, using mclient, a CSV into the database with the systemctl enabled, that what happens:

(env) henrique@vm:~/hotmapper$ mclient -d demo
user(henrique):monetdb
password:
Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2018-SP2)
Database: MonetDB v11.31.13 (Aug2018-SP2), 'mapi:monetdb://vm:50000/demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>COPY OFFSET 2 INTO localoferta_ens_superior("cod_local_oferta", "cod_ies", "cod_municipio", "nome_municipio", "cod_uf", "sigla_uf", "sede", "cod_curso_polo", "cod_curso", "nucleo_educacao_a_distancia", "universidade_aberta_do_brasil", "reitoria", "polo_de_apoio_presencial", "unidade_academica", "ano_censo") FROM '/home/henrique/2012.csv'("cod_local_oferta", "cod_ies", "cod_municipio", "nome_municipio", "cod_uf", "sigla_uf", "sede", "cod_curso_polo", "cod_curso", "nucleo_educacao_a_distancia", "universidade_aberta_do_brasil", "reitoria", "polo_de_apoio_presencial", "unidade_academica", "ano_censo") USING DELIMITERS '|', '\n', '"' NULL AS ''
more>;
Cannot open file '/home/henrique/2012.csv': No such file or directory

The file is accessible, as the ls and more commands show:

henrique@vm:~/hotmapper$ ls -l /home/henrique/2012.csv
-rwxrwxrwx 1 monetdb monetdb 9988799 jan 28 07:52 /home/henrique/2012.csv

henrique@vm:~/hotmapper$ more /home/henrique/2012.csv
CO_LOCAL_OFERTA_IES|CO_IES|CO_MUNICIPIO_LOCAL_OFERTA|NO_MUNICIPIO_LOCAL_OFERTA|CO_UF_LOCAL_OFERTA|SGL_UF_LOCAL_OFERTA|IN_SEDE|CO_CURSO_POLO|CO_CURSO|IN_LOCAL_OFERTA_NEAD
|IN_LOCAL_OFERTA_UAB|IN_LOCAL_OFERTA_REITORIA|IN_LOCAL_OFERTA_POLO|IN_LOCAL_OFERTA_UNID_ACADEMICA|ANO_CENSO
194|29|2311306|QUIXADA|23|CE|0|10382|1151448|0|0|0|0|1|2012
194|29|2311306|QUIXADA|23|CE|0|10413|1151457|0|0|0|0|1|2012
194|29|2311306|QUIXADA|23|CE|0|10444|1151646|0|0|0|0|1|2012
194|29|2311306|QUIXADA|23|CE|0|11060|1151669|0|0|0|0|1|2012
194|29|2311306|QUIXADA|23|CE|0|11091|1151671|0|0|0|0|1|2012
194|29|2311306|QUIXADA|23|CE|0|11122|1151677|0|0|0|0|1|2012
194|29|2311306|QUIXADA|23|CE|0|11153|1151706|0|0|0|0|1|2012
194|29|2311306|QUIXADA|23|CE|0|11184|1152477|0|0|0|0|1|2012
194|29|2311306|QUIXADA|23|CE|0||2208|0|0|0|0|1|2012
194|29|2311306|QUIXADA|23|CE|0||2213|0|0|0|0|1|2012

Now the log (/var/log/monetdb/merovingian.log) only prints something when I connect to the database using mclient. The log shows:

2019-02-01 08:15:33 MSG merovingian[2627]: proxying client (local) for database 'demo' to mapi:monetdb:///var/monetdb5/dbfarm/demo/.mapi.sock?database=demo
2019-02-01 08:15:33 MSG merovingian[2627]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying

Do you have any idea why I cannot use this command COPY if I am using MonetDB with the systemctl enabled?

Comment 26867

Date: 2019-02-01 14:19:36 +0100
From: @sjoerdmullender

I probably should have tried this earlier, but now that I have, I can say that I can reproduce the problem. It is in fact a permissions problem, even though the server report a different error. Though I can't find a message in the system log, I think it has to do with SELinux. When the server is started using systemctl, it runs in a different and more restrictive context. Usually programs restricted in this way are not allowed to access users' home directories (for good reason).

The solution is to not have the server read the file but have the client do it.
Both the mclient man [1] page and [2] have an example of how to do it.

In your case, something like this:

mclient -d demo -s "COPY OFFSET 2 INTO localoferta_ens_superior("cod_local_oferta", "cod_ies", "cod_municipio", "nome_municipio", "cod_uf", "sigla_uf", "sede", "cod_curso_polo", "cod_curso", "nucleo_educacao_a_distancia", "universidade_aberta_do_brasil", "reitoria", "polo_de_apoio_presencial", "unidade_academica", "ano_censo") FROM STDIN("cod_local_oferta", "cod_ies", "cod_municipio", "nome_municipio", "cod_uf", "sigla_uf", "sede", "cod_curso_polo", "cod_curso", "nucleo_educacao_a_distancia", "universidade_aberta_do_brasil", "reitoria", "polo_de_apoio_presencial", "unidade_academica", "ano_censo") USING DELIMITERS '|', '\n', '"' NULL AS ''" - < /home/henrique/2012.csv

Unfortunately, because of shell syntax, all " characters have to be quoted using .

[1] https://www.monetdb.org/Documentation/mclient-man-page
[2] https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData

Comment 26869

Date: 2019-02-01 14:38:13 +0100
From: @sjoerdmullender

Changeset 6af9a4a262e3

Comment 26870

Date: 2019-02-01 14:39:05 +0100
From: @sjoerdmullender

Sorry, changed the wrong bug.

Comment 26872

Date: 2019-02-04 11:41:45 +0100
From: Henrique <>

Thank you for your response. Indeed, the command you suggested works. But this COPY command is executed inside a python script we created, using the pymonetdb. So I guess it will not work there.

About the SELinux and AppArmor, I have disabled them when I ran the lastest tests, so I guess the problem is not caused by them.

So to make it working with our python script, I changed some settings from the monetdbd.service:

[Unit]
Deion=MonetDB database server daemon
Documentation=man:monetdbd https://www.monetdb.org/Documentation/monetdbd-man-page
After=network.target

[Service]
Type=forking
User=root
Group=root
ExecStart=/usr/bin/monetdbd start /var/monetdb5/dbfarm
ExecStop=/usr/bin/monetdbd stop /var/monetdb5/dbfarm
Restart=on-failure
PIDFile=/var/run/monetdb/merovingian.pid
PrivateDevices=no
ProtectSystem=full
ProtectHome=false

[Install]
WantedBy=multi-user.target

What I changed was: User, Group and ProtectHome

It seems to work, what do you think?

Comment 26884

Date: 2019-02-18 11:58:39 +0100
From: Henrique <>

Do you think the solution I found of changing the monetdb.service file, specially the "Protect Home", a valid solution worth of be published by your team?

Comment 26885

Date: 2019-02-18 14:42:55 +0100
From: @sjoerdmullender

In the next release (tentative schedule: April 2019), the COPY INTO command will have an option "ON CLIENT" which means the file is to be read by the client application. This is implemented for the C library, but could also be done by the Python library.

I think that is a better solution than running mserver5 as user root (I personally would never do that).

Comment 26886

Date: 2019-02-18 15:48:48 +0100
From: Henrique <>

I agree with you that using the root is not ideal. I just tested that if you only change the "ProtectHome" attribute from "True" to "False" it will work. Maybe this can be implemented for the next release.

Comment 26888

Date: 2019-02-18 16:42:00 +0100
From: @sjoerdmullender

What about ProtectHome=read-only ?

Comment 26890

Date: 2019-02-18 17:39:22 +0100
From: Henrique <>

I tested with the "read-only" attribute and it seems to work. I can read the file and insert into the database. Maybe this is the fix.

Comment 26892

Date: 2019-02-18 18:11:27 +0100
From: @sjoerdmullender

I'll probably do this in the next release then.

I don't really want to enable writing to users' home directories, but reading them might be ok. The users still need to open up their homes to all others for this to work, so that should be ok.

Comment 26895

Date: 2019-02-19 09:51:21 +0100
From: MonetDB Mercurial Repository <>

Changeset 57557e3fe6e0 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=57557e3fe6e0

Changeset description:

Allow reading of users' home directories when running under systemd.
This should fix bug #6680.

Comment 26896

Date: 2019-02-19 12:39:35 +0100
From: MonetDB Mercurial Repository <>

Changeset 6b83b51c2f30 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=6b83b51c2f30

Changeset description:

Add a tunable parameter, mserver5_can_read_home, to allow reading home dirs.
This is the second part of the fix for bug #6680.

Comment 26897

Date: 2019-02-19 12:47:02 +0100
From: @sjoerdmullender

Changing the ProtectHome parameter was one part of the fix to allow mserver5 under the control of systmd to read files in users' home directories. If SELinux is enabled, another part is also needed. I have implemented that using a Boolean parameter that can be manipulated using the setsebool program:
setsebool -P mserver5_can_read_home=true

I hope these changes together are enough to consider this bug fixed.