Transform type identity doesn't work

[Shen Flik]

Hi folks,

I have a table [customer_feedback] need to be sync between central database and region database. The feedback messages will be mostly added and updated in region database and in very rare case be updated in central database. However, due to the database design, the message record identity is not a UUID but an auto increment column. I have to insert every new message or updated message into central database. So I used the transform type identity as following.

--
-- Transform Tables from Node Group [REGION] to [CENTRAL]
--
INSERT INTO sym_transform_table (transform_id,transform_point,source_node_group_id,target_node_group_id,source_catalog_name,source_schema_name,source_table_name,target_catalog_name,target_schema_name,target_table_name,update_first,DELETE_action,column_policy,last_update_time,create_time)
VALUES('region_customer_feedback','LOAD','REGION','CENTRAL','localDB',null,'customer_feedback','centralDB',null,'customer_feedback', 0, 'NONE', 'SPECIFIED', current_timestamp, current_timestamp);

--
-- Transform Columns from Node Group [REGION] to [CENTRAL]
--
INSERT INTO sym_transform_column (transform_id,include_on,target_column_name,source_column_name,pk,transform_type,transform_expression,last_update_time,create_time)
VALUES('region_customer_feedback','*','feedback_id',null,1,'identity',null,current_timestamp,current_timestamp);
INSERT INTO sym_transform_column (transform_id,include_on,target_column_name,source_column_name,pk,transform_type,transform_expression,last_update_time,create_time)
VALUES('region_customer_feedback','*','feedback_content','feedback_content',0,'copy',null,current_timestamp,current_timestamp);
... (several other attributes)

The original record got updated always. In my test environment, there is only one region and it doesn't cause any conflict. But I beleive it is a problem in multiple region environment.
Is it right that the identity type transformed column should not be pk anymore? Then which column can be marked as pk of the transform to satisfying the at lease one pk column requirement?

My system is built on SymmetricDS 3.6.11 + MySQL 5.6.

Thanks in advance.
Flik

[Shen Flik]

Hi all,

After change the target schema name as same as source schema, the DataLoaderSerivce reports an issue. It is better than nothing happened.

Failed sql was: update centralDB.customer_feedback set cust_mobile = ? where cs_staff_id = ? and cust_id = ? and create_stmp = ? and cust_mobile = ? and feedback_content = ?
Failed sql parameters: [13487654323]
Failed sql parameters types: [12, 4, 4, 93, 12, -1]
Failed row data was: "8","8","2014-05-05 15:41:15","13487654323","bababa..."
Failed old data was: "8","8","2014-05-05 15:41:15","13487654324","bababa..."

2014-12-02 15:11:46,384 ERROR [MASTER-master] [DataLoaderService] [qtp536357250-19] Failed to load batch 310104-11 because: No value specified for parameter 2
org.jumpmind.db.sql.SqlException: No value specified for parameter 2
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.addRow(JdbcSqlTransaction.java:410)
at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:807)
at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.update(DefaultDatabaseWriter.java:456)
at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:151)
at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:131)
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.TransformWriter.write(TransformWriter.java:223)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:199)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:169)
at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:115)
at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener.end(DataLoaderService.java:802)
at org.jumpmind.symmetric.io.data.writer.StagingDataWriter.notifyEndBatch(StagingDataWriter.java:75)
at org.jumpmind.symmetric.io.data.writer.AbstractProtocolDataWriter.end(AbstractProtocolDataWriter.java:220)
at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:129)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport(DataLoaderService.java:431)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPush(DataLoaderService.java:345)
at org.jumpmind.symmetric.web.PushUriHandler.push(PushUriHandler.java:79)
at org.jumpmind.symmetric.web.PushUriHandler.handle(PushUriHandler.java:66)
at org.jumpmind.symmetric.web.SymmetricServlet.service(SymmetricServlet.java:103)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:565)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:479)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:524)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:227)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1031)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:406)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:186)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:965)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:111)
at org.eclipse.jetty.server.Server.handle(Server.java:348)
at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:452)
at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:894)
at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:948)
at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:851)
at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:235)
at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:77)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:606)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:46)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:603)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:538)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: No value specified for parameter 2
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2487)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2462)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2386)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1273)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:406)
... 43 more

How to set the pk for identity type transform?

Regards,
Flik

[Shen Flik]

Hi All,

I found the column of transform type identity could not be marked as primary key and it will lead all rest columns treated as key columns.
After changing the configuration as following by marking custoemr service staff ID, customer ID and create timestamp, other than identity column as key columns, as well as keeping source and target schema names same, identity type transform works for me.

INSERT INTO sym_transform_column (transform_id,include_on,target_column_name,source_column_name,pk,transform_type,transform_expression,last_update_time,create_time)
VALUES('region_customer_feedback','*','feedback_id',null,0,'identity',null,current_timestamp,current_timestamp);
INSERT INTO sym_transform_column (transform_id,include_on,target_column_name,source_column_name,pk,transform_type,transform_expression,last_update_time,create_time)
VALUES('region_customer_feedback','*','feedback_content','feedback_content',0,'copy',null,current_timestamp,current_timestamp);
INSERT INTO sym_transform_column (transform_id,include_on,target_column_name,source_column_name,pk,transform_type,transform_expression,last_update_time,create_time)
VALUES('region_customer_feedback','*','cs_staff_id','cs_staff_id',1,'copy',null,current_timestamp,current_timestamp);
INSERT INTO sym_transform_column (transform_id,include_on,target_column_name,source_column_name,pk,transform_type,transform_expression,last_update_time,create_time)
VALUES('region_customer_feedback','*','cust_id','cust_id',1,'copy',null,current_timestamp,current_timestamp);
INSERT INTO sym_transform_column (transform_id,include_on,target_column_name,source_column_name,pk,transform_type,transform_expression,last_update_time,create_time)
VALUES('region_customer_feedback','*','cust_mobile','cust_mobile',0,'copy',null,current_timestamp,current_timestamp);
INSERT INTO sym_transform_column (transform_id,include_on,target_column_name,source_column_name,pk,transform_type,transform_expression,last_update_time,create_time)
VALUES('region_customer_feedback','*','create_stmp',null,1,'variable','system_timestamp',current_timestamp,current_timestamp);

Best regards,
Flik