Uploaded image for project: 'Sqoop'
  1. Sqoop
  2. SQOOP-3144

Exception: org.apache.sqoop.common.SqoopException Message: GENERIC_JDBC_CONNECTOR_0016:Can't fetch schema -

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.99.7
    • Fix Version/s: None
    • Component/s: sqoop2-jdbc-connector
    • Environment:

      Description

      Requirement: Importing MySQL Database to HDFS
      Minor Issue: No Unique Primary Key included in DB
      Workaround: Added Year as partition Column

      Current Issue:
      org.apache.sqoop.common.SqoopException: GENERIC_JDBC_CONNECTOR_0016:Can't fetch schema

      Cause:
      This is because a double quote is appended, which is not parsed by MySQL DB.

      Using min/max query: SELECT MIN("year"), MAX("year") FROM "sports"."players"

      ======================================================================================================

      1. SQOOP LOG #

      2017-03-02 10:24:10,576 INFO [org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.configurePartitionProperties(GenericJdbcFromInitializer.java:152)] Using partition column: year
      2017-03-02 10:24:10,576 INFO [org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.configurePartitionProperties(GenericJdbcFromInitializer.java:238)] Using min/max query: SELECT MIN("
      year"), MAX("year") FROM "sports"."players"
      2017-03-02 10:24:10,576 ERROR [org.apache.sqoop.server.SqoopProtocolServlet.doPut(SqoopProtocolServlet.java:86)] Exception in PUT http://localhost:12000/sqoop/v1/job/MysqlToHdfs/start
      org.apache.sqoop.common.SqoopException: GENERIC_JDBC_CONNECTOR_0016:Can't fetch schema
      at org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.initialize(GenericJdbcFromInitializer.java:64)
      at org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.initialize(GenericJdbcFromInitializer.java:47)
      at org.apache.sqoop.driver.JobManager$1.call(JobManager.java:520)
      at org.apache.sqoop.driver.JobManager$1.call(JobManager.java:517)
      at org.apache.sqoop.utils.ClassUtils.executeWithClassLoader(ClassUtils.java:281)
      at org.apache.sqoop.driver.JobManager.initializeConnector(JobManager.java:516)
      at org.apache.sqoop.driver.JobManager.createJobRequest(JobManager.java:423)
      at org.apache.sqoop.driver.JobManager.start(JobManager.java:317)
      at org.apache.sqoop.handler.JobRequestHandler.startJob(JobRequestHandler.java:353)
      at org.apache.sqoop.handler.JobRequestHandler.handleEvent(JobRequestHandler.java:114)
      at org.apache.sqoop.server.v1.JobServlet.handlePutRequest(JobServlet.java:84)
      at org.apache.sqoop.server.SqoopProtocolServlet.doPut(SqoopProtocolServlet.java:81)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
      at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:808)
      at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669)
      at org.apache.hadoop.security.authentication.server.AuthenticationFilter.doFilter(AuthenticationFilter.java:594)
      at org.apache.hadoop.security.token.delegation.web.DelegationTokenAuthenticationFilter.doFilter(DelegationTokenAuthenticationFilter.java:291)
      at org.apache.hadoop.security.authentication.server.AuthenticationFilter.doFilter(AuthenticationFilter.java:553)
      at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
      at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)
      at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)
      at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
      at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)
      at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
      at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
      at org.eclipse.jetty.server.Server.handle(Server.java:499)
      at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
      at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
      at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
      at java.lang.Thread.run(Thread.java:745)
      Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"sports"."players"' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

      ======================================================================================================

      1. Running the Query on MYSQL #

      With Double Quotes:

      mysql> SELECT MIN("year"), MAX("year") FROM "sports"."players";
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"sports"."players"' at line 1

      Without Double Quotes:

      mysql> SELECT MIN(year), MAX(year) FROM sports.players;
      --------------------+

      MIN(year) MAX(year)

      --------------------+

      1950 2009

      --------------------+
      1 row in set (0.01 sec)

      mysql>

      1. MySQL Version #
        mysql> SHOW VARIABLES LIKE "%version%";
        ------------------------------------------------+
        Variable_name Value

        ------------------------------------------------+

        innodb_version 5.7.17
        protocol_version 10
        slave_type_conversions  
        tls_version TLSv1,TLSv1.1
        version 5.7.17-0ubuntu0.16.04.1
        version_comment (Ubuntu)
        version_compile_machine x86_64
        version_compile_os Linux

        ------------------------------------------------+
        8 rows in set (0.00 sec)

      1. MySQL Driver Version #

      hduser@cai-hadoop01:/usr/local/sqoop/bin$ ls -l ../lib
      total 968
      -rwxrwxrwx 1 root root 990927 Feb 12 15:26 mysql-connector-java-5.1.40-bin.jar
      hduser@cai-hadoop01:/usr/local/sqoop/bin$

      1. SQOOP ENV Vars #
        export SQOOP_HOME=/usr/local/sqoop
        export SQOOP_CONF_DIR=$SQOOP_HOME/conf
        export SQOOP_CLASSPATH=$SQOOP_CONF_DIR
        export SQOOP_SERVER_EXTRA_LIB=/usr/local/sqoop/lib
        export PATH=$SQOOP_HOME/bin:/bin:/sbin:$PATH

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              Sayyad Mohamed El-Sayyad
            • Votes:
              2 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:

                Time Tracking

                Estimated:
                Original Estimate - 48h
                48h
                Remaining:
                Remaining Estimate - 48h
                48h
                Logged:
                Time Spent - Not Specified
                Not Specified