View Issue Details

IDProjectCategoryView StatusLast Update
0001827SymmetricDSBugpublic2016-06-09 13:06
Reportersparker18Assigned Tochenson 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.6.2 
Target Version3.6.3Fixed in Version3.6.3 
Summary0001827: Long Oracle tables generate ORA-04095 on trigger creation
DescriptionSymmetricDS 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 InformationThe 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)
TagsNo tags attached.

Activities

chenson

2014-07-22 16:57

administrator   ~0000556

You can specify your own trigger name in sym_trigger to work around this.

Related Changesets

SymmetricDS: master 6e33c4c4

2014-07-25 12:12:38

chenson

Details Diff
0001827: Long Oracle tables generate ORA-04095 on trigger creation
0001827
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterService.java Diff File

Issue History

Date Modified Username Field Change
2014-07-22 16:53 sparker18 New Issue
2014-07-22 16:57 chenson Note Added: 0000556
2014-07-25 12:14 chenson Assigned To => chenson
2014-07-25 12:14 chenson Status new => assigned
2014-07-25 12:14 chenson Fixed in Version => 3.6.3
2014-07-25 12:14 chenson Target Version => 3.6.3
2014-07-25 12:14 chenson Status assigned => resolved
2014-07-25 12:14 chenson Resolution open => fixed
2014-07-25 13:00 chenson Changeset attached => SymmetricDS trunk r8502
2014-07-29 16:50 chenson Status resolved => closed
2015-07-30 21:49 chenson Changeset attached => SymmetricDS master 6e33c4c4