aiomysql does not work with ssl, but pymysql does work

[zedzhen]

Describe the bug

aiomysql does not connect

To Reproduce

create user with REQUIRE SSL

/etc/mysql/my.cnf

[client-server]
port = 3306
socket = /run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[mariadb]
ssl_cert = /[path to let's encrypt cert]/cert.pem
ssl_key = /[path to let's encrypt cert]/privkey.pem
ssl_ca = /[path to let's encrypt cert]/chain.pem

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_520_ci

test.py

import asyncio
import ssl

from aiomysql import Connection
from pymysql import Connection as sConnection

ctx = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_REQUIRED


async def f():
    async with Connection(host='[host]', port=3306, charset='utf8mb4', user='[user]',
                          password='***', db='[db]', ssl=ctx) as con:
        await con._connect()
        cur = await con.cursor()
        await cur.execute("SELECT * FROM users")
        print(await cur.fetchone())


with sConnection(host='[host]', port=3306, charset='utf8mb4', user='[user]',
                 password='***', database='[db]', ssl_verify_cert=True) as con:
    cur = con.cursor()
    cur.execute("SELECT * FROM users")
    print(cur.fetchone())

asyncio.run(f())

Expected behavior

aiomysql should connect successfully

Logs/tracebacks

Windows:
[correct data]
Traceback (most recent call last):
  File "D:\Programs\Python311\Lib\asyncio\sslproto.py", line 556, in _do_handshake
    self._sslobj.do_handshake()
  File "D:\Programs\Python311\Lib\ssl.py", line 979, in do_handshake
    self._sslobj.do_handshake()
ssl.SSLWantReadError: The operation did not complete (read) (_ssl.c:992)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "D:\[some_path]\.venv\Lib\site-packages\aiomysql\connection.py", line 540, in _connect
    await self._request_authentication()
  File "D:\[some_path]\.venv\Lib\site-packages\aiomysql\connection.py", line 769, in _request_authentication
    self._reader, self._writer = await _open_connection(
                                 ^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\[some_path]\.venv\Lib\site-packages\aiomysql\connection.py", line 88, in _open_connection
    transport, _ = await loop.create_connection(
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\Programs\Python311\Lib\asyncio\base_events.py", line 1106, in create_connection
    transport, protocol = await self._create_connection_transport(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\Programs\Python311\Lib\asyncio\base_events.py", line 1139, in _create_connection_transport
    await waiter
  File "D:\Programs\Python311\Lib\asyncio\proactor_events.py", line 401, in _loop_writing
    self._write_fut = self._loop._proactor.send(self._sock, data)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\Programs\Python311\Lib\asyncio\windows_events.py", line 558, in send
    self._register_with_iocp(conn)
  File "D:\Programs\Python311\Lib\asyncio\windows_events.py", line 747, in _register_with_iocp
    _overlapped.CreateIoCompletionPort(obj.fileno(), self._iocp, 0, 0)
OSError: [WinError 87] The parameter is incorrect.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "D:\[some_path]\test.py", line 32, in <module>
    asyncio.run(f())
  File "D:\Programs\Python311\Lib\asyncio\runners.py", line 190, in run
    return runner.run(main)
           ^^^^^^^^^^^^^^^^
  File "D:\Programs\Python311\Lib\asyncio\runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\Programs\Python311\Lib\asyncio\base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "D:\[some_path]\test.py", line 20, in f
    await con._connect()
  File "D:\[some_path]\.venv\Lib\site-packages\aiomysql\connection.py", line 563, in _connect
    raise OperationalError(
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '[host]'")

Ubuntu:
[correct data]
Traceback (most recent call last):
  File "/home/[user]/.py311/lib/python3.11/site-packages/aiomysql/connection.py", line 540, in _connect
    await self._request_authentication()
  File "/home/[user]/.py311/lib/python3.11/site-packages/aiomysql/connection.py", line 769, in _request_authentication
    self._reader, self._writer = await _open_connection(
                                 ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/[user]/.py311/lib/python3.11/site-packages/aiomysql/connection.py", line 88, in _open_connection
    transport, _ = await loop.create_connection(
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/asyncio/base_events.py", line 1112, in create_connection
    transport, protocol = await self._create_connection_transport(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/asyncio/base_events.py", line 1145, in _create_connection_transport
    await waiter
  File "/usr/lib/python3.11/asyncio/sslproto.py", line 575, in _on_handshake_complete
    raise handshake_exc
  File "/usr/lib/python3.11/asyncio/sslproto.py", line 557, in _do_handshake
    self._sslobj.do_handshake()
  File "/usr/lib/python3.11/ssl.py", line 979, in do_handshake
    self._sslobj.do_handshake()
ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1006)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/[user]/test.py", line 32, in <module>
    asyncio.run(f())
  File "/usr/lib/python3.11/asyncio/runners.py", line 190, in run
    return runner.run(main)
           ^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/asyncio/runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/asyncio/base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "/home/[user]/test.py", line 20, in f
    await con._connect()
  File "/home/[user]/.py311/lib/python3.11/site-packages/aiomysql/connection.py", line 563, in _connect
    raise OperationalError(
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '[host]'")

Python Version

$ python --version
Windows:
3.8.10
3.11.1
Ubuntu:
3.8.10
3.11.7

aiomysql Version

$ python -m pip show aiomysql
0.2.0

PyMySQL Version

$ python -m pip show PyMySQL
1.1.0

SQLAlchemy Version

$ python -m pip show sqlalchemy
not using

OS

Ubuntu 20.04/Windows 10

Database type and version

SELECT VERSION();
11.3.2-MariaDB-1:11.3.2+maria~ubu2204

Additional context

No response

Code of Conduct

• I agree to follow the aio-libs Code of Conduct

[LewisCowlesMotive]

I Can second this. I thought I was being a smart alec by wrapping calls via keyword-arguments. This took out a lower environment. So disappointed (mostly at myself 😂 )

[naphtul]

I'm having the same issue. Who can help us?

[jzhang-skysql]

Hello, I believe I am seeing a very similar issue in SQLAlchemy from one of our clients. I have opened a thread here: sqlalchemy/sqlalchemy#11969

Is there a status report on this bug?