Error creating index in Hybris 1808 during system update

[former_member704915]

Hi, I am getting the error java.sql.BatchUpdateException: error occurred during batching: ORA-00955: name is already used by an existing object while doing system update (only update running system). I am not sure which index or drop or recreate as these are all OOTB in my knowledge. Please suggest a solution.

Stack trace

CREATE INDEX IdxITEMPK_1203Sn ON zones1203sn (ITEMPK); CREATE OR REPLACE TRIGGER trg_zones1203sn_ID BEFORE INSERT ON zones1203sn FOR EACH ROW WHEN (new.ID IS NULL) BEGIN SELECT seq_zones1203sn_ID.nextval INTO :new.ID FROM dual; END;; CREATE INDEX supercatalogRelIDX_600 ON CATALOGS (P_SUPERCATALOG); CREATE INDEX originalpageRelIDX_1072 ON CMSPAGE (P_ORIGINALPAGE); CREATE INDEX variationposPosIDX_3604 ON CXABSTRACTACTION (P_VARIATIONPOS); CREATE INDEX cxResultsSessionKeyIdx_3612 ON CXRESULTS (P_SESSIONKEY); CREATE INDEX cxResultsKeyIdx_3612 ON CXRESULTS (P_KEY); CREATE INDEX sourceruleRelIDX_5100 ON ENGINERULES (P_SOURCERULE); CREATE INDEX auditreportconfigRelIDX_30 ON MEDIAS (P_AUDITREPORTCONFIG); CREATE INDEX cxPersonalizationProcessKeyIdx ON PROCESSES (P_KEY); CREATE INDEX IdxsourcePK_20009Sn ON PRODAVAILGROUP220009SN (SOURCEPK); CREATE INDEX IdxlanguagePK_20009Sn ON PRODAVAILGROUP220009SN (LANGUAGEPK); CREATE INDEX IdxtargetPK_20009Sn ON PRODAVAILGROUP220009SN (TARGETPK); CREATE UNIQUE INDEX UC_Abstract_Rule_5103 ON RULES (P_CODE, P_VERSION); CREATE UNIQUE INDEX IdxAbstractRuleModuleUnique_51 ON RULESMODULE (P_NAME); CREATE UNIQUE INDEX IdxDroolsKIEModuleUnique_5101 ON RULESMODULE (P_MVNARTIFACTID); CREATE INDEX Cond_match_idx_951 ON TASKCONDITIONS (P_TASK, P_FULFILLED, TYPEPKSTRING); CREATE INDEX Task_dr2_idx_950 ON TASKS (P_FAILED, P_RUNNINGONCLUSTERNODE, P_EXECUTIONHOURMILLIS, TYPEPKSTRING, P_NODEID); CREATE INDEX pathIndex_5 ON USERGROUPS (P_PATH)];

SQL state [null]; error code [17081];

error occurred during batching: ORA-00955: name is already used by an existing object ; nested exception is java.sql.BatchUpdateException: error occurred during batching: ORA-00955: name is already used by an existing object

     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
     at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
     at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:606) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
     at de.hybris.bootstrap.ddl.DefaultDbScriptsExecutor.executeBatch(DefaultDbScriptsExecutor.java:142) ~[ybootstrap.jar:?]
     ... 152 more

Caused by: java.sql.BatchUpdateException: error occurred during batching: ORA-00955: name is already used by an existing object

     at oracle.jdbc.driver.OracleStatement.executeBatch(OracleStatement.java:4615) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
     at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
     at de.hybris.platform.jdbcwrapper.interceptor.StatementWithJDBCInterceptor.lambda$28(StatementWithJDBCInterceptor.java:183) ~[coreserver.jar:?]
     at de.hybris.platform.jdbcwrapper.interceptor.recover.SQLRecoverableExceptionHandler.passThrough(SQLRecoverableExceptionHandler.java:101) ~[coreserver.jar:?]
     at de.hybris.platform.jdbcwrapper.interceptor.recover.SQLRecoverableExceptionHandler.get(SQLRecoverableExceptionHandler.java:59) ~[coreserver.jar:?]
     at de.hybris.platform.jdbcwrapper.interceptor.JDBCInterceptor.get(JDBCInterceptor.java:69) ~[coreserver.jar:?]
     at de.hybris.platform.jdbcwrapper.interceptor.StatementWithJDBCInterceptor.executeBatch(StatementWithJDBCInterceptor.java:183) ~[coreserver.jar:?]
     at de.hybris.platform.jdbcwrapper.StatementImpl.executeBatch(StatementImpl.java:481) ~[coreserver.jar:?]
     at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallback.doInStatement(JdbcTemplate.java:567) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
     at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallback.doInStatement(JdbcTemplate.java:554) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
     at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
     at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:606) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
     at de.hybris.bootstrap.ddl.DefaultDbScriptsExecutor.executeBatch(DefaultDbScriptsExecutor.java:142) ~[ybootstrap.jar:?]
 

[arvind-kumar_avinash]

Apart from of the reason of the duplicate database item (index, table, trigger etc.), this error may occur even due to mismatched database driver and/or improper database configuration. You can execute the following SQL command to find all indexes:

 SELECT * FROM USER_INDEXES

and check if any of the returned indexes is/are present in the error log. Please note that Update does not drop any column or table.

Helpful link:

https://help.hybris.com/1811/hcd/8c1e31a786691014824b843c1ac96b71.html

[arvind-kumar_avinash]

Hi - any update regarding this?

[former_member704915]

Hi It was an error with the sequence name and not with the index name. So, I had dropped all the sequence and re-created it. I used the below script to delete all the existing sequence:

*BEGIN FOR s IN (SELECT sequence_name FROM user_sequences) LOOP EXECUTE IMMEDIATE ('DROP SEQUENCE ' || s.sequence_name); END LOOP;

END;*

[Former Member]

Hello ,

I'm facing the same problem, i want to undertand how can 'user_sequences' cause this problem ? Does the 'last_number' excededed, in your case, the 'max_value' for one or all your sequence(s) or there is others reasons ?

Thanks in advance for your reply Regards