Connection to mysql (MariaDB) fails in python->SQLAlchemy while it succeeds in cmd

Question

I'm trying to connect to remote mysql (MariaDB) database with some security options within flask app using db_url. Simplified test version:

from sqlalchemy import create_engine
engine = create_engine(
    'mysql+mysqlconnector://user:[email protected]:3306/mydb?'+
    'ssl_key=/path/to/key.pem'+
    '&ssl_cert=/path/to/scrt.crt'
)
connection = engine.connect()

and get error which leads to problem with SSL

sqlalchemy.exc.InterfaceError: (mysql.connector.errors.InterfaceError) 2026 (HY000): SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol
(Background on this error at: http://sqlalche.me/e/rvf5)

(I also tried pymysql instead of mysqlconnector)

HOWEVER (what I don't understand) when I try to connect from command line - it works.

mysql -u user -ppassword mydb -h remote.host.com --ssl-cert=/path/to/scrt.crt --ssl-key=/path/to/key.pem

Just in case:

> SHOW VARIABLES LIKE "%version%";

+-----------------------------------+------------------------------------------+
| Variable_name                     | Value                                    |
+-----------------------------------+------------------------------------------+
| in_predicate_conversion_threshold | 1000                                     |
| innodb_version                    | 10.3.34                                  |
| protocol_version                  | 10                                       |
| slave_type_conversions            |                                          |
| system_versioning_alter_history   | ERROR                                    |
| system_versioning_asof            | DEFAULT                                  |
| version                           | 10.3.34-MariaDB-0ubuntu0.20.04.1         |
| version_comment                   | Ubuntu 20.04                             |
| version_compile_machine           | x86_64                                   |
| version_compile_os                | debian-linux-gnu                         |
| version_malloc_library            | system                                   |
| version_source_revision           | a36fc80aeb3f835fad02f443d65dc608b74b92d1 |
| version_ssl_library               | YaSSL 2.4.4                              |
| wsrep_patch_version               | wsrep_25.24                              |
+-----------------------------------+------------------------------------------+

Just in case 2. Also in openssl config /etc/ssl/openssl.cnf ( server one's, not remote.host.com)

...
[system_default_sect]
MinProtocol = TLSv1.1
...

Note: I recently updated server to Ubuntu 20.04.4 LTS and python to 3.8.10 (maybe it's not relevant)

@snakecharmerb unfortunately no. It was a great option but didn't work. Also I think I tracked this down to incompatible TLS versions. I'm waiting for our IT to verify versions on remote. — Alexander B.
– Alexander B., Commented Mar 22, 2022 at 4:22

Alexander B. · Accepted Answer · 2022-03-22 18:11:53Z

1

After lots of digging - the problem ended up being outdated version of remote database. After upgrade everything works as intended.

My understanding is TLS versions that was used on database was too insecure (non existent in %version% variables). After upgrade I got in the results:

tls_version    | TLSv1.1,TLSv1.2,TLSv1.3

which is compatible with version required by openssl on client (MinProtocol = TLSv1.1)

answered Mar 22, 2022 at 18:11

Alexander B.

6467 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connection to mysql (MariaDB) fails in python->SQLAlchemy while it succeeds in cmd

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related