sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Traiano Welcome <trai...@gmail.com>
Subject Re: How to debug SQOOP / JDBC Driver Failures for MySQL Import Jobs
Date Wed, 18 Mar 2015 05:37:35 GMT
Further testing:


On Wed, Mar 18, 2015 at 7:59 AM, Traiano Welcome <traiano@gmail.com> wrote:
> Thanks for the response, Stanley:
>
>
> On Wed, Mar 18, 2015 at 6:29 AM, Xu, Qian A <qian.a.xu@intel.com> wrote:
>> Hi Traiano,
>>
>> This is usually a firewall issue of the server xx.yy.xx.zz. Please make sure the
xx.yy.xx.zz :3306 is accessible from where your Sqoop query initiates.
>
>
> I can confirm this is not the case:
>
>
> - Can connect to port 3306 on xx.yy.xx.zz:
>
> ---
> [root@lol-dev-hdpdn8 ~]# nc xx.yy.xx.zz 3306
> [
> 5.5.41-0ubuntu0.12.04.1�\FUV10;xn��>*VT!F{[7A|omysql_native_password
> ^C
> ---
>
> - Can connect using the mysql client and list tables in the db:
>
> ---
> [root@lol-dev-hdpdn8 ~]# mysql -h xx.yy.xx.zz -u<user> -p <db>
> Enter password:
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 6076407
>
> ---
>
> - Finally, when I run a packet sniff listening for outbound packets to
> xx.yy.xx.zz from the sqoop server, I see no packets leaving the node
> to the db server:
>
>
> ---
> [root@lol-dev-hdpdn8 jdbc]# tcpdump host xx.yy.xx.zz and port 3306
> tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
> listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
> ^C
> 0 packets captured
> 0 packets received by filter
> 0 packets dropped by kernel
> ---
>
> Is there any other way I could drill down deeper into this ?
>

Just to confirm the JDBC driver is not the problem, I've put together
a test script that uses the java jdbc driver that sqoop is using, and
the script seems to successfully connect to the mysql db:

---
[root@lol-dev-hdpdn8 jdbc]# java -cp
.:/var/lib/sqoop2/mysql-connector-java-5.1.34-bin.jar JDBCExample
-------- MySQL JDBC Connection Testing ------------
MySQL JDBC Driver Registered!
You made it, take control your database now!
---

I can see communications traffic to the mysql db going outbound using
tcpdump, so this confirms no networking issue:

---
[root@lol-dev-hdpdn8 jdbc]# tcpdump -n host xx.yy.xx.zz and port 3306
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
08:27:11.643603 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[S], seq 3476709965, win 14600, options [mss
1460,nop,nop,sackOK,nop,wscale 7], length 0
08:27:11.643836 IP xx.yy.xx.zz.mysql > lol-dev-hdpdn8.39977: Flags
[S.], seq 1131758852, ack 3476709966, win 29200, options [mss
1460,nop,nop,sackOK,nop,wscale 7], length 0
08:27:11.643857 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[.], ack 1, win 115, length 0
08:27:11.644321 IP xx.yy.xx.zz.mysql > lol-dev-hdpdn8.39977: Flags
[P.], seq 1:96, ack 1, win 229, length 95
08:27:11.644341 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[.], ack 96, win 115, length 0
08:27:11.678496 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[P.], seq 1:91, ack 96, win 115, length 90
08:27:11.678777 IP xx.yy.xx.zz.mysql > lol-dev-hdpdn8.39977: Flags
[.], ack 91, win 229, length 0
08:27:11.678848 IP xx.yy.xx.zz.mysql > lol-dev-hdpdn8.39977: Flags
[P.], seq 96:107, ack 91, win 229, length 11
08:27:11.683985 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[P.], seq 91:1034, ack 107, win 115, length 943
08:27:11.684769 IP xx.yy.xx.zz.mysql > lol-dev-hdpdn8.39977: Flags
[P.], seq 107:774, ack 1034, win 243, length 667
08:27:11.705410 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[P.], seq 1034:1182, ack 774, win 125, length 148
08:27:11.705847 IP xx.yy.xx.zz.mysql > lol-dev-hdpdn8.39977: Flags
[P.], seq 774:863, ack 1182, win 258, length 89
08:27:11.707315 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[P.], seq 1182:1203, ack 863, win 125, length 21
08:27:11.707511 IP xx.yy.xx.zz.mysql > lol-dev-hdpdn8.39977: Flags
[P.], seq 863:874, ack 1203, win 258, length 11
08:27:11.707645 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[P.], seq 1203:1240, ack 874, win 125, length 37
08:27:11.708241 IP xx.yy.xx.zz.mysql > lol-dev-hdpdn8.39977: Flags
[P.], seq 874:885, ack 1240, win 258, length 11
08:27:11.708564 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[P.], seq 1240:1261, ack 885, win 125, length 21
08:27:11.708737 IP xx.yy.xx.zz.mysql > lol-dev-hdpdn8.39977: Flags
[P.], seq 885:896, ack 1261, win 258, length 11
08:27:11.708923 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[P.], seq 1261:1300, ack 896, win 125, length 39
08:27:11.709078 IP xx.yy.xx.zz.mysql > lol-dev-hdpdn8.39977: Flags
[P.], seq 896:907, ack 1300, win 258, length 11
08:27:11.712236 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[F.], seq 1300, ack 907, win 125, length 0
08:27:11.712447 IP xx.yy.xx.zz.mysql > lol-dev-hdpdn8.39977: Flags
[F.], seq 907, ack 1301, win 258, length 0
08:27:11.712461 IP lol-dev-hdpdn8.39977 > xx.yy.xx.zz.mysql: Flags
[.], ack 908, win 125, length 0

---


So this would appear to rule out an issue with the driver. Here is the
java test script FYI:

---
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

// compile with: javac JDBCExample.java and run like: java -cp
.:/var/lib/sqoop2/mysql-connector-java-5.1.34-bin.jar JDBCExampl

public class JDBCExample {

  public static void main(String[] argv) {

        System.out.println("-------- MySQL JDBC Connection Testing
------------");

        try {
                Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
                System.out.println("Where is your MySQL JDBC Driver?");
                e.printStackTrace();
                return;
        }

        System.out.println("MySQL JDBC Driver Registered!");
        Connection connection = null;

        try {
                connection = DriverManager
                .getConnection("jdbc:mysql://xx.yy.xx.zz:3306/<db
name>","<db user>", "<db user password>");

        } catch (SQLException e) {
                System.out.println("Connection Failed! Check output console");
                e.printStackTrace();
                return;
        }

        if (connection != null) {
                System.out.println("You made it, take control your
database now!");
        } else {
                System.out.println("Failed to make connection!");
        }
  }
}

---

How would I increase the debugging level in sqoop to check if it might
be having issues calling the driver correctly?


>
>
>
>>
>> Stanley
>>
>>
>> -----Original Message-----
>> From: Traiano Welcome [mailto:traiano@gmail.com]
>> Sent: Tuesday, March 17, 2015 11:02 PM
>> To: user@sqoop.apache.org
>> Subject: How to debug SQOOP / JDBC Driver Failures for MySQL Import Jobs
>>
>> Hi
>>
>> I'm following the guide for testing Sqoop data imports from mysql:
>>
>>  http://blog.cloudera.com/blog/2013/11/sqooping-data-with-hue/
>>
>> But the mysql sqoop job fails with "" in the HUE interface.  I'd appreciate some
help interpreting the sqoop java error trace I'm seeing in the sqoop logs:
>>
>> My connection settings are as follows:
>>
>> ---
>> JDBC driver class: com.mysql.jdbc.Driver JDBC Connection string: jdbc:mysql://xx.yy.xx.zz/dbname
>> ---
>>
>>  I've downloaded and added the jdbc driver from Oracle in /var/lib/sqoop on the sqoop
server node, following the cloudera guide
>> here:
>>
>>  http://www.cloudera.com/content/cloudera/en/documentation/core/latest/topics/cdh_ig_jdbc_driver_install.html
>>
>> However, the job keeps failing with the following java trace from the sqoop logs
(excuse the noise!):
>>
>>
>> ---
>> 2015-03-17 17:44:06,368 INFO
>> org.apache.sqoop.repository.JdbcRepositoryTransaction: Attempting transaction commit
>> 2015-03-17 17:44:06,385 INFO
>> org.apache.sqoop.repository.JdbcRepositoryTransaction: Attempting transaction commit
>> 2015-03-17 17:44:06,393 INFO
>> org.apache.sqoop.repository.JdbcRepositoryTransaction: Attempting transaction commit
>> 2015-03-17 17:45:09,396 ERROR
>> org.apache.sqoop.server.SqoopProtocolServlet: Exception in POST
>> http://lol-dev-hdpdn8.hadoop.local:12000/sqoop/v1/submission/action/2
>> org.apache.sqoop.common.SqoopException:
>> GENERIC_JDBC_CONNECTOR_0001:Unable to get a connection
>>         at org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.<init>(GenericJdbcExecutor.java:51)
>>         at org.apache.sqoop.connector.jdbc.GenericJdbcImportInitializer.configureJdbcProperties(GenericJdbcImportInitializer.java:125)
>>         at org.apache.sqoop.connector.jdbc.GenericJdbcImportInitializer.initialize(GenericJdbcImportInitializer.java:49)
>>         at org.apache.sqoop.connector.jdbc.GenericJdbcImportInitializer.initialize(GenericJdbcImportInitializer.java:40)
>>         at org.apache.sqoop.framework.JobManager.submit(JobManager.java:378)
>>         at org.apache.sqoop.handler.SubmissionRequestHandler.submissionSubmit(SubmissionRequestHandler.java:152)
>>         at org.apache.sqoop.handler.SubmissionRequestHandler.handleActionEvent(SubmissionRequestHandler.java:122)
>>         at org.apache.sqoop.handler.SubmissionRequestHandler.handleEvent(SubmissionRequestHandler.java:75)
>>         at org.apache.sqoop.server.v1.SubmissionServlet.handlePostRequest(SubmissionServlet.java:44)
>>         at org.apache.sqoop.server.SqoopProtocolServlet.doPost(SqoopProtocolServlet.java:63)
>>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)
>>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
>>         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
>>         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
>>         at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
>>         at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
>>         at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
>>         at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>>         at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
>>         at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
>>         at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
>>         at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
>>         at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
>>         at java.lang.Thread.run(Thread.java:744)
>> 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)
>> .
>> .
>> .
>> ---
>>
>>
>> I've checked for attempts to connect to the remove db server (tcpdump)
>>
>> I've checked for attempts to connect to the remove db server (tcpdump) but it looks
like connection attempts are not even leaving the sqoop node, so it seems sqoop is not successful
at creating a mysql connection to begin with. I've checked the remote mysql server connection
from the sqoop server using the mysql client as well - no issues there.
>>
>> I'm using sqoop on cloudera hadoop with cloudera manager 5.2.0 and java java version
"1.7.0_45"
>>
>> Specific package details:
>>
>> hue-sqoop-3.6.0+cdh5.2.0+509-1.cdh5.2.0.p0.37.el6.x86_64
>> sqoop2-client-1.99.3+cdh5.2.0+30-1.cdh5.2.0.p0.26.el6.noarch
>> sqoop2-1.99.3+cdh5.2.0+30-1.cdh5.2.0.p0.26.el6.noarch
>> sqoop-1.4.5+cdh5.2.0+47-1.cdh5.2.0.p0.26.el6.noarch
>>
>> I've checked the permissions on the .jar file in /usr/lib/sqoop2, and they seem fine:
>>
>> ---
>> [root@lol-dev-hdpdn8 sqoop2]# ls -l /var/lib/sqoop2/ total 8680
>> drwxr-xr-x 4 sqoop2 sqoop    4096 Sep 10  2014 mysql-connector-java-5.1.33
>> -rw-r--r-- 1 sqoop2 sqoop 3797240 Sep 10  2014 mysql-connector-java-5.1.33.tar.gz
>> -rwxr-xr-x 1 sqoop2 sqoop  960374 Mar 17 17:36 mysql-connector-java-5.1.34-bin.jar
>> -rwxr-xr-x 1 sqoop2 sqoop  539705 Mar 17 17:37 postgresql-9.0-801.jdbc4.jar
>> drwxr-xr-x 3 sqoop2 sqoop    4096 Oct 31 21:02 repository
>> drwxr-xr-x 3 sqoop2 sqoop    4096 Nov  1 17:07 sqljdbc_4.0
>> -rwxr-xr-x 1 sqoop2 sqoop 2391092 Nov  1 17:06 sqljdbc_4.0.2206.100_enu.tar.gz -rwxr-xr-x
1 sqoop2 sqoop  584207 Nov  1 17:12 sqljdbc4.jar -rwxr-xr-x 1 sqoop2 sqoop  563117 Nov  1
17:12 sqljdbc.jar
>> drwxr-xr-x 5 sqoop2 sqoop    4096 Mar 17 17:37 tomcat-deployment
>> [root@lol-dev-hdpdn8 sqoop2]#
>>
>> ---
>>
>> I've also tested bot  mysql-connector-java-5.1.34-bin.jar and mysql-connector-java-5.1.33-bin.jar,
restarting the sqoop service each time I changed the module, to no avail.
>>
>> Is there anyway to debug further into why the job is failing?
>>
>> Many thanks in advance for any help!
>>
>> Traiano

Mime
View raw message