View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0001261 | SymmetricDS | Bug | public | 2013-06-07 14:29 | 2019-05-31 16:07 |
| Reporter | seroteev | Assigned To | |||
| Priority | normal | ||||
| Status | resolved | Resolution | fixed | ||
| Product Version | 3.4.6 | ||||
| Target Version | 3.8.22 | Fixed in Version | 3.8.22 | ||
| Summary | 0001261: reload-table doesn't work with tables that have collate latin1_bin | ||||
| Description | You 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 Reproduce | CREATE 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 Information | The effect is the same on version 3.3.4 and 3.4.4 | ||||
| Tags | data type, dialect: mysql/mariadb | ||||
|
|
Reproduced on 3.5.16. |
|
|
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? |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2013-06-07 14:29 | seroteev | New Issue | |
| 2014-02-12 20:03 | elong | Note Added: 0000478 | |
| 2014-02-12 20:03 | elong | Assigned To | => elong |
| 2014-02-12 20:03 | elong | Status | new => assigned |
| 2014-02-12 20:03 | elong | Fixed in Version | => 3.5.17 |
| 2014-02-12 20:03 | elong | Target Version | => 3.5.17 |
| 2014-02-12 20:03 | elong | Steps to Reproduce Updated | View Revisions |
| 2014-02-12 20:48 | elong | Note Added: 0000479 | |
| 2014-02-14 02:09 | chenson | Summary | reload-table don't work with specific tables => reload-table doesn't work with specific tables |
| 2014-02-14 23:13 | elong | Fixed in Version | 3.5.17 => |
| 2014-02-14 23:13 | elong | Target Version | 3.5.17 => 3.6.0 |
| 2014-03-03 14:55 | chenson | Target Version | 3.6.0 => |
| 2019-04-22 17:32 | elong | Tag Attached: data type | |
| 2019-04-22 17:32 | elong | Tag Attached: mysql/mariadb | |
| 2019-04-23 20:49 | admin | Tag Renamed | mysql/mariadb => dialect: mysql/mariadb |
| 2019-05-13 18:36 | elong | Relationship added | related to 0003936 |
| 2019-05-13 18:36 | elong | Relationship deleted | related to 0003936 |
| 2019-05-13 19: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 16:06 | elong | Relationship added | related to 0003076 |
| 2019-05-31 16:07 | elong | Assigned To | elong => |
| 2019-05-31 16:07 | elong | Status | assigned => resolved |
| 2019-05-31 16:07 | elong | Resolution | open => fixed |
| 2019-05-31 16:07 | elong | Fixed in Version | => 3.8.22 |