0

I have looked around for a solution to this problem and nothing works so far. MySQL 8.0.20 tells me that I cannot load data infile to a database table because the database is running with the option --secure-file-priv set to on.
Some of the articles I've read on stackoverflow and elsewhere have suggested finding and altering my.cnf to bring about this change. I found my.cnf and when I opened it in an editor, it made mention of homebrew which I'm no longer using to install or maintain mysql. I tried making the change anyway, and it still made no difference.
I should probably mention that I'm running MySQL 8.0.20 which I installed from the terminal program without any package managers or anything else. My computer runs Mac OS X 10.15.6 without any trouble. I even downloaded the manual (it's ONLY 50.8 megabytes as a text file!) and I checked all occurrences of "load data infile" as well as "secure-file-priv" and they were all really remote references to something entirely else. I am almost finished with a function on a website that I wondered if I could EVER accomplish and it's really just a matter of getting past this problem. Thank you oh wise and learned individuals whom I predict will offer me the magic words to make this happen. Here is an update to this question. I have installed MySQL 8.0.20 using homebrew and I have found multiple instances of my.cnf. I have edited all the my.cnf files I can find, trying secure-file-priv="", secure_file_priv="", I've tried including spaces or tab characters between equals and the quotation marks, I've tried specifying the folder where the file actually is and no matter what I try, the result is still show variables like "secure%"; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | secure_file_priv | NULL | +------------------+-------+ 1 row in set (0.01 sec)

and of course ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Here is the NEED TO KNOW information about secure-file-priv, my.cnf and many other things about MySQL on any platform. It seems that when installed, MySQL puts arbitrary incidents of my.cnf in various folders throughout your system. On my Mac, there were my.cnf files in /usr/local/etc, /usr/local/mysql/etc/ and other places. I should mention that I've installed, uninstalled and re-installed MySQL about 5 or more times. Sometimes I used Homebrew and sometimes I didn't. Anyway, the punch line for the whole lesson is this - by running the command mysql --help you will get a long list of options to run the program and all the variables that are currently defined. Among those variables is a list of folders that MySQL will check in order to find the my.cnf file. As it happened on my machine, the list did not include any of the folders where the file is. For me, it was a simple matter of copying my.cnf into a folder that is checked by MySQL when it starts and badda-bing! It works! I can now import the comma-delimited file into a table and move along from there. Thank you everyone for your participation and patience with my asking the same question that several others have asked.

Jeff
  • 71
  • 1
  • 1
  • 5
  • Have you tried the `LOCAL` modifier? https://stackoverflow.com/a/40630313/4401980 – Muhammad Hamza Jul 24 '20 at 13:08
  • I did try the LOCAL modifier. It was a long time ago (ok, a few days) and it didn't work. I don't remember the response from MySQL. Needless to say it was not favorable. – Jeff Jul 24 '20 at 18:01
  • For the sake of a more specific answer, I went and tried the LOCAL modifier again. Here is the response. – Jeff Jul 24 '20 at 18:04
  • Loading local data is disabled; this must be enabled on both the client and server sides – Jeff Jul 24 '20 at 18:05
  • There is an answer to the above message you faced, check out if it works for you https://stackoverflow.com/a/60717467/4401980 – Muhammad Hamza Jul 25 '20 at 19:06

0 Answers0