'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.

  • When I edit my.cnf and set this variable in the mysqld-group to "", I can restart mariadb without any problems. -If I set this variable to a path (secure_file_priv="/path/", I get the following output:
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?

mysqlmariadb


Solution 1:[1]

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

Solution 2:[2]

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.

Solution 3:[3]

I don't know if this is the best solution, but it worked in Linux (Ubuntu 20.04.4 LTS, MariaDB: 10.6.7-MariaDB).

  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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 yaroslaff
Solution 2 Cosmin Staicu
Solution 3 J. Scott Elblein

Related Questions