2

I'm trying to import a database in Workbench, in localhost. I select the file (it's actyally a csv file) and when I click on "start import" I get this error:

Operation failed with exitcode 1
09:51:12 Restoring /home/carlo/Downloads/db_export.csv
Running: mysql --defaults-extra-file="/tmp/tmpt_73Sg/extraparams.cnf"  --host=127.0.0.1 --user=carlo --port=3306 --default-character-set=utf8 --comments < "/home/carlo/Downloads/db_export.csv"
ERROR 1045 (28000): Access denied for user 'carlo'@'localhost' (using password: YES)

The problem seems to be the password, but I'm sure I'm inserting it correctly. Also, before clicking on "start import" I'm asked for the password to connect and it works. What could be wrong?

I created my user from the mysql shell client, following the instructions on the mysql website, like this:

CREATE USER 'carlo'@'localhost' IDENTIFIED BY 'carlo';
GRANT ALL PRIVILEGES ON *.* TO 'carlo'@'localhost' WITH GRANT OPTION;
CREATE USER 'carlo'@'%' IDENTIFIED BY 'carlo';
GRANT ALL PRIVILEGES ON *.* TO 'carlo'@'%' WITH GRANT OPTION;
Carlo
  • 4,016
  • 7
  • 44
  • 65

3 Answers3

3

I had a similar issue in MySQL Workbench 8. The error I got was:

ERROR 1045 (28000): Access denied for user 'user'@'ip' (using password: NO) Operation failed with exitcode 1

But this error was misleading, because it wasn't an error related to password, just that my user didn't have permission to LOCK TABLES. So the solution for me was to dump the table I wanted to import again, and add --skip-add-locks flag after mysqldump (in my case I couldn't modify the DB user's permissions).

So if you run into this issue, also check your DB user's privileges.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Peter Szalay
  • 376
  • 3
  • 13
0

Since you are logged in already the import should work as expected. Just make sure you are importing an SQL file. The dump/restore feature in MySQL Workbench uses a private config file to avoid passing the password on the command line (which would be a security problem). However, it can only pass on what it was given. So, if your user name/PW combination doesn't work then you made a mistake there.

Grants are likely not the problem as the error is about a user not allowed to connect. So check especially this part. Also make sure you have the right host used (also for the user).

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181
  • The thing is that when I click on import, Workbench pops up a dialog window asking me for the password, so it should be it. I don't know how to extend that command in Workbench. Also, you're right about the csv file, but now I have the sql and the problem is the same – Carlo Feb 05 '14 at 09:19
  • I'm a bit confused. How does MySQL Workbench come into play when you use the command line? Using MySQL Workbench you can do it in the admin section to load your SQL. You have to open a connection for this section, so you have to be logged in anyway already. – Mike Lischke Feb 05 '14 at 11:24
  • I said I used the command line just to create the user. The error message comes from Workbench. I know I have to be logged in already, indeed in the question I said that it's strange because I can actually log in. – Carlo Feb 05 '14 at 13:45
  • Ah sorry, I totally misunderstood. I adjusted my answer. – Mike Lischke Feb 06 '14 at 08:04
0

Have you done FLUSH PRIVILEGES after Grant?

Even if it doesn't work, can you give us out

SELECT USER, HOST FROM MYSQL.USER;
SELECT USER(), CURRENT_USER();
Ravi
  • 79
  • 2
  • 11
  • I haven't done Flush Privileges. The first command output is: `ERROR 1146 (42S02): Table 'MYSQL.USER' doesn't exist`. The second one: `USER(): carlo@localhost, CURRENT_USER(): carlo@localhost` – Carlo Feb 10 '14 at 10:46
  • FLUSH PRIVILEGES is only needed if modifying the table directly. If one uses GRANT then FLUSH PRIVILEGES is _not_ needed. See https://stackoverflow.com/a/36464093/292408. – Elijah Lynn Feb 12 '21 at 08:32