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

["Sindhu Subhas (JIRA)" <ji...@apache.org>]

Sindhu Subhas created AMBARI-21977:
--------------------------------------

             Summary: Hive View data migration from 1.0 to 1.5 caused "unique constraint violation" issue and Hive View is not usable
                 Key: AMBARI-21977
                 URL: https://issues.apache.org/jira/browse/AMBARI-21977
             Project: Ambari
          Issue Type: Bug
          Components: ambari-views
    Affects Versions: 2.4.2
         Environment: Ambari 2.4.2
            Reporter: Sindhu Subhas


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:

{code:java}
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) 
...
{code}

Ran below statements on Ambari views DB as workaround:

{code:java}
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';
{code}

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



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)