Use csv file to mysql

Question

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.

@nbk That's not correct. ON, 1, and true all do the same thing when setting boolean variables. — Bill Karwin
– Bill Karwin, Commented Aug 1, 2020 at 16:22
ALL privileges on a specific database does not include SUPER. SUPER is a global privilege and it must be on *.*. Are you using Amazon RDS? You can't set global variables directly on RDS. You must change variables using parameter groups. — Bill Karwin
– Bill Karwin, Commented Aug 1, 2020 at 16:23
@BillKarwin no mysql needs the ON to replace the OFF, i have tested that fpor another probolem with mysql 8.0.21 — nbk
– nbk, Commented Aug 1, 2020 at 16:41
@nbk I just tested myself with 8.0.21. ON, 1, and true do the same thing at least with respect to local_infile. When I select @@local_infile; to see the result, all three values are converted to 1. Likewise OFF, 0, and false are converted to 0. — Bill Karwin
– Bill Karwin, Commented Aug 1, 2020 at 16:56

O. Jones · Accepted Answer · 2020-08-01 17:21:51Z

1

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.

answered Aug 1, 2020 at 17:21

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bill Karwin Over a year ago

Those privs aren't needed for LOAD DATA INFILE — they're needed for SET GLOBAL (anything).

K. Brezesky Over a year ago

Please what do you mean by shared server? As far as I'm concerned, I installed MySQL with my laptop and I set it on the default user "root". I'm taking MySQL courses on open classroom site web. Then for pedagogical reason, they ask to create another user " student" for application. So I have two users on MySQL. Is my server shared by doing that?

K. Brezesky Over a year ago

@BillKarwin I end up by concluding that by "student" is sharing the server of "root". Then I went through MySQL with the user 'root" and have been able to run the code: GRANT SUPER, SYSTEM_VARIABLES_ADMIN ON *.* TO 'student'@'localhost';. Then I went MySQL once more with the user "student" after giving it the "SUPER" privilege so that to insert some lines from csv file to MySQL database "elevage" by command line.

K. Brezesky Over a year ago

with the code :

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);

Unfortunately I run into the same error message: ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

Collectives™ on Stack Overflow

Use csv file to mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related