oozie - sqoop fails to store incremental state to the metastore

Question

I get this on saving incremental import state

16/05/15 21:43:05 INFO tool.ImportTool: Saving incremental import state to the metastore
16/05/15 21:43:56 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: Error communicating with database
at org.apache.sqoop.metastore.hsqldb.HsqldbJobStorage.createInternal(HsqldbJobStorage.java:426)
at org.apache.sqoop.metastore.hsqldb.HsqldbJobStorage.update(HsqldbJobStorage.java:445)
at org.apache.sqoop.tool.ImportTool.saveIncrementalState(ImportTool.java:164)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:518)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:228)
at org.apache.sqoop.tool.JobTool.run(JobTool.java:283)
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)
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
at org.apache.sqoop.metastore.hsqldb.HsqldbJobStorage.setV0Property(HsqldbJobStorage.java:707)
at org.apache.sqoop.metastore.hsqldb.HsqldbJobStorage.createInternal(HsqldbJobStorage.java:391)
... 12 more 

I have changed default metastore to MySQL. Everything else has been working just fine . I understand that some of the sources say that sqoop does not support it. In that case, I also need to know what is the default HSQL db url that I need to specify in --metaconnect argument?

In sqoop site.xml, I see that it is :

jdbc:hsqldb:file:/tmp/sqoop-meta/meta.db;shutdown=true

But what do I specify in --meta-connect? I am sure this is not to be specified in there.

I need to specify meta-connect explicitly because I am running jobs through oozie and so will need to specify meta-connect.

I have checked and rechecked but nothing is running on port 16000. sqoop-metastore command can ofcourse start it but then it's not running as a service in that case.

so what I am asking is either a way to run sqoop metastore as a service or need to know why is this failing to store state in metastore when it is configured for mysql.

Answer 1

Ran into the same problem today with MySQL and found out why.

Sqoop locks himself out i guess by using different jdbc connections within the same process. By default, MariaDB (MySQL) creates table using the INNODB engine witch introduces transactions... I guess nobody tested Sqoop with INNODB.

All I did was to recreate the SQOOP_SESSIONS table in the metastore and used MyISAM engine.

CREATE TABLE `SQOOP_SESSIONS_n` (
  `job_name` varchar(64) NOT NULL,
  `propname` varchar(128) NOT NULL,
  `propval` varchar(1024) DEFAULT NULL,
  `propclass` varchar(32) NOT NULL,
  UNIQUE KEY `SQOOP_SESSIONS_n_unq` (`job_name`,`propname`,`propclass`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

drop table SQOOP_SESSIONS;

rename table SQOOP_SESSIONS_n to SQOOP_SESSIONS;

Of course you wouldn't want to loose your created jobs if you had any. Just copy them BEFORE dropping the table:

insert into SQOOP_SESSIONS_n select * from SQOOP_SESSIONS;