0

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 !

2
  • You have set secure_file_priv to empty string - hence you have allowed LOAD DATA statement without any restriction by the source directory. Your file to be loaded is local file (according to shown filepath). In this case you do NOT need in LOCAL. So remove this keyword from the statement.
    – Akina
    Feb 3, 2023 at 5:52
  • @Akina No, the LOCAL is required because the file being uploaded from my laptop not from the server. Feb 3, 2023 at 22:48

1 Answer 1

3

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

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.