Sqoop Import not working with Mysql table data

[#1]

I am trying to import data from MySql which is IP base authentication. i.e. We have to add IP as a whitelist to connect to MySql DB server. We have added sqoop edge node ip in whitelist.

Sqoop version: 1.4.6-cdh5.9.0

1. List table commands run successfully.

sqoop list-tables --connect jdbc:mysql://host:3306/dbname --username user --password pwd

2. While trying to import table data, getting below exception

sqoop import --connect jdbc:mysql://host:3306/dbname --username username --password pswd --table tablename --target-dir /tmp/20170629 -m 1

Error: java.lang.RuntimeException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at org.apache.sqoop.mapreduce.db.DBInputFormat.setDbConf(DBInputFormat.java:170)
at org.apache.sqoop.mapreduce.db.DBInputFormat.setConf(DBInputFormat.java:161)
at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:73)
at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:133)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:749)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1698)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:223)
at org.apache.sqoop.mapreduce.db.DBInputFormat.setDbConf(DBInputFormat.java:168)
... 10 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:341)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2192)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2225)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2024)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:779)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:302)
at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:216)
... 11 more
Caused by: java.net.NoRouteToHostException: No route to host
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:579)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:300)
... 27 more

17/06/29 23:10:39 INFO mapreduce.Job: map 100% reduce 0%
17/06/29 23:10:40 INFO mapreduce.Job: Job job_1491952423410_4908 failed with state FAILED due to: Task failed task_1491952423410_4908_m_000000
Job failed as tasks failed. failedMaps:1 failedReduces:0

17/06/29 23:10:40 INFO mapreduce.Job: Counters: 8
Job Counters
Failed map tasks=4
Launched map tasks=4
Other local map tasks=4
Total time spent by all maps in occupied slots (ms)=28372
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=14186
Total vcore-seconds taken by all map tasks=14186
Total megabyte-seconds taken by all map tasks=29052928
17/06/29 23:10:40 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
17/06/29 23:10:40 INFO mapreduce.ImportJobBase: Transferred 0 bytes in 31.5536 seconds (0 bytes/sec)
17/06/29 23:10:40 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead
17/06/29 23:10:40 INFO mapreduce.ImportJobBase: Retrieved 0 records.
17/06/29 23:10:40 ERROR tool.ImportTool: Error during import: Import job failed!

[#2]

Check your

/etc/mysql/my.cnf

see if you are commenting out the because it solved me .

also check  this attribute

bind-address

[#3]

Thanks for reply.

I am importing data from 3rd party MySQL instance. They do client IP address as whitelist to access MySQL. I have added only edge node IP ( where we have installed sqoop), so wondering do I need to add all edge nodes IP ?

[#4]

for some reason i didnt get any notification regarding this post . 

yes where ever you are plaining to run you sqoop command tool which ever node at them . 

[#5]

And if your sqoop command launch a map/reduce job then you also need to add the IP of all the data-nodes (where there is a Yarn NodeManager).

[#6]

@SKumar2000

There could be multiple reasons for this issue

1. MySQL jdbc connector/driver is missing - you can ignore this as your first command is working

2. your sqoop command. Pls specify the target database, table in your import command as follows and try again

sqoop import \
--connect "jdbc:mysql://host:3306/dbname" \
--username uid \
--password pwd \
--table table_name \
--delete-target-dir \
--hive-database hive_db_name \
--hive-table hive_tablename \
--split-by col1 \
--target-dir 'dir_path'

[#7]

Have you installed jdbc connector. This is needed to link mysql and hdfs.

[#8]

Are you able to resolve this issue? I am stuck with exactly same error

[#9]

Could you share the error ? 

Do you have sqoop client being installed on the node ? 

whats you mysql cnf file looking 

if you have this

skip-networking

just comment it out and restart the mysql  i assume it your poc box.