View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0001827 | SymmetricDS | Bug | public | 2014-07-22 20:53 | 2016-06-09 17:06 |
| Reporter | sparker18 | Assigned To | chenson | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.6.2 | ||||
| Target Version | 3.6.3 | Fixed in Version | 3.6.3 | ||
| Summary | 0001827: Long Oracle tables generate ORA-04095 on trigger creation | ||||
| Description | SymmetricDS fails to generate unique trigger names on replicated tables if table names are long and similarly named, generating an ORA-04095 error. It seems that earlier versions of SymmetricDS added a numeric suffix to trigger names to guarantee uniqueness but I've been able to duplicate this issue in the following versions: 3.5.22 3.6.1 3.6.2 A portion of the log file is as follows: ... [dgdn-000] - OracleSymmetricDialect - Creating SYM_ON_I_FOR_PRDCTN_STTS_RPT_ trigger for DBOWNER.PRDCTN_STTS_RPT_CNFG [dgdn-000] - OracleSymmetricDialect - Creating SYM_ON_U_FOR_PRDCTN_STTS_RPT_ trigger for DBOWNER.PRDCTN_STTS_RPT_CNFG [dgdn-000] - OracleSymmetricDialect - Creating SYM_ON_D_FOR_PRDCTN_STTS_RPT_ trigger for DBOWNER.PRDCTN_STTS_RPT_CNFG ... [dgdn-000] - OracleSymmetricDialect - Creating SYM_ON_I_FOR_PRDCTN_STTS_RPT_ trigger for DBOWNER.PRDCTN_STTS_RPT_CNFG_PRMT [dgdn-000] - OracleSymmetricDialect - Failed to create trigger: create or replace trigger SYM_ON_I_FOR_PRDCTN_STTS_RPT_ after insert on "DBOWNER"."PRDCTN_STTS_RPT_CNFG_PRMT" for each row begin if 1=1 and sym_trigger_disabled() is null then insert into "SYMDS".sym_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) values( 'prdctn_stts_rpt_cnfg_prmt', 'I', 42, decode(:new."PRDCTN_STTS_RPT_CNFG_ID", null, '', '"'||cast(:new."PRDCTN_STTS_RPT_CNFG_ID" as number(30,10))||'"')||','|| decode(:new."LNG_CODE", null, '', '"'||cast(:new."LNG_CODE" as number(30,10))||'"')||','|| decode(:new."RPT_APLCTN_STTS_ORDR_ID", null, '', '"'||cast(:new."RPT_APLCTN_STTS_ORDR_ID" as number(30,10))||'"')||','|| decode(:new."APLCTN_STTS_CODE", null, '', '"'||cast(:new."APLCTN_STTS_CODE" as number(30,10))||'"'), 'master_channel', sym_transaction_id(), sym_pkg.disable_node_id, null, CURRENT_TIMESTAMP ); end if; end; [dgdn-000] - TriggerRouterService - Cleaning up trigger hist row of 42 after failing to create the associated trigger [dgdn-000] - TriggerRouterService - Failed to create triggers for DBOWNER.prdctn_stts_rpt_cnfg_prmt org.jumpmind.db.sql.SqlException: ORA-04095: trigger 'SYM_ON_I_FOR_PRDCTN_STTS_RPT_' already exists on another table, cannot replace it at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:288) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:279) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:326) at org.jumpmind.db.sql.JdbcSqlTransaction.execute(JdbcSqlTransaction.java:250) at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createTrigger(AbstractSymmetricDialect.java:313) at org.jumpmind.symmetric.db.oracle.OracleSymmetricDialect.createTrigger(OracleSymmetricDialect.java:96) at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessary(TriggerRouterService.java:1599) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTriggers(TriggerRouterService.java:1470) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTrigger(TriggerRouterService.java:1350) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTriggers(TriggerRouterService.java:1340) at org.jumpmind.symmetric.service.impl.TriggerRouterService.syncTriggers(TriggerRouterService.java:1059) at org.jumpmind.symmetric.service.impl.TriggerRouterService.syncTriggers(TriggerRouterService.java:1005) at org.jumpmind.symmetric.service.impl.TriggerRouterService.syncTriggers(TriggerRouterService.java:1001) at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:512) at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:494) at org.jumpmind.symmetric.web.SymmetricEngineHolder$EngineStarter.run(SymmetricEngineHolder.java:435) Caused by: java.sql.SQLSyntaxErrorException: ORA-04095: trigger 'SYM_ON_I_FOR_PRDCTN_STTS_RPT_' already exists on another table, cannot replace it at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336) at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916) at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878) at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:257) at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:250) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:324) ... 13 more This error occurs five times in our one environment; a full list of problematic tables names is given below. | ||||
| Additional Information | The pairs of table names that cause issues in our care are: - PRDCTN_STTS_RPT_CNFG (succeeds), PRDCTN_STTS_RPT_CNFG_PRMT (fails) - BC_PRSN_SCNDRY_CRSNG_IMG (succeeds), BC_PRSN_SCNDRY_CRSNG_FNGRPRNT (fails) - BC_PRSN_CRSNG_CNTXT (succeeds), BC_PRSN_CRSNG (fails) - RSTRCTN_LIST_EXDETAIL_RSLT (succeeds), RSTRCTN_LIST_EXDETAIL (fails) - BCS_CRSNG_APLCTN_SGNTR (succeeds), BCS_CRSNG_APLCTN_PHT (fails) | ||||
| Tags | No tags attached. | ||||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2014-07-22 20:53 | sparker18 | New Issue | |
| 2014-07-22 20:57 | chenson | Note Added: 0000556 | |
| 2014-07-25 16:14 | chenson | Assigned To | => chenson |
| 2014-07-25 16:14 | chenson | Status | new => assigned |
| 2014-07-25 16:14 | chenson | Fixed in Version | => 3.6.3 |
| 2014-07-25 16:14 | chenson | Target Version | => 3.6.3 |
| 2014-07-25 16:14 | chenson | Status | assigned => resolved |
| 2014-07-25 16:14 | chenson | Resolution | open => fixed |
| 2014-07-25 17:00 | chenson | Changeset attached | => SymmetricDS trunk r8502 |
| 2014-07-29 20:50 | chenson | Status | resolved => closed |
| 2015-07-31 01:49 | chenson | Changeset attached | => SymmetricDS master 6e33c4c4 |