×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Loading From A CSV File

Loading From A CSV File

Loading From A CSV File

(OP)
I'm fairly new to MySQL. I'm using version 8.0 on Linux Mint 20. I'm trying to fill a table with a csv file. The csv file is on another drive, so I used the following to load the table:

CODE --> SQL

LOAD DATA LOCAL INFILE '/path/to/file/whatever.csv'
INTO TABLE my_table
LINES TERMINATED BY '\r\n'; 

I forget what the original error was, but, searching, I found out about the mysql variable "secure_file_priv". The value of the variable is a path to where files can be uploaded from. In this case: /var/lib/mysql-files/
I was reading about how to disable that feature, but, rather than do that, I thought I would just copy the csv file to that directory (and maybe mess with disabling it later). I put the csv file directly in that folder. I tried again. I got some other cannot-open-file error.
Then I learned about the "local_infile" variable. I changed it from OFF to ON. Then I re-ran the code. I got:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I know it's running with that option on. That's why I tried to appease it and put my file in that exact directory. This feels very Catch 22.

RE: Loading From A CSV File

Quote (AbidingDude)

I changed it from OFF to ON. Then I re-ran the code.

Between those two steps, did you restart the service? You should restart services after config changes.

RE: Loading From A CSV File

(OP)
I did... pretty sure anyway... After running:

CODE --> SQL

SET GLOBAL local_infile=TRUE; 
I quit out of MySQL. The I ran:
sudo service mysql stop
sudo service mysql start

Then when I checked the local_infile variable again, is was set back to OFF. So I set it back on again. Then I loaded DBeaver (the program I've been using to practice database stuff). I tried again. DBeaver gave me a java.io.FileNotFoundException. I thought maybe it was some kind of client side problem. So I went back to my terminal where I was logged into mysql. I copied and pasted the same file loading code. I got this error:
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

Just to triple-check I ran:

CODE --> SQL

SHOW GLOBAL VARIABLES LIKE "local%"; 
And I get:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec) 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close