Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

aiomysql does not work with ssl, but pymysql does work #978

Open
1 task done
zedzhen opened this issue Feb 28, 2024 · 3 comments
Open
1 task done

aiomysql does not work with ssl, but pymysql does work #978

zedzhen opened this issue Feb 28, 2024 · 3 comments
Labels

Comments

@zedzhen
Copy link

zedzhen commented Feb 28, 2024

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
@zedzhen zedzhen added the bug label Feb 28, 2024
@zedzhen zedzhen changed the title aiomysql does not work with ssl, so pymysql does not work aiomysql does not work with ssl, but pymysql does work Mar 29, 2024
@LewisCowlesMotive
Copy link

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
Copy link

naphtul commented Aug 29, 2024

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

@jzhang-skysql
Copy link

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

4 participants