CODEWITHSUNDEEP
mysqlmariadb
Ben Engbers
Ben Engbers

Reputation: 453

Failed to normalize the argument for --secure-file-priv

I need to execute a load_file() statement. The documentation tells me that secure_file_priv has to be disabled by setting it to "" or should be set to a path. If not, load_file() will return NULL.

sudo systemctl restart mariadb.service

    Job for mariadb.service failed because the control process exited with error code.
    See "systemctl  status mariadb.service" and "journalctl  -xe" for details.

    > sudo systemctl  status mariadb.service
    mariadb.service - MariaDB 10.1 database server
       Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
       Active: failed (Result: exit-code) since Tue 2017-09-12 10:50:59 CEST; 13s ago
      Process: 4815 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
      Process: 5306 ExecStart=/usr/libexec/mysqld --basedir=/usr $MYSQLD_OPTS $_WSREP_NEW_CLUSTER (code=exited, status=1/FAILURE)
      Process: 5271 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
      Process: 5249 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited,         status=0/SUCCESS)
     Main PID: 5306 (code=exited, status=1/FAILURE)
       Status: "MariaDB server is down"
    
    sep 12 10:50:57 BEL002.HOME systemd[1]: Starting MariaDB 10.1 database server...
    sep 12 10:50:58 BEL002.HOME mysql-prepare-db-dir[5271]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
    sep 12 10:50:59 BEL002.HOME mysqld[5306]: 2017-09-12 10:50:59 139800931358976 [Warning] Failed to normalize the argument for --secure-file-priv.
    sep 12 10:50:59 BEL002.HOME mysqld[5306]: 2017-09-12 10:50:59 139800931358976 [ERROR] Aborting
    sep 12 10:50:59 BEL002.HOME systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
    sep 12 10:50:59 BEL002.HOME systemd[1]: Failed to start MariaDB 10.1 database server.
    sep 12 10:50:59 BEL002.HOME systemd[1]: mariadb.service: Unit entered failed state.
    sep 12 10:50:59 BEL002.HOME systemd[1]: mariadb.service: Failed with result 'exit-code'.

I have executed chmod 777 /path/.

If I set secure_file_priv to "", select load_file("file name") still returns NULL.

How can I disable secure_file_priv?

Upvotes: 1

Views: 3533

Answers (3)

Adeel Raza Azeemi
Adeel Raza Azeemi

Reputation: 793

by default now mariaDB and (I assue MySQL ) ensure that users cannot write except to the /tmp folder. but we can change this behaviour of MySQL by changing it service startup variable; so we could write to other directories. this could be done by changing two Protect variable in the mariaDB (MySQL) service file. You had to change both the variables in mariadb.service

sudo nano /lib/systemd/system/mariadb.service

ProtectSystem=full

to ProtectSystem=off

and

ProtectHome=true

to ProtectHome=false

in the file /etc/mysql/mariadb.conf.d/50-server.cnf; add the line in the [mysqld] section

secure-file-priv=

that it now you could write to any directory in the system

or you could write to a specific directory by following the following

  1. Create a folder on the top level directory i.e. /

    sudo mkdir mysql

  2. change is permission to 0777

    sudo chmod 0777 mysql

  3. Change it ownership to mysql

    sudo chown mysql:mysql mysql

  4. Change the permission in .cnf file

    sudo gedit /etc/mysql/mariadb.conf.d/50-server.cnf

  5. Add the line in [mysqld] section

    secure-file-priv = /mysql

  6. Restart the mysql service

    sudo systemctl restart mysql.service

  7. Export the table to csv file

    SELECT * INTO OUTFILE '/mysql/fileName.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM tableName;

That's it; you had successfully exported your table to a csv file.

Upvotes: 0

René Wachter
René Wachter

Reputation: 41

I use OpenSuSe LEAP 42.3

I changed in the /etc/my.cnf.d/secure_file_priv.cnf from

secure_file_priv = /var/lib/mysql-files

to

secure_file_priv = /var/lib/mysql

The folder /var/lib/mysql-files not exists, this is the reason.

Upvotes: 4

yaroslaff
yaroslaff

Reputation: 81

This could happen because of systemd protection. For me, on Debian 10 buster, I had similar problem:

if secure-file-priv=/home - daemon starts, but fails when SELECT ... INTO OUTFILE. If secure-file-priv=/home/username - daemon fails to start with Failed to normalize the argument for --secure-file-priv error. But if I use other path for secure-file-priv, everything works well, only /home directory was 'special'.

Easy fix: edit mariadb systemd service file (for debian this is /lib/systemd/system/mariadb.service). Find code:

# Prevent accessing /home, /root and /run/user
ProtectHome=true

and set ProtectHome=false.

If you have problems with other directories (/usr, /boot, /etc), check ProtectSystem option. And if problem with /tmp - check PrivateTmp.

Documentation: https://www.freedesktop.org/software/systemd/man/systemd.exec.html

Upvotes: 4

Related Questions