Hello,
i have made a change to a trigger table activating the sync_on_incoming_batch because it is the root node and i want to replicate data received from store to others stores, i have activated the registration of the clients node groups too, the sym_trigger row it is update in the client nodes but in the root node symmetricDs fails to recreate the triggers with the changes, when i relaunch "sym -p root-properties..." i get this:
2008-07-20 19:09:20,022 INFO [org.jumpmind.symmetric.service.impl.BootstrapService][main] Synchronizing triggers.
2008-07-20 19:09:21,407 WARN [org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect][main] Trigger does not exist
2008-07-20 19:09:21,411 INFO [org.jumpmind.symmetric.db.AbstractDbDialect][main] Creating INSERT trigger for oteros.cliente
2008-07-20 19:09:21,469 ERROR [org.jumpmind.symmetric.db.AbstractDbDialect][main] Failed to create post trigger:
create trigger ON_I_TO_CLNT after insert on oteros.cliente
for each row execute procedure oteros.fON_I_TO_CLNT();
2008-07-20 19:09:21,470 ERROR [org.jumpmind.symmetric.service.impl.BootstrapService][main] Failed to synchronize trigger for cliente
org.springframework.jdbc.BadSqlGrammarException: ConnectionCallback; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
Caused by:
org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:283)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
at org.jumpmind.symmetric.db.AbstractDbDialect$3.doInConnection(AbstractDbDialect.java:553)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:289)
at org.jumpmind.symmetric.db.AbstractDbDialect.initTrigger(AbstractDbDialect.java:533)
at org.jumpmind.symmetric.service.impl.BootstrapService.rebuildTriggerIfNecessary(BootstrapService.java:326)
at org.jumpmind.symmetric.service.impl.BootstrapService.updateOrCreateTriggers(BootstrapService.java:200)
at org.jumpmind.symmetric.service.impl.BootstrapService.syncTriggers(BootstrapService.java:133)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
at $Proxy14.syncTriggers(Unknown Source)
at org.jumpmind.symmetric.SymmetricEngine.start(SymmetricEngine.java:214)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.createConfigureAndStartEngine(SymmetricEngineContextLoaderListener.java:72)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.contextInitialized(SymmetricEngineContextLoaderListener.java:59)
at org.mortbay.jetty.handler.ContextHandler.startContext(ContextHandler.java:530)
at org.mortbay.jetty.servlet.Context.startContext(Context.java:135)
at org.mortbay.jetty.handler.ContextHandler.doStart(ContextHandler.java:500)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerCollection.doStart(HandlerCollection.java:147)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerWrapper.doStart(HandlerWrapper.java:117)
at org.mortbay.jetty.Server.doStart(Server.java:217)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.jumpmind.symmetric.SymmetricWebServer.start(SymmetricWebServer.java:68)
at org.jumpmind.symmetric.SymmetricLauncher.main(SymmetricLauncher.java:171)
2008-07-20 19:09:21,658 WARN [org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect][main] Trigger does not exist
2008-07-20 19:09:21,660 INFO [org.jumpmind.symmetric.db.AbstractDbDialect][main] Creating INSERT trigger for oteros.cliente
2008-07-20 19:09:21,680 ERROR [org.jumpmind.symmetric.db.AbstractDbDialect][main] Failed to create post trigger:
create trigger ON_I_TO_CLNT after insert on oteros.cliente
for each row execute procedure oteros.fON_I_TO_CLNT();
2008-07-20 19:09:21,680 ERROR [org.jumpmind.symmetric.service.impl.BootstrapService][main] Failed to synchronize trigger for cliente
org.springframework.jdbc.BadSqlGrammarException: ConnectionCallback; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
Caused by:
org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:283)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
at org.jumpmind.symmetric.db.AbstractDbDialect$3.doInConnection(AbstractDbDialect.java:553)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:289)
at org.jumpmind.symmetric.db.AbstractDbDialect.initTrigger(AbstractDbDialect.java:533)
at org.jumpmind.symmetric.service.impl.BootstrapService.rebuildTriggerIfNecessary(BootstrapService.java:326)
at org.jumpmind.symmetric.service.impl.BootstrapService.updateOrCreateTriggers(BootstrapService.java:200)
at org.jumpmind.symmetric.service.impl.BootstrapService.syncTriggers(BootstrapService.java:133)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
at $Proxy14.syncTriggers(Unknown Source)
at org.jumpmind.symmetric.SymmetricEngine.start(SymmetricEngine.java:214)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.createConfigureAndStartEngine(SymmetricEngineContextLoaderListener.java:72)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.contextInitialized(SymmetricEngineContextLoaderListener.java:59)
at org.mortbay.jetty.handler.ContextHandler.startContext(ContextHandler.java:530)
at org.mortbay.jetty.servlet.Context.startContext(Context.java:135)
at org.mortbay.jetty.handler.ContextHandler.doStart(ContextHandler.java:500)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerCollection.doStart(HandlerCollection.java:147)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerWrapper.doStart(HandlerWrapper.java:117)
at org.mortbay.jetty.Server.doStart(Server.java:217)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.jumpmind.symmetric.SymmetricWebServer.start(SymmetricWebServer.java:68)
at org.jumpmind.symmetric.SymmetricLauncher.main(SymmetricLauncher.java:171)
2008-07-20 19:09:21,681 INFO [org.jumpmind.symmetric.service.impl.BootstrapService][main] Done synchronizing triggers.
although the problem seems to be in '2008-07-20 19:09:21,407 WARN [org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect][main] Trigger does not exist' in postgresql this can bee avoided using "CREATE OR REPLACE FUNCTION" instead of plain create.
Regards,
Miguel Angel.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
although i have deleted manually the triggers functions still get an error in one of the new triggers created, it seems to be creating two times but only for "on_insert":
2008-07-20 19:28:31,324 INFO [org.jumpmind.symmetric.service.impl.BootstrapService][main] Synchronizing triggers.
2008-07-20 19:28:32,738 INFO [org.jumpmind.symmetric.db.AbstractDbDialect][main] Creating INSERT trigger for oteros.cliente
2008-07-20 19:28:32,744 INFO [org.jumpmind.symmetric.db.AbstractDbDialect][main] Creating UPDATE trigger for oteros.cliente
2008-07-20 19:28:32,748 INFO [org.jumpmind.symmetric.db.AbstractDbDialect][main] Creating DELETE trigger for oteros.cliente
2008-07-20 19:28:32,916 WARN [org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect][main] Trigger does not exist
2008-07-20 19:28:32,918 INFO [org.jumpmind.symmetric.db.AbstractDbDialect][main] Creating INSERT trigger for oteros.cliente
2008-07-20 19:28:32,920 ERROR [org.jumpmind.symmetric.db.AbstractDbDialect][main] Failed to create post trigger:
create trigger ON_I_TO_CLNT after insert on oteros.cliente
for each row execute procedure oteros.fON_I_TO_CLNT();
2008-07-20 19:28:32,921 ERROR [org.jumpmind.symmetric.service.impl.BootstrapService][main] Failed to synchronize trigger for cliente
org.springframework.jdbc.BadSqlGrammarException: ConnectionCallback; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
Caused by:
org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:283)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
at org.jumpmind.symmetric.db.AbstractDbDialect$3.doInConnection(AbstractDbDialect.java:553)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:289)
at org.jumpmind.symmetric.db.AbstractDbDialect.initTrigger(AbstractDbDialect.java:533)
at org.jumpmind.symmetric.service.impl.BootstrapService.rebuildTriggerIfNecessary(BootstrapService.java:326)
at org.jumpmind.symmetric.service.impl.BootstrapService.updateOrCreateTriggers(BootstrapService.java:200)
at org.jumpmind.symmetric.service.impl.BootstrapService.syncTriggers(BootstrapService.java:133)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
at $Proxy14.syncTriggers(Unknown Source)
at org.jumpmind.symmetric.SymmetricEngine.start(SymmetricEngine.java:214)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.createConfigureAndStartEngine(SymmetricEngineContextLoaderListener.java:72)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.contextInitialized(SymmetricEngineContextLoaderListener.java:59)
at org.mortbay.jetty.handler.ContextHandler.startContext(ContextHandler.java:530)
at org.mortbay.jetty.servlet.Context.startContext(Context.java:135)
at org.mortbay.jetty.handler.ContextHandler.doStart(ContextHandler.java:500)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerCollection.doStart(HandlerCollection.java:147)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerWrapper.doStart(HandlerWrapper.java:117)
at org.mortbay.jetty.Server.doStart(Server.java:217)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.jumpmind.symmetric.SymmetricWebServer.start(SymmetricWebServer.java:68)
at org.jumpmind.symmetric.SymmetricLauncher.main(SymmetricLauncher.java:171)
2008-07-20 19:28:32,923 INFO [org.jumpmind.symmetric.service.impl.BootstrapService][main] Done synchronizing triggers.
it seems that only can trigger to a destination node_group for every table/action, no? i have detected that the triggers have:
insert into public.sym_data_event (node_id, data_id, channel_id, transaction_id) (select node_id, currval('public.sym_data_data_id_seq'), 'central_t109_bidir', null from sym_node c where c.node_group_id='t109' and c.sync_enabled=1 );
changing c.node_group_id between t109 and t106 randomly (in the creation order), the problem here it is the way i have organized the triggers, i have not attached all the stores nodes to a same node_group because every store uses a schema name to some of the tables, tickets, offers and anymore because i dont want this tables to growth too much in size to have fast access and it is very easy with ticket lines for example in this sport stores, anyway i can reorganize to make use of only a trigger i think, i will try and post here my results, but i think should be possible to define different node_group destinations for any data event capture.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I can make some comments about the sym_trigger entries I saw you posted. Forgive me if I've misunderstood something.
The "t109" and "t106" node groups look like they are doing the same synchronization with "corp". Instead, you can setup one node group called "store", and then assign nodes to it. You could have node entries of "t019" and "t106" with a node group of "store".
Sometimes you want the "store" node to have a subset of data from a table on "corp". The sym_trigger entry lets you specify the node_select to route changes to nodes and initial_load_select to send the entire subset to a node. For example, a store_item table might have all the items a store can sell. The table has a column called store_id so that different items can be sold at different stores. To sync rows of the table to the right store, you specify a node_select of "and c.external_id = $(curTriggerValue).store_id" and an initial_load_select of "store_id = $(externalId)". This keeps the table small at the store, with only the data they need, but leaves it in one table at corp to consolidate maintenance and queries.
The channels are also meant to be setup once to categorize your dependent data. You want to think about when there is an error synchronizing a table, are there other tables that you want to continue synchronizing? For example, if an error occurs on the store_item table, it will stop synchronizing until you fix it. But, you may want to have the sale_transaction table continue to synchronize. To accomplish this, you create a channel called "item" and "transaction" and you assign them to the sym_trigger entries for those tables. A synchronization error only affects the channel at that node, not the entire group of nodes.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
for reference to anyone using postgresql since the automatic methods of delete triggers (activate inactive_time or set to 0 sync_on_[insert, update, delete]) are not working in this symmetricDs version with postgresql i have using this small script to make it automatically. You should stop symmetricDs before.
create a plain text file with a host for line:
192.168.1.4
192.168.1.215
192.168.1.154
use this in bash:
for host in cat filename_with_hosts; do echo $host; psql -U username -d database -h $host -t -o temp.sql -c "SELECT 'DROP FUNCTION ' || nam.nspname || '.' || pro.proname || '() cascade;' from pg_proc as pro join pg_namespace as nam on nam.oid=pro.pronamespace and nam.nspowner=16384 and pro.proname LIKE 'fon_%'"; psql -U username -d databases -h $host -q -f temp.sql; done
i am using here the catalogs to obtain the data i need to delete all at one time, to check the schemas i want i am using my oid owner (nam.nspowner=16384) but i could use (nam.nspname in ('schema1', 'schema2')) for example, the symmetricDs functions seems to be called automatically (almost on my hosts) fon_[d,i,u]_% so i use the like to filter other user defined functions in the schemas selected.
this generate and later use a file (temp.sql) with lines like this:
DROP FUNCTION datos_antiguos.fon_i_to_fclnt_mrc() cascade;
it is using cascade so it delete the trigger in the table too, after delete the functions make your sym_trigger changes, update the last_updated_time column to all sym_trigger entries in root node (update sym_trigger set last_updated_time=now()) and in node_security activate registration to client nodes (update sym_node_security set registration_enabled=1). This only deleted the triggers on your tables, if you want to recreate all symmetricDs installation instead of only change and recreate the triggers you can use the same trick to delete too the tables and sequences of symmetric in public schema (all use a name that begin with 'sym_').
I hope this can help someone.
Regards,
Miguel Angel.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
okay i have configured the two stores in a only group_node and now the bidirectional works, only that when recreate the triggers every store tries to create the trigger of the schema they dont have and i get this erros in log:
2008-07-20 22:10:28,143 ERROR [org.jumpmind.symmetric.service.impl.BootstrapService][main] The configured table does not exist in the datasource that is configured: caja <--- for every table schema
The other way i think would works it is change manually the triggers with bidirectional replication to add an insert into sym_data_event to every store group_id (using a group_id to every store like before) but i prefer not have to modify manually the triggers, any better idea?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Okay, I didn't realize it was sending the externalId for the nodeId. I went ahead and fixed that, which should allow it to look up the trigger and put the schema name on the front of the tables it is loading.
The reason the triggers cannot be updated is because they are created in the default schema by the template (postgresql.xml), but the PostgresqlDbDialect is looking for their existence in the source schema you specify. I changed it to look in the default schema, which should fix it.
Here is my latest snapshot for 1.3.4 with those fixes. There are checked into the branch and trunk also.
in the machines where i am using symmetric-ds the triggers are created in source schema, symmetric-ds creates in public his tables and sequences but use the source schema to create the triggers functions, it should be other problem i think, here you can see the first line of one of the triggers functions.
CREATE OR REPLACE FUNCTION t106.fon_d_to_mdl_frt()
and here from the table the trigger.
CREATE TRIGGER on_d_to_mdl_frt
AFTER DELETE
ON t106.modelo_oferta
FOR EACH ROW
EXECUTE PROCEDURE t106.fon_d_to_mdl_frt();
the reasons because i want to send a single event capture to different stores by different channels was primarily two:
-only one channel for every group of table replications for the 30 stores maybe can cause me more replication errors and stopped replication on stores not affected for this error.
-separation of any tables that i have designed to stay in different schemas (t109, t106... 30more) because of internal organization and performance, even in center database this tables can grown out of control, i ever can do an union if i want to show a subset or all the stores at the same time, but here my bosses seems to prefer study trends for stores, and make offers based in this unique trends in every store.
Anyway my app it is still in development so i can test to create shared tables in the main schema and replicate to the stores only the relevant entries like you have explained me, i would have to redesign many things but i could try (when i have time hehehe), about how i have configured for now, i have created an node group "store" and make insert in sym_trigger in root_node to replicate t106.caja and t109.caja from group store this way works with my tables organization but when client node t109 register and create triggers i get:
2008-07-20 22:10:28,143 ERROR [org.jumpmind.symmetric.service.impl.BootstrapService][main] The configured table does not exist in the datasource that is configured: caja
obviously trying to create the trigger for t106, and when i register in t106 node i get the same message trying to create the trigger of the t109, well i have this message for every replicated table and now that i have only two stores one at a time, but when i get 30 stores i will get this same error 29 times in every store, not seems to be a good solution, no?
Do i have any way to make a data_event to be replicated to more than one node_group other than manually changing the trigger function and inserting in sym_data_event all the group nodes i want to replicate the information captured? when i read the documentation i thought the node groups can be inherited too (my fault reading i know) so i tried the first time to create a group with all the common tables and later one for every schema of every store that inherited common and create his special triggers in this inherited node_group (this way i can have a only node_group replicating the common tables and the store schema only would need to be replicated to his store so i would not need to make a data event for many node_groups).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have had problems with replication detecting the schema name, i have found the reason seeing the source code flow and inserting any debugging prints:
in csvloader.java hasNext() method tries to get the source node with nodeservice.findNode, that launch this query:
select node_id, node_group_id, external_id, sync_enabled, sync_url, schema_version, database_type, database_version, symmetric_version from ${sync.table.prefix}_node where node_id = ?
but tokens[1] from the buffer has the value of external_id so if it is different from node_id (like in my case) you ever get a null value in sourceNode variable, later in setTable method in the same class it uses this variable to see if try to resolve schema name.
Hello,
i have made a change to a trigger table activating the sync_on_incoming_batch because it is the root node and i want to replicate data received from store to others stores, i have activated the registration of the clients node groups too, the sym_trigger row it is update in the client nodes but in the root node symmetricDs fails to recreate the triggers with the changes, when i relaunch "sym -p root-properties..." i get this:
2008-07-20 19:09:20,022 INFO [org.jumpmind.symmetric.service.impl.BootstrapService] [main] Synchronizing triggers.
2008-07-20 19:09:21,407 WARN [org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect] [main] Trigger does not exist
2008-07-20 19:09:21,411 INFO [org.jumpmind.symmetric.db.AbstractDbDialect] [main] Creating INSERT trigger for oteros.cliente
2008-07-20 19:09:21,469 ERROR [org.jumpmind.symmetric.db.AbstractDbDialect] [main] Failed to create post trigger:
create trigger ON_I_TO_CLNT after insert on oteros.cliente for each row execute procedure oteros.fON_I_TO_CLNT();2008-07-20 19:09:21,470 ERROR [org.jumpmind.symmetric.service.impl.BootstrapService] [main] Failed to synchronize trigger for cliente
org.springframework.jdbc.BadSqlGrammarException: ConnectionCallback; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
Caused by:
org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:283)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
at org.jumpmind.symmetric.db.AbstractDbDialect$3.doInConnection(AbstractDbDialect.java:553)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:289)
at org.jumpmind.symmetric.db.AbstractDbDialect.initTrigger(AbstractDbDialect.java:533)
at org.jumpmind.symmetric.service.impl.BootstrapService.rebuildTriggerIfNecessary(BootstrapService.java:326)
at org.jumpmind.symmetric.service.impl.BootstrapService.updateOrCreateTriggers(BootstrapService.java:200)
at org.jumpmind.symmetric.service.impl.BootstrapService.syncTriggers(BootstrapService.java:133)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
at $Proxy14.syncTriggers(Unknown Source)
at org.jumpmind.symmetric.SymmetricEngine.start(SymmetricEngine.java:214)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.createConfigureAndStartEngine(SymmetricEngineContextLoaderListener.java:72)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.contextInitialized(SymmetricEngineContextLoaderListener.java:59)
at org.mortbay.jetty.handler.ContextHandler.startContext(ContextHandler.java:530)
at org.mortbay.jetty.servlet.Context.startContext(Context.java:135)
at org.mortbay.jetty.handler.ContextHandler.doStart(ContextHandler.java:500)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerCollection.doStart(HandlerCollection.java:147)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerWrapper.doStart(HandlerWrapper.java:117)
at org.mortbay.jetty.Server.doStart(Server.java:217)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.jumpmind.symmetric.SymmetricWebServer.start(SymmetricWebServer.java:68)
at org.jumpmind.symmetric.SymmetricLauncher.main(SymmetricLauncher.java:171)
2008-07-20 19:09:21,658 WARN [org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect] [main] Trigger does not exist
2008-07-20 19:09:21,660 INFO [org.jumpmind.symmetric.db.AbstractDbDialect] [main] Creating INSERT trigger for oteros.cliente
2008-07-20 19:09:21,680 ERROR [org.jumpmind.symmetric.db.AbstractDbDialect] [main] Failed to create post trigger:
create trigger ON_I_TO_CLNT after insert on oteros.cliente for each row execute procedure oteros.fON_I_TO_CLNT();2008-07-20 19:09:21,680 ERROR [org.jumpmind.symmetric.service.impl.BootstrapService] [main] Failed to synchronize trigger for cliente
org.springframework.jdbc.BadSqlGrammarException: ConnectionCallback; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
Caused by:
org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:283)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
at org.jumpmind.symmetric.db.AbstractDbDialect$3.doInConnection(AbstractDbDialect.java:553)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:289)
at org.jumpmind.symmetric.db.AbstractDbDialect.initTrigger(AbstractDbDialect.java:533)
at org.jumpmind.symmetric.service.impl.BootstrapService.rebuildTriggerIfNecessary(BootstrapService.java:326)
at org.jumpmind.symmetric.service.impl.BootstrapService.updateOrCreateTriggers(BootstrapService.java:200)
at org.jumpmind.symmetric.service.impl.BootstrapService.syncTriggers(BootstrapService.java:133)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
at $Proxy14.syncTriggers(Unknown Source)
at org.jumpmind.symmetric.SymmetricEngine.start(SymmetricEngine.java:214)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.createConfigureAndStartEngine(SymmetricEngineContextLoaderListener.java:72)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.contextInitialized(SymmetricEngineContextLoaderListener.java:59)
at org.mortbay.jetty.handler.ContextHandler.startContext(ContextHandler.java:530)
at org.mortbay.jetty.servlet.Context.startContext(Context.java:135)
at org.mortbay.jetty.handler.ContextHandler.doStart(ContextHandler.java:500)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerCollection.doStart(HandlerCollection.java:147)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerWrapper.doStart(HandlerWrapper.java:117)
at org.mortbay.jetty.Server.doStart(Server.java:217)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.jumpmind.symmetric.SymmetricWebServer.start(SymmetricWebServer.java:68)
at org.jumpmind.symmetric.SymmetricLauncher.main(SymmetricLauncher.java:171)
2008-07-20 19:09:21,681 INFO [org.jumpmind.symmetric.service.impl.BootstrapService] [main] Done synchronizing triggers.
although the problem seems to be in '2008-07-20 19:09:21,407 WARN [org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect] [main] Trigger does not exist' in postgresql this can bee avoided using "CREATE OR REPLACE FUNCTION" instead of plain create.
Regards,
Miguel Angel.
although i have deleted manually the triggers functions still get an error in one of the new triggers created, it seems to be creating two times but only for "on_insert":
2008-07-20 19:28:31,324 INFO [org.jumpmind.symmetric.service.impl.BootstrapService] [main] Synchronizing triggers.
2008-07-20 19:28:32,738 INFO [org.jumpmind.symmetric.db.AbstractDbDialect] [main] Creating INSERT trigger for oteros.cliente
2008-07-20 19:28:32,744 INFO [org.jumpmind.symmetric.db.AbstractDbDialect] [main] Creating UPDATE trigger for oteros.cliente
2008-07-20 19:28:32,748 INFO [org.jumpmind.symmetric.db.AbstractDbDialect] [main] Creating DELETE trigger for oteros.cliente
2008-07-20 19:28:32,916 WARN [org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect] [main] Trigger does not exist
2008-07-20 19:28:32,918 INFO [org.jumpmind.symmetric.db.AbstractDbDialect] [main] Creating INSERT trigger for oteros.cliente
2008-07-20 19:28:32,920 ERROR [org.jumpmind.symmetric.db.AbstractDbDialect] [main] Failed to create post trigger:
create trigger ON_I_TO_CLNT after insert on oteros.cliente for each row execute procedure oteros.fON_I_TO_CLNT();2008-07-20 19:28:32,921 ERROR [org.jumpmind.symmetric.service.impl.BootstrapService] [main] Failed to synchronize trigger for cliente
org.springframework.jdbc.BadSqlGrammarException: ConnectionCallback; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
Caused by:
org.postgresql.util.PSQLException: ERROR: trigger "on_i_to_clnt" for relation "cliente" already exists
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:283)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
at org.jumpmind.symmetric.db.AbstractDbDialect$3.doInConnection(AbstractDbDialect.java:553)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:289)
at org.jumpmind.symmetric.db.AbstractDbDialect.initTrigger(AbstractDbDialect.java:533)
at org.jumpmind.symmetric.service.impl.BootstrapService.rebuildTriggerIfNecessary(BootstrapService.java:326)
at org.jumpmind.symmetric.service.impl.BootstrapService.updateOrCreateTriggers(BootstrapService.java:200)
at org.jumpmind.symmetric.service.impl.BootstrapService.syncTriggers(BootstrapService.java:133)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
at $Proxy14.syncTriggers(Unknown Source)
at org.jumpmind.symmetric.SymmetricEngine.start(SymmetricEngine.java:214)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.createConfigureAndStartEngine(SymmetricEngineContextLoaderListener.java:72)
at org.jumpmind.symmetric.SymmetricEngineContextLoaderListener.contextInitialized(SymmetricEngineContextLoaderListener.java:59)
at org.mortbay.jetty.handler.ContextHandler.startContext(ContextHandler.java:530)
at org.mortbay.jetty.servlet.Context.startContext(Context.java:135)
at org.mortbay.jetty.handler.ContextHandler.doStart(ContextHandler.java:500)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerCollection.doStart(HandlerCollection.java:147)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.mortbay.jetty.handler.HandlerWrapper.doStart(HandlerWrapper.java:117)
at org.mortbay.jetty.Server.doStart(Server.java:217)
at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
at org.jumpmind.symmetric.SymmetricWebServer.start(SymmetricWebServer.java:68)
at org.jumpmind.symmetric.SymmetricLauncher.main(SymmetricLauncher.java:171)
2008-07-20 19:28:32,923 INFO [org.jumpmind.symmetric.service.impl.BootstrapService] [main] Done synchronizing triggers.
the sym_trigger entries are:
37;"oteros";"cliente";"oteros";"";"t109";"corp";"central_t109_bidir";1;1;1;0;"";"";"";"";"";"";"";"";"";"";100;"2008-07-20 15:37:36.644503";"";"skuda";"2008-07-20 19:28:23.981506"
135;"oteros";"cliente";"oteros";"";"corp";"t106";"central_t106_bidir";1;1;1;1;"";"";"";"";"";"";"";"";"";"";100;"2008-07-20 15:37:42.146046";"";"skuda";"2008-07-20 19:28:23.981506"
136;"oteros";"cliente";"oteros";"";"t106";"corp";"central_t106_bidir";1;1;1;0;"";"";"";"";"";"";"";"";"";"";100;"2008-07-20 15:37:42.146744";"";"skuda";"2008-07-20 19:28:23.981506"
36;"oteros";"cliente";"oteros";"";"corp";"t109";"central_t109_bidir";1;1;1;1;"";"";"";"";"";"";"";"";"";"";100;"2008-07-20 15:37:36.643832";"";"skuda";"2008-07-20 19:28:23.981506"
Regards,
Miguel Angel.
it seems that only can trigger to a destination node_group for every table/action, no? i have detected that the triggers have:
insert into public.sym_data_event (node_id, data_id, channel_id, transaction_id) (select node_id, currval('public.sym_data_data_id_seq'), 'central_t109_bidir', null from sym_node c where c.node_group_id='t109' and c.sync_enabled=1 );
changing c.node_group_id between t109 and t106 randomly (in the creation order), the problem here it is the way i have organized the triggers, i have not attached all the stores nodes to a same node_group because every store uses a schema name to some of the tables, tickets, offers and anymore because i dont want this tables to growth too much in size to have fast access and it is very easy with ticket lines for example in this sport stores, anyway i can reorganize to make use of only a trigger i think, i will try and post here my results, but i think should be possible to define different node_group destinations for any data event capture.
I can make some comments about the sym_trigger entries I saw you posted. Forgive me if I've misunderstood something.
The "t109" and "t106" node groups look like they are doing the same synchronization with "corp". Instead, you can setup one node group called "store", and then assign nodes to it. You could have node entries of "t019" and "t106" with a node group of "store".
Sometimes you want the "store" node to have a subset of data from a table on "corp". The sym_trigger entry lets you specify the node_select to route changes to nodes and initial_load_select to send the entire subset to a node. For example, a store_item table might have all the items a store can sell. The table has a column called store_id so that different items can be sold at different stores. To sync rows of the table to the right store, you specify a node_select of "and c.external_id = $(curTriggerValue).store_id" and an initial_load_select of "store_id = $(externalId)". This keeps the table small at the store, with only the data they need, but leaves it in one table at corp to consolidate maintenance and queries.
The channels are also meant to be setup once to categorize your dependent data. You want to think about when there is an error synchronizing a table, are there other tables that you want to continue synchronizing? For example, if an error occurs on the store_item table, it will stop synchronizing until you fix it. But, you may want to have the sale_transaction table continue to synchronize. To accomplish this, you create a channel called "item" and "transaction" and you assign them to the sym_trigger entries for those tables. A synchronization error only affects the channel at that node, not the entire group of nodes.
for reference to anyone using postgresql since the automatic methods of delete triggers (activate inactive_time or set to 0 sync_on_[insert, update, delete]) are not working in this symmetricDs version with postgresql i have using this small script to make it automatically. You should stop symmetricDs before.
create a plain text file with a host for line:
192.168.1.4
192.168.1.215
192.168.1.154
use this in bash:
for host in
cat filename_with_hosts; do echo $host; psql -U username -d database -h $host -t -o temp.sql -c "SELECT 'DROP FUNCTION ' || nam.nspname || '.' || pro.proname || '() cascade;' from pg_proc as pro join pg_namespace as nam on nam.oid=pro.pronamespace and nam.nspowner=16384 and pro.proname LIKE 'fon_%'"; psql -U username -d databases -h $host -q -f temp.sql; donei am using here the catalogs to obtain the data i need to delete all at one time, to check the schemas i want i am using my oid owner (nam.nspowner=16384) but i could use (nam.nspname in ('schema1', 'schema2')) for example, the symmetricDs functions seems to be called automatically (almost on my hosts) fon_[d,i,u]_% so i use the like to filter other user defined functions in the schemas selected.
this generate and later use a file (temp.sql) with lines like this:
DROP FUNCTION datos_antiguos.fon_i_to_fclnt_mrc() cascade;
it is using cascade so it delete the trigger in the table too, after delete the functions make your sym_trigger changes, update the last_updated_time column to all sym_trigger entries in root node (update sym_trigger set last_updated_time=now()) and in node_security activate registration to client nodes (update sym_node_security set registration_enabled=1). This only deleted the triggers on your tables, if you want to recreate all symmetricDs installation instead of only change and recreate the triggers you can use the same trick to delete too the tables and sequences of symmetric in public schema (all use a name that begin with 'sym_').
I hope this can help someone.
Regards,
Miguel Angel.
okay i have configured the two stores in a only group_node and now the bidirectional works, only that when recreate the triggers every store tries to create the trigger of the schema they dont have and i get this erros in log:
2008-07-20 22:10:28,143 ERROR [org.jumpmind.symmetric.service.impl.BootstrapService] [main] The configured table does not exist in the datasource that is configured: caja <--- for every table schema
The other way i think would works it is change manually the triggers with bidirectional replication to add an insert into sym_data_event to every store group_id (using a group_id to every store like before) but i prefer not have to modify manually the triggers, any better idea?
Okay, I didn't realize it was sending the externalId for the nodeId. I went ahead and fixed that, which should allow it to look up the trigger and put the schema name on the front of the tables it is loading.
The reason the triggers cannot be updated is because they are created in the default schema by the template (postgresql.xml), but the PostgresqlDbDialect is looking for their existence in the source schema you specify. I changed it to look in the default schema, which should fix it.
Here is my latest snapshot for 1.3.4 with those fixes. There are checked into the branch and trunk also.
http://symmetricds.org/repo/org/jumpmind/symmetric/symmetric-ds/1.3.4-SNAPSHOT/symmetric-ds-1.3.4-20080725.180757-2.jar
Thanks,
Eric
in the machines where i am using symmetric-ds the triggers are created in source schema, symmetric-ds creates in public his tables and sequences but use the source schema to create the triggers functions, it should be other problem i think, here you can see the first line of one of the triggers functions.
CREATE OR REPLACE FUNCTION t106.fon_d_to_mdl_frt()
and here from the table the trigger.
CREATE TRIGGER on_d_to_mdl_frt
AFTER DELETE
ON t106.modelo_oferta
FOR EACH ROW
EXECUTE PROCEDURE t106.fon_d_to_mdl_frt();
about the problem of how i had organized my triggers do symmetric-ds work this way? http://sourceforge.net/forum/message.php?msg_id=5109318
is this a good solution or maybe will have problems in future when the numbers of node grown? http://sourceforge.net/forum/message.php?msg_id=5109441
Regards,
Miguel Angel.
the reasons because i want to send a single event capture to different stores by different channels was primarily two:
-only one channel for every group of table replications for the 30 stores maybe can cause me more replication errors and stopped replication on stores not affected for this error.
-separation of any tables that i have designed to stay in different schemas (t109, t106... 30more) because of internal organization and performance, even in center database this tables can grown out of control, i ever can do an union if i want to show a subset or all the stores at the same time, but here my bosses seems to prefer study trends for stores, and make offers based in this unique trends in every store.
Anyway my app it is still in development so i can test to create shared tables in the main schema and replicate to the stores only the relevant entries like you have explained me, i would have to redesign many things but i could try (when i have time hehehe), about how i have configured for now, i have created an node group "store" and make insert in sym_trigger in root_node to replicate t106.caja and t109.caja from group store this way works with my tables organization but when client node t109 register and create triggers i get:
2008-07-20 22:10:28,143 ERROR [org.jumpmind.symmetric.service.impl.BootstrapService] [main] The configured table does not exist in the datasource that is configured: caja
obviously trying to create the trigger for t106, and when i register in t106 node i get the same message trying to create the trigger of the t109, well i have this message for every replicated table and now that i have only two stores one at a time, but when i get 30 stores i will get this same error 29 times in every store, not seems to be a good solution, no?
Do i have any way to make a data_event to be replicated to more than one node_group other than manually changing the trigger function and inserting in sym_data_event all the group nodes i want to replicate the information captured? when i read the documentation i thought the node groups can be inherited too (my fault reading i know) so i tried the first time to create a group with all the common tables and later one for every schema of every store that inherited common and create his special triggers in this inherited node_group (this way i can have a only node_group replicating the common tables and the store schema only would need to be replicated to his store so i would not need to make a data event for many node_groups).
I have had problems with replication detecting the schema name, i have found the reason seeing the source code flow and inserting any debugging prints:
in csvloader.java hasNext() method tries to get the source node with nodeservice.findNode, that launch this query:
select node_id, node_group_id, external_id, sync_enabled, sync_url, schema_version, database_type, database_version, symmetric_version from ${sync.table.prefix}_node where node_id = ?
but tokens[1] from the buffer has the value of external_id so if it is different from node_id (like in my case) you ever get a null value in sourceNode variable, later in setTable method in the same class it uses this variable to see if try to resolve schema name.
if (sourceNode != null) {
Trigger trigger = configurationService.getTriggerFor(tableName,
sourceNode.getNodeGroupId());
if (trigger != null && trigger.getTargetSchemaName() != null) {
fullTableName = trigger.getTargetSchemaName() + "." + tableName;
}
}
so it never tries and assign the default schemaname public doing the replication to fail.
Regards,
Miguel Angel.