ERROR 1 (HY000) at line 1: (Errcode: 13 “Permission denied”)

I have to save my SQL query output in a text file. When i run the needed query:

mysql -u root -p nextcloud -e "select * from oc_share INTO OUTFILE '/root/sql/test.txt'"

..it throws this error:

ERROR 1 (HY000) at line 1: Can't create/write to file '/root/sql/test.txt' (Errcode: 13 "Permission denied")

Why does this happen. I am using Ubuntu 20.04.

Answer

You have no access to directory /root/sql because of secure_file_priv. Please do SHOW VARIABLES LIKE "secure_file_priv"; and see what directory you can use for outfile. This paramater can be set through my.cnf or cmd.

mysql> show variables like'%secure_file_priv%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+