Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

I'm trying to import data from mysql to HDFS using sqoop.

Solved Go to solution
Highlighted

I'm trying to import data from mysql to HDFS using sqoop.

New Contributor

veera@bigdata:/$ sqoop import --bindir $SQOOP_HOME/lib --connect jdbc:mysql://bigdata:3306/testdb --driver com.mysql.jdbc.Driver --username root --password root --table student -m 1 --target-dir /usr/sqoop/ Warning: /usr/local/sqoop/../hbase does not exist! HBase imports will fail. Please set $HBASE_HOME to the root of your HBase installation. Warning: /usr/local/sqoop/../hcatalog does not exist! HCatalog jobs will fail. Please set $HCAT_HOME to the root of your HCatalog installation. Warning: /usr/local/sqoop/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. 18/11/25 19:40:48 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6 18/11/25 19:40:48 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 18/11/25 19:40:48 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. 18/11/25 19:40:48 INFO manager.SqlManager: Using default fetchSize of 1000 18/11/25 19:40:48 INFO tool.CodeGenTool: Beginning code generation Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. 18/11/25 19:40:48 ERROR manager.SqlManager: Error executing statement: com.mysql.cj.jdbc.exceptions.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. com.mysql.cj.jdbc.exceptions.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 com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:741) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:57) at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1663) at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:662) at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352) at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:221) 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:1833) 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.ImportTool.importTable(ImportTool.java:478) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605) at org.apache.sqoop.Sqoop.run(Sqoop.java:143) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227) at org.apache.sqoop.Sqoop.main(Sqoop.java:236) Caused by: com.mysql.cj.core.exceptions.CJCommunicationsException: 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:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:54) at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:93) at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:133) at com.mysql.cj.core.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:149) at com.mysql.cj.mysqla.io.MysqlaSocketConnection.connect(MysqlaSocketConnection.java:83) at com.mysql.cj.mysqla.MysqlaSession.connect(MysqlaSession.java:144) at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1783) at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1653) ... 24 more Caused by: java.net.ConnectException: Connection refused (Connection refused) at java.net.PlainSocketImpl.socketConnect(Native Method) at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) at java.net.Socket.connect(Socket.java:589) at com.mysql.cj.core.io.StandardSocketFactory.connect(StandardSocketFactory.java:202) at com.mysql.cj.mysqla.io.MysqlaSocketConnection.connect(MysqlaSocketConnection.java:57) ... 27 more 18/11/25 19:40:48 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1651) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605) at org.apache.sqoop.Sqoop.run(Sqoop.java:143) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227) at org.apache.sqoop.Sqoop.main(Sqoop.java:236)

1 ACCEPTED SOLUTION

Accepted Solutions

Re: I'm trying to import data from mysql to HDFS using sqoop.

Expert Contributor

Hi @Veera Mundra

Error:

ERROR manager.SqlManager: Error executing statement: com.mysql.cj.jdbc.exceptions.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. 

MySQL specific CommunicationsException:Communications link failure, then it means that the DB isn't reachable. This can have one or more of the following causes.

1. IP address or hostname in JDBC URL is wrong.
2. Hostname in JDBC URL is not recognized by local DNS server.
3. Port number is missing or wrong in JDBC URL.
4. DB server is down.
5. Firewall issue.

To solve the one or the other, follow the following advice:

1. Verify and test them with ping.
2. Verify it based on my.cnf of MySQL DB.
3. Restart the DB.
4. Verify if mysqld is started without the --skip-networking option.
in the below case, --skip-networking is enabled
$ps ax | grep mysql | head -n 1
/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid --skip-networking
5. Make sure line skip-networking is commented in /etc/my.cnf 
6. Disable firewall and/or configure firewall/proxy to allow/forward the port. 
7. Look for the bind-address setting in my.cnf on the server, and make sure it's listening on the IP address(or hostname).
7. Replace bind-address = 127.0.0.1 with bind-address = 0.0.0.0 in my.cnf file. my.cnf file is available at /etc/mysql folder
Note:  Alternatively (and less securely!) the above step7 will set it up to listen on all addresses - local and remote: bind-address = 0.0.0.0
8. By giving proper permissions: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES;

Hope it helps!. Thanks

3 REPLIES 3

Re: I'm trying to import data from mysql to HDFS using sqoop.

Expert Contributor

Hi @Veera Mundra

Error:

ERROR manager.SqlManager: Error executing statement: com.mysql.cj.jdbc.exceptions.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. 

MySQL specific CommunicationsException:Communications link failure, then it means that the DB isn't reachable. This can have one or more of the following causes.

1. IP address or hostname in JDBC URL is wrong.
2. Hostname in JDBC URL is not recognized by local DNS server.
3. Port number is missing or wrong in JDBC URL.
4. DB server is down.
5. Firewall issue.

To solve the one or the other, follow the following advice:

1. Verify and test them with ping.
2. Verify it based on my.cnf of MySQL DB.
3. Restart the DB.
4. Verify if mysqld is started without the --skip-networking option.
in the below case, --skip-networking is enabled
$ps ax | grep mysql | head -n 1
/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid --skip-networking
5. Make sure line skip-networking is commented in /etc/my.cnf 
6. Disable firewall and/or configure firewall/proxy to allow/forward the port. 
7. Look for the bind-address setting in my.cnf on the server, and make sure it's listening on the IP address(or hostname).
7. Replace bind-address = 127.0.0.1 with bind-address = 0.0.0.0 in my.cnf file. my.cnf file is available at /etc/mysql folder
Note:  Alternatively (and less securely!) the above step7 will set it up to listen on all addresses - local and remote: bind-address = 0.0.0.0
8. By giving proper permissions: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES;

Hope it helps!. Thanks

Re: I'm trying to import data from mysql to HDFS using sqoop.

New Contributor

Thanks Sampath.

I resolved this issue but now I'm getting different error please find below and help me on this.

	INFO mapreduce.JobSubmitter: Cleaning up the staging area file:/app/hadoop/tmp/mapred/staging/veera1103812606/.staging/job_local1103812606_0001
18/11/28 20:15:49 ERROR tool.ImportTool: Encountered IOException running import job: ENOENT: No such file or directory
at org.apache.hadoop.io.nativeio.NativeIO$POSIX.chmodImpl(Native Method)
at org.apache.hadoop.io.nativeio.NativeIO$POSIX.chmod(NativeIO.java:230)
at org.apache.hadoop.fs.RawLocalFileSystem.setPermission(RawLocalFileSystem.java:657)
at org.apache.hadoop.fs.ChecksumFileSystem$1.apply(ChecksumFileSystem.java:501)
at org.apache.hadoop.fs.ChecksumFileSystem$FsOperation.run(ChecksumFileSystem.java:482)
at org.apache.hadoop.fs.ChecksumFileSystem.setPermission(ChecksumFileSystem.java:498)
at org.apache.hadoop.fs.FileSystem.mkdirs(FileSystem.java:599)
at org.apache.hadoop.mapreduce.JobResourceUploader.uploadFiles(JobResourceUploader.java:94)
at org.apache.hadoop.mapreduce.JobSubmitter.copyAndConfigureFiles(JobSubmitter.java:98)
at org.apache.hadoop.mapreduce.JobSubmitter.submitJobInternal(JobSubmitter.java:191)
at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1297)
at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1294)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1692)
at org.apache.hadoop.mapreduce.Job.submit(Job.java:1294)
at org.apache.hadoop.mapreduce.Job.waitForCompletion(Job.java:1315)
at org.apache.sqoop.mapreduce.ImportJobBase.doSubmitJob(ImportJobBase.java:196)
at org.apache.sqoop.mapreduce.ImportJobBase.runJob(ImportJobBase.java:169)
at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:266)
at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:673)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:497)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
at org.apache.sqoop.Sqoop.main(Sqoop.java:236)

Re: I'm trying to import data from mysql to HDFS using sqoop.

Expert Contributor

Hi, @Veera Mundra,

Glad that the issue is resolved.

Since this is a different issue, I would suggest to open a new thread for this issue so that the main thread doesn't get deviated. I'm not sure of this issue, may be other experts can help on this.

Thanks

Don't have an account?
Coming from Hortonworks? Activate your account here