View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0002922 | SymmetricDS | Bug | public | 2016-11-30 13:21 | 2019-05-13 20:17 |
| Reporter | iberras | Assigned To | |||
| Priority | normal | ||||
| Status | new | Resolution | open | ||
| Product Version | 3.8.9 | ||||
| Summary | 0002922: Send-Schema command to HSQL gets "object dependent exists" because trigger on table | ||||
| Description | Trying to update the schema with the send-schema command. When adding/removing/modifing a column, basically any modification, throws an error that a dependant object exists. There is no workarround for this and i have no other choice but to do a reload wich consumes a lot of time and bandwidth. Stack trace: I get this error: org.jumpmind.db.sql.SqlException: object dependent exists: PUBLIC.SYM_ON_I_FOR_CTVTN_CSRVRDVLPMNT in statement [ALTER TABLE "PUBLIC"."PUBLIC"."ACTIVATION" DROP COLUMN "LASTCONNECTION"] at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:303) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:294) at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:494) at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:402) at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:107) at org.jumpmind.db.platform.AbstractDatabasePlatform.alterTables(AbstractDatabasePlatform.java:219) at org.jumpmind.db.platform.AbstractDatabasePlatform.alterDatabase(AbstractDatabasePlatform.java:192) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.create(DefaultDatabaseWriter.java:509) at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:167) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:65) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:207) at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:200) at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:170) at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:116) at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport(DataLoaderService.java:526) at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPull(DataLoaderService.java:285) at org.jumpmind.symmetric.service.impl.PullService.execute(PullService.java:126) at org.jumpmind.symmetric.service.impl.NodeCommunicationService$1.run(NodeCommunicationService.java:479) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) | ||||
| Steps To Reproduce | It happens all the time. Send schema command with Central DB: MySQL and Client DB: HSQL. Add one column in MySQL, reload triggers, and then send the schema to the client HSQL and the error appears. | ||||
| Tags | ddl/schema | ||||
|
|
When the alter comes through to load on the target node, we could temporarily drop any triggers on it, perform the alter, then re-sync the triggers. In the meantime, a workaround is to manually drop the trigger before altering the table. You could send a BSH script that drops the trigger for that table, followed by sending the schema change, like this: symadmin send-script -n 001 't = new java.util.HashSet(); t.add("mytable"); engine.getTriggerRouterService().dropTriggers(t);' symadmin send-schema -n 001 mytable |
|
|
This was working before. I don't know what has changed. I think in 3.7.36 the send schema was working. That is a workaround, but im working with all the tables sending the send-schema with no tables as a parameter. Is there a way to drop all the triggers at once? Do you think that you could fix this for the next release? |
|
|
Technically, I don't see how this would have every worked. We could add a feature to drop triggers prior to altering tables and then resyncing triggers afterwards. It isn't a super huge priority for us though ... |
|
|
Well then, I will implement a solution that drops all the triggers and then executes the send schema. I will keep an eye on the change log if you implement this in any new release. Thank you very much! |
|
|
For anybody that has the same problem, if you are in the client and what to update all the schema with HSQLDB, use the function node.getTriggerRouterService().dropTriggers(); After that, do the send-schema, and afterwards do the node.syncTriggers(); Hope it helps someone! |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2016-11-30 13:21 | iberras | New Issue | |
| 2016-11-30 17:10 | elong | Note Added: 0000916 | |
| 2016-11-30 21:36 | iberras | Note Added: 0000919 | |
| 2016-11-30 22:25 | chenson | Note Added: 0000920 | |
| 2016-12-01 12:43 | iberras | Note Added: 0000921 | |
| 2016-12-01 14:45 | iberras | Note Added: 0000922 | |
| 2019-04-24 14:17 | elong | Tag Attached: ddl/schema | |
| 2019-05-13 20:17 | elong | Summary | Send-Schema command not working with HSQL => Send-Schema command to HSQL gets "object dependent exists" because trigger on table |