Pgloader: 3.6.1 import from mysql 255 fell through ECASE expression

Created on 26 May 2019 · 14Comments · Source: dimitri/pgloader

[x] pgloader --version
```
pgloader version "3.6.2~devel"
compiled with SBCL 1.3.14.debian
```
I tried both 3.6.1 on Ubuntu(compiled from source) and docker version. The log I sent to your email is obtained from the docker version.
[x] did you test a fresh compile from the source tree?

Yes, does not work also. Ubuntu 18.04
[x] did you search for other similar issues?
[x] how can I reproduce the bug?

docker run --rm --name pgloader dimitri/pgloader:latest pgloader --verbose --debug mysql://root:[email protected]:5691/crm postgresql://admin:[email protected]:6000/crm
[x] pgloader output you obtain

I'm sending the full output to your email, sorry for the trouble but there are table structures and I don't know if it is ok to make them public... The email title is Meowzz95 github issue full log

[ ] data that is being loaded, if relevant
[ ] How the data is different from what you expected, if relevant

I just use the simplest command to dump my existing data in MySQL to PG, however, I got 255 fell through ECASE expression

I'm sending the full log to your email. The email title is Meowzz95 github issue full log

Source

Meowzz95

Most helpful comment

Thanks a lot @Meowzz95 for extended testing, I have now merged https://github.com/qitab/qmynd/pull/11. It means the next normal build of pgloader will embed the fix here (after a make clean for people who already had a pgloader source and dependencies installed).

dimitri on 29 May 2019

🎉2

All 14 comments

I think I can make part of the log available here.

2019-05-26T15:24:27.313000Z SQL MySQL: sending query: SELECT `id`, `name` FROM `auth_group`
2019-05-26T15:24:27.363000Z ERROR A thread failed with error: 255 fell through ECASE expression.
                            Wanted one of (2 3 4 5 6 8 9 10 11 14 15 17 20 21
                                           23 27 28 30 31 32 33 35 41 42 45 46
                                           47 48 49 50 51 52 54 55 56 60 61 62
                                           63 64 65 69 72 76 77 78 79 82 83 87
                                           90 92 93 94 95 96 97 98 101 102 103
                                           104 105 106 107 108 109 110 111 112
                                           113 114 115 116 117 118 119 120 121
                                           122 123 124 128 129 130 131 132 133
                                           134 135 136 137 138 139 140 141 142
                                           143 144 145 146 147 148 149 150 151
                                           159 160 161 162 163 164 165 166 167
                                           168 169 170 171 172 173 174 175 176
                                           177 178 179 180 181 182 183 192 193
                                           194 195 196 197 198 199 200 201 202
                                           203 204 205 206 207 208 209 210 211
                                           212 213 214 215 223 224 225 226 227
                                           228 229 230 231 232 233 234 235 236
                                           237 238 239 240 241 242 243 244 245
                                           246 247 254).
2019-05-26T15:24:27.373000Z DEBUG SET application_name TO 'pgloader'
2019-05-26T15:24:27.387000Z ERROR Date/time: 2019-05-26-15:24An unhandled error condition has been signalled:
                              255 fell through ECASE expression.
                              Wanted one of (2 3 4 5 6 8 9 10 11 14 15 17 20 21
                                             23 27 28 30 31 32 33 35 41 42 45
                                             46 47 48 49 50 51 52 54 55 56 60
                                             61 62 63 64 65 69 72 76 77 78 79
                                             82 83 87 90 92 93 94 95 96 97 98
                                             101 102 103 104 105 106 107 108
                                             109 110 111 112 113 114 115 116
                                             117 118 119 120 121 122 123 124
                                             128 129 130 131 132 133 134 135
                                             136 137 138 139 140 141 142 143
                                             144 145 146 147 148 149 150 151
                                             159 160 161 162 163 164 165 166
                                             167 168 169 170 171 172 173 174
                                             175 176 177 178 179 180 181 182
                                             183 192 193 194 195 196 197 198
                                             199 200 201 202 203 204 205 206
                                             207 208 209 210 211 212 213 214
                                             215 223 224 225 226 227 228 229
                                             230 231 232 233 234 235 236 237
                                             238 239 240 241 242 243 244 245
                                             246 247 254).


Backtrace for: #<SB-THREAD:THREAD "lparallel" RUNNING {1007041E63}>
0: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE-TO-STREAM #<SB-IMPL::STRING-OUTPUT-STREAM {10066D3963}>)
1: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE #<SB-KERNEL:CASE-FAILURE expected-type: "(MEMBER 2 3 4 5 6 8 9 10 11 14 15 17 20 21 23 27 28 30 31 32 33 35 41 42 45 46 ..)"datum: 255> :OUTPUT NIL :IF-EXISTS :APPEND :VERBOSE NIL)
2: ((LAMBDA (CONDITION) :IN PGLOADER.LOAD:COPY-FROM) #<SB-KERNEL:CASE-FAILURE expected-type: "(MEMBER 2 3 4 5 6 8 9 10 11 14 15 17 20 21 23 27 28 30 31 32 33 35 41 42 45 46 ..)"datum: 255>)
3: (LPARALLEL.KERNEL::CONDITION-HANDLER #<SB-KERNEL:CASE-FAILURE expected-type: "(MEMBER 2 3 4 5 6 8 9 10 11 14 15 17 20 21 23 27 28 30 31 32 33 35 41 42 45 46 ..)"datum: 255>)
4: (SIGNAL #<SB-KERNEL:CASE-FAILURE expected-type: "(MEMBER 2 3 4 5 6 8 9 10 11 14 15 17 20 21 23 27 28 30 31 32 33 35 41 42 45 46 ..)"datum: 255>)
5: (ERROR SB-KERNEL:CASE-FAILURE :NAME ECASE :DATUM 255 :EXPECTED-TYPE (MEMBER 2 3 4 5 6 8 9 10 11 14 15 17 20 21 23 27 28 30 31 32 33 35 41 42 45 46 47 48 49 50 51 52 54 55 56 60 61 62 63 64 65 69 72 76 77 78 79 82 83 87 90 92 93 94 95 96 97 98 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 254) :POSSIBILITIES (2 3 4 5 6 8 9 10 11 14 15 17 20 21 23 27 28 30 31 32 33 35 41 42 45 46 47 48 49 50 51 52 54 55 56 60 61 62 63 64 65 69 72 76 77 78 79 82 83 87 90 92 93 94 95 96 97 98 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 254))
6: (QMYND-IMPL::MYSQL-CS-COLL-TO-CHARACTER-ENCODING 255)
7: (QMYND-IMPL::PARSE-TEXT-PROTOCOL-RESULT-COLUMN-AS-TEXT #() #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "crm" :TABLE "account_profile" :ORG-TABLE "account_profile" :NAME "nickname" :ORG-NAME "nickname" :CS-COLL 255 :COLUMN-LENGTH 1020 :TYPE 253 :FLAGS 0 :DECIMALS 0 :DEFAULT-VALUE NIL))
8: (QMYND-IMPL::PARSE-RESULTSET-ROW-AS-TEXT-VECTOR #<unavailable argument> #<unavailable argument>)
9: (QMYND-IMPL::MAP-RESULTSET-ROWS #<CLOSURE (LAMBDA (PGLOADER.LOAD::ROW) :IN PGLOADER.LOAD:QUEUE-RAW-DATA) {1009370BAB}> 5 #(#S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "crm" :TABLE "account_profile" :ORG-TABLE "account_profile" :NAME "id" :ORG-NAME "id" :CS-COLL 63 :COLUMN-LENGTH 11 :TYPE 3 :FLAGS 16899 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "crm" :TABLE "account_profile" :ORG-TABLE "account_profile" :NAME "nickname" :ORG-NAME "nickname" :CS-COLL 255 :COLUMN-LENGTH 1020 :TYPE 253 :FLAGS 0 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "crm" :TABLE "account_profile" :ORG-TABLE "account_profile" :NAME "company" :ORG-NAME "company" :CS-COLL 255 :COLUMN-LENGTH 1020 :TYPE 253 :FLAGS 4097 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "crm" :TABLE "account_profile" :ORG-TABLE "account_profile" :NAME "dob" :ORG-NAME "dob" :CS-COLL 63 :COLUMN-LENGTH 10 :TYPE 10 :FLAGS 128 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "crm" :TABLE "account_profile" :ORG-TABLE "account_profile" :NAME "user_id" :ORG-NAME "user_id" :CS-COLL 63 :COLUMN-LENGTH 11 :TYPE 3 :FLAGS 20485 :DECIMALS 0 :DEFAULT-VALUE NIL)) :AS-TEXT T :RESULT-TYPE VECTOR)
10: (QMYND-IMPL:SEND-COMMAND-QUERY "SELECT `id`, `nickname`, `company`, `dob`, `user_id` FROM `account_profile`" :ROW-FN #<CLOSURE (LAMBDA (PGLOADER.LOAD::ROW) :IN PGLOADER.LOAD:QUEUE-RAW-DATA) {1009370BAB}> :AS-TEXT T :RESULT-TYPE VECTOR)
11: (QMYND:MYSQL-QUERY #<QMYND-IMPL::MYSQL-INET-CONNECTION {100623BA93}> "SELECT `id`, `nickname`, `company`, `dob`, `user_id` FROM `account_profile`" :ROW-FN #<CLOSURE (LAMBDA (PGLOADER.LOAD::ROW) :IN PGLOADER.LOAD:QUEUE-RAW-DATA) {1009370BAB}> :AS-TEXT T :RESULT-TYPE VECTOR)
12: (PGLOADER.SOURCE.MYSQL::CALL-WITH-ENCODING-HANDLER #<PGLOADER.SOURCE.MYSQL:COPY-MYSQL {100911ECD3}> "account_profile" #<CLOSURE (LAMBDA NIL :IN PGLOADER.SOURCES:MAP-ROWS) {10066CDF4B}>)
13: ((:METHOD PGLOADER.SOURCES:MAP-ROWS (PGLOADER.SOURCE.MYSQL:COPY-MYSQL)) #<PGLOADER.SOURCE.MYSQL:COPY-MYSQL {100911ECD3}> :PROCESS-ROW-FN #<CLOSURE (LAMBDA (PGLOADER.LOAD::ROW) :IN PGLOADER.LOAD:QUEUE-RAW-DATA) {1009370BAB}>) [fast-method]
14: ((:METHOD PGLOADER.LOAD:QUEUE-RAW-DATA (PGLOADER.SOURCES:COPY T T)) #<PGLOADER.SOURCE.MYSQL:COPY-MYSQL {100911ECD3}> #S(LPARALLEL.VECTOR-QUEUE:VECTOR-QUEUE :IMPL #S(LPARALLEL.VECTOR-QUEUE::RAW-QUEUE :DATA #(0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0... *I REMOVED A FREAKING BUNCH OF 0s*) :START 0 :COUNT 0) :LOCK #<SB-THREAD:MUTEX "Anonymous lock" (free)> :NOTIFY-PUSH NIL :NOTIFY-POP NIL) 1) [fast-method]
15: ((FLET #:BODY-FN584 :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK) #<unavailable lambda list>)
16: ((LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK) #<unavailable lambda list>)
17: (LPARALLEL.KERNEL::EXEC-TASK/WORKER #<unavailable lambda list>)
18: (LPARALLEL.KERNEL::WORKER-LOOP #<unavailable lambda list>)
19: (LPARALLEL.KERNEL::%CALL-WITH-TASK-HANDLER #<unavailable lambda list>)
20: ((LAMBDA NIL :IN LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT))
21: (LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT #<CLOSURE (LAMBDA NIL :IN LPARALLEL.KERNEL::ENTER-WORKER-LOOP) {1006E86CEB}> #<FUNCTION FUNCALL> #<LPARALLEL.KERNEL:KERNEL :NAME "lparallel" :WORKER-COUNT 4 :USE-CALLER NIL :ALIVE T :SPIN-COUNT 2000 {1007040983}> #S(LPARALLEL.KERNEL::WORKER :HANDSHAKE/FROM-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1="Anonymous lock" (free)> :CVAR #<SB-THREAD:WAITQUEUE #2=Anonymous condition variable {100950D303}>) :HANDSHAKE/TO-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1# (free)> :CVAR #<SB-THREAD:WAITQUEUE #2# {100950D363}>) :EXIT-NOTIFICATION #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1# (free)> :CVAR NIL) :THREAD #<SB-THREAD:THREAD "lparallel" RUNNING {1007041E63}> :RUNNING-CATEGORY :DEFAULT :INDEX 3 :TASKS #S(LPARALLEL.SPIN-QUEUE:SPIN-QUEUE :HEAD (LPARALLEL.SPIN-QUEUE::DUMMY (#<CLOSURE #3=(LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK) {1006240CCB}> . :DEFAULT) (#<CLOSURE #3# {10062421CB}> . :DEFAULT) (#<CLOSURE #3# {100624368B}> . :DEFAULT) (#<CLOSURE #3# {1006244B7B}> . :DEFAULT) (#<CLOSURE #3# {100624601B}> . :DEFAULT) (#<CLOSURE #3# {10062475CB}> . :DEFAULT) (#<CLOSURE #3# {100667861B}> . :DEFAULT) (#<CLOSURE #3# {1006679BDB}> . :DEFAULT) . #4=((#<CLOSURE #3# {100667B11B}> . :DEFAULT))) :TAIL #4#)))
22: ((LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-WORKER-THREAD))
23: ((LAMBDA NIL :IN BORDEAUX-THREADS::BINDING-DEFAULT-SPECIALS))
24: ((FLET #:WITHOUT-INTERRUPTS-BODY-1139 :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE))
25: ((FLET SB-THREAD::WITH-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE))
26: ((FLET #:WITHOUT-INTERRUPTS-BODY-359 :IN SB-THREAD::CALL-WITH-MUTEX))
27: (SB-THREAD::CALL-WITH-MUTEX #<CLOSURE (FLET SB-THREAD::WITH-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE) {7F8623DAED6B}> #<SB-THREAD:MUTEX "thread result lock" owner: #<SB-THREAD:THREAD "lparallel" RUNNING {1007041E63}>> NIL T NIL)
28: (SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE #<SB-THREAD:THREAD "lparallel" RUNNING {1007041E63}> NIL #<CLOSURE (LAMBDA NIL :IN BORDEAUX-THREADS::BINDING-DEFAULT-SPECIALS) {1007041E0B}> (#<SB-THREAD:THREAD #1="lparallel" RUNNING {1007041E63}> #<SB-THREAD:THREAD #1# RUNNING {1007041913}> #<SB-THREAD:THREAD #1# RUNNING {10070413C3}> #<SB-THREAD:THREAD #1# RUNNING {1007040E73}> #<SB-THREAD:THREAD #1# RUNNING {100616C393}> #<SB-THREAD:THREAD "main thread" RUNNING {1006115F63}>) NIL NIL NIL NIL)
29: ("foreign function: call_into_lisp")
30: ("foreign function: new_thread_trampoline")

Meowzz95 on 27 May 2019

The line of interest in the backtrace is the following, where we learn that the MySQL driver used by pgloader doesn't know about the encoding of your database:

6: (QMYND-IMPL::MYSQL-CS-COLL-TO-CHARACTER-ENCODING 255)

See https://github.com/qitab/qmynd/blob/master/src/common/charsets.lisp for a list of the encodings that are supported. We have an open PR for the driver that fixes this problem: https://github.com/qitab/qmynd/pull/11. Can you build pgloader with this branch of the driver to test that it's all ok?

By doing the following, you should have a pgloader binary to play with that includes a fix to this issue, and once you've confirmed that the encoding specification is ok, I will be able to merge the PR for the qmynd driver!

$ cd /path/to/pgloader/sources
$ cd build/quicklisp/local-projects/
$ rm -rf qmynd
$ git clone https://github.com/cristianbica/qmynd
$ cd /path/to/pgloader/sources
$ make save

dimitri on 27 May 2019

👍1

I will try this although I’ve moved the data using Django’s dump and load data. Will let you know once I have a result. Thanks. Btw I guess I know nothing about lisp haha the log looks like cipher text to me :)

Meowzz95 on 28 May 2019

In the previous comment, I mentioned I can build from source, that was built from the bundle provided in Github release page... But now I'm trying to build from
git clone https://github.com/dimitri/pgloader.git. It seems that I can't successfully build in Ubuntu 18.04 nor Debian 9...

The following error message is obtained from the building process in Debian 9.

Sorry, I don't know how to copy from the terminal in Debian... I just installed it in VM...

Meowzz95 on 28 May 2019

Oops, right click is the trick...

[package nibbles].................................
[package ironclad]................................
[package ironclad-vm]...................
debugger invoked on a SB-INT:SIMPLE-PROGRAM-ERROR in thread
#<THREAD "main thread" RUNNING {1001E06FA3}>:
  invalid number of arguments: 5

Type HELP for debugger help, or (SB-EXT:EXIT) to exit from SBCL.

restarts (invokable by number or by possibly-abbreviated name):
  0: [RETRY                        ] Retry
                                     compiling #<IRONCLAD-SOURCE-FILE "ironclad" "src" "common">.
  1: [ACCEPT                       ] Continue, treating
                                     compiling #<IRONCLAD-SOURCE-FILE "ironclad" "src" "common">
                                     as having been successful.
  2:                                 Retry ASDF operation.
  3: [CLEAR-CONFIGURATION-AND-RETRY] Retry ASDF operation after resetting the
                                     configuration.
  4: [ABORT                        ] Give up on "pgloader"
  5: [CONTINUE                     ] Ignore runtime option --eval "(ql:quickload \"pgloader\")".
  6:                                 Skip rest of --eval and --load options.
  7:                                 Skip to toplevel READ/EVAL/PRINT loop.
  8: [EXIT                         ] Exit SBCL (calling #'EXIT, killing the process).

(SB-X86-64-ASM::|movzx| #<SB-ASSEM:SEGMENT :TYPE :REGULAR> #<SB-C::VOP :INFO UB32REF/LE :ARGS #<SB-C:TN-REF :TN #<SB-C:TN INPUT-BLOCK1!1[RDI]> :WRITE-P NIL :VOP UB32REF/LE> :RESULTS #<SB-C:TN-REF :TN #<SB-C:TN t2[RDX]> :WRITE-P T :VOP UB32REF/LE>> (:DWORD :QWORD) #<SB-C:TN t2[RDX]> #<SB-X86-64-ASM::EA :DWORD base=#<SB-C:TN INPUT-BLOCK1!1[RDI]> index=#<SB-C:TN t3[RDX]> disp=1>) [tl,external]
0]

Meowzz95 on 28 May 2019

I don't see useful information in the build log(maybe I can't get it, I know nothing about lisp)

If you need more debug info, let me know :)

Meowzz95 on 28 May 2019

It's a well known error so I think we have enough information for me to recognise it: your SBCL is too old, please grab a more recent version.

dimitri on 28 May 2019

Yes, I've confirmed replacing qmynd solves the problem. I can copy the whole DB using the new built binary. Thanks.

Meowzz95 on 29 May 2019

Thanks! now can you compare several data fields in both source and target database and make sure that the encoding conversion makes sense and didn't damage your data? IOW, it is the same text in MySQL and in Postgres after pgloader's migration is done?

dimitri on 29 May 2019

@di

Meowzz95 on 29 May 2019

@dimitri
Are you able to tell which table was causing the problem from the full log that I've sent you via email? So that I can carefully check that particular table.

Meowzz95 on 29 May 2019

From the following entry in the backtrace I appears to be column nickname from table account_profile that did target the error first. I would suppose that it's just the first text column that pgloader has to work on in your case though?

(QMYND-IMPL::PARSE-TEXT-PROTOCOL-RESULT-COLUMN-AS-TEXT #() #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "crm" :TABLE "account_profile" :ORG-TABLE "account_profile" :NAME "nickname" :ORG-NAME "nickname" :CS-COLL 255 :COLUMN-LENGTH 1020 :TYPE 253 :FLAGS 0 :DECIMALS 0 :DEFAULT-VALUE NIL))

dimitri on 29 May 2019

For nickname in account_profile
Both the source and dest table have empty values which are correct.

For other tables, the content looks the same too.
I've specifically checked some columns with Chinese characters, they are all good.

Source