Pgloader: latest install from brew can't connect to mysql due to SSL issue.
pgloader version "3.4.1"
compiled with SBCL 1.4.4
from a fresh install from homebrew
I tried with both mariadb and mysql . Can connect to both of them using the respective clients.
pgloader mysql://root@localhost/vc postgresql://tim@localhost/vc
KABOOM!
FATAL error: Failed to connect to mysql at "localhost" (port 3306) as user "root": SSL verify error: 19 X509_V_ERR_SELF_SIGNED_CERT_IN_CHAIN
An unhandled error condition has been signalled:
Failed to connect to mysql at "localhost" (port 3306) as user "root": SSL verify error: 19 X509_V_ERR_SELF_SIGNED_CERT_IN_CHAIN
What I am doing here?
Failed to connect to mysql at "localhost" (port 3306) as user "root": SSL verify error: 19 X509_V_ERR_SELF_SIGNED_CERT_IN_CHAIN
Waiting for the monitor thread to complete.
timuckun
All 20 comments
My understanding is that you need to tweak your local SSL library to accept self-signed certificates, or to upgrade your server's certificate to a fully verified one, with a whole chain of trust. See the SSL docs about how to achieve either of those solutions, as there's nothing related to pgloader here.
You could also open non-SSL connection just for the migration, too, if your environment security rules allow that.
dimitri
on 21 Feb 2018
What are the options on pgloader to specify non ssl connections or to use a particular cert? Do I use the same opts as the mysql client?
timuckun
on 21 Feb 2018
I should also add that I can connect to the server using the command line client. So there is something pgloader is doing that the mysql client is not.
timuckun
on 21 Feb 2018
From the docs at http://pgloader.readthedocs.io/en/latest/ref/mysql.html#mysql-database-source-specification-from you can use the sslmode parameter in a MySQL connection string. See http://pgloader.readthedocs.io/en/latest/pgloader.html#connection-string for details, and here I must admit that finding this requires some searching and reading, so we should improve the docs...
Can you try the following database uri: mysql://root@localhost/vc?sslmode=disable and report success or failure?
dimitri
on 22 Feb 2018
It doesn't like the question mark
pgloader -v mysql://root@localhost/vc?sslmode=disable postgresql://tim@localhost/vc
2018-02-22T06:49:43.008000Z NOTICE Starting pgloader, log system is ready.
2018-02-22T06:49:43.020000Z LOG Data errors in '/private/tmp/pgloader/'
2018-02-22T06:49:43.033000Z WARNING Could not parse source string "mysql://root@localhost/vc?sslmode=disable": At
mysql://root@localhost/vc?sslmode=disable
^ (Line 1, Column 25, Position 25)
In context MYSQL-DSN-DBNAME:
While parsing MYSQL-DSN-DBNAME. Problem:
The production
#\?
does not satisfy the predicate ALPHA-CHAR-P.
Expected:
the character , (COMMA)
or the character - (HYPHEN-MINUS)
or the character _ (LOW_LINE)
or any character satisfying DIGIT-CHAR-P
or any character satisfying ALPHA-CHAR-P
2018-02-22T06:49:43.033000Z FATAL Failed to parse "mysql://root@localhost/vc?sslmode=disable" as a source URI.
2018-02-22T06:49:43.033000Z LOG You might need to use --type.
Please upgrade to a more recent pgloader version: compile from fresh sources!
dimitri
on 22 Feb 2018
Nothing works!
Attempt 1. Clone the github repo, run make clean, modify the docker file to run make clean before it runs make do a docker build.
....
...
[saving current Lisp image into /opt/src/pgloader/build/bin/buildapp.sbcl:
writing 5680 bytes from the read-only space at 0x20000000
writing 3120 bytes from the static space at 0x20100000
writing 65798144 bytes from the dynamic space at 0x1000000000
done]
mkdir -p build/bin
build/bin/buildapp.sbcl --logfile /tmp/build.log \
--require sb-posix --require sb-bsd-sockets --require sb-rotate-byte \
--sbcl sbcl \
--asdf-path . \
--asdf-tree build/quicklisp/local-projects \
--manifest-file build/manifest.ql \
--asdf-tree build/quicklisp/dists \
--asdf-path . \
--load-system pgloader \
--load src/hooks.lisp \
--entry pgloader:main \
--dynamic-space-size 4096 \
--compress-core \
--output build/bin/pgloader.tmp
WARNING:
Couldn't re-execute SBCL with proper personality flags (/proc isn't mounted? setuid?)
Trying to continue anyway.
WARNING:
Couldn't re-execute SBCL with proper personality flags (/proc isn't mounted? setuid?)
Trying to continue anyway.
;; loading system "pgloader"
Fatal COMPILE-FILE-ERROR:
COMPILE-FILE-ERROR while
compiling #<CL-SOURCE-FILE "cl-postgres" "cl-postgres" "public">
Makefile:133: recipe for target 'build/bin/pgloader' failed
make: *** [build/bin/pgloader] Error 1
The command '/bin/sh -c make' returned a non-zero code: 2
Attempt two
Brew uninstall pgloader
brew unlink pgloader
#the following is necessary because it gives checksum errors
rm /Users/tim/Library/Caches/Homebrew/pgloader*
brew install --HEAD pgloader
.....
.....
.....
==> Downloading https://beta.quicklisp.org/archive/usocket/2016-10-31/usocket-0.7.0.1.tgz
######################################################################## 100.0%
==> Downloading https://beta.quicklisp.org/archive/uuid/2013-08-13/uuid-20130813-git.tgz
######################################################################## 100.0%
==> make pgloader-standalone BUILDAPP=buildapp
Last 15 lines from /Users/tim/Library/Logs/Homebrew/pgloader/01.make:
--sbcl sbcl \
--load-system pgloader \
--load src/hooks.lisp \
--entry pgloader:main \
--dynamic-space-size 4096 \
--compress-core \
--output build/bin/pgloader
;; loading system "pgloader"
Fatal SIMPLE-ERROR:
Compilation failed: PGLOADER.COPY also uses the following packages:
(PGLOADER.PGSQL)
See also:
The ANSI Standard, Macro DEFPACKAGE
The SBCL Manual, Variable *ON-PACKAGE-VARIANCE* in /private/tmp/pgloader-20180224-1015-xgqe2g/src/package.lisp
make: *** [pgloader-standalone] Error 1
READ THIS: https://docs.brew.sh/Troubleshooting.html
OK lets just try to do a make from the repo...
cd pgloader
make clean
make
Yea it actually builds. Now let's run it
build/bin/pgloader ../pgload.conf
KABOOM!
FATAL error: At
LOAD DATABASE
FROM mysql://root@localhost/vc?useSSL=false
^ (Line 2, Column 42, Position 56)
In context LOAD-MYSQL-COMMAND:
While parsing KW-INTO. Expected:
the character Tab
or the character Newline
or the character Return
or the character Space
or the string "--"
or the string "/*"
or the string "into"
While parsing ANOTHER-DSN-OPTION. Expected:
the character & (AMPERSAND)
While parsing DSN-TABLE-NAME. Problem:
The production
#\=
does not satisfy the predicate ALPHA-CHAR-P.
Expected:
the character . (FULL_STOP)
or the character , (COMMA)
or the character - (HYPHEN-MINUS)
or the character _ (LOW_LINE)
or any character satisfying DIGIT-CHAR-P
or any character satisfying ALPHA-CHAR-P
An unhandled error condition has been signalled: At
LOAD DATABASE
FROM mysql://root@localhost/vc?useSSL=false
^ (Line 2, Column 42, Position 56)
In context LOAD-MYSQL-COMMAND:
While parsing KW-INTO. Expected:
the character Tab
or the character Newline
or the character Return
or the character Space
or the string "--"
or the string "/*"
or the string "into"
While parsing ANOTHER-DSN-OPTION. Expected:
the character & (AMPERSAND)
While parsing DSN-TABLE-NAME. Problem:
The production
#\=
does not satisfy the predicate ALPHA-CHAR-P.
Expected:
the character . (FULL_STOP)
or the character , (COMMA)
or the character - (HYPHEN-MINUS)
or the character _ (LOW_LINE)
or any character satisfying DIGIT-CHAR-P
or any character satisfying ALPHA-CHAR-P
2018-02-24T14:19:56.020000+13:00 LOG Data errors in '/private/tmp/pgloader/'
2018-02-24T14:19:56.060000+13:00 LOG Parsing commands from file #P"/Users/tim/source/jo/venture_center/pgload.conf"
What I am doing here?
At
LOAD DATABASE
FROM mysql://root@localhost/vc?useSSL=false
^ (Line 2, Column 42, Position 56)
In context LOAD-MYSQL-COMMAND:
While parsing KW-INTO. Expected:
the character Tab
or the character Newline
or the character Return
or the character Space
or the string "--"
or the string "/*"
or the string "into"
While parsing ANOTHER-DSN-OPTION. Expected:
the character & (AMPERSAND)
While parsing DSN-TABLE-NAME. Problem:
The production
#\=
does not satisfy the predicate ALPHA-CHAR-P.
Expected:
the character . (FULL_STOP)
or the character , (COMMA)
or the character - (HYPHEN-MINUS)
or the character _ (LOW_LINE)
or any character satisfying DIGIT-CHAR-P
or any character satisfying ALPHA-CHAR-P
Ok Let's remove the extra parameters from the conf file so it looks like this
LOAD DATABASE
FROM mysql://root@localhost/vc
INTO postgresql://tim@localhost:5432/vc
WITH include drop, create tables, create indexes, reset sequences,
workers = 8, concurrency = 1,
multiple readers per thread, rows per range = 50000
BEFORE LOAD DO
$$ create schema if not exists vc; $$,
$$ create schema if not exists mv; $$;
build/bin/pgloader ../pgload.conf
2018-02-24T14:22:32.021000+13:00 LOG Data errors in '/private/tmp/pgloader/'
2018-02-24T14:22:32.023000+13:00 LOG Parsing commands from file #P"/Users/tim/source/jo/venture_center/pgload.conf"
KABOOM!
FATAL error: At end of input
127.0.0.1:5432:*:tim:
^ (Line 1, Column 21, Position 21)
In context PGPASS-ENTRY:
While parsing PGPASS-ENTRY. Expected:
the character * (ASTERISK)
or the character \ (REVERSE_SOLIDUS)
or any character satisfying PGPASS-CHAR-P
An unhandled error condition has been signalled: At end of input
127.0.0.1:5432:*:tim:
^ (Line 1, Column 21, Position 21)
In context PGPASS-ENTRY:
While parsing PGPASS-ENTRY. Expected:
the character * (ASTERISK)
or the character \ (REVERSE_SOLIDUS)
or any character satisfying PGPASS-CHAR-P
What I am doing here?
At end of input
127.0.0.1:5432:*:tim:
^ (Line 1, Column 21, Position 21)
In context PGPASS-ENTRY:
While parsing PGPASS-ENTRY. Expected:
the character * (ASTERISK)
or the character \ (REVERSE_SOLIDUS)
or any character satisfying PGPASS-CHAR-P
What the ?? It's now failing to parse pgpass! needless to say pgpass works fin
Ok put an * where the password would go.
Yea now it works!!!
But this seems very fragile to me. I would have much preferred to build this in docker than to build it natively and the parsing of the pgpass seems weird as well.
Anyway now I can attempt to go on.
timuckun
on 24 Feb 2018
Ok, we are in a bad situation currently. It looks like several components are broken at the same time. Also where did you get the syntax useSSL=false from? This is not supposed to work.
The project is as fragile a single person open source project always is. Contributions welcome, complaints only when they help fixing problems. Will have a look again at docker builds, but as I never ever use them, I can only see about problems when reported by users. Also, if all you want to say is that you don't like the project, remember: you don't have to use it.
dimitri
on 24 Feb 2018
So, here's a first set of improvements. I'm still having to deal with CL-CSV having changed its API (public and internal) and behaviour, so failures in the CSV parsing bits of pgloader. For the database parts this should have no impact, that said.
The docker build failed because it was using a very old SBCL version. That's due to the fact that it was still using a jessie base system, upgrading to stretch fixes the build. The Compilation failed: PGLOADER.COPY also uses the following packages was my mistake in a recent refactoring patch that I didn't test well enough, and it's now fixed with more refactoring; basically finishing the half-backed patch. Then the useSSL=false error is expected, try spelling it as written in the docs sslmode=disable. Finally I downgraded pgpass parsing errors to WARNINGs and made it so that we would accept empty passwords in the file, because that's what your error message is showing.
If you can try again, that should be ok for you now.
dimitri
on 25 Feb 2018
I apologize if my post came across as snarky that was not my intension. I just wanted to let you know every error message I got during the process. As I said it did finally work when I compiled it on the mac.
As for the sslmode=false or useSSL=false I used the useSSL on the mysql connection string because the mysql documentation says that's the parameter. The sslmode is the postgres parameter. I wasn't sure if pgloader was abstracting those or passing them on to the client as is. I presumed each connection string would be using the native connection parameters of the underlying driver.
Once again, I apologize for any negative tone in my post. That was not my intention and pgloader is an amazingly useful tool. I was very frustrated getting it to work and I was under a deadline of my own.
Cheers
timuckun
on 25 Feb 2018
Can confirm the same problem on a fresh High Sierra install. Unable to connect to mysql due to the same SSL error
robbyrussell
on 8 Mar 2018
The same as above.
sunfmin
on 8 Mar 2018
me too :-/
flaushi
on 13 Mar 2018
@sunfmin and @flaushi can you try with either ?sslmode=disable or useSSL=false and a current version of pgloader freshly compiled from the master's tree? Thanks.
dimitri
on 16 Mar 2018
Hello @dimitri . Thank you for the wonderful project.
I went through all possible issues on macOS and installed pgloader by brew. Here's what I've got while migrating from mysql to pg by using ?sslmode=disable or useSSL=false. Sometimes it returns nothing but sometimes it throws following error:
➜ pgloader mysql://root@localhost/f1db?useSSL=false pgsql://postgres@localhost:5432/f1db
2018-03-18T15:00:03.027000Z WARNING Could not parse source string "mysql://root@localhost/f1db?useSSL=false": At
mysql://root@localhost/f1db?useSSL=false
^ (Line 1, Column 27, Position 27)
In context MYSQL-DSN-DBNAME:
While parsing MYSQL-DSN-DBNAME. Problem:
The production
#\?
does not satisfy the predicate ALPHA-CHAR-P.
Expected:
the character , (COMMA)
or the character - (HYPHEN-MINUS)
or the character _ (LOW_LINE)
or any character satisfying DIGIT-CHAR-P
or any character satisfying ALPHA-CHAR-P
2018-03-18T15:00:03.230000Z FATAL Failed to parse "mysql://root@localhost/f1db?useSSL=false" as a source URI.
Current version:
➜ pgloader --version
pgloader version "3.4.1"
compiled with SBCL 1.4.4
Targeted db pgsql://postgres@localhost:5432/f1db is empty.
andrewkslv
on 18 Mar 2018
Hi @andrewkslv, the version you're using doesn't support your connection string option. Can't remember if it supported any option in the connection string, by the error message I would say it doesn't...
dimitri
on 19 Mar 2018
Dear @dimitri, I did try that. I solved my problem by first upgrading via --self-upgrade, and then I created a .load file instead using of command line arguments. Then pgloader worked like a charm! Even the migration of enums worked. Also my doctrine-symfony-combination did accept the migrated database immediately. Thanks!
flaushi
on 19 Mar 2018
@dimitri thank you for the reply. Your response gave me an idea what I'm doing wrong.
I moved one step back I used brew install --HEAD pgloader which is the latest version and I've got the desired result. Hope it'd help everyone.
➜ pgloader mysql://root@localhost/f1db?useSSL=false pgsql://postgres@localhost:5432/f1db
2018-03-19T13:22:06.019000+03:00 LOG Data errors in '/private/tmp/pgloader/'
2018-03-19T13:22:06.097000+03:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@localhost:3306/f1db {1005804073}>
2018-03-19T13:22:06.098000+03:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@localhost:5432/f1db {10059370D3}>
2018-03-19T13:22:10.240000+03:00 LOG report summary reset
table name errors rows bytes total time
------------------------- --------- --------- --------- --------------
fetch meta data 0 33 0.317s
Create Schemas 0 0 0.005s
Create SQL Types 0 0 0.006s
Create tables 0 26 0.076s
Set Table OIDs 0 13 0.006s
------------------------- --------- --------- --------- --------------
f1db.circuits 0 73 8.5 kB 0.022s
f1db.constructors 0 208 15.0 kB 0.021s
f1db.constructorresults 0 11142 186.2 kB 0.055s
f1db.drivers 0 844 80.1 kB 0.036s
f1db.laptimes 0 426633 11.2 MB 1.848s
f1db.constructorstandings 0 11896 249.3 kB 0.084s
f1db.driverstandings 0 31726 719.1 kB 0.201s
f1db.pitstops 0 6251 209.6 kB 0.041s
f1db.races 0 997 100.6 kB 0.016s
f1db.seasons 0 69 3.9 kB 0.009s
f1db.qualifying 0 7516 286.4 kB 0.113s
f1db.results 0 23777 1.3 MB 0.189s
f1db.status 0 134 1.7 kB 0.030s
------------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 2.150s
Create Indexes 0 20 2.072s
Index Build Completion 0 20 1.187s
Reset Sequences 0 10 0.110s
Primary Keys 0 13 0.016s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.000s
Set Search Path 0 1 0.002s
Install Comments 0 0 0.000s
------------------------- --------- --------- --------- --------------
Total import time ✓ 521266 14.3 MB 5.537s
brew install --HEAD pgloader
did not work
santacruz123
on 12 Apr 2018
@santacruz123 Maybe you have installed pgloader without the --HEAD option beforehand?
Try brew uninstall pgloader and then brew install --HEAD pgloader once again - did work for me!
matthee
on 30 Apr 2018
Related issues
ReinierNL
·
3Comments
VonSNAKE77
·
4Comments
chrisspen
·
8Comments
mmuruev
·
4Comments
richardkmichael
·
6Comments
Most helpful comment
@dimitri thank you for the reply. Your response gave me an idea what I'm doing wrong.
I moved one step back I used
brew install --HEAD pgloaderwhich is the latest version and I've got the desired result. Hope it'd help everyone.