Docker Container MySQL and PHP "LOAD DATA LOCAL INFILE" Permission Woes

  docker, docker-compose, mysql, pdo, php

I’ve got a docker container setup running on a machine, which has:

  • A PHP Web Server Container
  • A MySQL 8.0 Container
  • A PHPMyAdmin Container

Here’s roughly what my docker-compose.yml file has:

version: '3.3'
services:      
  front:
    build:
      context: ./front
      dockerfile: Dockerfile
    container_name: front-end
    depends_on:
      - db
    ports:
      - 8000:80
  
  # phpmyadmin
  phpmyadmin:
    container_name: phpmyadmin
    depends_on:
      - db
    image: phpmyadmin/phpmyadmin
    restart: always
    ports:
      - "8080:80"
    environment:
      PMA_HOST: db
      MYSQL_ROOT_PASSWORD: derp

  db:
    container_name: mysql8
    image: mysql:8.0
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment: 
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: dev_db
      MYSQL_USER: user
      MYSQL_PASSWORD: pass
    ports:
      - 6033:3306

And, for that Front part, here’s the Dockerfile

FROM php:7.3.3-apache
RUN apt-get update && apt-get upgrade -y libfreetype6-dev libjpeg62-turbo-dev libpng-dev
RUN docker-php-ext-install mysqli
RUN docker-php-ext-install pdo pdo_mysql
RUN docker-php-ext-configure gd --with-freetype-dir=/usr --with-jpeg-dir=/usr --with-png-dir=/usr
RUN docker-php-ext-install gd
RUN a2enmod rewrite
EXPOSE 80

It’s nothing too fancy, right? Probably.

Anyway, I’m working on a feature that allows the upload of a .csv file, which will then get loaded into a MySQL table. I’m running into some interesting errors when I attempt to do this, and after extensive digging and research, I’m not finding a solid solution.

Basically, when I try to execute this query:

try {
  $sql = "LOAD DATA LOCAL INFILE '" . $tmpName . "'
        INTO TABLE psy_products_temp_sales
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY 'rn'
        (@col1,@col2,@col3,@col4,@col5,@col6) 
        SET
        product_temp_name = @col1,
        product_temp_sku = @col2,
        product_temp_posted_date = @col3,
        product_temp_sales_period = @col4,
        product_temp_qty_sold = @col5,
        product_temp_total_earned = @col6;";
  $sql = $pdo->prepare($sql);
  $sql->execute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY);
} catch (Exception $e) {
  $output .= "Error: " . $e;
}

My PDO Object is set up like this:

$dsn = "mysql:host=" . $host . ";dbname=" . $db . ";charset=" . $charset;
$options = [
  PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  PDO::MYSQL_ATTR_LOCAL_INFILE => true,
  PDO::ATTR_EMULATE_PREPARES   => false
];
try {
  $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
  $log->error(
    "Could not establish database connection",
    ["message" => $e->getMessage()]);
  throw new PDOException($e->getMessage(), (int)$e->getCode());
}

Notice the PDO::MYSQL_ATTR_LOCAL_INFILE => true flag is set, and in my php.ini, mysqli.allow_local_infile is set to ON.

So when I attempt to run this, I don’t get an error, but MySQL is not reading the uploaded .csv file and loading it into the table. I’m able to actually READ that temporary .csv file directly using fopen(), but MySQL refuses to read it. And these .csv files have tens of thousands of rows, so I’m not looping through each row and running a query. No way.

When I attempt to run this in PHPMyAdmin, I get this error:

LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile

Then, when I try running that query in the MYSQL container itself in the CLI, I get this error:

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

The only thing that makes sense here is that the mysql8 container is being denied access to the front container’s /tmp directory?

If this is the case, how do I set this up to where permissions are granted? I’m not the most saavy with CLI or docker container cross communication, but there’s got to be a way for this to work.

Sorry this got long-winded. Thanks in advance!

Source: Ask PHP

LEAVE A COMMENT