I enabled Transaction and used Remote Metastore (MySQL - MariaDB) in Hive by edit hive-site.xml as below:
configuration>
<property>
<name>hive.support.concurrency</name>
<value>true</value>
</property>
<property>
<name>hive.enforce.bucketing</name>
<value>true</value>
</property>
<property>
<name>hive.exec.dynamic.partition.mode</name>
<value>nonstrict</value>
</property>
<property>
<name>hive.txn.manager</name>
<value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
<property>
<name>hive.compactor.initiator.on</name>
<value>true</value>
</property>
<property>
<name>hive.compactor.worker.threads</name>
<value>1</value>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://master/metastore</value>
<description>the URL of the MySQL database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive</value>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
</property>
<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>
<property>
<name>hive.metastore.uris</name>
<value>thrift://master:9083</value>
<description>IP address (or fully-qualified domain name) and port of the metastore host</description>
</property>
<property>
<name>hive.in.test</name>
<value>true</value>
</property>
</configuration>
When I run hive --service metastore it shows the error:
Error rolling back: Can't call rollback when autocommit=true
When I run query in Hive CLI show databases it shows the error:
FAILED: LockException [Error 10280]: Error communicating with the metastore
When I only enable Transaction or only use Remote Metastore, there is no error.
This is the part of Hive log:
2015-08-22 17:44:12,043 INFO [pool-3-thread-1]: metastore.HiveMetaStore (HiveMetaStore.java:logInfo(746)) - 1: source:172.28.10.148 get_all_databases
2015-08-22 17:44:12,049 INFO [pool-3-thread-1]: HiveMetaStore.audit (HiveMetaStore.java:logAuditEvent(371)) - ugi=hduser ip=172.28.10.148 cmd=source:172.28.10.148 get_all_databases
2015-08-22 17:44:12,053 INFO [pool-3-thread-1]: metastore.HiveMetaStore (HiveMetaStore.java:newRawStore(589)) - 1: Opening raw store with implemenation class:org.apache.hadoop.hive.metastore.ObjectStore
2015-08-22 17:44:12,055 INFO [pool-3-thread-1]: metastore.ObjectStore (ObjectStore.java:initialize(289)) - ObjectStore, initialize called
2015-08-22 17:44:12,128 INFO [pool-3-thread-1]: metastore.MetaStoreDirectSql (MetaStoreDirectSql.java:<init>(139)) - Using direct SQL, underlying DB is MYSQL
2015-08-22 17:44:12,128 INFO [pool-3-thread-1]: metastore.ObjectStore (ObjectStore.java:setConf(272)) - Initialized ObjectStore
2015-08-22 17:44:12,159 INFO [pool-3-thread-1]: metastore.HiveMetaStore (HiveMetaStore.java:logInfo(746)) - 1: source:172.28.10.148 get_functions: db=default pat=*
2015-08-22 17:44:12,159 INFO [pool-3-thread-1]: HiveMetaStore.audit (HiveMetaStore.java:logAuditEvent(371)) - ugi=hduser ip=172.28.10.148 cmd=source:172.28.10.148 get_functions: db=default pat=*
2015-08-22 17:44:20,027 INFO [pool-3-thread-1]: metastore.HiveMetaStore (HiveMetaStore.java:logInfo(746)) - 1: source:172.28.10.148 get_all_databases
2015-08-22 17:44:20,028 INFO [pool-3-thread-1]: HiveMetaStore.audit (HiveMetaStore.java:logAuditEvent(371)) - ugi=hduser ip=172.28.10.148 cmd=source:172.28.10.148 get_all_databases
2015-08-22 17:44:28,015 INFO [Thread-3]: metastore.HiveMetaStore (HiveMetaStore.java:run(5898)) - Shutting down hive metastore.
2015-08-22 18:03:55,331 INFO [main]: metastore.HiveMetaStore (HiveMetaStore.java:main(5881)) - Starting hive metastore on port 9083
2015-08-22 18:03:55,559 INFO [main]: metastore.HiveMetaStore (HiveMetaStore.java:newRawStore(589)) - 0: Opening raw store with implemenation class:org.apache.hadoop.hive.metastore.ObjectStore
2015-08-22 18:03:55,594 INFO [main]: metastore.ObjectStore (ObjectStore.java:initialize(289)) - ObjectStore, initialize called
2015-08-22 18:03:56,815 INFO [main]: metastore.ObjectStore (ObjectStore.java:getPMF(370)) - Setting MetaStore object pin classes with hive.metastore.cache.pinobjtypes="Table,StorageDescriptor,SerDeInfo,Partition,Database,Type,FieldSchema,Order"
2015-08-22 18:03:58,280 INFO [main]: metastore.MetaStoreDirectSql (MetaStoreDirectSql.java:<init>(139)) - Using direct SQL, underlying DB is MYSQL
2015-08-22 18:03:58,283 INFO [main]: metastore.ObjectStore (ObjectStore.java:setConf(272)) - Initialized ObjectStore
2015-08-22 18:03:58,626 INFO [main]: metastore.HiveMetaStore (HiveMetaStore.java:createDefaultRoles_core(663)) - Added admin role in metastore
2015-08-22 18:03:58,628 INFO [main]: metastore.HiveMetaStore (HiveMetaStore.java:createDefaultRoles_core(672)) - Added public role in metastore
2015-08-22 18:03:58,712 INFO [main]: metastore.HiveMetaStore (HiveMetaStore.java:addAdminUsers_core(712)) - No user is added in admin role, since config is empty
2015-08-22 18:03:58,962 INFO [main]: metastore.HiveMetaStore (HiveMetaStore.java:startMetaStore(6009)) - Starting DB backed MetaStore Server with SetUGI enabled
2015-08-22 18:03:58,965 INFO [main]: metastore.HiveMetaStore (HiveMetaStore.java:startMetaStore(6027)) - Started the new metaserver on port [9083]...
2015-08-22 18:03:58,965 INFO [main]: metastore.HiveMetaStore (HiveMetaStore.java:startMetaStore(6029)) - Options.minWorkerThreads = 200
2015-08-22 18:03:58,965 INFO [main]: metastore.HiveMetaStore (HiveMetaStore.java:startMetaStore(6031)) - Options.maxWorkerThreads = 1000
2015-08-22 18:03:58,965 INFO [main]: metastore.HiveMetaStore (HiveMetaStore.java:startMetaStore(6033)) - TCP keepalive = true
2015-08-22 18:03:59,974 INFO [Thread-4]: metastore.HiveMetaStore (HiveMetaStore.java:initializeAndStartThread(6156)) - Starting metastore thread of type org.apache.hadoop.hive.ql.txn.compactor.Initiator
2015-08-22 18:03:59,999 INFO [Thread-4]: txn.TxnHandler (TxnHandler.java:checkQFileTestHack(1277)) - Hacking in canned values for transaction manager
2015-08-22 18:04:00,276 ERROR [Thread-4]: metastore.HiveMetaStore (HiveMetaStore.java:run(6104)) - Failure when starting the compactor, compactions may not happen, java.lang.RuntimeException: Unable to set up transaction database for testing: Can't call rollback when autocommit=true
at org.apache.hadoop.hive.metastore.txn.TxnHandler.checkQFileTestHack(TxnHandler.java:1285)
at org.apache.hadoop.hive.metastore.txn.TxnHandler.<init>(TxnHandler.java:120)
at org.apache.hadoop.hive.metastore.txn.CompactionTxnHandler.<init>(CompactionTxnHandler.java:43)
at org.apache.hadoop.hive.ql.txn.compactor.CompactorThread.init(CompactorThread.java:78)
at org.apache.hadoop.hive.ql.txn.compactor.Initiator.init(Initiator.java:165)
at org.apache.hadoop.hive.metastore.HiveMetaStore.initializeAndStartThread(HiveMetaStore.java:6159)
at org.apache.hadoop.hive.metastore.HiveMetaStore.startCompactorInitiator(HiveMetaStore.java:6119)
at org.apache.hadoop.hive.metastore.HiveMetaStore.access$400(HiveMetaStore.java:236)
at org.apache.hadoop.hive.metastore.HiveMetaStore$4.run(HiveMetaStore.java:6100)
These are the versions I'm using:
Hive 1.2.1
hive-schema-1.2.0.mysql.sql
When I remove this property, no more error
<property>
<name>hive.in.test</name>
<value>true</value>
</property>