0

So I'm remotely accessing a MySQL server and I've tried everything under the sun in order to invoke the following command:

LOAD DATA INFILE 'C:\Users\user1\Desktop\csv\mycsv.csv' 
INTO TABLE mytable
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

However I get the following error:

Error Code: 1290. The MySQL server is running with the --secure-file-priv 
option so it cannot execute this statement

I then tried modifying the statement and adding LOCAL as such:

LOAD DATA LOCAL INFILE 'C:\Users\user1\Desktop\csv\mycsv.csv' 
INTO TABLE mytable
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

and received the following error

Error Code: 1148. The used command is not allowed with this MySQL version

I ran the following 2 commands to check my global variables pertaining to these two errors which returned the following:

SHOW VARIABLES LIKE "secure_file_priv";
Returns NULL

And 

SHOW GLOBAL VARIABLES LIKE 'local_infile'; 
Returns ON

I contacted the system administrator to change the my.ini. I set my local secure_file_priv to a directory on my PC with no luck. I have no access to the PC the server lies on so I can't move files to any local directories, only remotely accessed ones.

I have referred to every solution under How should I tackle --secure-file-priv in MySQL? and How to import CSV file to MySQL table with no luck.

If you have any other ideas please let me know. Thanks

mewkie
  • 37
  • 5

1 Answers1

0

secure_file_priv is set at start up and can not be dynamically changed. Make the following addition to your my.ini file, then restart mySQL.

secure-file-priv="C:\Users\user1\Desktop\csv\"
Nosajimiki
  • 978
  • 1
  • 8
  • 15
  • I have tried this with no luck. I imagine it's because I'm accessing the MySQL server remotely – mewkie Oct 24 '18 at 13:32
  • If mySQL is on another server, it will be looking for files on its C drive not your C drive. In many cases of shared hosting, mySQL is not even on the same directory structure as you have file level permissions to, which may make this impossible. That said, shared hosting often has something like phpMyAdmin installed, which allows you to manually push CSVs to the database via a GUI, but you may not be able to automate this for repetitive tasks. You should probably reach out to your hosting company for specific instructions. – Nosajimiki Oct 24 '18 at 18:43