Pgloader: Fatal error from latest docker with: pgloader SOURCE TARGET
I'm trying to do a migration of a MySQL database to a Postgres database on the same server. I ran the basic usage of the command pgloader SOURCE TARGET using the docker image and am getting the following error. I have also tried with a self-compiled version, and am getting the same error.
I edited the command below slightly to remove sensitive user/password/database names.
Command and output:
$ docker run --rm --name pgloader dimitri/pgloader:latest pgloader 'mysql://user:pass@localhost/dbname?sslmode=disable' 'postgresql://user:pass@localhost/dbname' --debug
WARNING:
Couldn't re-execute SBCL with proper personality flags (/proc isn't mounted? setuid?)
Trying to continue anyway.
KABOOM!
FATAL error: can't find the TRUENAME of wild pathname /opt/src/pgloader/mysql:/anonymous:anonymous@localhost/django_prod?sslmode=disable(physicalized from mysql://anonymous:anonymous@localhost/django_prod?sslmode=disable).
An unhandled error condition has been signalled:
can't find the TRUENAME of wild pathname /opt/src/pgloader/mysql:/anonymous:anonymous@localhost/django_prod?sslmode=disable(physicalized from mysql://anonymous:anonymous@localhost/django_prod?sslmode=disable).
What I am doing here?
can't find the TRUENAME of wild pathname /opt/src/pgloader/mysql:/anonymous:anonymous@localhost/django_prod?sslmode=disable(physicalized from mysql://anonymous:anonymous@localhost/django_prod?sslmode=disable).
The docker version info:
$ docker run --rm --name pgloader dimitri/pgloader:latest pgloader --version
WARNING:
Couldn't re-execute SBCL with proper personality flags (/proc isn't mounted? setuid?)
Trying to continue anyway.
pgloader version "3.4.2~devel"
compiled with SBCL 1.3.14.debian
The self-compiled version info:
$ /usr/local/bin/pgloader --version
pgloader version "3.4.2~devel"
compiled with SBCL 1.4.0-1.el7
ddasilva
All 11 comments
@dimitri Anything? I imagine I am doing something pretty straightforwardly wrong but I can't see it
ddasilva
on 15 Mar 2018
From the logs, is it possible that you got the URL wrong, missing some slashes? Also, please report with --debug and --verbose so that we have more information about the parsing of the URL strings, which seems to be failing for you.
dimitri
on 16 Mar 2018
Thank you! I was able to get past this error message by removing sslmode=disable. I am now getting the following error message:
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2018-03-18T21:06:34.026000-04:00 NOTICE Starting pgloader, log system is ready.
2018-03-18T21:06:34.050000-04:00 INFO Starting monitor
2018-03-18T21:06:34.060000-04:00 INFO SOURCE: "<REMOVED>"
2018-03-18T21:06:34.061000-04:00 INFO SOURCE URI: #<PGLOADER.SOURCE.MYSQL:MYSQL-CONNECTION <REMOVED>
{100521D5A3}>
2018-03-18T21:06:34.061000-04:00 INFO TARGET: "<REMOVED>"
2018-03-18T21:06:34.061000-04:00 INFO TARGET URI: #<REMOVED>
2018-03-18T21:06:34.061000-04:00 DEBUG LOAD DATA FROM #<PGLOADER.SOURCE.MYSQL:MYSQL-CONNECTION <REMOVED>>
2018-03-18T21:06:34.081000-04:00 DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION <REMOVED>>
2018-03-18T21:06:34.081000-04:00 DEBUG SET client_encoding TO 'utf8'
2018-03-18T21:06:34.084000-04:00 DEBUG SET application_name TO 'pgloader'
2018-03-18T21:06:34.111000-04:00 LOG Migrating from #<MYSQL-CONNECTION <REMOVED>>
2018-03-18T21:06:34.111000-04:00 LOG Migrating into #<PGSQL-CONNECTION <REMOVED>>
2018-03-18T21:06:34.178000-04:00 DEBUG CONNECTED TO #<MYSQL-CONNECTION <REMOVED>>
2018-03-18T21:06:34.182000-04:00 SQL MySQL: sending query: -- params: db-name
-- table-type-name
-- only-tables
-- only-tables
-- including
-- filter-list-to-where-clause incuding
-- excluding
-- filter-list-to-where-clause excluding
select c.table_name, t.table_comment,
c.column_name, c.column_comment,
c.data_type, c.column_type, c.column_default,
c.is_nullable, c.extra
from information_schema.columns c
join information_schema.tables t using(table_schema, table_name)
where c.table_schema = 'django_prod' and t.table_type = 'BASE TABLE'
order by table_name, ordinal_position;
KABOOM!
FATAL error: 76 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 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).
Date/time: 2018-03-18-21:06An unhandled error condition has been signalled:
76 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 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 "main thread" RUNNING {10006585B3}>
0: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE-TO-STREAM #<SB-IMPL::STRING-OUTPUT-STREAM {1006D9CE83}>)
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: 76> :OUTPUT NIL :IF-EXISTS :APPEND :VERBOSE NIL)
2: ((FLET "H0" :IN PGLOADER::MAIN) #<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: 76>)
3: (SB-KERNEL::%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: 76>)
4: (ERROR SB-KERNEL:CASE-FAILURE :NAME ECASE :DATUM 76 :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 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 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))
5: (QMYND-IMPL::MYSQL-CS-COLL-TO-CHARACTER-ENCODING 76)
6: (QMYND-IMPL::PARSE-TEXT-PROTOCOL-RESULT-COLUMN-AS-TEXT #(105 100) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "" :TABLE "c" :ORG-TABLE "" :NAME "COLUMN_NAME" :ORG-NAME "COLUMN_NAME" :CS-COLL 76 :COLUMN-LENGTH 192 :TYPE 253 :FLAGS 0 :DECIMALS 0 :DEFAULT-VALUE NIL))
7: (QMYND-IMPL::PARSE-RESULTSET-ROW-AS-TEXT-LIST 9 #<unavailable argument>)
8: (QMYND-IMPL:PARSE-RESULTSET-ROWS 9 #(#S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "mysql" :TABLE "c" :ORG-TABLE "tables" :NAME "TABLE_NAME" :ORG-NAME "TABLE_NAME" :CS-COLL 83 :COLUMN-LENGTH 192 :TYPE 253 :FLAGS 4225 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "" :TABLE "t" :ORG-TABLE "" :NAME "TABLE_COMMENT" :ORG-NAME "TABLE_COMMENT" :CS-COLL 63 :COLUMN-LENGTH 256 :TYPE 253 :FLAGS 128 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "" :TABLE "c" :ORG-TABLE "" :NAME "COLUMN_NAME" :ORG-NAME "COLUMN_NAME" :CS-COLL 76 :COLUMN-LENGTH 192 :TYPE 253 :FLAGS 0 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "" :TABLE "c" :ORG-TABLE "" :NAME "COLUMN_COMMENT" :ORG-NAME "COLUMN_COMMENT" :CS-COLL 83 :COLUMN-LENGTH 6144 :TYPE 252 :FLAGS 145 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "" :TABLE "c" :ORG-TABLE "" :NAME "DATA_TYPE" :ORG-NAME "DATA_TYPE" :CS-COLL 83 :COLUMN-LENGTH 50331645 :TYPE 252 :FLAGS 144 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "mysql" :TABLE "c" :ORG-TABLE "columns" :NAME "COLUMN_TYPE" :ORG-NAME "COLUMN_TYPE" :CS-COLL 83 :COLUMN-LENGTH 16777215 :TYPE 252 :FLAGS 4241 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "mysql" :TABLE "c" :ORG-TABLE "columns" :NAME "COLUMN_DEFAULT" :ORG-NAME "COLUMN_DEFAULT" :CS-COLL 83 :COLUMN-LENGTH 65535 :TYPE 252 :FLAGS 144 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "" :TABLE "c" :ORG-TABLE "" :NAME "IS_NULLABLE" :ORG-NAME "IS_NULLABLE" :CS-COLL 8 :COLUMN-LENGTH 3 :TYPE 253 :FLAGS 1 :DECIMALS 0 :DEFAULT-VALUE NIL) #S(QMYND-IMPL::COLUMN-DEFINITION-V41-PACKET :CATALOG "def" :SCHEMA "" :TABLE "c" :ORG-TABLE "" :NAME "EXTRA" :ORG-NAME "EXTRA" :CS-COLL 83 :COLUMN-LENGTH 171 :TYPE 253 :FLAGS 128 :DECIMALS 0 :DEFAULT-VALUE NIL)) :AS-TEXT T :RESULT-TYPE LIST)
9: (QMYND-IMPL:SEND-COMMAND-QUERY #<(SIMPLE-BASE-STRING 653) -- params: db-name
-- table-type-name
-- only-tables
-- only-tables
-- including
-- filter-list-to-where-clause incuding
-- excluding
-- filter-... {1006CA574F}> :ROW-FN NIL :AS-TEXT T :RESULT-TYPE LIST)
10: (QMYND:MYSQL-QUERY #<QMYND-IMPL::MYSQL-INET-CONNECTION {100644B313}> #<(SIMPLE-BASE-STRING 653) -- params: db-name
-- table-type-name
-- only-tables
-- only-tables
-- including
-- filter-list-to-where-clause incuding
-- excluding
-- filter-... {1006CA574F}> :ROW-FN NIL :AS-TEXT T :RESULT-TYPE LIST)
11: (PGLOADER.SOURCE.MYSQL::LIST-ALL-COLUMNS #1=#S(PGLOADER.CATALOG:SCHEMA :SOURCE-NAME #2="django_prod" :NAME "django_prod" :CATALOG #S(PGLOADER.CATALOG:CATALOG :NAME #2# :SCHEMA-LIST (#1#) :TYPES-WITHOUT-BTREE NIL) :TABLE-LIST NIL :VIEW-LIST NIL :IN-SEARCH-PATH T) :TABLE-TYPE :TABLE :ONLY-TABLES NIL :INCLUDING NIL :EXCLUDING NIL)
12: ((:METHOD PGLOADER.SOURCES:FETCH-METADATA (PGLOADER.SOURCE.MYSQL:COPY-MYSQL PGLOADER.CATALOG:CATALOG)) #<PGLOADER.SOURCE.MYSQL:COPY-MYSQL {1005EBC193}> #1=#S(PGLOADER.CATALOG:CATALOG :NAME #2="django_prod" :SCHEMA-LIST (#S(PGLOADER.CATALOG:SCHEMA :SOURCE-NAME #2# :NAME "django_prod" :CATALOG #1# :TABLE-LIST NIL :VIEW-LIST NIL :IN-SEARCH-PATH T)) :TYPES-WITHOUT-BTREE NIL) :MATERIALIZE-VIEWS NIL :ONLY-TABLES NIL :CREATE-INDEXES T :FOREIGN-KEYS T :INCLUDING NIL :EXCLUDING NIL) [fast-method]
13: ((:METHOD PGLOADER.LOAD:COPY-DATABASE (PGLOADER.SOURCES:DB-COPY)) #<PGLOADER.SOURCE.MYSQL:COPY-MYSQL {1005EBC193}> :ON-ERROR-STOP T :WORKER-COUNT 4 :CONCURRENCY 1 :MULTIPLE-READERS NIL :MAX-PARALLEL-CREATE-INDEX NIL :TRUNCATE NIL :DISABLE-TRIGGERS NIL :DATA-ONLY NIL :SCHEMA-ONLY NIL :CREATE-SCHEMAS T :CREATE-TABLES T :INCLUDE-DROP T :DROP-SCHEMA NIL :CREATE-INDEXES T :INDEX-NAMES :UNIQUIFY :RESET-SEQUENCES T :FOREIGN-KEYS T :REINDEX NIL :ONLY-TABLES NIL :INCLUDING NIL :EXCLUDING NIL :SET-TABLE-OIDS T :ALTER-TABLE NIL :ALTER-SCHEMA NIL :MATERIALIZE-VIEWS NIL) [fast-method]
14: ((LAMBDA ()))
15: (PGLOADER:RUN-COMMANDS (LAMBDA NIL (LET* ((PGLOADER.SOURCES:*DEFAULT-CAST-RULES* (QUOTE ((:SOURCE (:TYPE #1="int" :AUTO-INCREMENT T :TYPEMOD (< PGLOADER.TRANSFORMS:PRECISION 10)) :TARGET (:TYPE #2="serial")) (:SOURCE (:TYPE #1# :AUTO-INCREMENT T :TYPEMOD (<= 10 PGLOADER.TRANSFORMS:PRECISION)) :TARGET (:TYPE #3="bigserial")) (:SOURCE (:TYPE #1# :AUTO-INCREMENT NIL :TYPEMOD (< PGLOADER.TRANSFORMS:PRECISION 10)) :TARGET (:TYPE #1#)) (:SOURCE (:TYPE #1# :AUTO-INCREMENT NIL :TYPEMOD (<= 10 PGLOADER.TRANSFORMS:PRECISION)) :TARGET (:TYPE #4="bigint")) (:SOURCE (:TYPE #5="tinyint" :AUTO-INCREMENT T) :TARGET (:TYPE #2#)) (:SOURCE (:TYPE #6="smallint" :AUTO-INCREMENT T) :TARGET (:TYPE #2#)) (:SOURCE (:TYPE #7="mediumint" :AUTO-INCREMENT T) :TARGET (:TYPE #2#)) (:SOURCE (:TYPE #4# :AUTO-INCREMENT T) :TARGET (:TYPE #3#)) (:SOURCE (:TYPE #5# :TYPEMOD (= 1 PGLOADER.TRANSFORMS:PRECISION)) :TARGET (:TYPE #8="boolean" :DROP-TYPEMOD T) :USING PGLOADER.TRANSFORMS::TINYINT-TO-BOOLEAN) (:SOURCE (:TYPE "bit" :TYPEMOD (= 1 PGLOADER.TRANSFORMS:PRECISION)) :TARGET (:TYPE #8# :DROP-TYPEMOD T) :USING PGLOADER.TRANSFORMS::BITS-TO-BOOLEAN) (:SOURCE (:TYPE #4# :TYPEMOD (< 19 PGLOADER.TRANSFORMS:PRECISION)) :TARGET (:TYPE #9="numeric" :DROP-TYPEMOD T)) (:SOURCE (:TYPE #5# :UNSIGNED T) :TARGET (:TYPE #6# :DROP-TYPEMOD T)) (:SOURCE (:TYPE #6# :UNSIGNED T) :TARGET (:TYPE #10="integer" :DROP-TYPEMOD T)) (:SOURCE (:TYPE #7# :UNSIGNED T) :TARGET (:TYPE #10# :DROP-TYPEMOD T)) (:SOURCE (:TYPE #10# :UNSIGNED T) :TARGET (:TYPE #4# :DROP-TYPEMOD T)) (:SOURCE (:TYPE #5#) :TARGET (:TYPE #6# :DROP-TYPEMOD T)) (:SOURCE (:TYPE #6#) :TARGET (:TYPE #6# :DROP-TYPEMOD T)) (:SOURCE (:TYPE #7#) :TARGET (:TYPE #10# :DROP-TYPEMOD T)) (:SOURCE (:TYPE #10#) :TARGET (:TYPE #10# :DROP-TYPEMOD T)) (:SOURCE (:TYPE #11="float") :TARGET (:TYPE #11# :DROP-TYPEMOD T)) (:SOURCE (:TYPE #4#) :TARGET (:TYPE #4# :DROP-TYPEMOD T)) (:SOURCE (:TYPE "double") :TARGET (:TYPE "double precision" :DROP-TYPEMOD T)) (:SOURCE (:TYPE #9#) :TARGET (:TYPE #9# :DROP-TYPEMOD NIL)) (:SOURCE (:TYPE #12="decimal") :TARGET (:TYPE #12# :DROP-TYPEMOD NIL)) (:SOURCE (:TYPE "tinytext") :TARGET (:TYPE #13="text") :USING PGLOADER.TRANSFORMS::REMOVE-NULL-CHARACTERS) (:SOURCE (:TYPE #13#) :TARGET (:TYPE #13#) :USING PGLOADER.TRANSFORMS::REMOVE-NULL-CHARACTERS) (:SOURCE (:TYPE "mediumtext") :TARGET (:TYPE #13#) :USING PGLOADER.TRANSFORMS::REMOVE-NULL-CHARACTERS) (:SOURCE (:TYPE "longtext") :TARGET (:TYPE #13#) :USING PGLOADER.TRANSFORMS::REMOVE-NULL-CHARACTERS) (:SOURCE (:TYPE #14="varchar") :TARGET (:TYPE #14# :DROP-TYPEMOD NIL) :USING PGLOADER.TRANSFORMS::REMOVE-NULL-CHARACTERS) (:SOURCE (:TYPE #15="char") :TARGET (:TYPE #15# :DROP-TYPEMOD NIL) :USING PGLOADER.TRANSFORMS::REMOVE-NULL-CHARACTERS) (:SOURCE (:TYPE "binary") :TARGET (:TYPE #16="bytea") :USING PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA) (:SOURCE (:TYPE "varbinary") :TARGET (:TYPE #16#) :USING PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA) (:SOURCE (:TYPE "tinyblob") :TARGET (:TYPE #16#) :USING PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA) (:SOURCE (:TYPE "blob") :TARGET (:TYPE #16#) :USING PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA) (:SOURCE (:TYPE "mediumblob") :TARGET (:TYPE #16#) :USING PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA) (:SOURCE (:TYPE "longblob") :TARGET (:TYPE #16#) :USING PGLOADER.TRANSFORMS::BYTE-VECTOR-TO-BYTEA) (:SOURCE (:TYPE #17="datetime" :DEFAULT #18="0000-00-00 00:00:00" :NOT-NULL T) :TARGET (:TYPE #19="timestamptz" :DROP-DEFAULT T :DROP-NOT-NULL T) :USING PGLOADER.TRANSFORMS::ZERO-DATES-TO-NULL) (:SOURCE (:TYPE #17# :DEFAULT #18#) :TARGET (:TYPE #19# :DROP-DEFAULT T) :USING PGLOADER.TRANSFORMS::ZERO-DATES-TO-NULL) (:SOURCE (:TYPE #20="timestamp" :DEFAULT #18# :NOT-NULL T) :TARGET (:TYPE #19# :DROP-DEFAULT T :DROP-NOT-NULL T) :USING PGLOADER.TRANSFORMS::ZERO-DATES-TO-NULL) (:SOURCE (:TYPE #20# :DEFAULT #18#) :TARGET (:TYPE #19# :DROP-DEFAULT T) :USING PGLOADER.TRANSFORMS::ZERO-DATES-TO-NULL) (:SOURCE (:TYPE #21="date" :DEFAULT "0000-00-00") :TARGET (:TYPE #21# :DROP-DEFAULT T) :USING PGLOADER.TRANSFORMS::ZERO-DATES-TO-NULL) (:SOURCE (:TYPE #21#) :TARGET (:TYPE #21#)) (:SOURCE (:TYPE "year") :TARGET (:TYPE #10# :DROP-TYPEMOD T)) (:SOURCE (:TYPE #17#) :TARGET (:TYPE #19#) :USING PGLOADER.TRANSFORMS::ZERO-DATES-TO-NULL) (:SOURCE (:TYPE #20#) :TARGET (:TYPE #19#) :USING PGLOADER.TRANSFORMS::ZERO-DATES-TO-NULL) (:SOURCE (:TYPE "enum") :TARGET (:TYPE #22=#<FUNCTION PGLOADER.SOURCE.MYSQL::ENUM-OR-SET-NAME>)) (:SOURCE (:TYPE "set") :TARGET (:TYPE #22#) :USING PGLOADER.TRANSFORMS::SET-TO-ENUM-ARRAY) (:SOURCE (:TYPE "geometry") :TARGET (:TYPE #23="point") :USING PGLOADER.TRANSFORMS::CONVERT-MYSQL-POINT) (:SOURCE (:TYPE #23#) :TARGET (:TYPE #23#) :USING PGLOADER.TRANSFORMS::CONVERT-MYSQL-POINT) (:SOURCE (:TYPE "linestring") :TARGET (:TYPE "path") :USING PGLOADER.TRANSFORMS::CONVERT-MYSQL-LINESTRING)))) (PGLOADER.SOURCES:*CAST-RULES* (QUOTE NIL)) (PGLOADER.SOURCE.MYSQL:*DECODING-AS* (QUOTE NIL)) (PGLOADER.PARAMS:*MYSQL-SETTINGS* (QUOTE NIL)) (PGLOADER.PARAMS:ON-ERROR-STOP (GETF (QUOTE NIL) :ON-ERROR-STOP T)) (PGLOADER.PARAMS:*PG-SETTINGS* (PGLOADER.PGSQL:SANITIZE-USER-GUCS (QUOTE NIL))) (PGLOADER.PARAMS:*PGSQL-RESERVED-KEYWORDS* (PGLOADER.PGSQL:LIST-RESERVED-KEYWORDS #24=#<PGLOADER.PGSQL:PGSQL-CONNECTION <REMOVED> {1005448493}>)) (PGLOADER.PARAMS:*COPY-BATCH-ROWS* (OR NIL PGLOADER.PARAMS:*COPY-BATCH-ROWS*)) (PGLOADER.PARAMS:*COPY-BATCH-SIZE* (OR NIL PGLOADER.PARAMS:*COPY-BATCH-SIZE*)) (PGLOADER.PARAMS:*PREFETCH-ROWS* (OR NIL PGLOADER.PARAMS:*PREFETCH-ROWS*)) (PGLOADER.PARAMS:*ROWS-PER-RANGE* (OR NIL PGLOADER.PARAMS:*ROWS-PER-RANGE*)) (PGLOADER.PARAMS:*IDENTIFIER-CASE* (OR NIL PGLOADER.PARAMS:*IDENTIFIER-CASE*)) (PGLOADER.PARSER::SOURCE (MAKE-INSTANCE (QUOTE PGLOADER.SOURCE.MYSQL:COPY-MYSQL) :TARGET-DB #24# :SOURCE-DB #<PGLOADER.SOURCE.MYSQL:MYSQL-CONNECTION <REMOVED>))) NIL (PGLOADER.LOAD:COPY-DATABASE PGLOADER.PARSER::SOURCE :INCLUDING (QUOTE NIL) :EXCLUDING (QUOTE NIL) :MATERIALIZE-VIEWS (QUOTE NIL) :ALTER-TABLE (QUOTE NIL) :ALTER-SCHEMA (QUOTE NIL) :SET-TABLE-OIDS T :ON-ERROR-STOP PGLOADER.PARAMS:ON-ERROR-STOP) NIL)) :START-LOGGER NIL :FLUSH-SUMMARY T :SUMMARY NIL :LOG-FILENAME NIL :LOG-MIN-MESSAGES NIL :CLIENT-MIN-MESSAGES NIL)
16: (PGLOADER:LOAD-DATA :FROM #<PGLOADER.SOURCE.MYSQL:MYSQL-CONNECTION <REMOVED> :INTO #<PGLOADER.PGSQL:PGSQL-CONNECTION <REMOVED> :ENCODING :UTF-8 :FIELDS NIL :TARGET-TABLE-NAME NIL :OPTIONS NIL :GUCS NIL :CASTS NIL :BEFORE NIL :AFTER NIL :START-LOGGER NIL :FLUSH-SUMMARY T)
17: (PGLOADER.MONITOR::CALL-WITH-MONITOR #<CLOSURE (LAMBDA NIL :IN PGLOADER::MAIN) {1004F1323B}>)
18: (PGLOADER::MAIN ("./pgloader" "--debug" "--verbose" "<REMOVED>" "<REMOVED>))
19: ((LAMBDA NIL :IN "/home/daniel/pgloader/dumper-2SKVI5f7.lisp"))
20: ((FLET SB-UNIX::BODY :IN SAVE-LISP-AND-DIE))
21: ((FLET "WITHOUT-INTERRUPTS-BODY-36" :IN SAVE-LISP-AND-DIE))
22: ((LABELS SB-IMPL::RESTART-LISP :IN SAVE-LISP-AND-DIE))
2018-03-18T21:06:34.216000-04:00 INFO Stopping monitor
What I am doing here?
76 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 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).
I guess that the character encoding you're using for MySQL isn't known to the driver. What I see in the sources is the following (from https://github.com/qitab/qmynd/blob/master/src/common/charsets.lisp#L97). Do you have a way to either not use that charset or help us define it for the MySQL driver?
(defconstant +mysql-cs-coll-koi8u-binary+ 75)
;;; 76 is NOT USED
(defconstant +mysql-cs-coll-latin2-binary+ 77)
@dimitri I just went ahead and converted everything to ascii from latin1 in the MySQL table, but I am still getting this error.
I'm not doing anything fancy with character encodings; I'm not storing any unicode data anywhere so if an odd character set got inserted it must have been the default somewhere.
ddasilva
on 19 Mar 2018
Any ideas? For reference, this query should prove that all the tables are ascii:
mysql> SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "django_dev";
+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
| ascii |
+--------------------+
33 rows in set (0.00 sec)
The error message complains about the column_name field having the unknown collation number 76. So that's not a user's table but a catalog table. Also, the fix might be in the MySQL connection/protocol parameters for the default collation to use. I'm not good enough at MySQL to know how to debug that, I hope those hints are helpful to you...
dimitri
on 20 Mar 2018
Thanks a ton @dimitri , I'll continue looking into this
ddasilva
on 20 Mar 2018
I got this to work. Here's what I did for future on-comers:
The new character set collation was added in MySQL 8.x, and and is not present in 5.7 (latest stable). As a workaround, I created a separate MySQL 5.7 server and transferred the data there using mysqldump. From there, pgloader was able to read the data just fine.
Thanks for your help @dimitri.
ddasilva
on 22 Mar 2018
👍3
Can you please add a link to MySQL documentation about those new (changed?) character set collations in MySQL 8.x so that I can see about fixing pgloader and qmynd? Thanks
dimitri
on 22 Mar 2018
@dimitri I can't find any mention of this in the documentation either way (supporting or against). I noticed this by spinning up an older mysql server in docker and comparing the collations, based on a hunch.
ddasilva
on 28 Mar 2018
❤1
Was this page helpful?
0 / 5 - 0 ratings
Related issues
benvest
路
8Comments
Jim-Robbins
路
3Comments
0xbzho
路
3Comments
ReinierNL
路
3Comments
aa8y
路
7Comments
Most helpful comment
I got this to work. Here's what I did for future on-comers:
The new character set collation was added in MySQL 8.x, and and is not present in 5.7 (latest stable). As a workaround, I created a separate MySQL 5.7 server and transferred the data there using
mysqldump. From there, pgloader was able to read the data just fine.Thanks for your help @dimitri.