SQOOP /MySQL conncetion issue

[#1]

Hello there,

I am using the following command to import a MySQL into HDFS.

sqoop import --connect jdbc:mysql//master.centos:3306/employees --username ambari --password ambari --m 1 --table employee --target-dir /user/landing/sqlImport

When I try to execute the command I get the following error:

ERROR tool.BaseSqoopTool: Got error creating database manager: java.io.IOException: No manager for connect string: jdbc:mysql//master.centos:3306/employees at org.apache.sqoop.ConnFactory.getManager(ConnFactory.java:191) at org.apache.sqoop.tool.BaseSqoopTool.init(BaseSqoopTool.java:257) at org.apache.sqoop.tool.ImportTool.init(ImportTool.java:89) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:593) at org.apache.sqoop.Sqoop.run(Sqoop.java:148) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235) at org.apache.sqoop.Sqoop.main(Sqoop.java:244)

I suspect this is something related to the jdbc driver configuration for Sqoop.

What configurations should I follow to solve the issue?

Thanks-

Wellington

[#2]

Yes, for Mysql-5.5 and higher your need the latest version of mysql-connector and it cannot be installed using yum, download it from here. After installing it retry sqoop. Also on HDP Sandbox add "--driver com.mysql.jdbc.Driver" to your sqoop command.

[#3]

try specifying the driver class and since MySQL supports --direct, use that too

sqoop import --driver com.mysql.jdbc.Driver --connect jdbc:mysql://server.foo.com/db \
    --table bar \
    --direct -- --default-character-set=latin1 

[#4]

Thank you guys. I downloaded and install the connector (tar zxf mysql-connector-java-5.1.38.tar.gz). Then add the --driver and parameters at the sqoop import command. Here is the errors I am getting now:

[root@master lib]# sqoop import --connect jdbc:mysql//master.centos:3306/employees --driver com.mysql.jdbc.Driver --direct --username ambari --password ambari --m 1 --table employee --target-dir /user/landing/sqlImport
Warning: /usr/hdp/2.4.0.0-169/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/hdp/2.4.0.0-169/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
16/04/07 08:51:40 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.4.0.0-169
16/04/07 08:51:40 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
16/04/07 08:51:41 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
16/04/07 08:51:41 INFO manager.SqlManager: Using default fetchSize of 1000
16/04/07 08:51:41 INFO tool.CodeGenTool: Beginning code generation
16/04/07 08:51:41 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: No suitable driver found for jdbc:mysql//master.centos:3306/employees
java.sql.SQLException: No suitable driver found for jdbc:mysql//master.centos:3306/employees
   at java.sql.DriverManager.getConnection(DriverManager.java:689)
   at java.sql.DriverManager.getConnection(DriverManager.java:247)
   at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:885)
   at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
   at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:744)
   at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:767)
   at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:270)
   at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:241)
   at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:227)
   at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295)
   at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1845)
   at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645)
   at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
   at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
   at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
   at org.apache.sqoop.Sqoop.run(Sqoop.java:148)
   at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
   at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)
   at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)
   at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)
   at org.apache.sqoop.Sqoop.main(Sqoop.java:244)
16/04/07 08:51:41 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
   at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1651)
   at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
   at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
   at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
   at org.apache.sqoop.Sqoop.run(Sqoop.java:148)
   at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
   at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)
   at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)
   at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)
   at org.apache.sqoop.Sqoop.main(Sqoop.java:244)

What do you think I am missing? Should I install the connector on a certain way or from a certain folder?

Thanks-

[#5]

I think "jdbc:mysql//master.centos:3306/employees" is not a valid jdbc url for mysql. Try to add ':' character after jdbc:mysql:

[#6]

@Wellington De Oliveira

The /usr directory is the default install

Confirm that .jar is in the Java share directory. Make sure the .jar file has the appropriate permissions - 644.

[root@ambarihost ~]# ls –al /usr/share/java/mysql-connector-java.jar 
-rw-r--r--. 1 root root 819803 Jun 22 2012 mysql-connector-java-5.1.17.jar 
lrwxrwxrwx. 1 root root 31 Nov 8 20:51 mysql-connector-java.jar -> mysql-connector-java-5.1.17.jar

Try that and tell if it works out

[#7]

I actually had it there already:

[root@master ~]# ls -la /usr/share/java/mysql-connector-java.jar

lrwxrwxrwx 1 root root 31 Apr 3 13:49 /usr/share/java/mysql-connector-java.jar -> mysql-connector-java-5.1.17.jar

I have ran the import again an still getting the same error

[#8]

Hi @Wellington De Oliveira, change that symlink, it's still pointing to the old version of the connector:

cd /usr/share/java
rm mysql-connector-java.jar
ln -s mysql-connector-java-5.1.38.jar mysql-connector-java.jar

Check the name of the new version, not sure is it as I wrote.

[#9 · Accepted solution]

Hello all,

Sorry for not getting back with feedbacks during this week, but my environment was very unstable and I had to rebuild it on AWS. I have done the driver installation again: tar zxf /usr/mysql-connector-java-5.1.38.tar.gz. Then I realize that i still have the old version mysql-connector-java.jar under /usr/share/java. I then I want to copy the new file from /usr/mysql-connector-java-5.1.38 into the java folder but I realize that this is the file I have there: -rw-r--r--. 1 root root 983914 Dez 2 03:02 mysql-connector-java-5.1.38-bin.jar

Is this normal to have this file with "bin" before the jar?

I then copy it to /usr/share/java. I change it permissions with chmod 644 and try the sqoop command and get the same errors. Then I change the file name from mysql-connector-java-5.1.38-bin.jar into mysql-connector-java-5.1.38.jar and remove the mysql-connector-java.jar and keep having the same error.

Any insights appreciated!

Thanks!

Wellington

[#10]

Hi @Wellington De Oliveira, please see my answer of April 9 above, and update mysql-connector-java.jar which is a symbolic link, to point to the latest version of the driver.