Sqoop export to mysql job tries to connect wrong database

[#1]

I have a table in Hive, named table_hive and I want it export to mysql database(mydatabase), table_mysql on server2. I submit sqoop export job from server1 on which sqoop client is installed. After submit, it tries to connect server1's mysql database as if it were, which is not. I don't understand why it tries to connect server1 although command --connect parameter indicates server2.

sqoop export --connect jdbc:mysql://server2:3306/mydatabase --username user --password password --table table_mysql --export-dir /apps/hive/warehouse/staging.db/table_hive

Error codes:

/usr/hdp/2.6.3.0-235//sqoop/conf/sqoop-env.sh: line 21: HADOOP_CLASSPATH=${hcat -classpath}: bad substitution
Warning: /usr/hdp/2.6.3.0-235/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/12/01 15:50:13 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.6.3.0-235
17/12/01 15:50:13 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager)                            . Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
17/12/01 15:50:13 INFO manager.SqlManager: Using default fetchSize of 1000
17/12/01 15:50:13 INFO tool.CodeGenTool: Beginning code generation
17/12/01 15:50:14 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: Access denied for user 'user'@'server1' (using password: NO)
java.sql.SQLException: Access denied for user 'user'@'server1' (using password: YES)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)
        at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1709)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2488)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2521)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:839)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:421)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:350)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:904)
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:763)
        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786)
        at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289)
        at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
        at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:246)
        at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:328)
        at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1853)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1653)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
        at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:64)
        at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:243)
17/12/01 15:50:14 ERROR tool.ExportTool: Encountered IOException running export job: java.io.IOException: No columns to generate for ClassWriter

[#2 · Accepted solution]

Hi @Erkan ŞİRİN,

From the error, I could see that, it was the access issue that server2 is not allowing user from Server1 ( where you initiated the sqoop connection - user@server1), for mysql.

the solution would be provide appropriate mysql access for the user which is initiating the connection from Server1.

GRANT <PREV LIST> PRIVILEGES ON *.* TO '<user>'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
FLUSH PRIVILEGES;

% indicates from all the hosts, it is good to give the access to range of hosts as The connect string you supply will be used on TaskTracker nodes throughout your MapReduce cluster( pick one of node from your cluster)

before you initiate the connection you can verify the communication ( if you have mysql "Client" installed on the server1 ) by

mysql -h <Server2> -u <User1> -p <PASSWORD>

Hope this helps !!

[#3]

Thank you very much @bkosaraju it worked. I examined mysql.user table and realized user Hostname was not '%' . So I changed it with following as @bkosaraju recommended.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;

Alternatively I realized it can be changed from mysql databese user table with following query;

mysql>use mysql;
mysql> update user set Host='%' where User='user';