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>

Solution 1

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.

Solution 2

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.

Solution 3

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

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

Solution 4

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.

View more solutions

Comments

[jake]

So "GRANT ALL PRIVILEGES" doesn't include the FILE privilege?

[Bilal]

You're granting ALL PRIVILEGES at the database level, whereas the FILE privilege is at the server level.

[jake]

Ah yes, I just figured that out. Thanks for the help!

[Meetai.com]

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"

[HattrickNZ]

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 stackoverflow.com/a/18469353/2392358 mysqlimport

[H.Rabiee]

@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.

[happyskeptic]

Worked for me, and IMHO a much better approach than granting an important global permission to one user

[Jesse Nickles]

This still works on MySQL 8.0 e.g. mysql -L -f database < dump.sql and you don't need to use mysqlimport command either.