View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0002094SymmetricDSBugpublic2014-12-09 08:372014-12-26 13:37
ReporterxBlackCat Assigned Tochenson  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.6.13 
Target Version3.6.17Fixed in Version3.6.17 
Summary0002094: Row deletion is not synced if table has conflict resolution by timestamp
DescriptionMySQL 5.6.21

We have star topology in sync: one central and several side databases.
Central node replicate changes from a side node to other side nodes.

There is a table with two-column primary key and with conflict resolution NEWER_WINS by datetime column in the table. Routes/triggers for the table are configured to sync DELETE events.

The table rows are synced fine while there are only INSERT and UPDATE events occur. But if we delete a row on the central or one of side nodes the row is removed only on the server where the query was executed.

DELETE event is present in the table SYM_DATE of the source node but sync is not performed.
TagsNo tags attached.

Activities

chenson

2014-12-09 13:25

administrator   ~0000629

Can you attach the part a log file where the delete is attempted on the target node with debug turned on?

xBlackCat

2014-12-09 13:42

reporter   ~0000630

I'will try to reproduce the issue on test environment in the nearest feature. The issue was appeared on the system we have not allowed to play with debug flags.

xBlackCat

2014-12-09 15:23

reporter   ~0000631

I think this is the needed log part in the logs.

There is a confusing record "Conflict detected:"
To reproduce the situation I've added a new record to the table and then (after record is synchronized) removes the record on the same node. This is a log part from receiving node. I think there should no conflicts: the record isn't changed by anyone else.

Second part: why in the removing SQL (delete from `database`.`badtable` where `modifydate` is NULL and `user_id` = ? and `perk_id` = ?) presented '`modifydate` is NULL' instead of (possible) '`modifydate` = ?' with substitution of the field value. So, as delete records has no 'new' record, the conflict resolution fields should take values from "old" data set instead of empty "new" data set in case DELETE action.


2014-12-09 16:51:05,719 DEBUG [EventServer] [MySqlDatabasePlatform] [eventserver-pull-2] Just read table:
Table [name=badtable; catalog=database; schema=null; type=TABLE] columns: Column [name=id; javaName=null; type=BINARY; typeCode=-2; size=16; required=true; primaryKey=false; autoIncrement=false; defaultValue=null; precisionRadix=10; scale=0] Column [name=user_id; javaName=null; type=BINARY; typeCode=-2; size=16; required=true; primaryKey=true; autoIncrement=false; defaultValue=null; precisionRadix=10; scale=0] Column [name=perk_id; javaName=null; type=INTEGER; typeCode=4; size=10; required=true; primaryKey=true; autoIncrement=false; defaultValue=null; precisionRadix=10; scale=0] Column [name=user_modifier_id; javaName=null; type=BINARY; typeCode=-2; size=16; required=false; primaryKey=false; autoIncrement=false; defaultValue=null; precisionRadix=10; scale=0] Column [name=modifydate; javaName=null; type=TIMESTAMP; typeCode=93; size=19; required=false; primaryKey=false; autoIncrement=false; defaultValue=null; precisionRadix=10; scale=0] Column [name=isdeleted; javaName=null; type=INTEGER; typeCode=4; size=10; required=false; primaryKey=false; autoIncrement=false; defaultValue=0; precisionRadix=10; scale=0] Column [name=createdate; javaName=null; type=TIMESTAMP; typeCode=93; size=19; required=false; primaryKey=false; autoIncrement=false; defaultValue=null; precisionRadix=10; scale=0] Column [name=venueserver_id; javaName=null; type=INTEGER; typeCode=4; size=10; required=false; primaryKey=false; autoIncrement=false; defaultValue=null; precisionRadix=10; scale=0]; indices: Unique index [badtable_id] columns: Index column [name=id; size=null]; foreign keys:
2014-12-09 16:51:05,719 DEBUG [EventServer] [ProtocolDataReader] [eventserver-pull-2] CSV parsed: old,3F07E39AC268104C81B237BCF01A2527,293,9AC710A772A7A147AD5A4955533314DB,3F0D3F3F3F2B113F3F1F3F363F3F0000,2014-12-09 14:46:57.159804,0,2014-12-09 14:46:57.159804,16
2014-12-09 16:51:05,719 DEBUG [EventServer] [ProtocolDataReader] [eventserver-pull-2] CSV parsed: delete,3F07E39AC268104C81B237BCF01A2527,293
2014-12-09 16:51:05,719 DEBUG [EventServer] [DefaultDatabaseWriter] [eventserver-pull-2] Preparing dml: delete from `database`.`badtable` where `modifydate` is NULL and `user_id` = ? and `perk_id` = ?
2014-12-09 16:51:05,719 DEBUG [EventServer] [JdbcSqlTransaction] [eventserver-pull-2] Preparing: delete from `database`.`badtable` where `modifydate` is NULL and `user_id` = ? and `perk_id` = ?
2014-12-09 16:51:05,719 DEBUG [EventServer] [DefaultDatabaseWriter] [eventserver-pull-2] Submitting data [[B@4a4258da, 293] with types [-2, 4]
2014-12-09 16:51:05,719 DEBUG [EventServer] [JdbcSqlTransaction] [eventserver-pull-2] Adding {{63,7,-29,-102,-62,104,16,76,-127,-78,55,-68,-16,26,37,39},293}
2014-12-09 16:51:05,719 DEBUG [EventServer] [AbstractDatabaseWriterConflictResolver] [eventserver-pull-2] Conflict detected: badtable_exists_conflict_back in batch 17987 at line 1 for table database.badtable
2014-12-09 16:51:05,719 DEBUG [EventServer] [AbstractDatabaseWriterConflictResolver] [eventserver-pull-2] Old data: "3F07E39AC268104C81B237BCF01A2527","293","9AC710A772A7A147AD5A4955533314DB","3F0D3F3F3F2B113F3F1F3F363F3F0000","2014-12-09 14:46:57.159804","0","2014-12-09 14:46:57.159804","16"

xBlackCat

2014-12-10 08:40

reporter   ~0000632

After debug the situation I've find out that tables with conflict resolving works weird for DELETE action (and the same is for UPDATE action as I can see):

The reason of the NULL value for DELETE statement in the following code:

org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.java (273 - 280)
                lookupDataMap = getLookupDataMap(data);

                boolean[] nullKeyValues = new boolean[lookupKeys.size()];
                for (int i = 0; i < lookupKeys.size(); i++) {
                    Column column = lookupKeys.get(i);
                    nullKeyValues[i] = !column.isRequired()
                            && lookupDataMap.get(column.getName()) == null;
                }

The method AbstractDatabaseWriter#getLookupDataMap(CsvData data) returns data set of primary keys only for DELETE action. So if a conflict resolving rule by table column is applied to the table, the data will be always NULL for the column in the result query. As result the data query falling into CONFLICT state and data is not deleted on other nodes.

This behavior is not depends on nullable status of conflict resolving column: if the column is nullable - result SQL is hardcoded with 'where modifydate = NULL' and if column is not allowed null values - query is valid ('where modifydate = ?') and to the placeholder is passed NULL.

Similar logic is used for UPDATE action, so I think the UPDATE action is affected as well. (confirmed for nullable conflict resolution column)

So, the OLD_DATA data set should be used for lookupDataMap to fill data in WHERE part of the result statement for UPDATE and DELETE statement.

xBlackCat

2014-12-11 14:34

reporter   ~0000637

Patched AbstractDatabaseWriter#getLookupDataMap(CsvData data) method:

    protected Map<String, String> getLookupDataMap(CsvData data) {
        Map<String, String> keyData = null;
        if (data.getDataEventType() == DataEventType.INSERT) {
            keyData = data.toColumnNameValuePairs(sourceTable.getColumnNames(), CsvData.ROW_DATA);
        } else if (data.getDataEventType() == DataEventType.DELETE || data.getDataEventType() == DataEventType.UPDATE) {
            if (data.contains(CsvData.OLD_DATA)) {
                keyData = data.toColumnNameValuePairs(sourceTable.getColumnNames(), CsvData.OLD_DATA);
            } else {
                keyData = data.toKeyColumnValuePairs(sourceTable);
            }
        } else {
            keyData = data.toKeyColumnValuePairs(sourceTable);
        }
        return keyData;
    }

For me the patch solved problems with removing rows and strange queries for UPDATE statement.

Related Changesets

SymmetricDS: master e40fa304

2014-12-11 15:16:52

chenson

Details Diff 		0002094: Row deletion is not synced if table has conflict resolution by timestamp Affected Issues
0002094
mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/AbstractDatabaseWriter.java Diff File
mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriter.java Diff File

Issue History

Date Modified Username Field Change
2014-12-09 08:37 xBlackCat New Issue
2014-12-09 13:25 chenson Note Added: 0000629
2014-12-09 13:42 xBlackCat Note Added: 0000630
2014-12-09 15:23 xBlackCat Note Added: 0000631
2014-12-10 08:40 xBlackCat Note Added: 0000632
2014-12-11 14:34 xBlackCat Note Added: 0000637
2014-12-11 14:43 chenson Target Version => 3.7.0
2014-12-11 19:51 chenson Fixed in Version => 3.6.17
2014-12-11 19:51 chenson Target Version 3.7.0 => 3.6.16
2014-12-11 20:17 chenson Target Version 3.6.16 => 3.6.17
2014-12-11 20:28 chenson Status new => resolved
2014-12-11 20:28 chenson Resolution open => fixed
2014-12-11 20:28 chenson Assigned To => chenson
2014-12-11 21:00 chenson Changeset attached => SymmetricDS 3.6 r9040
2014-12-11 21:00 chenson Changeset attached => SymmetricDS trunk r9041
2014-12-26 13:37 chenson Status resolved => closed
2015-07-31 01:49 chenson Changeset attached => SymmetricDS master e40fa304