sqlalchemy force all connections to close mysql

2.6k views Asked by At

We use sqlalchemy in some complicated unit tests. At the end of each test, we tear down the database entirely using drop_all. Now and then, someone forgets to close a session embedded deep inside a unit test. We then have to spend a fair amount of time trying to figure out just what is going wrong, and ultimately to close the sqlalchemy session.

We want a want to reliably way to force all the connections to the database to close. We have tried session.close_all(), but this did not do what I describe.

Edit: Below is the mysql processlist we see when the process hangs attempting to drop the database. Presumably Id 285 is the offending process:

+-----+------+-----------------+----------+---------+------+---------------------------------+------------------------------+
| Id  | User | Host            | db       | Command | Time | State                           | Info                         |
+-----+------+-----------------+----------+---------+------+---------------------------------+------------------------------+
| 172 | root | localhost       | mydb     | Query   |    0 | init                            | show processlist             |
| 285 | root | localhost:47147 | mydb     | Sleep   |    6 |                                 | NULL                         |
| 289 | root | localhost:47152 | mydb     | Query   |    5 | Waiting for table metadata lock | DROP TABLE `mytable`         |
+-----+------+-----------------+----------+---------+------+---------------------------------+------------------------------+

Edit: Per request, I am also including the transaction log:

------------
TRANSACTIONS
------------
Trx id counter 1144929
Purge done for trx's n:o < 1144827 undo n:o < 0 state: running but idle
History list length 398
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 37, OS thread handle 0x8f46bb40, query id 776 localhost root init
show engine innodb status
---TRANSACTION 0, not started
MySQL thread id 45, OS thread handle 0x8f2ceb40, query id 774 localhost 127.0.0.1 root Waiting for table metadata lock
DROP TABLE `mytable`
---TRANSACTION 1144823, ACTIVE 12 sec
MySQL thread id 41, OS thread handle 0x8f2ffb40, query id 595 localhost 127.0.0.1 root cleaning up
Trx read view will not see trx with id >= 1144824, sees < 1144824
1

There are 1 answers

2
John On

In flask, this should do the trick:

@app.teardown_appcontext
def teardown_db(exception):
    db.session.commit()
    db.session.close()