hive多并发问题报错：Caused by: java.sql.BatchUpdateException: Duplicate entry 'admin-ROLE-All-admin-ROLE' for key 'GLOBALPRIVILEGEINDEX'

报错日志：

at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:114) at 
com.sun.proxy.$Proxy82.grantPrivileges(Unknown Source) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultRoles_core(HiveMetaStore.java:679) 
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultRoles(HiveMetaStore.java:648) at 
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:462) 
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.(RetryingHMSHandler.java:66) at 
org.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:72) at 
org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:5768) at 
org.apache.hadoop.hive.metastore.HiveMetaStoreClient.(HiveMetaStoreClient.java:199) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.(HiveMetaStoreClient.java:181) at org.apache.hive.hcatalog.common.HiveClientCache$CacheableHiveMetaStoreClient.(HiveClientCache.java:330) ... 64 more 
Caused by: java.sql.BatchUpdateException: Duplicate entry 'admin-ROLE-All-admin-ROLE' for key 'GLOBALPRIVILEGEINDEX' at 
com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1167) at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1778) at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1262) at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970) at com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:424) at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeBatch(ParamLoggingPreparedStatement.java:372) at org.datanucleus.store.rdbms.SQLController.processConnectionStatement(SQLController.java:628) at 
org.datanucleus.store.rdbms.SQLController.processStatementsForConnection(SQLController.java:596) at org.datanucleus.store.rdbms.SQLController$1.transactionFlushed(SQLController.java:683) at org.datanucleus.store.connection.AbstractManagedConnection.transactionFlushed(AbstractManagedConnection.java:86) at 
org.datanucleus.store.connection.ConnectionManagerImpl$2.transactionFlushed(ConnectionManagerImpl.java:454) at 
org.datanucleus.TransactionImpl.flush(TransactionImpl.java:203) at 
org.datanucleus.TransactionImpl.commit(TransactionImpl.java:267) at 
org.datanucleus.api.jdo.JDOTransaction.commit(JDOTransaction.java:98) ... 81 more 
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'admin-ROLE-All-admin-ROLE' for key 'GLOBALPRIVILEGEINDEX' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at 
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) at 
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) at 
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487) at 
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079) at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1756) ... 93 more

 

解决方案：

1.重新调度脚本进行执行测试一下

2.减少脚本的调度数量

3.设置参数： datanucleus.autoCreateScheme=false ； datanucleus.fixedStoredata=true ；（注：此种方法尽量在hive-site.xml配置文件中进行设置，否则可能不生效，要是用oozie调度，此文件更新到相应的hadoop目录下）

主要引起原因： java.sql.BatchUpdateException: Duplicate entry 'admin-ROLE-All-admin-ROLE' for key 'GLOBALPRIVILEGEINDEX'–主键重复 分析： 在以上场景中，多个进程同时在删除表创建表导致metastore频繁的修改，导致datanucleus在操作mysql的catalog时产生很多的叫诸如DELETEME.TIMESTAMP的表。 这些表会被datanucleus马上删掉。同时另一边datanucleus有个线程在频繁的读取mysql所有表的所有列信息。mysql的JDBC驱动通过先查询所有的表，然后遍历所有 表的列来达到这个目的。如果在这个过程中有表被删了，mysql就会报一个异常。 然而这个异常被hive解释为“default”这个默认的数据库不存在，同时datanucleus.autoCreateScheme这个参数设为true和datanucleus.fixedStoredata设为false，所以 hive就会往metastore里面又加一条记录（对应于mysql的metastore.DBS这个表），从而引发了这个异常 java.sql.BatchUpdateException: Duplicate entry 'admin-ROLE-All-admin-ROLE' for key 'GLOBALPRIVILEGEINDEX'

参考：

http://shzhangji.com/cnblogs/2013/09/06/hive-deleteme-error/