Hi! I am getting following error with target-MySQ...
# troubleshooting
e
Hi! I am getting following error with target-MySQL: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'X.Y.Z.N' ([SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: IP address mismatch, certificate is not valid for 'X.Y.Z.N'. (_ssl.c:1133))") However I have certificates set up on machine and can query the MySQL database from it. How do I pass them into the sqlalchemy engine for the target?
e
(cc @visch is this something you've seen before?)
v
iirc you need access to sending sqlalchemy_options see https://github.com/MeltanoLabs/tap-mysql/blob/0d6fcddce37c0f85fee1f32d451b251802579abf/tap_mysql/tap.py#L209 I haven't used target-mysql from the hub so I don't know but it looks like there isn't an easy way to control this option with that target
e
Got it, I see
Copy code
sqlalchemy_url = URL.create(
            drivername="mysql+pymysql",
            username=config["user"],
            password=config["password"],
            host=config["host"],
            port=config["port"],
            database=config["database"],
            query=config.get("sqlalchemy_options"),  # type: ignore[arg-type])
But this isn’t code we should be updating? Here is my redacted config if it helps:
Copy code
name: target-mysql
  variant: thkwag
  pip_url: thk-target-mysql
  config:
    sqlalchemy_url: <mysql+pymysql://meltano_user:<your_password>@34.69.22.60:3306/meltano_the_daily_upside?ssl_ca=/home/meltano/meltano-the-daily-upside/tdu-meltano/.secrets/server-ca.pem&ssl_cert=/home/meltano/meltano-the-daily-upside/tdu-meltano/.secrets/client-cert.pem&ssl_key=/home/meltano/meltano-the-daily-upside/tdu-meltano/.secrets/client-key.pem>
@Edgar Ramírez (Arch.dev) @visch are there any changes I should be making in config? Edgar mentioned and ssl_key_password param
e
I'd try to open a Python REPL with
target-mysql
installed and run something like
Copy code
import sqlalchemy as sa

URL = "<mysql+pymysql://meltano_user>:<your_password>@34.69.22.60:3306/meltano_the_daily_upside?ssl_ca=/home/meltano/meltano-the-daily-upside/tdu-meltano/.secrets/server-ca.pem&ssl_cert=/home/meltano/meltano-the-daily-upside/tdu-meltano/.secrets/client-cert.pem&ssl_key=/home/meltano/meltano-the-daily-upside/tdu-meltano/.secrets/client-key.pem"

engine = sa.create_engine(URL)
with engine.connect() as conn:
  conn.execute("SELECT 1")
e
Ok, thank you
I think that did it, now getting a key error which means connected at least
👍 1
e
Looking at how they construct the URL, no you probably should omit the
pymysql
driver from it: https://github.com/thkwag/target-mysql/blob/c84fe01850b0470d39e4d0c56fd884e2019174c4/target_mysql/sinks.py#L56-L63
e
Awesome!! Thanks for help Edgar and @visch You two rock Here’s doc I used for reference
dancingpenguin 1
e
Np! Glad you got it working 🙌
1
m
Hey, to the same topic. Do you know if I could pass
ssl=True
or
ssl_mode='REQUIRED'
somewhere in this class? I see that here we can pass it to Connection, but not sure if I am not mixing something