Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

bytes after (0x02 0x01 0x00) get truncated in column with type text #299

Open
n0099 opened this issue Apr 11, 2024 · 0 comments
Open

bytes after (0x02 0x01 0x00) get truncated in column with type text #299

n0099 opened this issue Apr 11, 2024 · 0 comments

Comments

@n0099
Copy link

n0099 commented Apr 11, 2024

mysql side

CREATE TABLE test(t text);
INSERT INTO test(t) VALUES (CONCAT(0x020100, '1234'));
SELECT hex(t) FROM test;
HEX(t)
02010031323334

pgsql side

CREATE FOREIGN TABLE IF NOT EXISTS test(t text)
    SERVER mysql
    OPTIONS (dbname 'db', table_name 'test');
SELECT encode(t::bytea, 'hex') FROM test;
encode
0201

all bytes after 0x00 and itself are removed, but it will be complete fetched when using bytea as field type:

CREATE FOREIGN TABLE IF NOT EXISTS test(t bytea)
    SERVER mysql
    OPTIONS (dbname 'db', table_name 'test');
SELECT encode(t::bytea, 'hex') FROM test;
encode
02010031323334

It's seems related to confluentinc/kafka-connect-jdbc#1216 as postgresql doesn't support storing 0x00 in text unlike mysql due to c-style NUL-terminated string https://stackoverflow.com/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-encoding-utf8-0x0 https://postgrespro.com/list/thread-id/1897280

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant