View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005086 | SymmetricDS | Bug | public | 2021-09-17 12:52 | 2021-11-04 14:58 |
| Reporter | Quaisaq | Assigned To | josh-a-hicks | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.12.11 | ||||
| Target Version | 3.12.12 | Fixed in Version | 3.12.12 | ||
| Summary | 0005086: Wrong logic for deleting triggers on SQL Anywhere | ||||
| Description | When performing a trigger synchronisation, the first step is to delete the old triggers. It seems that code used for deleting triggers on SQL Anywhere uses the naming format "schema_name"."trigger_name". This is however wrong, as this results in a "table not found" error, as the correct format is "schema_name"."table_name"."trigger_name" in SQL Anywhere. Triggers are defined on tables, not on schemas. At the moment, this means I have to reset my triggers every time a change to tables occurs, as SymmetricDS is unable to synchronise the triggers. I can see on GitHub that the logic for when to perform trigger deletes was modified on May 6 by @erilong. The old code was likely never executed, and the new code seems correct, however this may be why this has not been reported earlier. | ||||
| Steps To Reproduce | 1) Start SQL Anywhere database 2) Connect SymmetricDS 3) Create table_trigger for table X (remember to define source_schema_name) 4) Await SymmetricDS trigger synchronisation 5) Alter table, so a new trigger is required 6) Await SymmetricDS trigger synchronisation | ||||
| Additional Information | An example error log: [spacemanager-denmark-demo-111] - TriggerRouterService - Synchronizing triggers [spacemanager-denmark-demo-111] - SqlAnywhereSymmetricDialect - Dropping SYM_ON_I_FOR_QCLDGR_SPCMNGRLCL trigger for Spaceman.spaceman..IQacLedger [spacemanager-denmark-demo-111] - SqlAnywhereSymmetricDialect - Error removing SYM_ON_I_FOR_QCLDGR_SPCMNGRLCL: SQL Anywhere Error -141: Table 'spaceman' not found [spacemanager-denmark-demo-111] - TriggerRouterService - Failed to create triggers for spaceman.IQacLedger org.jumpmind.db.sql.SqlException: SQL Anywhere Error -141: Table 'spaceman' not found at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:313) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:302) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:398) at org.jumpmind.symmetric.db.sqlanywhere.SqlAnywhereSymmetricDialect.removeTrigger(SqlAnywhereSymmetricDialect.java:142) at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessary(TriggerRouterService.java:2083) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTriggers(TriggerRouterService.java:1948) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTrigger(TriggerRouterService.java:1813) at org.jumpmind.symmetric.service.impl.TriggerRouterService$2.run(TriggerRouterService.java:1691) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) at java.base/java.lang.Thread.run(Thread.java:831) Caused by: java.sql.SQLException: SQL Anywhere Error -141: Table 'spaceman' not found at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2780) at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(SybStatement.java:2665) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:295) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:272) at com.sybase.jdbc4.jdbc.SybStatement.executeLoop(SybStatement.java:2594) at com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:2581) at com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:1293) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194) at org.jumpmind.symmetric.db.sqlanywhere.SqlAnywhereSymmetricDialect$1.execute(SqlAnywhereSymmetricDialect.java:151) at org.jumpmind.symmetric.db.sqlanywhere.SqlAnywhereSymmetricDialect$1.execute(SqlAnywhereSymmetricDialect.java:142) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:396) ... 10 more | ||||
| Tags | dialect: sql anywhere, sync-trigger, trigger | ||||
|
|
Error found on line 137 here: https://github.com/JumpMind/symmetric-ds/blob/3.12/symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereSymmetricDialect.java Changing to use Table.getFullyQualifiedTableName(catalogName, schemaName, tableName) instead of schemaName should solve the issue |
|
SymmetricDS: 3.13 181b6bf6 2021-10-11 12:58:01 joshahicks Details Diff |
0005086: Wrong logic for deleting triggers on SQL Anywhere |
Affected Issues 0005086 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereSymmetricDialect.java | Diff File | ||
|
SymmetricDS: 3.13 54b6ebce 2021-10-26 18:35:52 joshahicks Details Diff |
0005086: Wrong logic for deleting triggers on SQL Anywhere |
Affected Issues 0005086 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereSymmetricDialect.java | Diff File | ||
|
SymmetricDS: 3.12 31949a22 2021-11-03 12:29:26 joshahicks Details Diff |
0005086: Wrong logic for deleting triggers on SQL Anywhere |
Affected Issues 0005086 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereSymmetricDialect.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2021-09-17 12:52 | Quaisaq | New Issue | |
| 2021-09-17 12:52 | Quaisaq | Tag Attached: dialect: sql anywhere | |
| 2021-09-17 12:52 | Quaisaq | Tag Attached: sync-trigger | |
| 2021-09-17 12:52 | Quaisaq | Tag Attached: trigger | |
| 2021-09-17 12:59 | Quaisaq | Note Added: 0002003 | |
| 2021-10-11 20:00 | Changeset attached | => SymmetricDS 3.13 181b6bf6 | |
| 2021-10-13 17:09 | emiller | Assigned To | => josh-a-hicks |
| 2021-10-13 17:09 | emiller | Status | new => resolved |
| 2021-10-13 17:09 | emiller | Resolution | open => fixed |
| 2021-10-13 17:09 | emiller | Fixed in Version | => 3.12.12 |
| 2021-10-13 17:09 | emiller | Target Version | => 3.12.12 |
| 2021-11-03 13:00 | Changeset attached | => SymmetricDS 3.12 31949a22 | |
| 2021-11-03 17:00 | Changeset attached | => SymmetricDS 3.13 54b6ebce | |
| 2021-11-04 14:58 | admin | Status | resolved => closed |