mysqlimport: Error: 1045, Access denied

Question

Does anyone know why I get this error when running mysqlimport?

mysqlimport -u someone -pwhatever --columns=a,b,c,d,e bar /var/tmp/baz.sql
mysqlimport: Error: 1045, Access denied for user 'someone'@'%' (using password: YES), when using table: baz

However...

mysql -u someone -pwhatever
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 199
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+------------------------------------------------------------------------------------------------------------+
| Grants for someone@%                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'someone'@'%' IDENTIFIED BY PASSWORD '*BLAHBLAHBLAH' |
| GRANT ALL PRIVILEGES ON `bar`.* TO 'someone'@'%'                                          |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

score 31 · Answer 1 · answered Feb 26 '12 at 13:03

31

You can avoid the need for the extra privileges by using the --local parameter to mysqlimport:

--local, -L

           Read input files locally from the client host.

answered Feb 26 '12 at 13:03

Omry Yadan

25,948
16
54
77

2

Worked for me, and IMHO a much better approach than granting an important global permission to one user – happyskeptic Nov 08 '16 at 07:55

Mike Conigliaro · Accepted Answer · 2016-07-27T15:27:25.670

25

OK, it turns out that the FILE privilege is a "global" privilege, which apparently means you can't selectively enable it on certain databases, tables. etc. That's why my previous grant statement on bar.* had no effect:

GRANT ALL PRIVILEGES ON `bar`.* TO 'someone'@'%'

You need to grant FILE privileges on *.*:

GRANT FILE ON *.* to 'someone'@'%';

Hope this helps someone.

edited Jul 27 '16 at 15:27

answered Jul 26 '11 at 22:45

Mike Conigliaro

970
1
9
22

If you get 'ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES', after setting different parameters, revert back to what this answer shows: "GRANT FILE ON *.* to user@localhost;" - "the FILE privileges are global and cannot be applied to a single database" – Meetai.com Jun 26 '14 at 02:50

score 13 · Answer 3 · answered Oct 15 '13 at 19:06

13

Some would instead opt for this command, skipping the extra FILE grant.

mysql -u username -p <yourdbname> < yourfile.sql

answered Oct 15 '13 at 19:06

H.Rabiee

3,918
3
20
34

2

how do i get this to work on importing a csv file? Do i just rewrite the csv file in a .sql format and then imprt it? or can I use something like http://stackoverflow.com/a/18469353/2392358 `mysqlimport` – HattrickNZ Nov 02 '15 at 22:35
@HattrickNZ well that command is only if you have statements in your .sql file. If you have .csv I guess you would have to do something different, like posted in your link. – H.Rabiee Nov 03 '15 at 07:03

score 6 · Answer 4 · answered Jul 26 '11 at 22:13

6

mysqlimport is a command-line interface to the LOAD DATA INFILE statement, for which you need the 'FILE' privilege (server level).

From LOAD DATA INFILE syntax:

Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege.

answered Jul 26 '11 at 22:13

Bilal

922
6
7

So "GRANT ALL PRIVILEGES" doesn't include the FILE privilege? – Mike Conigliaro Jul 26 '11 at 22:32
3

You're granting ALL PRIVILEGES at the database level, whereas the FILE privilege is at the server level. – Bilal Jul 26 '11 at 22:35
Ah yes, I just figured that out. Thanks for the help! – Mike Conigliaro Jul 26 '11 at 22:47

mysqlimport: Error: 1045, Access denied

4 Answers4

Linked