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

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)

CodePudding user response：

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)