View Issue Details

IDProjectCategoryView StatusLast Update
0003755SymmetricDSBugpublic2019-08-26 12:45
ReporterYuntian Assigned To 
Prioritynormal 
Status closedResolutionopen 
Product Version3.9.14 
Summary0003755: Cannot update row when primary key data type is char/nchar(n)
DescriptionWhen 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 Reproduce1. 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
Tagsdata sync, dialect: sql-server

Activities

Yuntian

2018-10-12 16:35

reporter  

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/
sds.sh (3,331 bytes)   
symmetric.log (223,383 bytes)

Yuntian

2018-10-12 16:37

reporter   ~0001271

Error message in Description is not exactly how it is in the log file. The trailing space on primary key was removed.

hanes

2018-10-17 17:08

developer   ~0001281

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?

admin

2019-08-26 12:45

administrator   ~0001577

Auto closing all issues waiting for feedback after 4 months. We don't have enough information to take any action.

Issue History

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