1

Been trying to solve this problem for hours now and nothing seems to be working. I am trying to set up MySQL to be able to upload csv files automatically in code like this:

LOAD DATA INFILE '/Users/aubrey/Documents/dbproject/dates_table.csv' 
INTO TABLE dates 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

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

I read up on stackoverflow and mysql docs about the secure-file-priv options. The result of SHOW VARIABLES LIKE "secure_file_priv"; is NULL. So I need to change that to be the directory I want in the my.cnf file. Cool.

I did that. /etc/my.cnf file

And after restarting the server, that shows up in the workbench Options File (Administration tab, under Instances) Options file shows directory path

But when I run SHOW VARIABLES LIKE "secure_file_priv"; again, I still get NULL still null

I've tried both secure-file-priv and secure_file_priv, both show up in the options correctly but give a null in SHOW VARIABLES.

What am I doing wrong?

Running Ventura 13.1, MySQL 8.0.32ARM64

Aubrey
  • 55
  • 1
  • 1
  • 5
  • Did you restart the mysql service after changing the value in the my.cnf file? Also check your MySQL error log. There will be a warning if the directory is insecure. For example, if it is accessible to all users, it won't be allowed. – Bill Karwin Feb 16 '23 at 18:38
  • @BillKarwin I did restart the servers after changing the file. This might be a problem, when I look for the error logs I get this: "Error creating log reader: [Errno 13] Permission denied: '/usr/local/mysql/data/mysqld.local.err' "... – Aubrey Feb 16 '23 at 18:46
  • That's probably because the file is not readable by your non-privileged user. Try reading it with `sudo`. – Bill Karwin Feb 16 '23 at 18:49
  • Only two [Warning] are: [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. – Aubrey Feb 16 '23 at 19:00
  • Hmm. I tried it (also Mac ARM64 with MySQL 8.0.32 installed form homebrew), and it works for me. – Bill Karwin Feb 16 '23 at 20:22
  • Just tried uninstalling, then installing with Homebrew and I get the same thing. my.cnf says the directory, the options file has the same directory, but show variables still null. AARGHUJNEFWBIWNDMJK – Aubrey Feb 16 '23 at 21:25
  • My only other suggestion is that you're editing the wrong my.cnf file. It can be quite difficult to figure out which my.cnf file is the one in use on a Mac. – Bill Karwin Feb 16 '23 at 22:38
  • That could be, but I just don't know why the Options File would pick up the changes in the my.cnf file but the DB doesn't. Thanks for your efforts though! – Aubrey Feb 17 '23 at 16:48

0 Answers0