Since my explanation of the problem seems not to be clear enough, let me give it another shot.
Setup:
The web application we have uses single mySQL user to connect to the DB, what is more or less classic approach. To avoid confusion I'm going to refer to mysql user(s) as sqluser and to users that log into web applications as simply users. Now, users log in to the web application from their workstations which can be desktops or laptops (mainly laptops).
Scenario:
I'll use reporting module from our web application to illustrate the problem. When the user request a report from the system, we use temporary tables to pull, process and format data – php receives ready resultset to display, no data processing is done on php side. The problem is that temporary tables are more or less randomly shared across mysql connections, what leads us to believe some of those connections are reused. It is unacceptable, because two users generating same report (but say from different time range) would overwrite each other's temporary tables.
Currently we overcame this problem with transactions: each report request is wrapped inside a transaction - temporary tables are created and dropped within, making them transparent to parallel or reused connections.
What we want:
We want a new, unshared connection per each user/workstation that does not share temporary tables. In other words we want a new mysql connection for each user connecting to DB that will retain its temporary tables but not share them across parallel connections with different users.
Potential solution:
Is to make a matching sqluser for each web application user and use this sqluser for DB connection for given user. Due to additional layer of complexity (managing the matchup of sqlusers to users) this solution provides, I'd rather this wasn't out first choice.
Question:
Can we somehow force mysql to open mysql connection on per user/workstation basis that would not share temporary tables across connection pool? is there a framework (php or python) out there that would have such functionality out-of-the-box?
I hope this explanation is slightly clearer then the original.
Original text:
Is it possible to open mysql connection on per workstation basis when logging on to the same mysql user?
Here's the scenario: the webapp we have is connecting to mysql using single mysql user, what makes mysql reuse open connections, what is good to a point. The problem starts when we need to create temporary tables on per-machine basis. Currently the workaround is to use transactions and remove temporary tables afterwards, but we'd like them to be reusable within workstation's session (within webapp user context).
Another solution would be to create mysql user for each webapp user and open connection on that user upon login. But then you have to manage constant rotation and match-up of mysql users to webapp users.
Instead we'd like to somehow force mysql to open new connection per webapp user, either using workstation's internal IP, macaddress, etc. Anybody knows if this is even possible?
We are planing on rewriting this webapp from scratch, so basically even really deep changes are possible at this point, hence the question. Perhaps there's php (or python) framework that can achieve this out-of-the-box?
