0

I am running mysql version 5.7.27 for Linux on a remote server. I have a php Script that should load some big csv files into a database via following code $query = "LOAD DATA INFILE 'C:/some/big/file/file.csv' REPLACE INTO TABLE csv_table CHARACTER SET UTF8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES";

The script is running normally but mysql sends out the following error:

Errormessage: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I already did a lot of googling about this error and eventually found some helpful tips about how to set the variable and what to set: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv I also learned mysql will look at 3 places for the configuration:

/etc/my.cnf

/etc/mysql/my.cnf

/usr/local/etc/my.cnf ~/.my.cnf

in respective order.

The my.cnf includes etc/mysql/mysql.conf.d file so I put the following line there [mysqld] secure_file_priv = '/home/my/folder/'

When I check the setting via SHOW VARIABLES LIKE 'secure-f%'; I get the correct value! But when I try to execute the script, I still receive the same old error message. Since the setting is correctly printed, I am not sure what to make of this. Please help me figure out how to get rid of this error. Thanks

EDIT:

Thanks for the hint! It's embarassing but it was really like you said. I tested locally (worked) and copy pasted the code onto the server, where there is no C:/ .. of course. Thank you. After this I got a different error which could be solved with the help of this: LOAD DATA INFILE Error Code : 13

angularNoob
  • 27
  • 7
  • Please be a bit more specific about your paths. Since you (just) will get the error if you specified a wrong path, we basically (just) have to check if you used the correct paths. You can of course obfuscate names, but there is such a big difference between some file in `C:/` and `/home/my/folder` (which usually either the mysqld process or you cannot access, so it's probably not a good choice anyway, unless it is a placeholder for something else) that we really do not know what you are actually doing (or if you e.g. try to load a local file on a windows client that way) – Solarflare Aug 14 '19 at 08:24
  • Thanks for your answer and sorry for the late reply. I will edit main post :) – angularNoob Aug 23 '19 at 07:48

0 Answers0