Open dhensen opened 6 months ago
I made a mistake in what I wrote above. I'm not using two pools, the difference is I'm calling acquire in two different places. The sanic request/response middleware already calls acquire before and after my endpoint implementation runs, then during my endpoint core I call acquire once more. I feel like that's something that should be fine.
In the additional context, I posted a screenshot. Here the code immediately breaks in the first loop of the while True. So this gives me zero rows, when there obviously should be rows. This flips each time I call my endpoint, so one time there are rows, next time they are not there. This starts happening when I run the update statement after having done the select statement. It keeps happening when I update again and set the value back to what it was initially.
This morning I woke up an realized this has got to do with transaction isolation levels...
When I commit after my select query, this problem disappears. Or when I create my pool with autocommit=True, it of course also solves this.
But I'm still not sure if this is expected behaviour. If I do pool.acquire() I expect to get a connection based on the current truth of whatever is committed to the database when acquiring. Pools are re-using connections, so is there a state bug in here? of it my responsability to reset or commit? Also really don't get why the explicit acquire in my update statement shows this problem and not the conn I already acquired in the request middleware. The only thing I can think of.. is that acquire always gives the next available or new connection, which is always a different that the last connection it handed out, and two realities start existing. One where I run the update query and commit, and other connections, that are never committed and thus show the old state of the database.
@Nothing4You any idea?
Describe the bug
I can reproduce letting aiomysql return wrong data when I'm
using more than one Poolcalling pool.acquire multiple times. In the documentation it nowhere warns me not to do this, is this a bug?To Reproduce
Install:
pip install sanic==23.6.0 aiomysql==0.2.0
Database:docker run -p "13306:3306" --env="MYSQL_ROOT_PASSWORD=repro" --env="MYSQL_DATABASE=repro" --env="MYSQL_USER=repro" --env="MYSQL_PASSWORD=repro" -d mysql:8
Run python3 repro.py.
One in one terminal run: watch -n1 -- curl http://localhost:8004/verify/foobar1337 In another terminal run once: curl http://localhost:8004/update_token/foobar1001
Now see the watch output toggle between null and the previous result.
Then stop the api. Edit code to ACQUIRE_NEW_CONN = False Then run again, seems like problem solved.
Or see https://youtu.be/q7BgBZQ1Jow (when I shot this I didnt have ACQUIRE_NEW_CONN yet, I fix it manually)
Expected behavior
I expect it to answer to queries with the truth, not some old value that belongs to previous queries connection.
Logs/tracebacks
Python Version
aiomysql Version
PyMySQL Version
SQLAlchemy Version
OS
Arch Linux, but I get this inside Docker as well.
Database type and version
Additional context
Code of Conduct