When and why should I reset session in a connection pool?

All we need is an easy explanation of the problem, so here it is.

I am using Python to implement a high speed query push service into a MySQL database.

Considering a broad usage of various MySQL elements like temporary tables in stored procedures, when and why should I use pool_reset_session feature on a 256 connections-based pool?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Always set pool_reset_session to True, unless you have a specific and strong reason not to. I cannot think of a reason not to reset the session when returning a connection to the pool.

The risk of not resetting a session is that data associated with your session will be leaked into other sessions. Say for example you are doing a banking transaction on a website that serves multiple concurrent users. Part of the code saves some data about your transaction to a temporary table. Then the code returns that MySQL connection to the connection pool, where it is handed off to some other user’s banking interaction. Their session can now see some details about your banking! The same applies to other session state, like user variables, or last_insert_id.

If you need to keep using session state, like reading temp tables several times, then your code should not return that connection to the pool until it is done with that temp table.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply