search

dimitri / pgloader

Migrate to PostgreSQL in a single command!
http://pgloader.io
Other
5.49k stars 551 forks source link

MySQL: Access denied [1045], but mysql or mysqldump work fine #996

Open jbauers opened 5 years ago

jbauers commented 5 years ago

Hey! We're running pgloader on one host against multiple MySQL databases on different target hosts (not a localhost/127.0.0.1 issue). Against one of the target hosts, we see the following error:

FATAL error: Failed to connect to mysql at "<M_HOST>" (port <M_PORT>) as user "<M_USER>": MySQL Error [1045]: "Access denied for user '<M_USER>'@'<S_IP>' (using password: YES)"

From the same host, with the same credentials, the following works however:

MYSQL_PWD=<M_PASS> mysql -u <M_USER> -h <M_HOST>

So does mysqldump. Tried running pgloader both Docker/non-Docker:

On host (Linux 4.15.0-48-generic #51-Ubuntu 16.04):

pgloader version "3.5.2"
compiled with SBCL 1.4.5.debian

On host inside Docker container, w/ and w/o --net=host (Linux 4.9.125-linuxkit debian:stable-slim):

WARNING:
Couldn't re-execute SBCL with proper personality flags (/proc isn't mounted? setuid?)
Trying to continue anyway.
pgloader version "3.6.26cc9ca"
compiled with SBCL 1.3.14.debian

The error is the same. Credentials don't have any characters that need to be escaped. Checked settings server-side and they should allow the connection. Could you give me a pointer? Not sure what else I could try, it seems quite weird (and the other 3 hosts/DBs work perfectly). Target MySQL:

Server version: 5.7.26-0ubuntu0.16.04.1-log (Ubuntu)

Here's the (redacted) command with --debug:

pgloader --debug mysql://<M_USER>:<M_PASS>@<M_HOST>:<M_PORT>/<M_DB> pgsql://<P_USER>:<P_PASS>@<P_HOST>:<P_PORT>/<P_DB>
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2019-07-04T13:33:35.015000Z NOTICE Starting pgloader, log system is ready.
2019-07-04T13:33:35.050000Z INFO Starting monitor
2019-07-04T13:33:35.052000Z INFO     SOURCE: "mysql://<M_USER>:<M_PASS>@<M_HOST>:<M_PORT>/<M_DB>"
2019-07-04T13:33:35.052000Z INFO SOURCE URI: #<PGLOADER.SOURCE.MYSQL:MYSQL-CONNECTION mysql://<M_USER>@<M_HOST>:<M_PORT>/<M_DB> {1005806423}>
2019-07-04T13:33:35.052000Z INFO     TARGET: "pgsql://<P_USER>:<P_PASS>@<P_HOST>:<P_PORT>/<P_DB>"
2019-07-04T13:33:35.052000Z INFO TARGET URI: #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://<P_USER>@<P_HOST>:<P_PORT>/<P_DB> {1005A5D1C3}>
2019-07-04T13:33:35.052000Z DEBUG LOAD DATA FROM #<PGLOADER.SOURCE.MYSQL:MYSQL-CONNECTION mysql://<M_USER>@<M_HOST>:<M_PORT>/<M_DB> {1005806423}>
2019-07-04T13:33:35.071000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://<P_USER>@<P_HOST>:<P_PORT>/<P_DB> {1005A5D1C3}>
2019-07-04T13:33:35.071000Z DEBUG SET client_encoding TO 'utf8'
2019-07-04T13:33:35.071000Z DEBUG SET application_name TO 'pgloader'
2019-07-04T13:33:35.080000Z LOG Migrating from #<MYSQL-CONNECTION mysql://<M_USER>@<M_HOST>:<M_PORT>/<M_DB> {1005806423}>
2019-07-04T13:33:35.080000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://<P_USER>@<P_HOST>:<P_PORT>/<P_DB> {1005A5D1C3}>
KABOOM!
FATAL error: Failed to connect to mysql at "<M_HOST>" (port <M_PORT>) as user "<M_USER>": MySQL Error [1045]: "Access denied for user '<M_USER>'@'<S_IP>' (using password: YES)"
Date/time: 2019-07-04-15:33An unhandled error condition has been signalled:
                              Failed to connect to mysql at "<M_HOST>" (port <M_PORT>) as user "<M_USER>": MySQL Error [1045]: "Access denied for user '<M_USER>'@'<S_IP>' (using password: YES)"

Backtrace for: #<SB-THREAD:THREAD "main thread" RUNNING {10006305B3}>
0: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE-TO-STREAM #<SB-IMPL::STRING-OUTPUT-STREAM {1006D79163}>)
1: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE #<PGLOADER.CONNECTION:DB-CONNECTION-ERROR {1006D78FD3}> :OUTPUT NIL :IF-EXISTS :APPEND :VERBOSE NIL)
2: ((FLET "H0" :IN PGLOADER::MAIN) #<PGLOADER.CONNECTION:DB-CONNECTION-ERROR {1006D78FD3}>)
3: (SB-KERNEL::%SIGNAL #<PGLOADER.CONNECTION:DB-CONNECTION-ERROR {1006D78FD3}>)
4: (ERROR PGLOADER.CONNECTION:DB-CONNECTION-ERROR :MESG "MySQL Error [1045]: \"Access denied for user '<M_USER>'@'<S_IP>' (using password: YES)\"" :TYPE "mysql" :HOST "<M_HOST>" :PORT <M_PORT> :USER "<M_USER>")
5: ((:METHOD PGLOADER.SOURCES:FETCH-METADATA (PGLOADER.SOURCE.MYSQL:COPY-MYSQL PGLOADER.CATALOG:CATALOG)) #<PGLOADER.SOURCE.MYSQL:COPY-MYSQL {10064FBC73}> #1=#S(PGLOADER.CATALOG:CATALOG :NAME #2="<M_DB>" :SCHEMA-LIST (#S(PGLOADER.CATALOG:SCHEMA :SOURCE-NAME #2# :NAME "<M_DB>" :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]
6: ((:METHOD PGLOADER.LOAD:COPY-DATABASE (PGLOADER.SOURCES:DB-COPY)) #<PGLOADER.SOURCE.MYSQL:COPY-MYSQL {10064FBC73}> :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]
7: ((LAMBDA ()))
8: (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 #17# :ON-UPDATE-CURRENT-TIMESTAMP T :NOT-NULL T) :TARGET (:TYPE #19# :DROP-DEFAULT T :DROP-NOT-NULL 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 #20# :ON-UPDATE-CURRENT-TIMESTAMP T :NOT-NULL T) :TARGET (:TYPE #19# :DROP-DEFAULT T :DROP-NOT-NULL 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 pgsql://<P_USER>@<P_HOST>:<P_PORT>/<P_DB> {1005A5D1C3}>)) (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 mysql://<M_USER>@<M_HOST>:<M_PORT>/<M_DB> {1005806423}>))) 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)
9: (PGLOADER:LOAD-DATA :FROM #<PGLOADER.SOURCE.MYSQL:MYSQL-CONNECTION mysql://<M_USER>@<M_HOST>:<M_PORT>/<M_DB> {1005806423}> :INTO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://<P_USER>@<P_HOST>:<P_PORT>/<P_DB> {1005A5D1C3}> :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)
10: (PGLOADER.MONITOR::CALL-WITH-MONITOR #<CLOSURE (LAMBDA NIL :IN PGLOADER::MAIN) {1005506E9B}>)
11: (PGLOADER::MAIN ("pgloader" "--debug" "mysql://<M_USER>:<M_PASS>@<M_HOST>:<M_PORT>/<M_DB>" "pgsql://<P_USER>:<P_PASS>@<P_HOST>:<P_PORT>/<P_DB>"))
12: ((LAMBDA NIL :IN "/build/pgloader-Mx2SY9/pgloader-3.5.2/dumper-2SKVI5f7.lisp"))
13: ((FLET SB-UNIX::BODY :IN SAVE-LISP-AND-DIE))
14: ((FLET "WITHOUT-INTERRUPTS-BODY-36" :IN SAVE-LISP-AND-DIE))
15: ((LABELS SB-IMPL::RESTART-LISP :IN SAVE-LISP-AND-DIE))

2019-07-04T13:33:35.169000Z INFO Stopping monitor

What I am doing here?

Failed to connect to mysql at "<M_HOST>" (port <M_PORT>) as user "<M_USER>": MySQL Error [1045]: "Access denied for user '<M_USER>'@'<S_IP>' (using password: YES)"

Any help would be greatly appreciated :)

dimitri commented 5 years ago

Hi!

I'm not sure what to add to your detailed report... can you check the MySQL server logs for those refused authentication attempts? After all it might still by a typo in the password field or something like this?

jdufresne commented 3 years ago

I'm experiencing the same issue. mysql CLI authenticates, mysqldump authenticates, but pgloader results in "Access denied".

The logs don't reveal anything interesting:

2021-10-21T01:21:02.657532Z 63641 [Note] Access denied for user <REDACTED> (using password: YES)

I am very willing to help debug this. If there is additional information I can provide please ask.

jdufresne commented 3 years ago

After spending some more time on this, it looks like pgloader fails for users with REQUIRE SSL as described in the MySQL docs: https://dev.mysql.com/doc/refman/5.7/en/alter-user.html

Some of my DBs had this, others did not. Only the ones that have it enabled fail with pgloader. Disabling it allows pgloader connections.

@jbauers to see if your user has this value set, you can run the query:

mysql> select user, ssl_type from mysql.user where ssl_type != '';

If I understand ticket #493 correctly, this MySQL connection feature may not be supported.