SymmetricDS How do I transform a MySQL Longtext column to a PostgreSQL Text column? - Stack Overflow

admin管理员组

文章数量:1122832

I am working on a replication between a old MySQL 8 database and a new PostgreSQL database.

This is a two way replication but I am having issues with syncing a LongText column in MySQL to a Text column in PostgreSQL.

I have setup the triggers and transforms which work, the data is being synced between the 2 databases.

The issue I am having is that when data is send from MySQL to PostgreSQL, the data is stored as Hexadecimal.

MySQL value: a.b.c.d
PostgreSQL result value: 612E622E632E64

Also when I update the column in PostgreSQL, I get an error when SymmetricDS tries to update the row in MySQL: Incorrect string value: '\xDFM\x84\xDFM\x84...' for column 'foo'

Since I don't know how much data is exactly stored in the MySQL column, I can't change this column to a different type.

How can I make SymmetricDS replicate these columns and have the result in plain text on both databases?

My SymmetricDS triggers, routers and transformation configuration:

insert into sym_trigger
(trigger_id, source_table_name, channel_id, last_update_time, create_time, sync_on_incoming_batch)
values 
 ('foo_table_trigger', 'foo', 'my_channel', current_timestamp, current_timestamp, 1),
 ('bar_table_trigger', 'bar', 'my_channel', current_timestamp, current_timestamp, 1);

insert into sym_trigger_router
(trigger_id, router_id, initial_load_order, last_update_time, create_time)
values 
 ('foo_table_trigger', 'source-2-target', 100, current_timestamp, current_timestamp),
 ('bar_table_trigger', 'target-2-source', 100, current_timestamp, current_timestamp);

insert into sym_transform_table 
(transform_id, source_node_group_id, target_node_group_id, transform_point,
source_table_name,target_table_name, update_action, delete_action, transform_order, 
column_policy, update_first,last_update_by, last_update_time, create_time)
values 
  ('fooTransform', 'Test-source', 'Test-target', 'LOAD', 'foo', 'bar', 'UPD_ROW', 
   'DEL_ROW', 3, 'IMPLIED', 1, 'testing', current_timestamp, current_timestamp),
  ('barTransform', 'Test-target', 'Test-source', 'EXTRACT','bar', 'foo', 'UPD_ROW', 
   'DEL_ROW', 2, 'IMPLIED', 1, 'testing', current_timestamp, current_timestamp);

insert into sym_transform_column 
    (transform_id, include_on, target_column_name, source_column_name, pk, transform_type, 
    transform_expression, transform_order, last_update_time, last_update_by, create_time)
values 
('fooTransform', '*', 'id', 'GUID', 1, 'copy', '', 1, current_timestamp, 'testing', current_timestamp),
('fooTransform', '*', 'myTextCol', 'myLongTextCol', 0, 'copy', '', 2, current_timestamp,'testing', current_timestamp),
('barTransform', '*', 'GUID', 'id', 1, 'copy', '', 1, current_timestamp, 'testing', current_timestamp),
('barTransform', '*', 'myLongTextCol', 'myTextCol', 0, 'copy', '', 2, current_timestamp,'accountSecurityTransform_column', current_timestamp);

I tried switching the TEXT column type in PostgreSQL to varchar but that had the same result.

I expected that the data would be saved as a plain text value instead of Hexadecimal.

edit: Here are example tables, myStringCol works fine because the SymmetricDS configuration in sym_transform_table is set to IMPLIED.

Which means it will automatically sync matching columns. When it doesn't match, a column transform needs to be added to sym_transform_column which I did for myLongTextCol and myTextCol.

-- MySQL table
CREATE TABLE foo
(
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    myStringCol VARCHAR(255) NOT NULL,
    myLongTextCol LONGTEXT NOT NULL,
    GUID CHAR(36),
    INDEX GUID_index (GUID)
)

-- PostgreSQL table
CREATE TABLE bar
(
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    myStringCol VARCHAR(255) NOT NULL,
    myTextCol TEXT NOT NULL
);

I am working on a replication between a old MySQL 8 database and a new PostgreSQL database.

This is a two way replication but I am having issues with syncing a LongText column in MySQL to a Text column in PostgreSQL.

I have setup the triggers and transforms which work, the data is being synced between the 2 databases.

The issue I am having is that when data is send from MySQL to PostgreSQL, the data is stored as Hexadecimal.

MySQL value: a.b.c.d
PostgreSQL result value: 612E622E632E64

Also when I update the column in PostgreSQL, I get an error when SymmetricDS tries to update the row in MySQL: Incorrect string value: '\xDFM\x84\xDFM\x84...' for column 'foo'

Since I don't know how much data is exactly stored in the MySQL column, I can't change this column to a different type.

How can I make SymmetricDS replicate these columns and have the result in plain text on both databases?

My SymmetricDS triggers, routers and transformation configuration:

insert into sym_trigger
(trigger_id, source_table_name, channel_id, last_update_time, create_time, sync_on_incoming_batch)
values 
 ('foo_table_trigger', 'foo', 'my_channel', current_timestamp, current_timestamp, 1),
 ('bar_table_trigger', 'bar', 'my_channel', current_timestamp, current_timestamp, 1);

insert into sym_trigger_router
(trigger_id, router_id, initial_load_order, last_update_time, create_time)
values 
 ('foo_table_trigger', 'source-2-target', 100, current_timestamp, current_timestamp),
 ('bar_table_trigger', 'target-2-source', 100, current_timestamp, current_timestamp);

insert into sym_transform_table 
(transform_id, source_node_group_id, target_node_group_id, transform_point,
source_table_name,target_table_name, update_action, delete_action, transform_order, 
column_policy, update_first,last_update_by, last_update_time, create_time)
values 
  ('fooTransform', 'Test-source', 'Test-target', 'LOAD', 'foo', 'bar', 'UPD_ROW', 
   'DEL_ROW', 3, 'IMPLIED', 1, 'testing', current_timestamp, current_timestamp),
  ('barTransform', 'Test-target', 'Test-source', 'EXTRACT','bar', 'foo', 'UPD_ROW', 
   'DEL_ROW', 2, 'IMPLIED', 1, 'testing', current_timestamp, current_timestamp);

insert into sym_transform_column 
    (transform_id, include_on, target_column_name, source_column_name, pk, transform_type, 
    transform_expression, transform_order, last_update_time, last_update_by, create_time)
values 
('fooTransform', '*', 'id', 'GUID', 1, 'copy', '', 1, current_timestamp, 'testing', current_timestamp),
('fooTransform', '*', 'myTextCol', 'myLongTextCol', 0, 'copy', '', 2, current_timestamp,'testing', current_timestamp),
('barTransform', '*', 'GUID', 'id', 1, 'copy', '', 1, current_timestamp, 'testing', current_timestamp),
('barTransform', '*', 'myLongTextCol', 'myTextCol', 0, 'copy', '', 2, current_timestamp,'accountSecurityTransform_column', current_timestamp);

I tried switching the TEXT column type in PostgreSQL to varchar but that had the same result.

I expected that the data would be saved as a plain text value instead of Hexadecimal.

edit: Here are example tables, myStringCol works fine because the SymmetricDS configuration in sym_transform_table is set to IMPLIED.

Which means it will automatically sync matching columns. When it doesn't match, a column transform needs to be added to sym_transform_column which I did for myLongTextCol and myTextCol.

-- MySQL table
CREATE TABLE foo
(
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    myStringCol VARCHAR(255) NOT NULL,
    myLongTextCol LONGTEXT NOT NULL,
    GUID CHAR(36),
    INDEX GUID_index (GUID)
)

-- PostgreSQL table
CREATE TABLE bar
(
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    myStringCol VARCHAR(255) NOT NULL,
    myTextCol TEXT NOT NULL
);

• mysql
• postgresql
• database-replication
• symmetricds

Share Improve this question Follow edited Nov 26, 2024 at 8:08 ArtBindu 1,96211 gold badge1111 silver badges2020 bronze badges asked Nov 21, 2024 at 16:50 JordiJordi 111 silver badge22 bronze badges 3

• 1 1) To question add the table definitions for both the MySQL And Postgres tables. 2) Where is the actual transformation code? – Adrian Klaver Commented Nov 21, 2024 at 16:58
• There is currently no custom transformation code. I was expecting SymmetricDS to do the transformation on its own like it would do for other columns. – Jordi Commented Nov 22, 2024 at 8:45
• Looks to me like the transform code is converting the LONGTEXT to Postgres bytea. I have no idea why though. Someone who is familiar with SymmetricDS will need to comment on that. – Adrian Klaver Commented Nov 22, 2024 at 16:23

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Those tables would normally sync with default settings, so there is something in your environment causing trouble. Here are some things to check.

1. Check in the SymmetricDS engine properties to see if there is a setting for db.read.strings.as.bytes. If there is, go ahead and remove it. Restart SymmetricDS and run "sync triggers" so it can rebuild triggers to match.

2. If you have control over the table definition, then make sure you are using LONGTEXT on MySQL. If the default character set of the server or database is "binary", then it will silently convert character types to binary ones. Describe the table on MySQL to confirm if this is happening:

mysql> desc foo;
+---------------+----------------+------+-----+---------+----------------+
| Field         | Type           | Null | Key | Default | Extra          |
+---------------+----------------+------+-----+---------+----------------+
| id            | int            | NO   | PRI | NULL    | auto_increment |
| myStringCol   | varbinary(255) | NO   |     | NULL    |                |
| myLongTextCol | longblob       | NO   |     | NULL    |                |
| GUID          | binary(36)     | YES  | MUL | NULL    |                |
+---------------+----------------+------+-----+---------+----------------+

If it gave you LONGBLOB, then you can specify the character set you need for using LONGTEXT. When you create the table or alter it, specify the character set, like this:

alter table foo modify column myLongTextCol longtext character set utf8mb4;

After re-creating or altering the table, you need to run "sync triggers" on SymmetricDS so it can rebuild the triggers to match.

3. If you don't have control over the table definition, then you will need to transform the data between character and binary. Set the transform_type to "bsh" (BeanShell) and transform from MySQL (which captures in hex) to Postgres with:

import org.apache.commons.codec.binary.Hex;
return new String(Hex.decodeHex(currentValue));

Transform from Postgres (which captures in base64) to MySQL with:

import org.apache.commons.codec.binary.Base64;
new String(Base64.encodeBase64(currentValue.getBytes()));

本文标签： SymmetricDS How do I transform a MySQL Longtext column to a PostgreSQL Text columnStack Overflow