SSL Support (plus mysql_clear_password plugin for RDS)

[terrycain]

Git closed last one when i force pushed.

Long discussion in #225

@jettify @terrisgit - new PR, less code :D

Also credit to Alex as I made a test where MySQL then tried to renegotiate the auth plugin, so I imported his process_auth function (and github realises it was him 😄)

@jettify whats next.

[codecov]

Codecov Report

Merging #280 into master will not change coverage.
The diff coverage is n/a.

@@           Coverage Diff           @@
##           master     #280   +/-   ##
=======================================
  Coverage   92.93%   92.93%           
=======================================
  Files           9        9           
  Lines        1161     1161           
  Branches      173      173           
=======================================
  Hits         1079     1079           
  Misses         53       53           
  Partials       29       29

---

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update ac6267d...485f012. Read the comment docs.

[terrycain]

It passes travis, merge merge merge!!! :D

[jettify]

I will try to review tonight, have lots of thing to do this days after work.

…

On Thu, Apr 19, 2018, 02:43 Terry Cain ***@***.***> wrote: It passes travis, merge merge merge!!! :D — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#280 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AANoZ8vZPj1NpnJKUwe5mOvqr88eowCgks5tp8-zgaJpZM4Ta7lS> .

[jettify]

@terrycain you did very good job! PR concise and elegant!

[jettify]

New version available on PyPI https://pypi.org/project/aiomysql/0.0.13/

[terrycain]

@jettify No problem, was easy after learning that trick with passing in an active socket to open_connection.

Will pr again in a bit to add SSL example + an example of how to use it with RDS's token thing.

Onto other things, mind checking out my aiobotocore pr 😉

[terrisgit]

I'll be user number 2 (ha ha joke's on me)! This is awesome! I really want to use aioboto but it needs to support RDS auth tokens KMS and SNS.

[terrycain]

@terrisgit It probably already does, raise some issues and I'll get cracking

[terrisgit]

This implementation of SSL is failing a lot when attempting to run SQL commands. I am still investigating.

[terrycain]

Hmm not good, if you can find some repeatable code that will trigger it I'll get on that this weekend

[jettify]

Can you share traceback?

…

On Fri, Apr 20, 2018, 20:25 Terry Cain ***@***.***> wrote: Hmm not good, if you can find some repeatable code that will trigger it I'll get on that this weekend — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#280 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AANoZ_bkaRsOMNvUX3NvsOPKz489AMhCks5tqhoZgaJpZM4Ta7lS> .

[terrycain]

The reason why it happens in #280 is because that pr contained like 1% of the code of #225.

You got a code snippet that'll cause it, as then I can use that as a test platform

[terrycain]

ahh so its fine until you try and kill a connection?

[terrycain]

Ok, i'll spin up something to see if i can reproduce

[terrycain]

Ok very weird, if I run the following in debug mode under pycharm, it works fine 100% of the time.
If I run it in pycharm without debugging I have less than a 1% success rate.

@jettify @terrisgit Any ideas why it would work whilst debugging and not when running normally?

import asyncio
import ssl

import aiomysql


async def main(loop):
    ctx = ssl.SSLContext(ssl.PROTOCOL_TLSv1)
    ctx.check_hostname = False
    ctx.load_verify_locations(cafile='../tests/ssl_resources/ssl/ca.pem')

    print('pre_pool')
    pool = await aiomysql.create_pool(
        host='localhost',
        port=3308,
        user='root',
        password='root',
        loop=loop, ssl=ctx
    )

    print('start')
    await tls_query(pool)
    print('end')


async def tls_query(pool):
    async with pool.get() as conn:
        async with conn.cursor() as cur:
            # Run simple command
            await cur.execute("SHOW DATABASES;")
            value = await cur.fetchall()

            print(value)

            # Check TLS variables
            await cur.execute("SHOW STATUS LIKE '%Ssl_version%';")
            value = await cur.fetchone()
            print(value[1])


event_loop = asyncio.get_event_loop()
event_loop.run_until_complete(main(event_loop))

Traceback:

Traceback (most recent call last):
  File "/home/terry/VCS/aiomysql/examples/example_ssl.py", line 42, in <module>
    event_loop.run_until_complete(main(event_loop))
  File "/usr/lib/python3.5/asyncio/base_events.py", line 375, in run_until_complete
    self.run_forever()
  File "/usr/lib/python3.5/asyncio/base_events.py", line 345, in run_forever
    self._run_once()
  File "/usr/lib/python3.5/asyncio/base_events.py", line 1276, in _run_once
    event_list = self._selector.select(timeout)
  File "/usr/lib/python3.5/selectors.py", line 441, in select
    fd_event_list = self._epoll.poll(timeout, max_ev)
KeyboardInterrupt
Fatal write error on socket transport
protocol: <asyncio.sslproto.SSLProtocol object at 0x7f66bf7f21d0>
transport: <_SelectorSocketTransport fd=6>
Traceback (most recent call last):
  File "/usr/lib/python3.5/asyncio/selector_events.py", line 700, in write
    n = self._sock.send(data)
OSError: [Errno 9] Bad file descriptor
Fatal error on SSL transport
protocol: <asyncio.sslproto.SSLProtocol object at 0x7f66bf7f21d0>
transport: <_SelectorSocketTransport closing fd=6>
Traceback (most recent call last):
  File "/usr/lib/python3.5/asyncio/selector_events.py", line 700, in write
    n = self._sock.send(data)
OSError: [Errno 9] Bad file descriptor

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3.5/asyncio/sslproto.py", line 632, in _process_write_backlog
    self._transport.write(chunk)
  File "/usr/lib/python3.5/asyncio/selector_events.py", line 704, in write
    self._fatal_error(exc, 'Fatal write error on socket transport')
  File "/usr/lib/python3.5/asyncio/selector_events.py", line 589, in _fatal_error
    self._force_close(exc)
  File "/usr/lib/python3.5/asyncio/selector_events.py", line 601, in _force_close
    self._loop.call_soon(self._call_connection_lost, exc)
  File "/usr/lib/python3.5/asyncio/base_events.py", line 497, in call_soon
    handle = self._call_soon(callback, args)
  File "/usr/lib/python3.5/asyncio/base_events.py", line 506, in _call_soon
    self._check_closed()
  File "/usr/lib/python3.5/asyncio/base_events.py", line 334, in _check_closed
    raise RuntimeError('Event loop is closed')
RuntimeError: Event loop is closed

[terrisgit]

Control-C was a red herring. The problem is related, as you suspected, to closing a connection pool when a connection is in use. The following hangs or fails in #280 but not in #225 . Sorry it's not minimal but I'm sure you can poke at it and find a simpler version.

from random import randint

import asyncio
import ssl

import aiomysql


async def main(loop):
    ctx = ssl.SSLContext(ssl.PROTOCOL_TLSv1)
    ctx.check_hostname = False
    # ctx.load_verify_locations(cafile=' .. ')

    pool = None
    counter = 0

    while True:
        if not pool:
            pool = await aiomysql.create_pool(
                host=' ',
                port=33XX,
                user=' ',
                password=' ',
                loop=loop, ssl=ctx
            )

        asyncio.ensure_future(tls_query(counter, pool))

        counter += 1

        if not counter % randint(1, 4):
            print("Closing the pool")
            pool.close()
            await pool.wait_closed()
            pool = None

            if counter > 30:
                break
        elif not counter % 5:
            await asyncio.sleep(randint(0, 5))
        else:
            await asyncio.sleep(0)


async def tls_query(counter, pool):
    print(f"Running query {counter}")
    try:
        async with pool.get() as conn:
            async with conn.cursor() as cur:
                await cur.execute("select 1")
                value = await cur.fetchall()
                print(f"{counter} - {value}")
    except Exception as error:
        print(f"Failed {counter} {error}")  # Cannot acquire connection after closing pool


event_loop = asyncio.get_event_loop()
event_loop.run_until_complete(main(event_loop))
pending = asyncio.Task.all_tasks()
event_loop.run_until_complete(asyncio.gather(*pending))

print("Exiting")

Failed 8 (2003, "Can't connect to MySQL server on '...'")
7 - ((1,),)
Traceback (most recent call last):
  File "/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/asyncio/selector_events.py", line 455, in _sock_connect
    sock.connect(address)
BlockingIOError: [Errno 36] Operation now in progress

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/terris/venv/edc2/lib/python3.6/site-packages/aiomysql/connection.py", line 471, in _connect
    loop=self._loop)
  File "/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/asyncio/streams.py", line 81, in open_connection
    lambda: protocol, host, port, **kwds)
  File "/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/asyncio/base_events.py", line 765, in create_connection
    yield from self.sock_connect(sock, address)
  File "/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/asyncio/selector_events.py", line 449, in sock_connect
    self._sock_connect(fut, sock, address)
  File "/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/asyncio/selector_events.py", line 463, in _sock_connect
    self.add_writer(fd, self._sock_connect_cb, fut, sock, address)
  File "/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/asyncio/selector_events.py", line 346, in add_writer
    self._ensure_fd_no_transport(fd)
  File "/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/asyncio/selector_events.py", line 258, in _ensure_fd_no_transport
    fd, transport))
RuntimeError: File descriptor 9 is used by transport <_SelectorSocketTransport fd=9 read=polling write=<idle, bufsize=0>>

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

Traceback (most recent call last):
  File "terry.py", line 59, in <module>
    event_loop.run_until_complete(main(event_loop))
  File "/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/asyncio/base_events.py", line 468, in run_until_complete
    return future.result()
  File "terry.py", line 25, in main
    loop=loop, ssl=ctx
  File "/Users/terris/venv/edc2/lib/python3.6/site-packages/aiomysql/utils.py", line 75, in __await__
    resp = yield from self._coro
  File "/Users/terris/venv/edc2/lib/python3.6/site-packages/aiomysql/pool.py", line 30, in _create_pool
    yield from pool._fill_free_pool(False)
  File "/Users/terris/venv/edc2/lib/python3.6/site-packages/aiomysql/pool.py", line 173, in _fill_free_pool
    **self._conn_kwargs)
  File "/Users/terris/venv/edc2/lib/python3.6/site-packages/aiomysql/utils.py", line 70, in __iter__
    resp = yield from self._coro
  File "/Users/terris/venv/edc2/lib/python3.6/site-packages/aiomysql/connection.py", line 79, in _connect
    yield from conn._connect()
  File "/Users/terris/venv/edc2/lib/python3.6/site-packages/aiomysql/connection.py", line 502, in _connect
    self._host) from e
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on ' ...'")

[jettify]

Can you please check this patch? #282

[terrycain]

@jettify That patch looks like it did the trick 😄

@terrisgit Looks like the patch #282 seems to fix your example, I dont see any bad descriptor exceptions
I do see some Failed 27 Cannot acquire connection after closing pool but that is expected.

[terrisgit]

Yes, that fixed it! Hope you release it soon.