Some further information: On Wed, Mar 18, 2015 at 8:37 AM, Traiano Welcome <[email protected]> wrote: > 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?
If I run sqoop from the cli, the job seems successful in connecting to the remote db: ---- [root@lol-dev-hdpdn8 yum.repos.d]# sqoop list-tables --connect jdbc:mysql://xx.yy.xx.zz:3306/DB --username=<user> --password=<pass> Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. 15/03/18 11:23:20 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.2.0 15/03/18 11:23:20 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 15/03/18 11:23:20 INFO manager.SqlManager: Using default fetchSize of 1000 hist_1 settings sites_list ---- So, neither a jdbc, java or network issue. It's beginning to look pretty SQOOP specific. >>> >>> Many thanks in advance for any help! >>> >>> Traiano
