2

I am trying to import some data from mysql to hive via sqoop. It works when both sqoop and mysql db are on the same host, otherwise it fails. This is the query I am executing.

[user@xyz ~]$ sqoop import --connect "jdbc:mysql://abc.something.com/test" --username user --password pass --table dataSql --hive-import --hive-table test.dataHive --target-dir /tmp/sqoop$RANDOM

Please note that I am currently on host xyz and I am trying to connect to a mysql db on host abc. Following are the error I see.

ERROR: org.apache.sqoop.manager.SqlManager - Error executing statement: java.sql.SQLException: Access denied for user 'user'@'xyz.something.com' (using password: YES)
.
.
.
ERROR: org.apache.sqoop.manager.CatalogQueryManager - Failed to list columns java.sql.SQLException: Access denied for user 'user'@'xyz.something.com' (using password: YES)
.
.
.

and so on. My question is, why is my machine appending its hostname to username ('user'@'xyz.something.com')? How do I solve it?

Vikrant Goel
  • 654
  • 6
  • 20

5 Answers5

1

Sqoop import data from mysql to hive, it will use jdbc interface which connects mysql with mysql-connect-j.

When I use mysql-connector-java-5.1.9.jar, it also has this problem. And I replace it with mysql-connector-java-5.1.38.jar, then sqoop works fine.

Yanhui Zhou
  • 872
  • 6
  • 20
  • I wish I could try it out, I don't have the same setup anymore. But this sounds promising! – Vikrant Goel Oct 10 '16 at 17:03
  • @VikrantGoel Maybe it can be found in myssql-connect-j source code. It will use host with '@' append to username. But I have not found the major difference points between the two version yet. – Yanhui Zhou Oct 11 '16 at 02:25
1

When sqoop and mysql are on the same host, most likely the ip source is localhost, on the remote host this is not longer true and you get access denied.

Log into mysql and do this query:

mysql> select user, host from user;
+------------+---------------------+
| user       | host                |
+------------+---------------------+
| amon       | %                   |
| cm         | %                   |
| hive       | %                   |
| hue        | %                   |
| nav        | %                   |
| navms      | %                   |
| oozie      | %                   |
| retail_dba | %                   |
| rman       | %                   |
| sentry     | %                   |
| root       | 127.0.0.1           |
| root       | localhost           |
| root       | quickstart.cloudera |
+------------+---------------------+

check if your user name user is into this table and has % (any) as hostname or xyz.something.com. It your user is not in this table you need to add it:

mysql> create user 'test'@'%' identified by 'password'; 
mysql> grant select privileges on *.* to 'test'@'%';
mysql> flush privileges;
ozw1z5rd
  • 3,034
  • 3
  • 32
  • 49
0

MySQL connections can be restricted to specific users connecting from specific hosts.

In this case I suspect that the GRANT command was applied to 'some_user@localhost' so that remote connections are blocked...

Have a look at the answers to that post to get the idea.

Community
  • 1
  • 1
Samson Scharfrichter
  • 8,884
  • 1
  • 17
  • 36
  • the problem I am facing is not with mysql specifically. Its with Sqoop, or maybe my environment. That it changes username to username@hostname – Vikrant Goel Aug 25 '15 at 21:55
  • [Back to work, damn] It's not the MySQL client that "changes" the user name. It's the MySQL server that applies *network security rules* based on the IP of the client. Try connecting from an Excel macro on your PC, for example. – Samson Scharfrichter Sep 01 '15 at 21:47
0

'user'@'xyz.something.com' this is actually the user that visit the DB.

As your sqoop host is xyz, the "user" from machine "xyz" is visiting the machine "abc", but the password is wrong (or maybe there isn't a "user" from machine "xyz" is allowed to visit "abc"), so the DB from the "abc" throw out this error and "xyz" sqoop print this message.

Hope this gonna help.

Feiran
  • 61
  • 9
0

The --username user --password pass here specifies the user credentials given access to use the test database, it is not for the mysql as a whole. So modify this accordingly.

KayV
  • 12,987
  • 11
  • 98
  • 148