I try to insert some lines from csv file to MySQL database "elevage" by command line. The file is names "animal.csv". Below is my request:
`mysql> LOAD DATA LOCAL INFILE 'F:/MYSQL/animal.csv'
-> INTO TABLE Animal
-> FIELDS TERMINATED BY ';' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n'
-> (espece, sexe, date_naissance, nom, commentaires);
and I run into this error message
`ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides`.
Then have used the following code:
`SHOW GLOBAL VARIABLES LIKE 'local_infile'`;
and I found: local_file was "OFF". Then I tried to set it to "ON" using the following code :
`SET GLOBAL local_infile=1;`
Unfortunately, I run into another error message:
`ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation`
Although, when I check the grants for the user "student" in which I'm working:
mysql> SHOW GRANTS FOR CURRENT_USER();
I get:
+--------------------------------------------------------------+
| Grants for student@localhost |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `student`@`localhost` |
| GRANT ALL PRIVILEGES ON `elevage`.* TO `student`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
which means that "student" has all the privileges on the database "elevage".
Please advice.
The SYSTEM_VARIABLES_ADMIN privilege (as well as the deprecated SUPER privilege) is not a per-database privilege, but rather a per-server privilege. And, it's an administrators' privilege, not a users' privilege.
Why do you need such privileges to use LOAD DATA? Because that command requires its user to write files directly into the database server machine's file space. So users of that command must be completely trusted by the database server.
To use LOAD DATA LOCAL you'll need to get a server administrator to grant those privileges to you, with something like this:
GRANT SUPER, SYSTEM_VARIABLES_ADMIN ON *.* TO 'student'@'localhost';
If this is a shared server, it seems unlikely that your administrator will grant you this privilege. If it's your own server, you are the administrator, and you can use your root account either to grant your student account the privilege, or use the root account directly to run your LOAD DATA command.