I am working on this portion of the MySQL tutorial for loading data from a .TSV file.
I have this data file:
name owner species sex birth death
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-04-29
Stored in this location:
/Users/martinfrigaard/MySQLData/pet.txt
My first attempt used the following commands,
LOAD DATA INFILE '/pet.txt' INTO TABLE pet
LINES TERMINATED BY '\r';
and it brought the following error:
ERROR 1148 (42000): The used command is not allowed with this MySQL version
After reading and searching the internet, I found an SO post on how to change the local_infile settings.
SET GLOBAL local_infile = true;
-- Query OK, 0 rows affected (0.00 sec)
And when I checked:
SHOW GLOBAL VARIABLES LIKE 'local_infile';
This looked like it worked.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.00 sec)
But when I run the LOAD DATA file command again,
LOAD DATA INFILE '/pet.txt' INTO TABLE pet
LINES TERMINATED BY '\r';
it results in the following error.
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Setting the GLOBAL secure-file-priv import settings?
I have adjusted the settings in the configuration file in three locations:
1.) As according to this post
mmbp:~ martinfrigaard$ cat ~/.my.cnf
[mysqld_safe]
[mysqld]
secure_file_priv="/Users/martinfrigaard/MySQLData"
2) According to this post
mmbp:~ martinfrigaard$ cat .my.cnf
[mysqld_safe]
[mysqld]
secure_file_priv="/Users/martinfrigaard/MySQLData"
3) And according to this post
GNU nano 2.9.8 /etc/my.cnf
[mysqld]
[mysql]
secure-file-priv = "/Users/martinfrigaard/MySQLData"
But when I restart mysql and check the secure-file-priv settings,
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | NULL |
+------------------+-------+
1 row in set (0.00 sec)
I see this is still null. Any ideas on what else I am missing?
