Reply
New Contributor
Posts: 1
Registered: ‎12-26-2015

Exercies 1 - jdbc connection error

Not able to run sqoop command to import table data. jdbc connection error.

 

error log:

 

[root@cloudera1 ec2-user]# sqoop import-all-tables -m 3 --connect jdbc:mysql://cloudera1:3306/retail_db --username=retail_dba --password=cloudera --compression-codec=snappy --as-parquetfile --warehouse-dir=/user/hive/warehouse --hive-import
Warning: /opt/cloudera/parcels/CDH-5.4.2-1.cdh5.4.2.p0.2/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
15/12/26 21:06:07 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.4.2
15/12/26 21:06:07 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
15/12/26 21:06:07 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
15/12/26 21:06:07 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
15/12/26 21:06:07 WARN tool.BaseSqoopTool: It seems that you're doing hive import directly into default
15/12/26 21:06:07 WARN tool.BaseSqoopTool: hive warehouse directory which is not supported. Sqoop is
15/12/26 21:06:07 WARN tool.BaseSqoopTool: firstly importing data into separate directory and then
15/12/26 21:06:07 WARN tool.BaseSqoopTool: inserting data into hive. Please consider removing
15/12/26 21:06:07 WARN tool.BaseSqoopTool: --target-dir or --warehouse-dir into /user/hive/warehouse in
15/12/26 21:06:07 WARN tool.BaseSqoopTool: case that you will detect any issues.
15/12/26 21:06:08 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
15/12/26 21:06:08 ERROR manager.CatalogQueryManager: Failed to list tables
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:377)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:338)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2232)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
        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:377)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:880)
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
        at org.apache.sqoop.manager.CatalogQueryManager.listTables(CatalogQueryManager.java:102)
        at org.apache.sqoop.tool.ImportAllTablesTool.run(ImportAllTablesTool.java:95)
        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: java.net.ConnectException: Connection refused
        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:213)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:297)
        ... 24 more
15/12/26 21:06:08 ERROR sqoop.Sqoop: Got exception running Sqoop: 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.
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.manager.CatalogQueryManager.listTables(CatalogQueryManager.java:118)
        at org.apache.sqoop.tool.ImportAllTablesTool.run(ImportAllTablesTool.java:95)
        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.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:377)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:338)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2232)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
        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:377)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:880)
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
        at org.apache.sqoop.manager.CatalogQueryManager.listTables(CatalogQueryManager.java:102)
        ... 7 more
Caused by: java.net.ConnectException: Connection refused
        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:213)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:297)
        ... 24 more
[root@cloudera1 ec2-user]# sqoop import-all-tables -m 3 --connect jdbc:mysql://cloudera1:3306/retail_db --username=retail_dba --password=cloudera --compression-codec=snappy --as-parquetfile --warehouse-dir=/user/hive/warehouse --hive-import
Warning: /opt/cloudera/parcels/CDH-5.4.2-1.cdh5.4.2.p0.2/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
15/12/26 21:06:11 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.4.2
15/12/26 21:06:11 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
15/12/26 21:06:11 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
15/12/26 21:06:11 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
15/12/26 21:06:11 WARN tool.BaseSqoopTool: It seems that you're doing hive import directly into default
15/12/26 21:06:11 WARN tool.BaseSqoopTool: hive warehouse directory which is not supported. Sqoop is
15/12/26 21:06:11 WARN tool.BaseSqoopTool: firstly importing data into separate directory and then
15/12/26 21:06:11 WARN tool.BaseSqoopTool: inserting data into hive. Please consider removing
15/12/26 21:06:11 WARN tool.BaseSqoopTool: --target-dir or --warehouse-dir into /user/hive/warehouse in
15/12/26 21:06:11 WARN tool.BaseSqoopTool: case that you will detect any issues.
15/12/26 21:06:11 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
15/12/26 21:06:11 ERROR manager.CatalogQueryManager: Failed to list tables
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:377)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:338)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2232)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
        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:377)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:880)
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
        at org.apache.sqoop.manager.CatalogQueryManager.listTables(CatalogQueryManager.java:102)
        at org.apache.sqoop.tool.ImportAllTablesTool.run(ImportAllTablesTool.java:95)
        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: java.net.ConnectException: Connection refused
        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:213)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:297)
        ... 24 more
15/12/26 21:06:11 ERROR sqoop.Sqoop: Got exception running Sqoop: 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.
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.manager.CatalogQueryManager.listTables(CatalogQueryManager.java:118)
        at org.apache.sqoop.tool.ImportAllTablesTool.run(ImportAllTablesTool.java:95)
        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.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:377)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:338)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2232)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
        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:377)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:880)
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
        at org.apache.sqoop.manager.CatalogQueryManager.listTables(CatalogQueryManager.java:102)
        ... 7 more
Caused by: java.net.ConnectException: Connection refused
        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:213)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:297)
        ... 24 more

Cloudera Employee
Posts: 435
Registered: ‎07-12-2013

Re: Exercies 1 - jdbc connection error

Sounds like MySQL is just not running (although it should be). Try 'sudo service mysqld restart' (or 'sudo service mysqld status' to check if it is running) and then run the Sqoop job again.

New Contributor
Posts: 3
Registered: ‎01-04-2016

Re: Exercies 1 - jdbc connection error

Not sure if this helps. I ran into the same issue from what it looks like.

It seems the tutorial provides the sqoop command using some variables that I'm personally not familiar with..

 

{{cluster_data.worker_node_hostname.length}}

and

{{cluster_data.manager_node_hostname}}

 

I read some other forum post saying to replace the length with a 1 or 2. 

And then I replaced the manager_node_hostname with my hostname (quickstart.cloudera) 

and it seemed to work perfectly.

 

My command went from:

 

 

> sqoop import-all-tables \
    -m {{cluster_data.worker_node_hostname.length}} \
    --connect jdbc:mysql://{{cluster_data.manager_node_hostname}}:3306/retail_db \
    --username=retail_dba \
    --password=cloudera \
    --compression-codec=snappy \
    --as-parquetfile \
    --warehouse-dir=/user/hive/warehouse \
    --hive-import

 

To...

> sqoop import-all-tables \
    -m 2 \
    --connect jdbc:mysql://quickstart.cloudera:3306/retail_db \
    --username=retail_dba \
    --password=cloudera \
    --compression-codec=snappy \
    --as-parquetfile \
    --warehouse-dir=/user/hive/warehouse \
    --hive-import

I'm using the downloaded VM instead of Cloudera Live (Not sure I really even know the difference). 

The first thing it told me to do was startup 5 services, make sure they were green - which I did.

Next was to run a scoop command that didn't work as written.

 

I've been working in linux for about 4 years now - not a battle-hardened vet, but definitely not green. I wasn't familiar with the double braces. That might be a place where the tutorial can be tweaked some? 

 

Hope it helps!

Cloudera Employee
Posts: 435
Registered: ‎07-12-2013

Re: Exercies 1 - jdbc connection error

The variables you're referring to need to be filled in manually because it depends on your cluster topology. In the QuickStart VM, there's a link to the tutorial hosted in the VM, and it should resolve these variables for you. The tutorial as hosted on the website (which it looks like you're referring to) is really just for informational purposes because outside of Cloudera Live and the QuickStart VM, you would need to install all the sample datasets on the local disk and a MySQL database before you could do the tutorial. You would need to fill in the hostname for the MySQL database (quickstart.cloudera on the QuickStart VM) and the -m flag is how many mappers to use for the Sqoop job. A good rule of thumb to use for that is the number of disks on all your DataNodes (1 in the VM, 3 in Cloudera Live clusters).

New Contributor
Posts: 3
Registered: ‎01-04-2016

Re: Exercies 1 - jdbc connection error

That makes perfect sense. Thanks for the clarification there!
New Contributor
Posts: 1
Registered: ‎04-30-2017

Re: Exercies 1 - jdbc connection error

I've been using the docker quickstart image.  The issue in my case was to use quickstart.cloudera rather than just quickstart when referencing the jdbc --connect option 

New Contributor
Posts: 2
Registered: ‎10-29-2015

Re: Exercies 1 - jdbc connection error

Hi,

 

Here is what I did to resolve the problem:

1. set the ACCUMULO_HOME variable and exported it

2. restarted the mysql service

3. put the following (--P and then type the password):

sqoop import-all-tables \
    -m 1 \
    --connect jdbc:mysql://quickstart.cloudera:3306/retail_db \
    --username=retail_dba \
    --P \
    --compression-codec=snappy \
    --as-parquetfile \
    --warehouse-dir=/user/hive/warehouse \
    --hive-import

 

Hope this helps!

 

Best wishes,

Skender

New Contributor
Posts: 12
Registered: ‎08-10-2017

Re: Exercies 1 - jdbc connection error

Big thanks for this!!!! Was totally stuck, hopefully they will correct the tutorial.
Announcements