Mysql: save query INTO OUTFILE error: --secure-file-priv option

[kyleiwaniec]

Hi,

I would like to save the output of my query to a file using the ... INTO OUTFILE 'myFile.csv' syntax. I am getting the following error:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I've tried specifying the full path like this:

... INTO OUTFILE '/var/lib/mysql-files/myFile.csv'

with no luck.

I'm not sure how to modify this setting: --secure-file-priv to specify the path I would like to use.

I'm totally new to docker, and somewhat new to MYSQL. If someone could guide me through the steps I would be very grateful. Thanks.

[kyleiwaniec]

For anyone with a similar issue, this is how I solved it:

1. Use the -d flag so your container doesn't immediately quit which will enable you to open a shell (I don't fully understand this part)

docker run -t -d --name <container> -v /path/to/datadir:/var/lib/mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -e MYSQL_USER=<user> -e MYSQL_PASSWORD=<password>-d mysql:8

1. open the container shell

docker exec -it <container> bash

1. There is no text editor in the container so get vim:

apt-get update
apt-get install vim

1. Edit the conf file which includes the --secure-file-priv option. Set the path to a directory you want to save files in. In this case it's a directory inside the same directory as the datadir so I can easily access it from the host system.

vi /etc/mysql/my.cnf

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# some more stuff.....

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= /var/lib/mysql/iq4Stuff

# some more stuff...

1. exit the container shell
   exit

2. restart the container
   docker restart <container>

3. start mysql
   mysql

4. choose your DB
   use <database>

5. check that the variable was updated. It should look like this:

SHOW VARIABLES LIKE "secure_file_priv";

1. when outputting to file, use the path specified:

SELECT ... INTO OUTFILE '/var/lib/mysql/iq4Stuff/myFile.csv'

[wglambert]

Going to prune this issue, it's still indexed and searchable as such

[bakytn]

if it's relevant to anyone: you can also add to the command line when launching with _docker-compose_ or _stack deploy_

command: --secure-file-priv=/path/to/folder

[pakinja]

if it's relevant to anyone: you can also add to the command line when launching with _docker-compose_ or _stack deploy_

command: --secure-file-priv=/path/to/folder

Hello, do you mean this docker-compose up --secure-file-priv=/home/pakin/Documents/... because it does not works. I need to copy a csv to a table. Thanks!

[yosifkit]

@pakinja it needs to go in the compose yaml (https://docs.docker.com/compose/compose-file/#command):

version: '3.1'

services:

  db:
    image: mysql:5
    command: --secure-file-priv=/home/pakin/Documents/...
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example

[pakinja]

@pakinja it needs to go in the compose yaml (https://docs.docker.com/compose/compose-file/#command):

version: '3.1'

services:

  db:
    image: mysql:5
    command: --secure-file-priv=/home/pakin/Documents/...
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example

Thank you @yosifkit I tried it but it does not work. My yaml file is:

version: '3'

services:

  mysql-dev:
    image: mysql:8.0.2
    command: --secure-file-priv=/home/pakin/Documents/coppel/db-on-docker
    environment:
      MYSQL_ROOT_PASSWORD: xxx
      MYSQL_DATABASE: pruebas
    ports:
      - "3308:3306"

I run it with docker-compose up and gives the error:

ERROR: mysqld failed while attempting to check config
mysql-dev_1  | command was: "mysqld --secure-file-priv=/home/pakin/Documents/coppel/db-on-docker --verbose --help"
mysql-dev_1  | 
mysql-dev_1  | mysqld: Error on realpath() on '/home/pakin/Documents/coppel/db-on-docker' (Error 2 - No such file or directory)
mysql-dev_1  | 2019-08-14T20:53:31.634248Z 0 [Warning] The syntax '--symbolic-links/-s' is deprecated and will be removed in a future release
mysql-dev_1  | 2019-08-14T20:53:31.634384Z 0 [ERROR] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /home/pakin/Documents/coppel/db-on-docker
mysql-dev_1  | 2019-08-14T20:53:31.637009Z 0 [ERROR] Aborting
dbondocker_mysql-dev_1 exited with code 1

I appreciate any help, I am novice in Docker.

[yosifkit]

@pakinja, you'll need to also give that folder from your host to the container; see volumes in the docs: https://docs.docker.com/compose/compose-file/#volumes.

[pakinja]

@pakinja, you'll need to also give that folder from your host to the container; see volumes in the docs: https://docs.docker.com/compose/compose-file/#volumes.

Thank you @yosifkit I just change my yaml file to:
```version: '3.2'
services:
mysql-dev:
image: mysql:8.0.2
volumes:
- type: volume
source: my-db
target: /home/pakin/Documents/coppel/db-on-docker
command: --secure-file-priv=/home/pakin/Documents/coppel/db-on-docker
environment:
MYSQL_ROOT_PASSWORD: 1234
MYSQL_DATABASE: pruebas
ports:
- "3308:3306"

Names our volume

volumes:
my-db:
```

Run it with docker-compose up and now I can see in mysql workbench that the variable secure_file_priv is equal to /home/pakin/Documents/coppel/db-on-docker but still having error code 13. Can't get stat of /home/pakin/Documents/coppel/db-on-docker/db.csv (Errcode: 2- No such file or directory)
Thank you very much for your help!

[tianon]

Please move support questions to the Docker Community Forums, the Docker Community Slack, or Stack Overflow. (GitHub issues are not a support forum.)