sqoop import issue with mysql

问题:

I have a hadoop ha setup based on cdh5.I have tried to import tables from mysql by using sqoop failed with following error.

15/03/20 12:47:53 ERROR manager.SqlManager: Error reading from database: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@33573e93 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@33573e93 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.

I have used the below command..

sqoop import --connect jdbc:mysql://<mysql hostname>:3306/haddata --username root --password password --table authors --hive-import

My mysql server version is 5.1.73-3. and used 5.1.34 and 5.1.17 version of mysql-connector-java

sqoop version is 1.4.5-cdh5.3.2

Please let me know any suggestion/comments.

回答1:

Try including the option --driver com.mysql.jdbc.Driver in the import command.

回答2:

Try using the below modified command, which can suit your purpose

sqoop import --connect jdbc:mysql://<mysql hostname>:3306/haddata --driver com.mysql.jdbc.Driver --username root --password password --table authors --hive-import

回答3:

follow this link

Include the driver argument --driver com.mysql.jdbc.Driver in sqoop command.

sqoop import --connect jdbc:mysql://<mysql hostname>:3306/<db name> --username **** --password **** --table <table name> --hive-import --driver com.mysql.jdbc.Driver

The --driver parameter forces sqoop to use the latest mysql-connector-java.jar installed for mysql db on the sqoop machine

回答4:

Try with mysql-connector-java-5.1.31.jar, it is compatable with sqoop 1.4.5.

mysql-connector-java-5.1.17.jar driver does not work with sqoop 1.4.5.

refer :

https://issues.apache.org/jira/browse/SQOOP-1400

回答5:

If you have com.mysql.jdbc_5.1.5.jar or any version of com.mysql.jdbc_5.X.X.jar file in $HADOOP_HOME/bin folder, then remove that, and execute your SQOOP query.

回答6:

including the option --driver com.mysql.jdbc.Driver in the import command worked for me.

回答7:

Sqoop does not ship with third party JDBC drivers. You must download them separately and save them to the /var/lib/sqoop/ directory on the server.

Note: The JDBC drivers need to be installed only on the machine where Sqoop runs. You do not need to install them on all hosts in your Hadoop cluster.

You can download driver from here : https://dev.mysql.com/downloads/connector/j/5.1.html

回答8:

Try the exact command as like below.

sqoop import --connect "jdbc:mysql://localhost:3306/books" --username=root --password=root --table authors --as-textfile --target-dir=/datasqoop/authors_db --columns "id, name, email" --split-by id --driver com.mysql.jdbc.Driver

This will resolve your issues.

回答9:

Find the jar locations that are being used in sqoop, in my case, it is pointing to the link /usr/share/java/mysql-connector-java.jar

so when I check the link /usr/share/java/mysql-connector-java.jar it points to mysql-connector-java-5.1.17.jar

/usr/share/java/mysql-connector-java.jar -> mysql-connector-java-5.1.17.jar

as 5.1.17 is having this issue, try 5.1.37 or higher.

unlink /usr/share/java/mysql-connector-java.jar

ln -s /usr/share/java/mysql-connector-java.jar /usr/share/java/mysql-connector-java-5.1.37.jar