Handling Encoding Issues from SQL Server to PostgreSQL

[dekinf]

Hi everyone,
I’m replicating data from SQL Server to PostgreSQL (UTF-8) and hitting encoding issues on some columns.
I can fix it by manually casting each problematic column to NVARCHAR(MAX) in the stream SQL, but it’s not ideal.
Is there a more generic or best-practice way to handle encoding for all columns in DB-to-DB replication (maybe something similar to the encoding options available for files)?

Any tips would be greatly appreciated.
Thanks!

[flarco]

hey @dekinf, yes this is something that needs to be improved.

Could you share this information about your problematic columns? I have an idea.

SELECT c.COLUMN_NAME, c.COLLATION_NAME, c.DATA_TYPE, COLLATIONPROPERTY(c.COLLATION_NAME, 'CodePage') AS CodePage
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @table AND c.DATA_TYPE IN ('char', 'varchar', 'text')

[dekinf]

Hi @flarco ! Thanks for the quick response!
Actually, I was surprised to see that the problematic columns are of type uniqueidentifier, so both COLLATION_NAME and CodePage return NULL.
I also suspected that columns with text types might cause the issue, but so far, those seem to be fine.

Thanks!

[flarco]

@dekinf interesting, is it similar to this issue: #645?
Could you share the error? are you using windows?

[dekinf]

Hi @flarco , thanks for the response!
I’m not sure if this is the same issue.
Maybe what’s happening is that the replication process is attempting to treat the UUID’s raw bytes as text, which causes the UTF-8 encoding error?

Thanks!

The error I get is:

WRN {"Severity":"ERROR","Code":"22021","Message":"invalid byte sequence for encoding "UTF8": 0xe5 0x10 0x1f","Detail":"","Hint":"","Position":"","InternalPosition":"","InternalQuery":"","Where":"COPY my_table_tmp, line 1","Schema":"","Table":"","Column":"","DataTypeName":"","Constraint":"","File":"mbutils.c","Line":"1665","Routine":"report_invalid_encoding"}
DBG drop table if exists "my_schema"."my_table_tmp"
DBG table "my_schema"."my_table_tmp" dropped
DBG closed "postgres" connection (conn-postgres)
DBG closed "sqlserver" connection (conn-sqlserver)
INF execution failed

Perhaps the 'encodings' source option could help? See https://docs.slingdata.io/concepts/replication/source-options#supported-encodings. Also try the replace_non_printable transform. See https://docs.slingdata.io/concepts/replication/transforms

fatal:
--- proc.go:283 main ---
--- sling_cli.go:517 main ---
--- sling_cli.go:553 cliInit ---
--- cli.go:287 CliProcess ---
~ failure running replication (see docs @ https://docs.slingdata.io/sling-cli)
--- sling_run.go:228 processRun ---

--------------------------- my_schema.my_table ---------------------------
--- proc.go:283 main ---
--- sling_cli.go:517 main ---
--- sling_cli.go:553 cliInit ---
--- cli.go:287 CliProcess ---
--- sling_run.go:226 processRun ---
--- sling_run..go:61 RunReplication ---
--- sling_run.go:548 replicationRun ---
--- sling_run.go:417 runTask ---
--- task_run.go:204 Execute ---

--- task_run.go:141 func2 ---
--- task_run.go:849 runDbToDb ---
--- task_run_write.go:305 WriteToDb ---
--- database.go:2419 BulkImportFlow ---
~ could not bulk import
--- database.go:2406 func1 ---
~ could not copy data
--- database_postgres.go:234 BulkImportStream ---
~ could not execute statement
--- database_postgres.go:218 func2 ---
pq: invalid byte sequence for encoding "UTF8": 0xe5 0x10 0x1f

--- task_run.go:141 func2 ---
~ Could not WriteToDb
--- task_run.go:849 runDbToDb ---
~ could not insert into "my_schema"."my_table_tmp"
--- task_run_write.go:311 WriteToDb ---

--- task_run.go:141 func2 ---
--- task_run.go:849 runDbToDb ---
--- task_run_write.go:305 WriteToDb ---
--- database.go:2419 BulkImportFlow ---
--- database.go:2406 func1 ---
--- database_postgres.go:234 BulkImportStream ---
~ could not COPY into table "my_schema"."my_table_tmp"
--- database_postgres.go:214 func2 ---
pq: invalid byte sequence for encoding "UTF8": 0xe5 0x10 0x1f

--- task_run.go:141 func2 ---
--- task_run.go:849 runDbToDb ---
--- task_run_write.go:305 WriteToDb ---
--- database.go:2419 BulkImportFlow ---
~ could not bulk import
--- database.go:2406 func1 ---
~ could not copy data
--- database_postgres.go:234 BulkImportStream ---
~ could not execute statement
--- database_postgres.go:218 func2 ---
pq: invalid byte sequence for encoding "UTF8": 0xe5 0x10 0x1f

[flarco]

@dekinf can you try with the latest release (1.4.24)?

[dekinf]

@flarco Wow, thank you very much for the fast change! It looks like it’s working great now