View Issue Details

IDProjectCategoryView StatusLast Update
0002922SymmetricDSBugpublic2019-05-13 16:17
ReporteriberrasAssigned To 
Prioritynormal 
Status newResolutionopen 
Product Version3.8.9 
Target VersionFixed in Version 
Summary0002922: Send-Schema command to HSQL gets "object dependent exists" because trigger on table
DescriptionTrying 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 ReproduceIt 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.
Tagsddl/schema

Activities

elong

2016-11-30 12:10

developer   ~0000916

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

iberras

2016-11-30 16:36

reporter   ~0000919

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?

chenson

2016-11-30 17:25

administrator   ~0000920

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 ...

iberras

2016-12-01 07:43

reporter   ~0000921

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!

iberras

2016-12-01 09:45

reporter   ~0000922

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!

Issue History

Date Modified Username Field Change
2016-11-30 08:21 iberras New Issue
2016-11-30 12:10 elong Note Added: 0000916
2016-11-30 16:36 iberras Note Added: 0000919
2016-11-30 17:25 chenson Note Added: 0000920
2016-12-01 07:43 iberras Note Added: 0000921
2016-12-01 09:45 iberras Note Added: 0000922
2019-04-24 10:17 elong Tag Attached: ddl/schema
2019-05-13 16:17 elong Summary Send-Schema command not working with HSQL => Send-Schema command to HSQL gets "object dependent exists" because trigger on table