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

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.99.7
• Component/s: sqoop2-jdbc-connector
• Labels: beginner easyfix easytest newbie

Environment

hduser@cai-hadoop01:/usr/local/sqoop/bin$ cat /etc/release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04.1 LTS"
NAME="Ubuntu"
VERSION="16.04.1 LTS (Xenial Xerus)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 16.04.1 LTS"
VERSION_ID="16.04"
HOME_URL="http://www.ubuntu.com/"
SUPPORT_URL="http://help.ubuntu.com/"
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"
UBUNTU_CODENAME=xenial
hduser@cai-hadoop01:/usr/local/sqoop/bin$

-----------------------------------------------------------

hduser@cai-hadoop01:/usr/local/sqoop/bin$ uname -a
Linux cai-hadoop01 4.4.0-45-generic #66-Ubuntu SMP Wed Oct 19 14:12:37 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

• Flags: Important
• External issue URL: https://translate.google.com/translate?sl=auto&tl=en&js=y&prev=_t&hl=en&ie=UTF-8&u=http%3A%2F%2Fwww.aboutyun.com%2Fthread-20141-1-1.html&edit-text=
• External issue ID: Sqoop1.99.7 dataSql statement with double quotation marks, resulting in data migration error
• Assignee: Unassigned
• Reporter: Mohamed El-Sayyad
• Created: 02/Mar/17 14:29
• Updated: 17/Oct/17 20:00

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