[AMBARI-21977] Hive View data migration from 1.0 to 1.5 caused "unique constraint violation" issue and Hive View is not usable

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.4.2
• Component/s: ambari-views

Environment

Ambari 2.4.2

• Assignee: Unassigned
• Reporter: Sindhu Subhas
• Created: 18/Sep/17 05:13
• Updated: 18/Sep/17 05:13

Description

After migrating the saved queries of hive views from 1.0.0 to 1.5.0, Hive Views is unusable as customer cannot issue any queries.
Ambari server log reports unique constraint violation:

06 Sep 2017 19:49:19,232 INFO [HiveViewActorSystem-akka.actor.jdbc-connector-dispatcher-5-EventThread] ConnectionStateManager:228 - State change: CONNECTED 
06 Sep 2017 19:49:19,261 INFO [HiveViewActorSystem-akka.actor.jdbc-connector-dispatcher-5] ZooKeeper:684 - Session: 0x25d742087830fac closed 
06 Sep 2017 19:49:19,261 INFO [HiveViewActorSystem-akka.actor.jdbc-connector-dispatcher-5-EventThread] ClientCnxn:524 - EventThread shut down 
06 Sep 2017 19:49:32,884 ERROR [ambari-client-thread-109] DataStoreImpl:656 - Caught exception trying to store view entity JobImpl{id='null, owner='ip56, hiveQueryId='null, dagId='null, queryId='null} 
javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.v20151217-774c696): org.eclipse.persistence.exceptions.DatabaseException 
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (AMBARIVIEWS.SYS_C0019026) violated 

Error Code: 1 
Call: INSERT INTO DS_JOBIMPL_201 (DS_id, DS_applicationId, DS_confFile, DS_dagId, DS_dagName, DS_dataBase, DS_dateSubmitted, DS_duration, DS_forcedContent, DS_globalSettings, DS_guid, DS_hiveQueryId, DS_logFile, DS_owner, DS_queryFile, DS_queryId, DS_referrer, DS_sessionTag, DS_sqlState, DS_status, DS_statusDir, DS_statusMessage, DS_title) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
bind => [23 parameters bound] 
at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:159) 
at org.apache.ambari.server.view.persistence.DataStoreImpl.store(DataStoreImpl.java:142) 
at org.apache.ambari.view.hive2.persistence.DataStoreStorage.store(DataStoreStorage.java:61) 
at org.apache.ambari.view.hive2.resources.CRUDResourceManager.save(CRUDResourceManager.java:117) 
... 
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.v20151217-774c696): org.eclipse.persistence.exceptions.DatabaseException 
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (AMBARIVIEWS.SYS_C0019026) violated 
Error Code: 1 
Call: INSERT INTO DS_JOBIMPL_201 (DS_id, DS_applicationId, DS_confFile, DS_dagId, DS_dagName, DS_dataBase, DS_dateSubmitted, DS_duration, DS_forcedContent, DS_globalSettings, DS_guid, DS_hiveQueryId, DS_logFile, DS_owner, DS_queryFile, DS_queryId, DS_referrer, DS_sessionTag, DS_sqlState, DS_status, DS_statusDir, DS_statusMessage, DS_title) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
bind => [23 parameters bound] 
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331) 
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:902) 
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:964) 
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:633) 
at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatch(ParameterizedSQLBatchWritingMechanism.java:149) 
...

Ran below statements on Ambari views DB as workaround:

update AMBARI_SEQUENCES set SEQUENCE_VALUE=1000 WHERE SEQUENCE_NAME='ds_jobimpl_254_id_seq';
update AMBARI_SEQUENCES set SEQUENCE_VALUE=1000 WHERE SEQUENCE_NAME='ds_jobimpl_201_id_seq';

However, while after doing new Data Migration, issue surfaces again.