View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0003755 | SymmetricDS | Bug | public | 2018-10-12 16:35 | 2019-08-26 12:45 |
Reporter | Yuntian | Assigned To | |||
Priority | normal | ||||
Status | closed | Resolution | open | ||
Product Version | 3.9.14 | ||||
Summary | 0003755: Cannot update row when primary key data type is char/nchar(n) | ||||
Description | When the primary key is char/nchar the initial load is fine but any update on source node cannot be populated to destination node with following error: Detected conflict while executing UPDATE on SyncTest.Test. The primary key data was: {id=1 }. Failed to fallback. The original error message was: Duplicate entry '1' for key 'PRIMARY' When primary key in the set up script is changed to int, the update can complete successfully. I'm trying to stick to minimum configuration on my set up script so it is easy for me to see what's wrong though. Maybe there is a setting to enable support for char/nchar but that's not on by default unlike int which works out of box. Please see attachments for set up script and full log. | ||||
Steps To Reproduce | 1. Set up Debian 9 for SDS, MSSQL 2014 for src and MySQL 8 for dest 2. Run sds.sh on Debian to set up and check MySQL for initial load 3. Run "update Test set Value = 'Test2'" on MSSQL 4. Detected conflict while executing UPDATE | ||||
Tags | data sync, dialect: sql-server | ||||
|
sds.sh (3,331 bytes)
#!/bin/bash # 1. Prepare Environment apt update; apt upgrade -y apt install unzip default-jre -y wget https://netcologne.dl.sourceforge.net/project/symmetricds/symmetricds/symmetricds-3.9/symmetric-server-3.9.14.zip cd /opt/ # 2. Install and Set Up unzip ~/symmetric-server-3.9.14.zip mv symmetric-server-3.9.14/ symmetric-server/ cd symmetric-server/ echo "3.9.14" > version.txt SOURCE=MSSQL01 SOURCE_IP=192.168.0.1 SOURCE_DB_NAME=SyncTest SOURCE_USER=user SOURCE_PASSWORD=password DEST=MYSQL01 DEST_IP=192.168.0.2 DEST_DB_NAME=SyncTest DEST_USER=user DEST_PASSWORD=password echo "engine.name=$SOURCE" >> engines/$SOURCE.properties echo "group.id=src" >> engines/$SOURCE.properties echo "external.id=000" >> engines/$SOURCE.properties echo "sync.url=http://localhost:31415/sync/$SOURCE" >> engines/$SOURCE.properties echo "registration.url=" >> engines/$SOURCE.properties echo "db.driver=net.sourceforge.jtds.jdbc.Driver" >> engines/$SOURCE.properties echo "db.url=jdbc:jtds:sqlserver://$SOURCE_IP:1433/$SOURCE_DB_NAME;sendStringParametersAsUnicode=false;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880" >> engines/$SOURCE.properties echo "db.user=$SOURCE_USER" >> engines/$SOURCE.properties echo "db.password=$SOURCE_PASSWORD" >> engines/$SOURCE.properties echo "auto.reload=true" >> engines/$SOURCE.properties echo "initial.load.create.first=true" >> engines/$SOURCE.properties echo "engine.name=$DEST" >> engines/$DEST.properties echo "group.id=dest" >> engines/$DEST.properties echo "external.id=001" >> engines/$DEST.properties echo "sync.url=http://localhost:31415/sync/$DEST" >> engines/$DEST.properties echo "registration.url=http://localhost:31415/sync/$SOURCE" >> engines/$DEST.properties echo "db.driver=com.mysql.jdbc.Driver" >> engines/$DEST.properties echo "db.url=jdbc:mysql://$DEST_IP/$DEST_DB_NAME?useSSL=false&tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull" >> engines/$DEST.properties echo "db.user=$DEST_USER" >> engines/$DEST.properties echo "db.password=$DEST_PASSWORD" >> engines/$DEST.properties bin/sym_service install bin/sym_service start bin/symadmin open-registration --engine $SOURCE dest 001 bin/dbsql --engine $SOURCE <<- END create table Test( ID char(10) not null, Value char(10) null, primary key (ID) ); insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('src', 'dest', 'P'); insert into sym_router (router_id, source_node_group_id, target_node_group_id, create_time, last_update_time) values ('Default','src', 'dest', current_timestamp, current_timestamp); insert into sym_trigger (trigger_id, source_table_name, channel_id, create_time, last_update_time) values ('Test', 'Test', 'default', current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id, router_id, create_time, last_update_time) values ('Test', 'Default', current_timestamp, current_timestamp); insert into Test values ('1', 'Test'); exit END bin/sym_service stop bin/sym_service start watch -n 0 tail -n 50 logs/symmetric.log # 3. Uninstall bin/symadmin --engine $DEST uninstall bin/symadmin --engine $SOURCE uninstall bin/sym_service stop bin/sym_service uninstall cd .. rm -rf symmetric-server/ |
|
Error message in Description is not exactly how it is in the log file. The trailing space on primary key was removed. |
|
I am unable to recreate this issue after trying a few different approaches. Is there something additional about the database table that you didn't mention? Could you make use of varchar(10) as a work around perhaps? |
|
Auto closing all issues waiting for feedback after 4 months. We don't have enough information to take any action. |
Date Modified | Username | Field | Change |
---|---|---|---|
2018-10-12 16:35 | Yuntian | New Issue | |
2018-10-12 16:35 | Yuntian | File Added: sds.sh | |
2018-10-12 16:35 | Yuntian | File Added: symmetric.log | |
2018-10-12 16:37 | Yuntian | Note Added: 0001271 | |
2018-10-17 17:08 | hanes | Status | new => feedback |
2018-10-17 17:08 | hanes | Note Added: 0001281 | |
2019-04-25 14:32 | elong | Tag Attached: data sync | |
2019-04-25 14:32 | elong | Tag Attached: dialect: sql-server | |
2019-08-26 12:45 | admin | Note Added: 0001577 | |
2019-08-26 12:45 | admin | Status | feedback => closed |