Loading From A CSV File

[AbidingDude]

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:

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.

 

[spamjim]

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.

 

[AbidingDude]

I did... pretty sure anyway... After running:

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:

SHOW GLOBAL VARIABLES LIKE "local%";

[pre]And I get:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.00 sec)[/pre]