Query returns result from another query

[ilex]

Here is my problem. I use aiomysql in aiohttp handlers. Something like this:

async def handler(request):
    data = await get_data(request.app['db_pool'])

async def get_data(db_pool):
    # prepare query q and params
    async with db_pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(q, params)
            rows = await cur.fetchall()
    # do something with rows
    # and return result

And some times rows are not the result for the given query q but the result from other query. There are no errors. It just silently returns wrong result. The only way i could determine that situation was that queries had different number of fields so there was an index out of bounds error in the log some times.

So the question is - do i run it in a right way? Or should i use any locks somewhere? Or is it a bug in the library?

[jettify]

Could you show how is your query look like? Do you have autocommit=True ?

[ilex]

Yes, i use autocommit=True while creating pool:

app['db_pool'] = loop.run_until_complete(
    aiomysql.create_pool(
        host='127.0.0.1', port=3306,
        user=settings['db_user'],
        password=settings['db_pass'],
        db=settings['db_name'],
        autocommit=True,
        loop=loop
    )
)

Well queries are simple and could be as follow:

1. SELECT id, key FROM t1 WHERE key='somekey';
2. SELECT id, created, field1, field2 FROM t2 WHERE id=13 ORDER BY created DESC LIMIT 1

So when the result of (1) is returned as the result for (2) when i try to access 3-d field - there is an index out of bounds error. In the case when result for (2) is returned from another (2) there are no errors but we have wrong data for given id. Actually to determine this situation i added some extra code which compared id from result with id from where clause and if they were not equal i just logged that. And such records did appeared in log.

[ilex]

To clerify there is async def get_data_1(db_pool) for (1) and async def get_data_2(db_pool) for (2). They are very similar and the difference is in # do something with rows

[jettify]

Interesting, could you try execute tests with master? Is it somehow related to CanceledError ? What is easiest way to reproduce this issue, so I can debug locally

[ilex]

I looked through the code of aiomysql and it seems to me i found a possible reason of the issue. Here is a piece of code from file aiomysql/pool.py (class Pool):

    def acquire(self):
        """Acquire free connection from the pool."""
        coro = self._acquire()
        return _PoolAcquireContextManager(coro, self)

    def _acquire(self):
        if self._closing:
            raise RuntimeError("Cannot acquire connection after closing pool")
        with (yield from self._cond):
            while True:
                yield from self._fill_free_pool(True)
                if self._free:
                    conn = self._free.popleft()
                    assert not conn.closed, conn
                    assert conn not in self._used, (conn, self._used)
                    self._used.add(conn)
                    return conn
                else:
                    yield from self._cond.wait()

A private _acquire method here should be a coroutine but @asyncio.coroutine decorator is missed. So it becomes a generator and does not work as expected.

[jettify]

This is not a problem, asyncio works fine without decorator. I am updating aiomysql to work with new version of PyMySQL, hopefully that will fix an issue. Could you provide minimal reproducible example so i can debug locally?

[ilex]

Yes, it seems you are right.
I can't reproduce the issue locally. At the server the code works fine for a while too and there are no errors. But at some point it starts to fail (still silently and i can catch that only with the additional comparison of the result id value with value from WHERE clause and raise an Exception to log when they are not equal). The only thing i can mention that first error is <class 'concurrent.futures._base.CancelledError'> so log looks like:

2016-07-20 18:55:09,972 - ERROR - <class 'concurrent.futures._base.CancelledError'> :
2016-07-20 18:55:27,361 - ERROR - <class 'Exception'> : Wrong result for id "13" aiomysql returns (34, 'image.jpg', datetime.datetime(2016, 7, 20, 13, 27, 12))
2016-07-20 19:02:21,221 - ERROR - <class 'Exception'> : Wrong result for id "100" aiomysql returns (55, 'image2.jpg', datetime.datetime(2016, 7, 20, 19, 1, 33))
...

I will try a new version. Hope it'll help.

[jettify]

got it, there is patch with fix here #79 , I will apply it very soon.

[jettify]

should be fixed in master

[jettify]

new version released to pypi

[Vinc135]

Hey I have the same error. Could you explain me how to fix it? I am using version 0.1.0 from aiomysql

[Nothing4You]

Hi @Vinc135,

you're probably seeing a different issue.

Please raise a new issue with your problem.

[Vinc135]

Probably not but I downgraded the version and now the error is gone

[Nothing4You]

While it may very well be a regression with the same root cause, this issue has been resolved back in 2016.

If you're seeing the same problem on 0.1.0 please report it as a new issue.

Without any additional information I'm afraid I can't help you.

[Vinc135]

I reported the error in a new issue. Can you help me there?

[rsking01]

The same problem appeared on 0.1.1. I use one cursor for all requests