Sqoop connects to mysql exception: NoSuchMethodError: org.hsqldb.DatabaseURL.parseURL

Sqoop connects to mysql exception: NoSuchMethodError: org.hsqldb.DatabaseURL.parseURL

Abnormal recovery

Need to import mysql data into hive. First install sqoop according to the online tutorial, try to connect to mysql, the program throws an exception (Sure enough, all members of the hadoop family, from installation and configuration to operation, none of them is worry-free)

$ sqoop list-databases --connect jdbc:mysql://localhost:3306 --username xxx --password 'xxx'
20/08/05 22:31:24 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
20/08/05 22:31:25 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
20/08/05 22:31:25 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
Exception in thread "main" java.lang.NoSuchMethodError: org.hsqldb.DatabaseURL.parseURL(Ljava/lang/String;ZZ)Lorg/hsqldb/persist/HsqlProperties;
        at org.hsqldb.jdbc.JDBCDriver.getConnection(Unknown Source)
        at org.hsqldb.jdbc.JDBCDriver.connect(Unknown Source)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:904)
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:59)
        at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.java:57)
        at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
 

Investigation process

What's the matter? Stackoverflow found a similar problem ( stackoverflow.com/questions/7... ), the answer is the Sure that you have have the make only One Version of the CLASSPATH hsqldb ON your, I mean the $CLASSPATHnext two jar package led to this exception. With a echo $CLASSPATHlook:

.:/usr/lib/jvm/java-8-openjdk-amd64/jre/lib/ext:/usr/lib/jvm/java-8-openjdk-amd64/lib/dt.jar:/usr/lib/jvm/java-8-openjdk-amd64/lib/tools.jar:/usr/local/hive/apache-hive-3.1.2-bin/lib:/usr/lib/jvm/java-8-openjdk-amd64/lib:/usr/local/sqoop/lib 
 

Check out these paths, indeed, only a hsqldb-xxx.jarpack ah, Best not the solution. . .

I saw some other similar questions on Google (not sqoop, but the name of the exception is roughly similar), and the answers mostly reflect that the problem is on the classpath. It is reasonable to think about it. After all, the exception indicates that the method cannot be found. Maybe you should find ideas from the classpath. Modify $CLASSPATHenvironment variables, adding $SQOOP_HOME/lib, $SQOOP_HOME/lib/xxx.jar, $HADOOP_HOME/libboth to no avail, really scary.

Under frustrated, trying to sqoopcommand script ( $SQOOP_HOME/sqoopfound some unusual clues) in. The last line of the script is as follows:

exec ${HADOOP_COMMON_HOME}/bin/hadoop org.apache.sqoop.Sqoop "$@"
 

That sqoopcommand is invoked when performing hadoop, so running when classpathin fact you want to add $HADOOP_CLASSPATH. This latter variable is in at the time prior to installation hadoop ~/.bashrcis initialized export HADOOP_CLASSPATH=$(find $HADOOP_HOME -name '*.jar' | xargs echo | tr ' ' ':'). Will it have conflicting hsql jar packages? an examination:

$ find $HADOOP_HOME -name '*.jar' | grep hsqldb
/usr/local/hadoop/share/hadoop/mapreduce/lib-examples/hsqldb-2.0.0.jar
 

that's it! Sure enough, I found it! After rename the hsqldb of this path, start sqoop to connect to mysql normally.

A few thoughts

  1. Two conflicting jar package /usr/local/sqoop/lib/hsqldb-1.8.0.10.jar, /usr/local/hadoop/share/hadoop/mapreduce/lib-examples/hsqldb-2.0.0.jarare sqoop hadoop their own installation and Sqoop dependent hadoop. If there is a conflict, it can't run, why should it be included in the installation package? This installation package is packaged
  2. All programs of the hadoop family (hive, sqoop, hdfs, etc.) are executable shell scripts that call hadoop, and the runtime environment of hadoop itself is applicable to them.
  3. When the problem is difficult, you can try to add echo to the script to output intermediate content