Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access

Question

I am in a time consuming process of migrating a very large dataset stored in csv file on my laptop to our database. The target database is using mysql v.5.7.27. this db instance is running inside a docker container. I already access to the root db user, it appear to have all the priviledges granted:

SHOW GRANTS;

results:
'GRANT ALL PRIVILEGES ON *.* TO ''root''@''%'' WITH GRANT OPTION'

So far what I have tried:

1. explicitly pass the flag local-infile=1 to let the server allow loading local data loading by clients, refer to https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile

2. override the secure_file_priv setting on startup of sql server, set to empty based on the doc it should disable the secure setting: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv

3. grant root user FILE priviledge on all tables for localhost:

GRANT FILE on *.* to 'root'@'localhost';

Below is the complete docker compose section for starting up the db:

....
awesome-db:
    build: ./awesome/database
    hostname: "localhost"
    environment:
      MYSQL_ROOT_PASSWORD: xxxx
      MYSQL_DATABASE: dbname
    ports:
      - "3308:3306"
    command: mysqld --local-infile=1 --init-file="/tmp/database/schema.sql" --secure_file_priv=""

....

After tried all this, upon running LOAD DATA LOCAL INFILE command like the one below

LOAD DATA LOCAL INFILE '/mylaptopDir/someData.csv'
INTO TABLE AwesomeTable
FIELDS TERMINATED BY ','
IGNORE 1 ROWS;

result in following error:

LOAD DATA LOCAL INFILE 'someData.csv' INTO TABLE AwesomeTable FIELDS TERMINATED BY ',' IGNORE 1 ROWS    Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.   0.0035 sec

I would appreciate for constructive and helpful idea or suggestions on resolving this problem !

Answer 1

In case this helps someone else. Posting the solution: It turns out that my workbench client requires this parameter : OPT_LOCAL_INFILE=1

Refer to this blog here: https://bugs.mysql.com/bug.php?id=91872

After adding it, the query can be executed