Kevinismus Kevinismus - 1 year ago 66
MySQL Question

Access denied when calling mysqlimport

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
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
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download