Latest docker versions are crashing when migrating from MSSQL

[t00]

I am trying to run migration using pgloader latest docker image and during the migration it seems to crash on some fields. This does not happen on locally installed 3.6.2 on Arch Linux used to migrate the same database. Problem with 3.6.2 is that "WITH preserve index names" feature seems to be not working.

The docker command line for pgloader is: docker run --security-opt seccomp=unconfined --network host --rm --name pgloader -vpwd:/config dimitri/pgloader:ccl.latest pgloader /config/migrate.docker

and migrate.docker command:

LOAD DATABASE
FROM mssql://msuser:mspass@localhost:/msdb
INTO postgresql://pguser:pgpass@localhost/pgdb

SET MSSQL PARAMETERS textsize TO '104857600'
WITH CREATE TABLES, CREATE INDEXES
ALTER SCHEMA 'dbo' RENAME TO 'public'
CAST type int with extra auto_increment to serial
WITH PRESERVE INDEX NAMES
;

dimitri/pgloader:latest image is not only crashing but freezing during execution with some bizzarre errors. Being not familiar with lisp, call stack seems very difficult to decipher but hopefully might give some clue.

Max connections reached, increase value of TDS_MAX_CONN
2021-02-14T07:57:12.764007Z ERROR A thread failed with error: The value #<A Foreign Pointer #x7FDBC8025BD0> is not of the expected type ARRAY.
2021-02-14T07:57:12.765710Z ERROR Date/time: 2021-02-14-07:57!
An unhandled error condition has been signalled: The value #<A Foreign Pointer #x7FDBC8025BD0> is not of the expected type ARRAY.


 (7FDBE4258240) : 0 (PRINT-CALL-HISTORY :CONTEXT NIL :PROCESS NIL :ORIGIN NIL :DETAILED-P NIL :COUNT 1152921504606846975 :START-FRAME-NUMBER 0 :STREAM #<STRING-OUTPUT-STREAM  #x302004C94C0D> :PRINT-LEVEL 2 :PRINT-LENGTH 5 :PRINT-STRING-LENGTH :DEFAULT :SHOW-INTERNAL-FRAMES NIL :FORMAT :TRADITIONAL) 941
 (7FDBE42583D8) : 1 (PRINT-BACKTRACE-TO-STREAM #<STRING-OUTPUT-STREAM  #x302004C94C0D>) 85
 (7FDBE4258408) : 2 (PRINT-BACKTRACE #<TYPE-ERROR #x302004C94D0D> :OUTPUT NIL :IF-EXISTS :APPEND :VERBOSE NIL) 797
 (7FDBE42584A0) : 3 (FUNCALL #'#<(:INTERNAL (PGLOADER.LOAD:COPY-FROM (PGLOADER.SOURCES:COPY)))> #<TYPE-ERROR #x302004C94D0D>) 197
 (7FDBE42584C8) : 4 (CONDITION-HANDLER #<TYPE-ERROR #x302004C94D0D>) 493
 (7FDBE4258540) : 5 (SIGNAL #<TYPE-ERROR #x302004C94D0D>) 989
 (7FDBE4258598) : 6 (%ERROR #<TYPE-ERROR #x302004C94D0D> NIL 17572800278712) 117
 (7FDBE42585C0) : 7 (%AREF1 #<A Foreign Pointer #x7FDBC8025BD0> 118) 565
 (7FDBE42585E8) : 8 (FUNCALL #'#<(:INTERNAL (PGLOADER.SOURCES:MAP-ROWS (PGLOADER.SOURCE.MSSQL:COPY-MSSQL)))> #<BABEL-ENCODINGS:INVALID-UTF8-STARTER-BYTE #x302004C956DD>) 245
 (7FDBE4258618) : 9 (SIGNAL #<BABEL-ENCODINGS:INVALID-UTF8-STARTER-BYTE #x302004C956DD>) 989
 (7FDBE4258670) : 10 (%ERROR #<BABEL-ENCODINGS:INVALID-UTF8-STARTER-BYTE #x302004C956DD> (:OCTETS #(163) :ENCODING :UTF-8 :BUFFER ...) 17572800278739) 117
 (7FDBE4258698) : 11 (FUNCALL #'#<BABEL-ENCODINGS::UTF-8-DECODER> #<A Foreign Pointer #x7FDBC8025BD0> 0 163 "Test time limit 1 min v2: Answered Question Id 3492 - Very short answer qn (1): Choice Id 6480 text entered: Da#?!&\"!\"" 0) 485
 (7FDBE4258740) : 12 (FOREIGN-STRING-TO-LISP #<A Foreign Pointer #x7FDBC8025BD0> :OFFSET 0 :COUNT 163 :MAX-CHARS 72057594037927935 :ENCODING :UTF-8) 1197
 (7FDBE42587E0) : 13 (MAP-QUERY-RESULTS "SELECT [Id], convert(varchar(30), [Date], 126), cast([IP] as numeric(20)), [Text], [DiagnosticText], [ExamEventId], [DeviceCode], [IsError], [DeviceId], [ExamId], [QuestionId], [CandidateId], [ExaminerId] FROM [dbo].[AuditPersonEntries];" :ROW-FN #<COMPILED-LEXICAL-CLOSURE (:INTERNAL #) #x30200423601F> :CONNECTION #<MSSQL:DATABASE-CONNECTION #x302004507DCD>) 1445
 (7FDBE42588E0) : 14 (FUNCALL #'#<#<STANDARD-METHOD PGLOADER.SOURCES:MAP-ROWS (PGLOADER.SOURCE.MSSQL:COPY-MSSQL)>> #<PGLOADER.SOURCE.MSSQL:COPY-MSSQL #x302001DCCBBD> :PROCESS-ROW-FN #<COMPILED-LEXICAL-CLOSURE (:INTERNAL #) #x30200423601F>) 2525
 (7FDBE42589A8) : 15 (%%CHECK-KEYWORDS #(1 #(:PROCESS-ROW-FN) #<METHOD-FUNCTION PGLOADER.SOURCES:MAP-ROWS #>) 17572800278848) 229
 (7FDBE4258A20) : 16 (FUNCALL #'#<#<STANDARD-METHOD PGLOADER.LOAD:QUEUE-RAW-DATA (PGLOADER.SOURCES:COPY T T)>> #<PGLOADER.SOURCE.MSSQL:COPY-MSSQL #x302001DCCBBD> #S(LPARALLEL.VECTOR-QUEUE:VECTOR-QUEUE :IMPL #S(LPARALLEL.VECTOR-QUEUE::RAW-QUEUE :DATA # :START 2266 ...) :LOCK #<RECURSIVE-LOCK "Anonymous lock" [ptr @ #x7FDBE002F540] #x302001E9220D> ...) 1) 997
 (7FDBE4258A70) : 17 (FUNCALL #'#<(:INTERNAL #:BODY-FN116623 LPARALLEL.KERNEL::MAKE-CHANNELED-TASK)>) 245
 (7FDBE4258AD8) : 18 (FUNCALL #'#<(:INTERNAL LPARALLEL.KERNEL::MAKE-CHANNELED-TASK)>) 117
 (7FDBE4258B28) : 19 (EXEC-TASK/WORKER (#<COMPILED-LEXICAL-CLOSURE # #x302001E9214F> . :DEFAULT) #S(LPARALLEL.KERNEL::WORKER :HANDSHAKE/FROM-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL # :LOCK #<# # [ptr @ #x7FDBE002DC40] #x302001CA1D8D> ...) :HANDSHAKE/TO-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL # :LOCK #<# # [ptr @ #x7FDBE002DD00] #x302001CA1CBD> ...) ...)) 197
 (7FDBE4258B90) : 20 (WORKER-LOOP #<KERNEL :NAME "lparallel" :WORKER-COUNT 4 :USE-CALLER NIL :ALIVE T #x302001CA413D> #S(LPARALLEL.KERNEL::WORKER :HANDSHAKE/FROM-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL # :LOCK #<# # [ptr @ #x7FDBE002DC40] #x302001CA1D8D> ...) :HANDSHAKE/TO-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL # :LOCK #<# # [ptr @ #x7FDBE002DD00] #x302001CA1CBD> ...) ...)) 237
 (7FDBE4258C00) : 21 (%CALL-WITH-TASK-HANDLER #<COMPILED-LEXICAL-CLOSURE (:INTERNAL LPARALLEL.KERNEL::ENTER-WORKER-LOOP) #x302001CAEF4F>) 789
 (7FDBE4258CA8) : 22 (FUNCALL #'#<(:INTERNAL LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT)>) 869
 (7FDBE4258D28) : 23 (FUNCALL #<COMPILED-LEXICAL-CLOSURE (:INTERNAL LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT) #x302001CAEEFF>) 149
 (7FDBE4258D58) : 24 (CALL-WITH-WORKER-CONTEXT #<COMPILED-LEXICAL-CLOSURE (:INTERNAL LPARALLEL.KERNEL::ENTER-WORKER-LOOP) #x302001CAEF4F> #<Compiled-function FUNCALL #x30000007FEFF> #<KERNEL :NAME "lparallel" :WORKER-COUNT 4 :USE-CALLER NIL :ALIVE T #x302001CA413D> #S(LPARALLEL.KERNEL::WORKER :HANDSHAKE/FROM-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL # :LOCK #<# # [ptr @ #x7FDBE002DC40] #x302001CA1D8D> ...) :HANDSHAKE/TO-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL # :LOCK #<# # [ptr @ #x7FDBE002DD00] #x302001CA1CBD> ...) ...)) 341
 (7FDBE4258DC0) : 25 (FUNCALL #'#<(:INTERNAL LPARALLEL.KERNEL::MAKE-WORKER-THREAD)>) 157
 (7FDBE4258E18) : 26 (FUNCALL #'#<(:INTERNAL BORDEAUX-THREADS::BINDING-DEFAULT-SPECIALS)>) 797
 (7FDBE4258E60) : 27 (RUN-PROCESS-INITIAL-FORM #<PROCESS lparallel(9) [Active] #x302001CA151D> (#<COMPILED-LEXICAL-CLOSURE # #x302001CA12AF>)) 709
 (7FDBE4258EF0) : 28 (FUNCALL #'#<(:INTERNAL (CCL::%PROCESS-PRESET-INTERNAL (PROCESS)))> #<PROCESS lparallel(9) [Active] #x302001CA151D> (#<COMPILED-LEXICAL-CLOSURE # #x302001CA12AF>)) 573
 (7FDBE4258F98) : 29 (FUNCALL #'#<(:INTERNAL CCL::THREAD-MAKE-STARTUP-FUNCTION)>) 277
KABOOM!
FATAL error: The value #<A Foreign Pointer #x7FDBC8025BD0> is not of the expected type ARRAY.
An unhandled error condition has been signalled: The value #<A Foreign Pointer #x7FDBC8025BD0> is not of the expected type ARRAY.

[t00]

The call stack may not be that hard to decipher - I noticed INVALID-UTF8-STARTER-BYTE error indicating some malformed text in the database.

When migrating using locally installed pgloader (Arch) this issue is also noticed but without such loud exception:

2021-02-14T08:49:00.851000Z ERROR Database error 22021: invalid byte sequence for encoding "UTF8": 0x00
CONTEXT: COPY auditroleentries, line 1845

After identifying this row I got another issue for another row: INVALID-UTF8-CONTINUATION-BYTE

Is there a way to disable strict UFT8 validation? - some data is stored as provided from external sources and is not searchable.

[theiosdevguy]

Hi @t00 Did you find a way to resolve this? I am myself stuck with this.

[t00]

Hi @theiosdevguy unfortunately I did not find a way around the problem and implemented a database migration myself using C# + npgsql by manually copying data between tables one by one in a loop and enabling primary key indexes at the end.

SQL snippets:

ALTER TABLE {tableName} DISABLE TRIGGER ALL;
COPY {tableName} ({columns}) FROM STDIN (FORMAT BINARY);
SELECT pg_catalog.setval(pg_get_serial_sequence('{tableName}', '{key}'), MAX({key})) FROM {tableName};
ALTER TABLE {tableName} ENABLE TRIGGER ALL;

C# code:

private static async Task<long> CopyTable(NpgsqlConnection targetConnection, DbDataReader rdr, string copySql)
{
    long count = 0;
    await using var writer = targetConnection.BeginBinaryImport(copySql);
    while (await rdr.ReadAsync())
    {
        var values = new object[rdr.FieldCount];
        rdr.GetValues(values);
        for (var i = 0; i < values.Length; i++)
        {
            if (values[i] is string s && s.Contains((char) 0))
            {
                values[i] = s.Replace((char) 0, ' ');
            }
        }

        await writer.WriteRowAsync(default, values);
        count++;
    }

    await writer.CompleteAsync();
    await writer.CloseAsync();
    return count;
}

This process being a part of our app itself, made an upgrade process a little easier for the user and there were no complaints as the process did not care about malformed UTF8 data present in tables, which I have no issue with.

[Brobrechts]

(knowing the last comment is quite old, but since the issue is still open, I wanted to add a small note here that could potentially help)

I was having the same issue (same UTF-8 like errors in the logs) when migrating from mssql to postgres using the docker image dimitri/pgloader:ccl.latest

As described in the documentation, the client charset should be set for the freetds driver to utf-8: https://pgloader.readthedocs.io/en/latest/ref/mssql.html#ms-sql-driver-setup-and-encoding

In the non-ccl docker image the file freetds.conf gets added explicitly which has the client charset = utf-8 setting See https://hub.docker.com/layers/dimitri/pgloader/latest/images/sha256-756758ca8269404bdca9384c2fb04bcc7de744a713d26885ba8b556e4005eab4?context=explore

In the pgloader:ccl image I don't see it being added (unless I'm overlooking it?).

I manage to mount my own config file with the client charset = utf-8 option, and that did help.

So the question is, Should the ccl image also add the freetds.conf file with the client charset = utf-8 explicitly set ( like the non ccl container? )

[khanyuriy]

@Brobrechts this is exactly the cause, thank you!