Repository navigation
Documentation on using connection pools with forking is incorrect #7815
Description
Describe the bug
The documentation in doc/build/core/pooling.rst (section _pooling_multiprocessing) currently suggests calling Engine.dipose when forking, but the specific details of doing so seems incorrect and leads to errors when followed. Specifically I'm talking about bullet point 2, which says to call Engine.dispose "as soon one is within the new process", and provides example code that calls dispose() in the child.
For mysql/mariadb, if there's a connection in the parent process inherited by the child, closing the connection in the child process not only closes the file descriptor socket, but also sends a kind of "quit" message on the socket (I'm not sure of the exact name, I'm not familiar with the mysql protocol). So, the connection becomes unusable in the parent process, and trying to use it results in mysql error 2013 "Lost connection to MySQL server during query" or 2006 "MySQL server has gone away". I haven't looked into the details of any other database drivers, but it seems like there's a decent chance others work like this, as well.
Even if the parent or child process doesn't touch the connection at all, it's possible this same situation could occur if the relevant objects become GC'd and close the underlying conn. The only way I'm aware of to avoid this is to close the connection in the parent process, before the fork occurs. The only way to handle it in the child would be to close the underlying socket without sending the "quit" message over the socket, but it looks like that's not really practical (if even possible) with any mysql drivers I've looked at, since the socket fd tends to be a private internal thing you're not supposed to be messing with.
Another minor wrinkle here is that in the child process, any connections created tend to not be closed automatically like they would be in the parent process. With multiprocessing.Process, the child process exits with os._exit(), to deliberately avoid running any code in callers, and so any connection objects aren't GC'd or cleaned up in parent finally: clauses or anything like that. As a result, the underlying socket gets closed when the process exits without sending the ending "quit" message on the protocol, which gets logged as a warning by default with mariadbd, something like "5704 [Warning] Aborted connection 5704 to db: 'xxx' user: 'xxx' host: 'xxx' (Got an error reading communication packets)". This isn't a big problem, but is a bit rude; the suggested solution in the documentation could avoid this by just running Engine.dispose() before the child exits.
I assume the situation is very different when using multiprocessing on macos or windows, because the default forking method has very different semantics, but I haven't checked if this is relevant there.
To Reproduce
#!/usr/bin/python3
# forktest_broken.py
import multiprocessing
import os
import sqlalchemy as sa
engine = sa.create_engine(os.environ['DB_URL'])
def run_query():
with engine.connect() as conn:
conn.execute(sa.text('SELECT 1'))
print("[pid %d] query finished" % os.getpid())
def run_in_process():
engine.dispose()
run_query()
run_query()
p = multiprocessing.Process(target=run_in_process)
p.start()
p.join()
run_query()Error
$ DB_URL='xxx' ./forktest_broken.py
[pid 79334] query finished
[pid 79335] query finished
Traceback (most recent call last):
File "/usr/lib64/python3.10/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/usr/lib64/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "/usr/lib64/python3.10/site-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/usr/lib64/python3.10/site-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/usr/lib64/python3.10/site-packages/MySQLdb/connections.py", line 239, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/adeason/./forktest_broken.py", line 24, in <module>
run_query()
File "/home/adeason/./forktest_broken.py", line 11, in run_query
conn.execute(sa.text('SELECT 1'))
File "/usr/lib64/python3.10/site-packages/sqlalchemy/engine/base.py", line 1289, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/usr/lib64/python3.10/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/lib64/python3.10/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement
ret = self._execute_context(
File "/usr/lib64/python3.10/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
self._handle_dbapi_exception(
File "/usr/lib64/python3.10/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
util.raise_(
File "/usr/lib64/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/usr/lib64/python3.10/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/usr/lib64/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "/usr/lib64/python3.10/site-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/usr/lib64/python3.10/site-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/usr/lib64/python3.10/site-packages/MySQLdb/connections.py", line 239, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query')
[SQL: SELECT 1]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Versions
- OS: Fedora 35 (also Debian 11, CentOS7/8)
- Python: 3.10.2
- SQLAlchemy: 1.4.31
- Database: mariadb
- DBAPI (eg: psycopg, cx_oracle, mysqlclient): MySQLdb
Additional context
Here is an example of a snippet of code that closes the connection in the parent like I've suggested:
#!/usr/bin/python3
import multiprocessing
import os
import sqlalchemy as sa
engine = sa.create_engine(os.environ['DB_URL'])
class Process(multiprocessing.Process):
def start(self):
engine.dispose()
super().start()
def run(self):
run_query()
engine.dispose() # not strictly necessary, just to be nice
def run_query():
with engine.connect() as conn:
conn.execute(sa.text('SELECT 1'))
print("[pid %d] query finished" % os.getpid())
run_query()
p = Process()
p.start()
p.join()
run_query()And an example run:
$ DB_URL='mysql://xxx' ./forktest_fix.py
[pid 79362] query finished
[pid 79363] query finished
[pid 79362] query finished
Something like this example is what I'd suggest in the documentation. I can submit a PR for changing the docs, but I thought I should make sure I have some agreement here and I haven't gotten everything completely wrong.
Activity
zzzeek commentedon Mar 11, 2022
sure, we likely never expected that the parent engine was used for a program that does the work in forks. will add this now
dispose session outside of child proc
dispose session outside of child proc
dispose session outside of child proc
zzzeek commentedon Mar 11, 2022
ill have to remember why the doc ended up being the way it was, because of course if you close the connections in the child proc, that messes up the parent. maybe older versions of engine.dispose() didn't actually close all the connections, but that seems a little unlikely (but maybe). in practice we normally used the event -based approach afterwards which solves the problem more granularly.
overall a better solution entirely would be a parameter on dispose() that just loses the connection pool and doesn't try to close the connections. that's pretty easy to do. not sure why the whole thing hasn't really come up.
adeason commentedon Mar 11, 2022
zzzeek commentedon Mar 11, 2022
I thought that the parent would keep using those connections in the parent. but yes I guess the sockets being sent over still represent resource use in the child as well, even though the child doesnt have access to them? I hardly know very well how that works out in practice.
technically the "event based" approach has this problem too, if it is in fact a problem.