Hi, All
I got a error message of 'Lock wait timeout exceeded' in hive metastore as follows;
what causes this problem?
HDP (2.1.2.1)
Hive(1.2.1.2.3)
MySQL(5.6.27)
after googling, I got the following recommendation;
first, add two values in my.cnf and restart mysqld.
this resolution right?
Is it enough to set ONLY 'innodb_lock_wait_timeout' value?
innodb_lock_wait_timeout = 300
transaction-isolation = READ-COMMITTED
Is the reason that cause timeout is deadlock possible?
Is there any configuration of Hive to be changed?
NestedThrowablesStackTrace:
Insert of object "org.apache.hadoop.hive.metastore.model.MPartition@2a3d337" using statement "INSERT INTO `PARTITIONS` (`PART_ID`,`LAST_ACCESS_TIME`,`TBL_ID`,`SD_ID`,`CREATE_TIME`,`PART_NAME`) VALUES (?,?,?,?,?,?)" failed : Lock wait timeout exceeded; try restarting transaction
org.datanucleus.exceptions.NucleusDataStoreException: Insert of object "org.apache.hadoop.hive.metastore.model.MPartition@2a3d337" using statement "INSERT INTO `PARTITIONS` (`PART_ID`,`LAST_ACCESS_TIME`,`TBL_ID`,`SD_ID`,`CREATE_TIME`,`PART_NAME`) VALUES (?,?,?,?,?,?)" failed : Lock wait timeout exceeded; try restarting transaction
at org.datanucleus.store.rdbms.request.InsertRequest.execute(InsertRequest.java:505)
at org.datanucleus.store.rdbms.RDBMSPersistenceHandler.insertTable(RDBMSPersistenceHandler.java:167)
at org.datanucleus.store.rdbms.RDBMSPersistenceHandler.insertObject(RDBMSPersistenceHandler.java:143)
at org.datanucleus.state.JDOStateManager.internalMakePersistent(JDOStateManager.java:3784)
at org.datanucleus.state.JDOStateManager.makePersistent(JDOStateManager.java:3760)
at org.datanucleus.ExecutionContextImpl.persistObjectInternal(ExecutionContextImpl.java:2219)
at org.datanucleus.ExecutionContextImpl.persistObjectWork(ExecutionContextImpl.java:2065)
at org.datanucleus.ExecutionContextImpl.persistObjects(ExecutionContextImpl.java:2005)
at org.datanucleus.ExecutionContextThreadedImpl.persistObjects(ExecutionContextThreadedImpl.java:231)
at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistentAll(JDOPersistenceManager.java:776)
at org.apache.hadoop.hive.metastore.ObjectStore.addPartitions(ObjectStore.java:1368)
at sun.reflect.GeneratedMethodAccessor43.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:114)
at com.sun.proxy.$Proxy0.addPartitions(Unknown Source)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.add_partitions_core(HiveMetaStore.java:2181)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.add_partitions_req(HiveMetaStore.java:2215)
at sun.reflect.GeneratedMethodAccessor41.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107)
at com.sun.proxy.$Proxy3.add_partitions_req(Unknown Source)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$add_partitions_req.getResult(ThriftHiveMetastore.java:9632)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$add_partitions_req.getResult(ThriftHiveMetastore.java:9616)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge.java:681)
at org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge.java:676)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657)
at org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor.process(HadoopThriftAuthBridge.java:676)
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2113)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2049)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2034)
at com.jolbox.bonecp.PreparedStatementHandle.executeUpdate(PreparedStatementHandle.java:205)
at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeUpdate(ParamLoggingPreparedStatement.java:399)
at org.datanucleus.store.rdbms.SQLController.executeStatementUpdate(SQLController.java:439)
at org.datanucleus.store.rdbms.request.InsertRequest.execute(InsertRequest.java:410)
... 36 more
Best Regards,
Park
