title: MySQL Docker Container INFILE/ INTO OUTFILE statement on MacOS System
body: <p>I hava a Java program and a mysql Docker container (image: mysql:5.7.20).
  My MacOs is High Sierra 10.13.4.</p>
  
  <p><strong>The problem in short</strong></p>
  
  <p>Using Docker on MacOS (10.13.4.). Inside a docker container (image: mysql:5.7.20) mostly the queries (executed from a java program)</p>
  
  <ul>
  <li><code>LOAD DATA INFILE ...</code></li>
  <li><code>SELECT ... INTO OUTFILE ...</code></li>
  </ul>
  
  <p>are working fine, but sometimes the java program throws the exceptions:</p>
  
  <ul>
  <li>SQLException: Can't create/write to file ‘…’ (Errcode: 2 - No such file or directory)</li>
  <li>SQLException: Can't get stat of ‘…’ Errcode: 2 - No such file or directory)</li>
  <li>SQLException: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement</li>
  <li>SQLException: File ‘…’ not found (Errcode: 2 - No such file or directory)</li>
  </ul>
  
  <p>btw. the file exists and the permissions should be fine (see longer version)</p>
  
  <p><strong>The longer version</strong></p>
  
  <p>The process is the following:</p>
  
  <ul>
  <li>a .csv file gets created</li>
  <li>this .csv file is copied into a directory, which is mounted for the docker container
  
  <ul>
  <li>docker-compose volumes section: <code>- "./data/datahub/import:/var/lib/mysql-files/datahub/import"</code></li>
  </ul></li>
  <li>then MySQL reads this .csv file into a table:
  
  <ul>
  <li><code>LOAD DATA INFILE '.csv-file' REPLACE INTO TABLE 'my-table';</code></li>
  </ul></li>
  <li>then some stuff on that database happens</li>
  <li>after that MySQL writes an .csv output file
  
  <ul>
  <li><code>SELECT</code>tbl<code>.</code>sku<code>,</code>tbl<code>.</code>deleted<code>,</code>tbl<code>.</code>data_source_values<code>INTO OUTFILE 'output.csv' FIELDS TERMINATED BY '|' ENCLOSED BY '"' ESCAPED BY '"' FROM (SELECT ...</code></li>
  </ul></li>
  </ul>
  
  <p>This project has some java integration-tests for this process. These tests are mostly green, but sometimes they fail with:</p>
  
  <ul>
  <li>SQLException: Can't create/write to file ‘…’ (Errcode: 2 - No such file or directory)</li>
  <li>SQLException: Can't get stat of ‘…’ Errcode: 2 - No such file or directory)</li>
  <li>SQLException: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement</li>
  <li>SQLException: File ‘…’ not found (Errcode: 2 - No such file or directory)</li>
  </ul>
  
  <p>The docker-compose file looks like:</p>
  
  <pre><code>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:
  </code></pre>
  
  <p>The Log from that Docker database container shows the following (but sometimes, this happened when all test are green, too)</p>
  
  <ul>
  <li><code>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)</code></li>
  </ul>
  
  <p>The .csv file inside the datahub container, shows the following, fo <code>ls -lha</code></p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 <code>mysql</code> via <code>su mysql</code> inside the Docker container, I get the following:</p>
  
  <pre><code>$ 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
  </code></pre>
  
  <p>Now some strange stuff happened. </p>
  
  <ul>
  <li>with root user, i could make a <code>cat deactivated_product_merged_bub.csv</code></li>
  <li>with mysql user i couldn't i got: </li>
  </ul>
  
  <p>Output:</p>
  
  <pre><code>$ cat deactivated_product_merge_bub.csv
  cat: deactivated_product_merge_bub.csv: No such file or directory
  </code></pre>
  
  <p>I made a <code>stat deactivated_product_merged_bub.csv</code> as MySQL user and suddenly I could make a <code>cat</code> on that file (as you see I <code>chmod 777</code> to that file to get the <code>cat</code> working - but it didn't work).</p>
  
  <ul>
  <li><code>stat</code> as root</li>
  </ul>
  
  <p>Output:</p>
  
  <pre><code>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: -
  </code></pre>
  
  <ul>
  <li><code>stat</code> as mysql user</li>
  </ul>
  
  <p>Output:</p>
  
  <pre><code>$ 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: -
  </code></pre>
  
  <p><strong>Question</strong></p>
  
  <p>Does anyone knows, what happened here or has a hint for what I could search to dig deeper?</p>
  
  <p>My speculation is that it's because using Docker with MacOs and the mounted volume.</p>