unfortunately I can't get MySQLdb to work and neither connect via command line to an AWS server which requires me to use SSL. This strangely works fine via SequelPro.
What works?
Beside the mentioned GUI, programmatically I use mysqlconnector instead of MySQLdb as engine and pass ssl args and this works:
from sqlalchemy import create_engine
import pandas as pd
pd.read_sql("SHOW DATABASES",
create_engine(CONNECTION_STRING.replace('mysqldb', 'mysqlconnector'),
connect_args={'ssl_ca': '~/Downloads/rds-combined-ca-bundle.pem'}))
What doesn't work?
For some reasons I don't want to use mysqlconnector but MySQLdb. Therefore I tried the following two ways:
conn = create_engine(CONNECTION_STRING)
pd.read_sql_query("SHOW DATABASES", conn)
conn = create_engine(
CONNECTION_STRING,
connect_args={'ssl': {'ca': '~/Downloads/rds-combined-ca-bundle.pem'}})
pd.read_sql_query("SHOW DATABASES", conn)
For the first one it gives the expected access denied:
OperationalError: (_mysql_exceptions.OperationalError) (1045, "Access denied for user 'MYUSER'@'MYHOST' (using password: YES)") (Background on this error at: http://sqlalche.me/e/e3q8)
And for the second one:
OperationalError: (_mysql_exceptions.OperationalError) (2026, 'SSL connection error: error:00000001:lib(0):func(0):reason(1)') (Background on this error at: http://sqlalche.me/e/e3q8)
From googling that "error:00000001:lib(0):func(0):reason(1)" error I thought about misusing the package and made multiple attempts with various variations of basically (including mode=VERIFY_IDENTITY, passing the cipher) this:
mysql -h HOST --ssl-ca=~/Downloads/rds-combined-ca-bundle.pem -u USER -p
Enter password:
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)
throwing the same error.
Ideas
Maybe this works on the mysqlconnector (without passing anything) because the ssl package (which is not used by the other both options I tried as far as I can see but definitely from connector) outputs on ssl.get_default_verify_paths():
DefaultVerifyPaths(cafile='~/anaconda2/ssl/cert.pem', capath=None, openssl_cafile_env='SSL_CERT_FILE', openssl_cafile='~/anaconda2/ssl/cert.pem', openssl_capath_env='SSL_CERT_DIR', openssl_capath='~/anaconda2/ssl/certs')
, so there is some default configuration which makes this work (same for sequelPro probably). But all in all I just don't understand why it works fine with sequelPro and the connector package but not with command line nor MySQLdb.
Trying the following (from here) works fine:
mysql -u MYUSER -h MYHOST --ssl -p
But is not preferred due to the deprecation of the --ssl flag.
I hope some of you can see where I am missing something out!
