Reply
New Contributor
Posts: 2
Registered: ‎06-29-2017

Sqoop Import not working with Mysql table data

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!

Champion
Posts: 426
Registered: ‎05-16-2016

Re: Sqoop Import not working with Mysql table data

Check your

/etc/mysql/my.cnf

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

 

also check  this attribute

 

bind-address
New Contributor
Posts: 2
Registered: ‎06-29-2017

Re: Sqoop Import not working with Mysql table data

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 ?

Champion
Posts: 426
Registered: ‎05-16-2016

Re: Sqoop Import not working with Mysql table data

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 . 

 

Highlighted
Posts: 11
Topics: 0
Kudos: 1
Solutions: 0
Registered: ‎06-14-2017

Re: Sqoop Import not working with Mysql table data

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).

Explorer
Posts: 16
Registered: ‎06-03-2017

Re: Sqoop Import not working with Mysql table data

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

 

Posts: 343
Topics: 11
Kudos: 51
Solutions: 29
Registered: ‎09-02-2016

Re: Sqoop Import not working with Mysql table data

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

Announcements
New solutions