Opening mysql connection on per workstation basis_问答_开发者

Since my explanation of the problem seems not to be clear enough, let me开发者_JS百科 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?

MySQL documentation addresses your problem quite clearly:

A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege. Note CREATE TABLE does not automatically commit the current active transaction if you use the TEMPORARY keyword.

Source: http://dev.mysql.com/doc/refman/5.5/en/create-table.html

What led to your assumption, that tmp tables are shared between several connections?

When you connect to the database

each (HTTP) request will open a new connection
each (HTTP) request will close the connection once php garbage collection comes into play

UNLESS

you are using persistent connections, which I generally don't recommend
might be the same with connection pooling which is new in the latest MySQL versions. I don't know for sure how the server handles session isolation

If I've understood you right you want the facility to have (in effect) a temporary table where the scope is not just the current session, but all sessions for that user and client address. If so, opening more connections is nothing to do with the problem. You'd need to rewrite the mysqld to accomodate this!

what makes mysql reuse open connections

No - only if you use connection pooling / persistent connections.

Currently the workaround is to use transactions

That sort of implies that there is a finite number of structures for the temporary tables - if this is the case, why not just create these are permanent tables with an additional column (populated by a trigger?) to hold the username? Then, if you really want to be pedantic, block SELECT access to the table and force users to retrieve data from a view which filters their username.

Perhaps there's php (or python) framework that can achieve this out-of-the-box?

erm...no - in addition to the fact it would not be possible, it would be the wrong place to imlement logic form managing the databases internal data structures.

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.

If that's a potential solution then you've not explained the problem very well.

It sounds like you're trying to emulate behaviour where a transaction spans multiple page requests (and therefore multiple mysql sessions). In which case database temporary tables have nothing to do with any viable solution.