CDH4, Sqoop2 and JDBC drivers: no suitable driver found

Question

I am trying to use Sqoop 2 to import data from a MySQL database to HDFS, basically following the instructions here. However, the Sqoop server is unable to make a connection to the MySQL database due to appropriate drivers not found.

Setup:

Here is some background on my setup:

1. Hadoop cluster: I have a three machine Hadoop cluster running CDH 4.4.0. Sqoop 2 was configured through the Cloudera Manager, and is running on the same machine as the Namenode. I am developing on a Windows machine which is also where my MySQL database lives. The Hadoop cluster is a set of three Ubuntu Server machines.

2. MySQL database: I have a MySQL database running on my Windows machine, and I have checked that the MySQL database can be accessed from each of the machines in my Hadoop cluster.

3. Client application: My client application is an Eclipse project on my Windows machine which basically opens up a Sqoop client corresponding to a Sqoop server (I have verified that the Sqoop server and client are running on my Namenode).

Here is the main class of my client application.

package com.fc.SqoopImport;

import java.util.List;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.*;

import org.apache.sqoop.client.*;
import org.apache.sqoop.*;
import org.apache.sqoop.common.*;
import org.apache.sqoop.model.*;
import org.apache.sqoop.validation.Status;

import com.mysql.jdbc.*;

public class SqoopImport {
    // utlity function to cycle through the connector and framework forms for errors
    private static void printMessage(List<MForm> formList) {
    for(MForm form : formList) {
      List<MInput<?>> inputlist = form.getInputs();
      if (form.getValidationMessage() != null) {
        System.out.println("Form message: " + form.getValidationMessage());
      }
      for (MInput minput : inputlist) {
        if (minput.getValidationStatus() == Status.ACCEPTABLE) {
          System.out.println("Warning:" + minput.getValidationMessage());
        } else if (minput.getValidationStatus() == Status.UNACCEPTABLE) {
          System.out.println("Error:" + minput.getValidationMessage());
        }
      }
    }
    }

    public static void main(String[] args) throws Exception {
        String driver = "com.mysql.jdbc.Driver";
        Class.forName(driver);

        // location of the server running Sqoop 2 server
        String urlSqoop2Server = "http://fc-01.fc.com:12000/sqoop/";  
        SqoopClient clientSqoop2 = new SqoopClient(urlSqoop2Server);

        // dummy connection object
        MConnection sqoopConnSAP = clientSqoop2.newConnection(1);
        MConnectionForms sqoopConnSAPFrameworkForm = sqoopConnSAP.getFrameworkPart();
        MConnectionForms sqoopConnSAPConnForm = sqoopConnSAP.getConnectorPart();

        sqoopConnSAP.setName("SqoopConnSAP");

        // Set the values for the connection form
        sqoopConnSAPConnForm.getStringInput("connection.connectionString").setValue("jdbc:mysql://192.168.31.172:3306/dbsap");  

        sqoopConnSAPConnForm.getStringInput("connection.jdbcDriver").setValue("com.mysql.jdbc.Driver");
        sqoopConnSAPConnForm.getStringInput("connection.username").setValue("root");
        sqoopConnSAPConnForm.getStringInput("connection.password").setValue("1234");

        sqoopConnSAPFrameworkForm.getIntegerInput("security.maxConnections").setValue(10);

        Status statusConnSAP = clientSqoop2.createConnection(sqoopConnSAP);  

        if(statusConnSAP.canProceed()) {
            System.out.println("Created. New connection ID: " + sqoopConnSAP.getPersistenceId());
        } else {
            System.out.println("Check for status and forms errors.");
            printMessage(sqoopConnSAP.getConnectorPart().getForms());
            printMessage(sqoopConnSAP.getFrameworkPart().getForms());

        }

    }
} 

Error:

Running this project gives the following error:

Check for status and forms errors.
Form message: Can't connect to the database with given credentials: No suitable driver found for jdbc:mysql:192.168.31.172:3306/dbsap
Error:Can't load specified driver

Diagnosis:

The appropriate JDBC drivers (mysql-connector-java-5.1.26-bin.jar) is part of my Eclipse project, and for good measure, I have added this to the sqoop2 lib folder

/opt/cloudera/parcels/CDH-4.4.0-1.cdh4.4.0.p0.39/lib/sqoop2/client-lib

as well. However, this is the part I am not sure of, since the CDH4 documentation says]1 that in case Sqoop was installed using Cloudera Manager, the location of the appropriate JDBC driver should be added to HADOOP_CLASSPATH. So, I did

export HADOOP_CLASSPATH=/usr/lib/jdbcJars:HADOOP_CLASSPATH;

on my Hadoop Namenode, so that an echo $HADOOP_CLASSPATH gives /usr/lib/jdbcJars. Again, I am not entirely sure of the utility of this since my client application is not being developed on the Hadoop cluster.

The last thing that I have not tried yet is creating a new /usr/lib/sqoop/lib directory and adding the JDBC driver there.

Any help figuring this out would be appreciated.

Answer 1

put the mysql-jdbc-driver into the dir:

/usr/lib/sqoop2/webapps/sqoop/WEB-INF/lib/mysql-connector-java-5.1.25.jar

and restart the sqoop2 server

Answer 2

Never ever alter content of parcel directory (/opt/cloudera/parcels/*). There are always different ways how to configure components. For example based on the official documentation, you need to copy the MySQL JDBC driver into /var/lib/sqoop2 directory on the node where you are running Sqoop2 server.