MySQL Docker Container INFILE/ INTO OUTFILE statement on MacOS System

Question

I hava a Java program and a mysql Docker container (image: mysql:5.7.20). My MacOs is High Sierra 10.13.4.

The problem in short

Using Docker on MacOS (10.13.4.). Inside a docker container (image: mysql:5.7.20) mostly the queries (executed from a java program)

• LOAD DATA INFILE ...
• SELECT ... INTO OUTFILE ...

are working fine, but sometimes the java program throws the exceptions:

• SQLException: Can't create/write to file ‘…’ (Errcode: 2 - No such file or directory)
• SQLException: Can't get stat of ‘…’ Errcode: 2 - No such file or directory)
• SQLException: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
• SQLException: File ‘…’ not found (Errcode: 2 - No such file or directory)

btw. the file exists and the permissions should be fine (see longer version)

The longer version

The process is the following:

• a .csv file gets created
• this .csv file is copied into a directory, which is mounted for the docker container
  • docker-compose volumes section: - "./data/datahub/import:/var/lib/mysql-files/datahub/import"
• then MySQL reads this .csv file into a table:
  • LOAD DATA INFILE '.csv-file' REPLACE INTO TABLE 'my-table';
• then some stuff on that database happens
• after that MySQL writes an .csv output file
  • SELECTtbl.sku,tbl.deleted,tbl.data_source_valuesINTO OUTFILE 'output.csv' FIELDS TERMINATED BY '|' ENCLOSED BY '"' ESCAPED BY '"' FROM (SELECT ...

This project has some java integration-tests for this process. These tests are mostly green, but sometimes they fail with:

• SQLException: Can't create/write to file ‘…’ (Errcode: 2 - No such file or directory)
• SQLException: Can't get stat of ‘…’ Errcode: 2 - No such file or directory)
• SQLException: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
• SQLException: File ‘…’ not found (Errcode: 2 - No such file or directory)

The docker-compose file looks like:

version: '3'
  services:
    datahub_db:
      image: "mysql:5.7.20"
      restart: always
      environment:
        - MYSQL_ROOT_PASSWORD=${DATAHUB_DB_ROOT_PASSWORD}
        - MYSQL_DATABASE=${DATAHUB_DB_DATABASE}
      volumes:
        - "datahub_db:/var/lib/mysql"
        - "./data/datahub/import:/var/lib/mysql-files/datahub/import"
        - "./data/akeneo/import:/var/lib/mysql-files/akeneo/import"
      ports:
        - "${DATAHUB_DB_PORT}:3306"

...

volumes:
  datahub_db:

The Log from that Docker database container shows the following (but sometimes, this happened when all test are green, too)

• datahub_db_1 | 2018-06-01T10:04:33.937646Z 144 [Note] Aborted connection 144 to db: 'datahub_test' user: 'root' host: '172.18.0.1' (Got an error reading communication packets)

The .csv file inside the datahub container, shows the following, fo ls -lha

root@e02e2074fb6b:/var/lib/mysql- 
files/datahub/import/test/products/kaw# ls -lha
total 4.0K
drwxr-xr-x 3 root root  96 Jun  1 09:36 .
drwxr-xr-x 3 root root  96 Jun  1 09:36 ..
-rw-r--r-- 1 root root 378 Jun  1 06:47 deactivated_product_merged_bub.csv

I think there is no problem, that this file belongs to root because mostly this file can get read by MySQL. When I change to user mysql via su mysql inside the Docker container, I get the following:

$ ls -al
total 4
drwxr-xr-x 3 mysql mysql  96 Jun  1 09:36 .
drwxr-xr-x 3 mysql mysql  96 Jun  1 09:36 ..
-rw-r--r-- 1 mysql mysql 378 Jun  1 06:47 deactivated_product_merged_bub.csv

Now some strange stuff happened.

• with root user, i could make a cat deactivated_product_merged_bub.csv
• with mysql user i couldn't i got:

Output:

$ cat deactivated_product_merge_bub.csv
cat: deactivated_product_merge_bub.csv: No such file or directory

I made a stat deactivated_product_merged_bub.csv as MySQL user and suddenly I could make a cat on that file (as you see I chmod 777 to that file to get the cat working - but it didn't work).

• stat as root

Output:

root@e02e2074fb6b:/var/lib/mysql-files/datahub/import/test/products/kaw# stat 
deactivated_product_merged_bub.csv 
  File: 'deactivated_product_merged_bub.csv'
  Size: 378         Blocks: 8          IO Block: 4194304 regular file
Device: 4fh/79d Inode: 4112125     Links: 1
Access: (0777/-rwxrwxrwx)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2018-06-01 09:23:38.000000000 +0000
Modify: 2018-06-01 06:47:44.000000000 +0000
Change: 2018-06-01 09:04:53.000000000 +0000
  Birth: -

• stat as mysql user

Output:

$ stat deactivated_product_merged_bub.csv
  File: 'deactivated_product_merged_bub.csv'
  Size: 378         Blocks: 8          IO Block: 4194304 regular file
Device: 4fh/79d Inode: 4112125     Links: 1
Access: (0777/-rwxrwxrwx)  Uid: (  999/   mysql)   Gid: (  999/   mysql)
Access: 2018-06-01 09:32:25.000000000 +0000
Modify: 2018-06-01 06:47:44.000000000 +0000
Change: 2018-06-01 09:04:53.000000000 +0000
  Birth: -

Question

Does anyone knows, what happened here or has a hint for what I could search to dig deeper?

My speculation is that it's because using Docker with MacOs and the mounted volume.

Answer 1

It seems to me that you're likely running into two separate problems here, both related to configuration of the MySQL server.

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.

This option requires any file loading or file export to be placed in the specified storage location in the file system. You can find the location using the following command from either the MySQL command line client or from MySQL Workbench:

mysql> show variables like 'secure-file-priv';

The result from this should identify the location where files can be loaded and written out to. No other location should be allowed when secure-file-priv is enabled.

(Got an error reading communication packets)

This is often caused by data being loaded or exported which exceeds the MySQL variable max-allowed-packet.

You can similarly find the value for max-allowed-packet using the following command from either the MySQL command line client or from MySQL Workbench:

mysql> show variables like 'max-allowed-packet';

This has been addressed and well answered in this question: https://dba.stackexchange.com/questions/19135/mysql-error-reading-communication-packets

In summation: In /etc/my.cnf or my.ini under the [mysqld] section:

[mysqld]
max_allowed_packet=268435456

‬In the running MySQL instance:

SET GLOBAL max_allowed_packet = 268435456;

(No restart of the service is required.)

As an aside, the MySQL account that you're using must have been granted the 'FILE' permission in order to load or export. Since you're using the MySQL root (administrative) account this should not be a factor, but I thought I would include it for anyone who comes across this.

I hope this helps.