Error using tSqoopExport to export data from an HDFS to a MySQL database

Symptoms

When trying to export the data from an HDFS to a MySQL database using tSqoopExport, you encounter the following error:

sqoop export --connect jdbc:mysql://xxx.xxx.com:3306/dbname --driver com.mysql.jdbc.Driver --username root --password XXXXXX --table sample_07 --export-dir '/user/test/' -m 1;
17/08/23 11:58:16 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: Access denied for user 'root'@'xxx.xxx.com' (using password: YES) 
java.sql.SQLException: Access denied for user 'root'@'xxx.xxx.com' (using password: YES)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:935)
 at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4101)
 at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1300)
 at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2337)
 at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)
 at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)
 at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
 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:381)
 at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
 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:885)
 at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
 at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:744)
 at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:767)
 at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:270)
 at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:241)
 at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:227)
 at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295)
 at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1845)
 at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645)
 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:148)
 at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
 at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)
 at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)
 at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)
 at org.apache.sqoop.Sqoop.main(Sqoop.java:244)

 

Diagnosis

The MySQL user does not have appropriate privileges on the DB.

 

Solution

Run the following command:

$ mysql -u root -p
mysql> GRANT ALL ON *.* to root@'ipaddress' IDENTIFIED BY 'mysql root password';
mysql> FLUSH PRIVILEGES;
mysql> exit

 

Then attempt a connection from the IP address you specified:

mysql -h address-of-remove-server -u root -p

 

OR

 

Confirm the MySQL user:

mysql -u root -h $(hostname -f) -p -e "select count(user) from mysql.user;"

The output should be a simple count.

Version history
Revision #:
6 of 6
Last update:
‎10-18-2017 06:55 PM
Updated by:
 
Contributors