the4thdoctor / pg_chameleon

MySQL to PostgreSQL replica system
http://www.pgchameleon.org
BSD 2-Clause "Simplified" License
384 stars 83 forks source link

data is not correct when copy mysql blob data into postgresql bytea #160

Open giant-ksj opened 1 year ago

giant-ksj commented 1 year ago

Describe the bug A clear and concise description of what the bug is.

Source - MySQL

show create table test;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int NOT NULL,
  `testcol` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

select * from test;
+----+------------------+
| id | testcol          |
+----+------------------+
|  1 | 0x31             |
+----+------------------+
1 row in set (0.00 sec)

Target - PostgreSQL

\d+ test
                                  Table "public.test"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
 id      | integer |           | not null |         | plain    |              |
 testcol | bytea   |           |          |         | extended |              |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Access method: heap

select * from test;
 id | testcol
----+---------
  1 | \x3331

when mysql_fdw appear same data with mysql.

select * from mysql_fdw.test;
 id | testcol
----+---------
  1 | \x31

which is correct?

To Reproduce Steps to reproduce the behavior:

Expected behavior A clear and concise description of what you expected to happen.

Screenshots If applicable, add screenshots to help explain your problem.

Environment(please complete the following information):

Additional context Add any other context about the problem here.

bukem commented 1 year ago

Hello,

The removing blob from the hexify_always list seems to address our similar issue https://github.com/the4thdoctor/pg_chameleon/blob/50ed941f3ab7e56e6f175b846efbf7bb2c50577a/pg_chameleon/lib/mysql_lib.py#L25 . Just not sure how appropriate is this fix. What are the risks of excluding blob from this list?

Thank you, Mikhail