Access denied when calling mysqlimport

Question

I have seen more than 10 answers related to my issue on SO and other sites, yet still I can't make the following call to 'mysqlimport' work. I would love the advice of someone who knows what they're doing.

After calling "mysql.server start", I try the following:

mysqlimport -v --ignore-lines=1 --fields-terminated-by=, --columns='a,b,c' --local -u kevinismus -p testdb /path/to/file.csv

but get the error:

"mysqlimport: Error: 1045 Access denied for user 'kevinismus'@'localhost' (using password: YES)"

The only way I have gotten this command to work is when I call

mysql.server start --skip-grant-tables

which I would rather not do. I have tried the answer here, I have flushed privileges, restarted the server, and called mysql_upgrade like suggested, all to no avail. It seems I have all privileges available:

    mysql> select * from mysql.user where User='kevinismus' and Host='localhost'\G

                  Host: localhost
                  User: kevinismus
              Password: 
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: 
      password_expired: N
1 row in set (0.00 sec)

Additionally, if I call SHOW GRANTS for kevinismus@localhost I see:

mysql> show grants for kevinismus@localhost;
+---------------------------------------------------------------------------+
| Grants for kevinismus@localhost                                           |
+---------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'kevinismus'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------------+
1 row in set (0.01 sec)

So what gives? What am I missing about MySQL privileges that prevents me from calling mysqlimport (I know this really calls LOAD DATA INFILE under the hood)? Any help would be greatly appreciated.

FWIW, I'm using MySQL version 5.6.23 and OSX 10.10.4.

Is the password blank or you have removed it? Your mysqlimport command shows "-p" but the result shows "using password YES". Do you enter the password when it asks for it? — Alexander Rubin, Sep 24 '15 at 03:21
So it looks like ''kevinismus'@'localhost' does not have a password. Please try to remove "-p" from mysqlimport or simply hit "enter" when it asks for a password. — Alexander Rubin, Sep 24 '15 at 19:33
AH! That was it. Thanks @AlexanderRubin! If you want to write that up as an answer, I'll accept it. — Kevinismus, Sep 24 '15 at 20:00

score 1 · Accepted Answer · answered Sep 25 '15 at 14:23

It looks like 'kevinismus'@'localhost' does not have a password ("show grants" does not show "identified by ''" part and the select from mysql.users shows blank line in the password fields).

mysqlimport has " -p " option, so it will ask for a password prompt. In this case you can remove "-p" from mysqlimport or simply hit "enter" when it asks for a password.

score 0 · Answer 2 · answered Nov 08 '16 at 07:57

0

I think this is already answered at mysqlimport: Error: 1045, Access denied

In short either use the mysqlimport --local option or grant the FILE privelege: GRANT FILE ON *.* to 'someone'@'%';

answered Nov 08 '16 at 07:57

happyskeptic

123
1
6

Access denied when calling mysqlimport

2 Answers2