View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0002219SymmetricDSBugpublic2015-02-26 23:582015-03-06 15:16
Reporterspadalkar Assigned Tochenson  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.7.8 
Target Version3.7.9Fixed in Version3.7.9 
Summary0002219: When Primary key is binary; SymmetricDS fails capture changes.
DescriptionWhen table primary key binary; any changes to that tables are not captured. It fails with following error:

There are no keys defined for dummy.test_pet. Cannot build an update statement. The only keys defined are binary and they have been removed.


Symmetric: Failed row data was: <---MISSING VALUE--->,"DOG",,"1","0","526","0"
Symmetric: Failed old data was: <---MISSING VALUE--->,"DOG",,"1","0","14","0"
Steps To Reproducedesc test_pet;
+------------------+----------------------+------+-----+------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+------------------+-------+
| test_pet_uuid | binary(16) | NO | PRI | | |
| test_pet_name | varchar(255) | NO | | | |
| pet_parent_uuid | binary(16) | NO | | | |
| pet_num | smallint(5) unsigned | NO | MUL | 0 | |
| pet_limit | int(10) unsigned | YES | | 0 | |
| pet_events | int(10) unsigned | NO | | 0 | |
| deleted | tinyint(1) | NO | | 0 | |
+------------------+----------------------+------+-----+------------------+-------+

Additional InformationSymmetric: 2015-02-26 18:44:39,940 INFO [csm-001] [PushService] [csm-001-push-1] Push data sent to ims:000:000
Symmetric: 2015-02-26 18:44:39,958 INFO [ims-000] [DefaultDatabaseWriter] [qtp411126517-20] Failed to process a update event in batch 134.
Symmetric: Failed row data was: ,"DOG",,"1","0","526","0"
Symmetric: Failed old data was: ,"DOG",,"1","0","14","0"
Symmetric:
Symmetric: 2015-02-26 18:44:39,958 ERROR [ims-000] [DataLoaderService] [qtp411126517-20] Failed to load batch 001-134 because: There are no keys defined for dummy.test_pet. Cannot build an update statement. The only keys defined are binary and they have been removed.
Symmetric: java.lang.IllegalStateException: There are no keys defined for sfsnort.domain_control_info. Cannot build an update statement. The only keys defined are binary and they have been removed.
Symmetric: at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.update(DefaultDatabaseWriter.java:420)
Symmetric: at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:152)
Symmetric: at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
Symmetric: at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:65)
Symmetric: at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
Symmetric: at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:204)
Symmetric: at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:200)
Symmetric: at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:170)
Symmetric: at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:116)
Symmetric: at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener.end(DataLoaderService.java:809)
Symmetric: at org.jumpmind.symmetric.io.data.writer.StagingDataWriter.notifyEndBatch(StagingDataWriter.java:75)
Symmetric: at org.jumpmind.symmetric.io.data.writer.AbstractProtocolDataWriter.end(AbstractProtocolDataWriter.java:220)
Symmetric: at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:130)
Symmetric: at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport(DataLoaderService.java:430)
Symmetric: at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPush(DataLoaderService.java:344)
Symmetric: at org.jumpmind.symmetric.web.PushUriHandler.push(PushUriHandler.java:79)
Symmetric: at org.jumpmind.symmetric.web.PushUriHandler.handle(PushUriHandler.java:66)
Symmetric: at org.jumpmind.symmetric.web.SymmetricServlet.service(SymmetricServlet.java:103)
Symmetric: at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
Symmetric: at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:565)
Symmetric: at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:479)
Symmetric: at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)
Symmetric: at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:524)
Symmetric: at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:227)
Symmetric: at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1031)
Symmetric: at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:406)
Symmetric: at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:186)
Symmetric: at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:965)
Symmetric: at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
Symmetric: at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:111)
Symmetric: at org.eclipse.jetty.server.Server.handle(Server.java:348)
Symmetric: at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:452)
Symmetric: at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:894)
Symmetric: at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:948)
Symmetric: at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:851)
Symmetric: at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:235)
Symmetric: at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:77)
Symmetric: at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:606)
Symmetric: at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:46)
Symmetric: at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:603)
Symmetric: at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:538)
Symmetric: at java.lang.Thread.run(Thread.java:745)
TagsNo tags attached.

Activities

spadalkar

2015-02-27 00:00

reporter   ~0000674

Server is MYSQL & Client is Sybase SQL Anywhere 16

chenson

2015-03-01 21:11

administrator   ~0000675

What is the table definition for the target table?

spadalkar

2015-03-02 20:55

reporter   ~0000676

sybase > select c.column_name, c.base_type_str, c.nulls, c.`default`, c.width, c.column_type from systabcol c, systab t where c.table_id = t.table_id and t.table_name='test_pet';
+------------------+-------------------+-------+----------------------------------------------------+-------+-------------+
| column_name | base_type_str | nulls | default ... | width | column_type |
+------------------+-------------------+-------+----------------------------------------------------+-------+-------------+
| test_pet_uuid | binary(16) | N | '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\ ... | 16 | R |
| test_pet_name | varchar(255) | N | '' ... | 255 | R |
| pet_parent_uuid | binary(16) | N | '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\ ... | 16 | R |
| pet_num | unsigned smallint | N | 0 ... | 2 | R |
| pet_limit | unsigned int | Y | 0 ... | 4 | R |
| pet_events | unsigned int | N | 0 ... | 4 | R |
| deleted | tinyint | N | 0 ... | 1 | R |
+------------------+-------------------+-------+----------------------------------------------------+-------+-------------+

chenson

2015-03-04 13:59

administrator   ~0000677

Do you have use_stream_lobs turned on?

spadalkar

2015-03-04 18:16

reporter   ~0000678

Yes use_stream_lobs is turned on.

insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time,use_capture_lobs,use_stream_lobs)
values('test_pet_t','test_pet','sybase_to_mysql',current_timestamp,current_timestamp,1,1);

chenson

2015-03-05 00:25

administrator   ~0000679

Do you need use_stream_lobs turned on? I think it causes the issue.

spadalkar

2015-03-05 21:40

reporter   ~0000680

As per suggestion for Bug ID : 0002184 we started using use_stream_lobs = 1. Can you suggest any alternative configuration if possible? Thanks.

chenson

2015-03-05 21:47

administrator   ~0000681

Right now SymmetricDS considers binary and varbinary types lobs. I think we might be mistaken in calling them lobs. We haven't run across too many scenarios where binary types were being used in keys, so we never really really considered that they would be better off not considered lobs (meaning the use_stream_lobs would effect them).

If you are luck enough to not have a mix of binary pks and large objects in the same table you can turn use_stream_lobs off for those tables.

I'm currently testing a fix for this where binary and varbinary jdbc types are NOT considered for use_stream_lobs which would fix the issue altogether.

Related Changesets

SymmetricDS: master 7bd24011

2015-03-05 16:04:17

chenson

Details Diff 		0002219: When Primary key is binary; SymmetricDS fails capture changes. Affected Issues
0002219
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDatabasePlatform.java Diff File

SymmetricDS: master ebb01610

2015-03-05 20:28:46

chenson

Details Diff 		0002219: When Primary key is binary; SymmetricDS fails capture changes. Affected Issues
0002219
mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriter.java Diff File

Issue History

Date Modified Username Field Change
2015-02-26 23:58 spadalkar New Issue
2015-02-27 00:00 spadalkar Note Added: 0000674
2015-03-01 21:11 chenson Note Added: 0000675
2015-03-02 20:55 spadalkar Note Added: 0000676
2015-03-04 13:59 chenson Note Added: 0000677
2015-03-04 18:16 spadalkar Note Added: 0000678
2015-03-05 00:25 chenson Note Added: 0000679
2015-03-05 21:40 spadalkar Note Added: 0000680
2015-03-05 21:47 chenson Note Added: 0000681
2015-03-05 22:00 chenson Changeset attached => SymmetricDS trunk r9400
2015-03-06 02:00 chenson Changeset attached => SymmetricDS trunk r9402
2015-03-06 15:14 chenson Status new => resolved
2015-03-06 15:14 chenson Fixed in Version => 3.7.9
2015-03-06 15:14 chenson Resolution open => fixed
2015-03-06 15:14 chenson Assigned To => chenson
2015-03-06 15:14 chenson Target Version => 3.7.9
2015-03-06 15:16 chenson Status resolved => closed
2015-07-31 01:49 chenson Changeset attached => SymmetricDS master ebb01610
2015-07-31 01:49 chenson Changeset attached => SymmetricDS master 7bd24011