Re: Loading CSV Data with commas

[Jim Palmer]

Peter Brawley Wrote:
-------------------------------------------------------
I ran the following:

> mysql --verbose --help | grep my.cnf

MBP-2014-Jim:~ jkpalmer$ mysql --verbose --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

I then ran
MBP-2014-Jim:~ jkpalmer$ cat /etc/my.cnf

and it returned

[mysqld]
secure-file-priv = "/mysqlfiles/"
caching_sha2_password_auto_generate_rsa_keys = off

then ran
MBP-2014-Jim:~ jkpalmer$ cat /usr/local/mysql/etc/my.cnf

and it returned
cat: /usr/local/mysql/etc/my.cnf: No such file or directory

then ran
MBP-2014-Jim:~ jkpalmer$ cat /etc/mysql/my.cnf

and it returned
cat: /etc/mysql/my.cnf: No such file or directory

then ran
MBP-2014-Jim:~ jkpalmer$ cat ~/.my.cnf
and it returned
cat: /Users/jkpalmer/.my.cnf: No such file or directory

So I assume that the file my.cnf is located in /etc - CORRECT?

Trying several different values for secure_file_priv

"/mysqlfiles"
"/mysqlfiles/"
/mysqlfiles
/mysqlfiles/
mysqlfiles

for each of the above, I change the value in my.cnf, save the file, stop the server and restart it and then execute

LOAD DATA INFILE '/mysqlfiles/Sample.csv'
INTO TABLE finances.MCC_Codes
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(mcc_code, end_mcc_code, mcc_description);

query all produce the same 1290 error:
08:07:57 LOAD DATA INFILE '/mysqlfiles/Sample.csv' INTO TABLE finances.MCC_Codes FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (mcc_code, end_mcc_code, mcc_description) Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 0.0066 sec