View Issue Details

IDProjectCategoryView StatusLast Update
0001261SymmetricDSBugpublic2019-05-31 12:07
ReporterseroteevAssigned To 
Prioritynormal 
Status resolvedResolutionfixed 
Product Version3.4.6 
Target VersionFixed in Version3.8.22 
Summary0001261: reload-table doesn't work with tables that have collate latin1_bin
DescriptionYou can't reload-table in mysql when you have specific data fields
The error is:
DataExtractorService - Failed to extract batch 001-25
org.jumpmind.symmetric.SymmetricException: The extracted row data did not have the expected (1) number of columns: [B@5228ea06
    at org.jumpmind.symmetric.service.impl.DataExtractorService$SelectFromTableSource$1.mapRow(DataExtractorService.java:1028)
Steps To ReproduceCREATE TABLE number_of_columns_bug (
test varchar(255) character set latin1 collate latin1_bin NOT NULL DEFAULT '',
textfield text NOT NULL,
PRIMARY KEY (test)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

insert into number_of_columns_bug values('testField1','SometextField');

../bin/symadmin reload-table number_of_columns_bug

You must have entry in triggers and trigger_router for 'number_of_columns_bug'
Additional InformationThe effect is the same on version 3.3.4 and 3.4.4
Tagsdata type, dialect: mysql/mariadb

Relationships

related to 0003076 closedgwilmer MySQL binary collation sequence causes incorrect decoding of text data from source 

Activities

elong

2014-02-12 15:03

developer   ~0000478

Reproduced on 3.5.16.

elong

2014-02-12 15:48

developer   ~0000479

The issue is MySQL's "collate latin1_bin". The column is defined as a VARCHAR so we expect to treat it like a text field. All the fields get converted to a text form and passed through CONCAT(). The problem is the field with "collate latin1_bin" is causing CONCAT() to return a binary result instead of a text one. The manual on CONCAT() says it will do that when it encounters binary data.

I tested queries in SquirrelSQL, and this query comes back as binary data:

select concat(test, textfield) from number_of_columns_bug;

However, I can force the results to come back correctly by casting the field, like this:

select concat(cast(test as char), textfield) from number_of_columns_bug;

The "collate latin1_bin" seems to designate that characters are actually handled with binary data.

The same CONCAT() function is used with the triggers, so why does it behave one way and work fine there, but act differently when we run a SELECT statement?

Issue History

Date Modified Username Field Change
2013-06-07 10:29 seroteev New Issue
2014-02-12 15:03 elong Note Added: 0000478
2014-02-12 15:03 elong Assigned To => elong
2014-02-12 15:03 elong Status new => assigned
2014-02-12 15:03 elong Fixed in Version => 3.5.17
2014-02-12 15:03 elong Target Version => 3.5.17
2014-02-12 15:03 elong Steps to Reproduce Updated View Revisions
2014-02-12 15:48 elong Note Added: 0000479
2014-02-13 21:09 chenson Summary reload-table don't work with specific tables => reload-table doesn't work with specific tables
2014-02-14 18:13 elong Fixed in Version 3.5.17 =>
2014-02-14 18:13 elong Target Version 3.5.17 => 3.6.0
2014-03-03 09:55 chenson Target Version 3.6.0 =>
2019-04-22 13:32 elong Tag Attached: data type
2019-04-22 13:32 elong Tag Attached: mysql/mariadb
2019-04-23 16:49 admin Tag Renamed mysql/mariadb => dialect: mysql/mariadb
2019-05-13 14:36 elong Relationship added related to 0003936
2019-05-13 14:36 elong Relationship deleted related to 0003936
2019-05-13 15:15 elong Summary reload-table doesn't work with specific tables => reload-table doesn't work with tables that have collate latin1_bin
2019-05-31 12:06 elong Relationship added related to 0003076
2019-05-31 12:07 elong Assigned To elong =>
2019-05-31 12:07 elong Status assigned => resolved
2019-05-31 12:07 elong Resolution open => fixed
2019-05-31 12:07 elong Fixed in Version => 3.8.22