Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Sqoop export to mysql job tries to connect wrong database

avatar
Expert Contributor

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
1 ACCEPTED SOLUTION

avatar
Super Collaborator

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 !!

View solution in original post

2 REPLIES 2

avatar
Super Collaborator

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 !!

avatar
Expert Contributor

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';