Further testing:
On Wed, Mar 18, 2015 at 7:59 AM, Traiano Welcome <[email protected]> wrote: > Thanks for the response, Stanley: > > > On Wed, Mar 18, 2015 at 6:29 AM, Xu, Qian A <[email protected]> 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:[email protected]] >> Sent: Tuesday, March 17, 2015 11:02 PM >> To: [email protected] >> 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
