Open alexsysm opened 4 years ago
10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
Huh. I am looking at list-reserved-keywords
at https://github.com/dimitri/pgloader/blob/689dd4a806c78dc93f7c1d40e5d08dd65a165644/src/pgsql/connection.lisp#L628 and see nothing related to this list of ECASE keys. I cannot see such an ECASE either in the pgloader source code.
The only thing I can see so far is that 1.4.0-1.el7
is an ancient SBCL version. Can you try the same with a newer one?
OK Thanks @phoe
as a matter of fact, i got work-arround solution of this. but I didn't look into inside pgloader logic.
I got a password just like this --> PassWd!10)
at first, I made pgloader <-> postgres section trusted. so I don't have to put any password while migrating. and I set a new password back on it. it's done.
I was thinking that those password characters are one of causes. and i doubt that sbcl matters of this.
anyway thank for your concern.
I ran into the same problem.
10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
➜ ~ psql postgresql://postgres:postgres@localhost/abc123 // THIS WORKS
➜ ~ pgloader mysql://root@localhost/abc123 postgresql://postgres:postgres@localhost/abc123 // THIS DOESN'T
Likewise; details are different, but the ECASE is the same. Wondering about version mismatch between the server (Postgres 12.4) and pgloader 3.6.2, which is the sort of thing that might generate this sort of obscure error, if, say, the server requests a password authentication method that this version of pgloader doesn't support. (Or some other protocol option or feature... FWIW, my server's expecting SCRAM-SHA-256, in case that's it, but it could well be something else.)
See also issue #1207 -- which is "add support for SCRAM-SHA-256". I guess that increases the odds that this is at the root of my issue here...
It was definitely the root of my problem. I switched back to Postgres 13.5 and everything was fine again.
It was definitely the root of my problem. I switched back to Postgres 13.5 and everything was fine again.
How to solve the problem of versions above 14?
It was definitely the root of my problem. I switched back to Postgres 13.5 and everything was fine again.
It was ok at 13.5
Also running into this problem with postgres version 14 but it works with postgres version 13. Looking at the parameter group (using RDS) password_encryption
defaults to md5
in version 13 but default blank in version 14... so i am assuming it uses scram-sha-256
so it would be great if this issue could be resolved:
I've run into this problem too. I was using postgresql 13 but I setup the scram-sha-256 for the security reason.
The mitigation steps are:
postgresql.conf
, find the line password_encryption = scram-sha-256
, change it to password_encryption = md5
.pg_hba.conf
, change all the scram-sha-256
to md5
for the listening address.\password import_user
.password_encryption
back to scram-sha-256
and authentication method back to scram-sha-256
in pg_hba.conf
and restart the database server.Hopefully the support for scram-sha-256
is coming.
I ran into the same problem.
10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
➜ ~ psql postgresql://postgres:postgres@localhost/abc123 // THIS WORKS
➜ ~ pgloader mysql://root@localhost/abc123 postgresql://postgres:postgres@localhost/abc123 // THIS DOESN'T
Same here! psql
seem to be working fine but pgloader
doesn't work :sweat:
I have a script which can run pgloader successfully from a host shell against a localhost db, but I get this ECASE error when I run the same script from a docker container against docker.for.mac.host.internal
. Is there something about Docker networking that is colliding with pgloader's network impl?
SCRAM authentication has been added to the PostgreSQL driver that pgloader uses a while back now (see https://github.com/marijnh/Postmodern/releases/tag/v1.30) ; so it should work. What version of pgloader are you using in docker where you have this error?
Can you try using the docker images provided at https://pgloader.readthedocs.io/en/latest/install.html#docker-images and see if those have the same problem?
Many thanks for responding.
SCRAM authentication has been added to the PostgreSQL driver that pgloader uses a while back now (see https://github.com/marijnh/Postmodern/releases/tag/v1.30) ; so it should work. What version of pgloader are you using in docker where you have this error?
I ran pgloader --version
from the container I built, and this is the output:
pgloader version "3.6.1"
compiled with SBCL 1.4.15.debian
Can you try using the docker images provided at https://pgloader.readthedocs.io/en/latest/install.html#docker-images and see if those have the same problem?
I tried the docker image ghcr.io/dimitri/pgloader:latest
(3.6.7-latest) on my M1 Mac, and I get this error log.
Please note: I was able to brew install pgloader
on my Mac, and I can run it successfully from the command line. It's just when Docker is involved that it doesn't work.
pgloader version "3.6.7" compiled with SBCL 2.2.6
same problem.
UPDATE i created an admin admin account password by changing password policy to low - didn't fix.
Could it be data / encoding problem - I wonder...
UPDATE 2 - I run it with -d - debug
UPDATE 3 - it says it failed to create schema - is there a way to bypass..?
8: ((FLET "BODY-FN0" :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK))
9: ((LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK))
10: (LPARALLEL.KERNEL::EXEC-TASK/WORKER #<unavailable argument> #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 NIL) :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 NIL) :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 {700E9F0003}> :RUNNING-CATEGORY :DEFAULT :INDEX 3 :TASKS #S(LPARALLEL.SPIN-QUEUE:SPIN-QUEUE :HEAD (LPARALLEL.SPIN-QUEUE::DUMMY (#<FUNCTION #2=(LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK) {700EB7C4BB}> . :DEFAULT) (#<FUNCTION #2# {700EB7C4EB}> . :DEFAULT) (#<FUNCTION #2# {700EB7C36B}> . :DEFAULT) (#<FUNCTION #2# {701112F37B}> . :DEFAULT) (#<FUNCTION #2# {701112F3AB}> . :DEFAULT) (#<FUNCTION #2# {701112F3DB}> . :DEFAULT) (#<FUNCTION #2# {701112F40B}> . :DEFAULT) (#<FUNCTION #2# {701112F43B}> . :DEFAULT) (#<FUNCTION #2# {701112F46B}> . :DEFAULT) (#<FUNCTION #2# {701112F49B}> . :DEFAULT) (#<FUNCTION #2# {701112F4CB}> . :DEFAULT) (#<FUNCTION #2# {701112F4FB}> . :DEFAULT) . #3=((#<FUNCTION #2# {701112F52B}> . :DEFAULT))) :TAIL #3#)))
11: (LPARALLEL.KERNEL::WORKER-LOOP #<LPARALLEL.KERNEL:KERNEL :NAME "lparallel" :WORKER-COUNT 4 :USE-CALLER NIL :ALIVE T :SPIN-COUNT 2000 {700E9D0023}> #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 NIL) :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 NIL) :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 {700E9F0003}> :RUNNING-CATEGORY :DEFAULT :INDEX 3 :TASKS #S(LPARALLEL.SPIN-QUEUE:SPIN-QUEUE :HEAD (LPARALLEL.SPIN-QUEUE::DUMMY (#<FUNCTION #2=(LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK) {700EB7C4BB}> . :DEFAULT) (#<FUNCTION #2# {700EB7C4EB}> . :DEFAULT) (#<FUNCTION #2# {700EB7C36B}> . :DEFAULT) (#<FUNCTION #2# {701112F37B}> . :DEFAULT) (#<FUNCTION #2# {701112F3AB}> . :DEFAULT) (#<FUNCTION #2# {701112F3DB}> . :DEFAULT) (#<FUNCTION #2# {701112F40B}> . :DEFAULT) (#<FUNCTION #2# {701112F43B}> . :DEFAULT) (#<FUNCTION #2# {701112F46B}> . :DEFAULT) (#<FUNCTION #2# {701112F49B}> . :DEFAULT) (#<FUNCTION #2# {701112F4CB}> . :DEFAULT) (#<FUNCTION #2# {701112F4FB}> . :DEFAULT) . #3=((#<FUNCTION #2# {701112F52B}> . :DEFAULT))) :TAIL #3#)))
12: (LPARALLEL.KERNEL::%CALL-WITH-TASK-HANDLER #<unavailable argument>)
13: ((LAMBDA NIL :IN LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT))
14: (LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT #<FUNCTION (LAMBDA NIL :IN LPARALLEL.KERNEL::ENTER-WORKER-LOOP) {700E9D00DB}> #<FUNCTION FUNCALL> #<LPARALLEL.KERNEL:KERNEL :NAME "lparallel" :WORKER-COUNT 4 :USE-CALLER NIL :ALIVE T :SPIN-COUNT 2000 {700E9D0023}> #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 NIL) :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 NIL) :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 {700E9F0003}> :RUNNING-CATEGORY :DEFAULT :INDEX 3 :TASKS #S(LPARALLEL.SPIN-QUEUE:SPIN-QUEUE :HEAD (LPARALLEL.SPIN-QUEUE::DUMMY (#<FUNCTION #2=(LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK) {700EB7C4BB}> . :DEFAULT) (#<FUNCTION #2# {700EB7C4EB}> . :DEFAULT) (#<FUNCTION #2# {700EB7C36B}> . :DEFAULT) (#<FUNCTION #2# {701112F37B}> . :DEFAULT) (#<FUNCTION #2# {701112F3AB}> . :DEFAULT) (#<FUNCTION #2# {701112F3DB}> . :DEFAULT) (#<FUNCTION #2# {701112F40B}> . :DEFAULT) (#<FUNCTION #2# {701112F43B}> . :DEFAULT) (#<FUNCTION #2# {701112F46B}> . :DEFAULT) (#<FUNCTION #2# {701112F49B}> . :DEFAULT) (#<FUNCTION #2# {701112F4CB}> . :DEFAULT) (#<FUNCTION #2# {701112F4FB}> . :DEFAULT) . #3=((#<FUNCTION #2# {701112F52B}> . :DEFAULT))) :TAIL #3#)))
15: ((LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-WORKER-THREAD))
16: ((LAMBDA NIL :IN BORDEAUX-THREADS::BINDING-DEFAULT-SPECIALS))
17: ((FLET SB-UNIX::BODY :IN SB-THREAD::RUN))
18: ((FLET "WITHOUT-INTERRUPTS-BODY-9" :IN SB-THREAD::RUN))
19: ((FLET SB-UNIX::BODY :IN SB-THREAD::RUN))
20: ((FLET "WITHOUT-INTERRUPTS-BODY-2" :IN SB-THREAD::RUN))
21: (SB-THREAD::RUN)
What I am doing here?
Failed to connect to mysql at "ec2-1111111.ap-southeast-2.compute.amazonaws.com" (port 3306) as user "root": The condition Socket error in "connect": EINTR (Interrupted system call) occurred with errno: 0.
I’m getting the error on PG 15 with pgloader 3.6.7 (newest on Debian stable, but the release note seem to mention nothing relevant for the 2 newer versions):
postgres (PostgreSQL) 15.0 (Debian 15.0-1.pgdg110+1)
pgloader version "3.6.7~devel" compiled with SBCL 2.1.1.debian
pgloader --debug --verbose mysql://mysqluser:mysqlpass@server:3326/homeassistant postgresql://homeassistant:pgpass@192.168.1.63:5432/homeassistant
pgloader version 3.6.7~devel
compiled with SBCL 2.1.1.debian
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2022-11-05T11:27:46.020000Z NOTICE Starting pgloader, log system is ready.
2022-11-05T11:27:46.044000Z INFO Starting monitor
2022-11-05T11:27:46.052000Z LOG pgloader version "3.6.7~devel"
KABOOM!
DB-CONNECTION-ERROR: Failed to connect to pgsql at "192.168.1.63" (port 5432) as user "homeassistant": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
Date/time: 2022-11-05-12:27!
An unhandled error condition has been signalled:
Failed to connect to pgsql at "192.168.1.63" (port 5432) as user "homeassistant": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
Backtrace for [… snipped …]
2022-11-05T11:27:46.096000Z INFO Stopping monitor
What I am doing here?
Failed to connect to pgsql at "192.168.1.63" (port 5432) as user "homeassistant": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
Directly connecting with psql to postgresql://homeassistant:pgpass@192.168.1.63:5432/homeassistant
works fine.
Thank you for this, ran into the same issues instructions above worked in my case.
I've run into this problem too. I was using postgresql 13 but I setup the scram-sha-256 for the security reason.
The mitigation steps are:
Edit postgresql.conf, find the line password_encryption = scram-sha-256, change it to password_encryption = md5. Edit pg_hba.conf, change all the scram-sha-256 to md5 for the listening address. Restart the postgresql server. Launch psql, change the password for the user I used for import \password import_user. Run pgloader with the new password. The database should be imported now. Change password_encryption back to scram-sha-256 and authentication method back to scram-sha-256 in pg_hba.conf and restart the database server. Reset the password again. Hopefully the support for scram-sha-256 is coming.
@apiszcz thank you, the workarround also works with 15 but please @dimitri add support fot this :)
I also just stumbled upon this problem. Are there any plans to support this any time soon? It's been half a year since the last comment and the issue is still open.
Needs a fix. Just trying pgloader for the first time and it's nowhere near as simple as the "easy two command migration" that the README claims it to be.
I am testing this with Google hosted AlloyDB and i'm also getting 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
, any way to mitigate it?
Same issue here:
Failed to connect to pgsql at "10.34.25.62" (port 5432) as user "user": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
FWIW, I gave up on pgloader, way too many issues. I ended up using nmig: https://github.com/AnatolyUss/nmig Worked first try. I only need a one-time migration so it was a much nicer experience.
FWIW, I gave up on pgloader, way too many issues. I ended up using nmig: https://github.com/AnatolyUss/nmig Worked first try. I only need a one-time migration so it was a much nicer experience.
https://github.com/dimitri/pgloader/issues/1207#issuecomment-1645166711
So, if 20200101 is the minimal cl-postmodern version required to support scram-sha-256, then:
If you use Debian, use version >= 12 (Bookworm), that bundles version 20211113 (source) The previous version (11, Bullseye) bundles version 20180430 (source) If you use Ubuntu, use version >= 22.04 (Jammy), that bundles version 20211113 (source) The previous version (20.04, Focal) bundles version 20180430 (source)
I had the same problem, Using mysql v8.0.34 and postgresql 14.8
When using pgloader from package managers on Ubuntu 20, it didn't work. The package manager was installing pgloader v 3.6.7
But when I installed pgloader from source using this little answer on stackoverflow: https://stackoverflow.com/a/76648875/821662 (or the official doc: https://pgloader.readthedocs.io/en/latest/install.html#building-from-sources-on-debian )
I now have pgloader v3.6.999 and it worked :-)
I had the same problem, Using mysql v8.0.34 and postgresql 14.8
When using pgloader from package managers on Ubuntu 20, it didn't work. The package manager was installing pgloader v 3.6.7
But when I installed pgloader from source using this little answer on stackoverflow: https://stackoverflow.com/a/76648875/821662 (or the official doc: https://pgloader.readthedocs.io/en/latest/install.html#building-from-sources-on-debian )
I now have pgloader v3.6.999 and it worked :-)
cool, it works
I had the same problem, Using mysql v8.0.34 and postgresql 14.8
When using pgloader from package managers on Ubuntu 20, it didn't work. The package manager was installing pgloader v 3.6.7
But when I installed pgloader from source using this little answer on stackoverflow: https://stackoverflow.com/a/76648875/821662 (or the official doc: https://pgloader.readthedocs.io/en/latest/install.html#building-from-sources-on-debian )
I now have pgloader v3.6.999 and it worked :-)
worked for me as well thnx a lot
I had the same problem, Using mysql v8.0.34 and postgresql 14.8
When using pgloader from package managers on Ubuntu 20, it didn't work. The package manager was installing pgloader v 3.6.7
But when I installed pgloader from source using this little answer on stackoverflow: https://stackoverflow.com/a/76648875/821662 (or the official doc: https://pgloader.readthedocs.io/en/latest/install.html#building-from-sources-on-debian )
I now have pgloader v3.6.999 and it worked :-)
also worked for me, thank you!
pgloader version "3.6.2" compiled with SBCL 2.0.6.debian
postgres 15
getting this connection error with the above version in docker.
psql connects fine
pgloader
I put real password in {password}
the result i got
postgres version
PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
mysql version 8.0.18