View Issue Details

IDProjectCategoryView StatusLast Update
0003389SymmetricDSImprovementpublic2021-07-13 12:41
Reportersnpe Assigned To 
Prioritylow 
Status newResolutionopen 
Product Version3.9.2 
Summary0003389: Dbcompare ordering by PK with timestamp or varchar with utf8
DescriptionWe use symmetricds to replicate between MySQL and SQL Anywhere.

dbcompare returns a lot different rows.
I have noticed two issues:

1) when create comparison sql, symmetricds uses the order by as
ORDER BY <pk_column>
if PK column is text (varchar) that contains utf-8 characters, MySQL and SQL Anywhere will return different ordering
Solution: use the CONVERT function on MySQL and the CSCONVERT function on SQL Anywhere

2) SQL Anywhere saves timestamp values with milliseconds and MySQL (by default) without milliseconds
Solution: add options to round timestamp values similar as numbers
Steps To ReproduceWe use commercial databases and not sure that can provide reproducible case.
Additional InformationI have fixed both issue with patched symmetricds 3.9.x and I'm willing to create a patch.
Tagsdbcompare

Activities

kstojanovska

2018-01-30 18:35

developer   ~0001108

Hi,

Could you provide the patch you used or do a pull request so we can review your approach in the code?

kstojanovska

2018-01-30 18:46

developer   ~0001109

Which version of Sybase are you running? Could you provide us with sample create table statements and some sample data for both MySql and SqlAnywhere so that we can try to reproduce?

snpe

2018-01-30 19:54

reporter   ~0001110

I'm using SQL Anywhere 11 and MySQL 5.7.21. I will provide a pull request and test case soon.

kstojanovska

2018-02-06 21:32

developer   ~0001121

Last edited: 2018-02-06 21:32

View 2 revisions

We have added an option to specify a date-time format which should fix problem 2 here.

Here is a link to the issue: https://www.symmetricds.org/issues/view.php?id=3407

Issue History

Date Modified Username Field Change
2018-01-21 21:27 snpe New Issue
2018-01-22 15:21 chenson Status new => confirmed
2018-01-25 13:49 kstojanovska Assigned To => kstojanovska
2018-01-25 13:49 kstojanovska Status confirmed => assigned
2018-01-30 18:35 kstojanovska Note Added: 0001108
2018-01-30 18:46 kstojanovska Note Added: 0001109
2018-01-30 19:54 snpe Note Added: 0001110
2018-02-06 21:32 kstojanovska Note Added: 0001121
2018-02-06 21:32 kstojanovska Note Edited: 0001121 View Revisions
2018-10-10 18:11 hanes Target Version => 3.10.0
2018-10-29 17:00 hanes Priority normal => low
2018-12-05 19:55 hanes Assigned To kstojanovska =>
2018-12-05 19:56 hanes Summary dbcompare, timestamp and utf8 => Timestamp and utf8 collation issues with dbcompare
2019-03-14 12:39 admin Target Version 3.10.0 => 3.10.1
2019-04-23 13:46 elong Target Version 3.10.1 => 3.10.2
2019-04-24 17:25 elong Tag Attached: dbcompare
2019-05-08 12:51 admin Target Version 3.10.2 => 3.10.3
2019-05-14 17:01 elong Summary Timestamp and utf8 collation issues with dbcompare => Dbcompare ordering by PK with timestamp or varchar with utf8
2019-06-24 17:45 elong Target Version 3.10.3 => 3.10.4
2019-08-09 19:41 elong Target Version 3.10.4 => 3.10.5
2019-10-31 14:59 admin Target Version 3.10.5 => 3.10.6
2019-11-14 19:47 elong Target Version 3.10.6 => 3.10.7
2019-12-09 15:25 admin Target Version 3.10.7 => 3.10.8
2020-01-02 14:40 admin Target Version 3.10.8 => 3.10.9
2020-01-22 19:03 elong Target Version 3.10.9 => 3.10.10
2020-02-14 13:46 elong Target Version 3.10.10 => 3.10.11
2020-03-16 20:14 admin Target Version 3.10.11 => 3.10.12
2020-05-26 12:28 elong Target Version 3.10.12 => 3.11.10
2020-06-24 20:14 elong Target Version 3.11.10 => 3.12.2
2020-07-20 13:06 elong Target Version 3.12.2 => 3.12.3
2020-08-31 19:47 elong Target Version 3.12.3 => 3.12.4
2020-09-23 15:32 admin Target Version 3.12.4 => 3.12.5
2020-11-09 13:45 admin Target Version 3.12.5 => 3.12.6
2021-01-08 20:42 admin Target Version 3.12.6 => 3.12.7
2021-02-25 13:03 admin Status assigned => new
2021-02-25 13:03 admin Target Version 3.12.7 => 3.12.8
2021-03-30 19:13 admin Target Version 3.12.8 => 3.12.9
2021-04-28 14:41 admin Target Version 3.12.9 => 3.12.10
2021-05-28 11:54 admin Target Version 3.12.10 => 3.12.11
2021-07-13 12:41 admin Target Version 3.12.11 =>