4

I know that PHP automatically closes open MySQL connections at the end of the script, and the only viable way to open a persistent connection is to use the appropriate PHP function; many questions have been asked and answered. What I would like to know is the benefit - or the inconvenient - of keeping a temporary connection instead of a persistent connection

EDIT : A persistent connection for each PHP user session.
For instance, statements such as the following:

session_start();
$connection = new mysqli($host, $user, $pass, $db);
$_SESSION['connection'] = $connection;

might set up a reference to a mysqli object useful for performing multiple queries across navigation in the website within the same session by the same user.

If the connection is supposed to be used soon after its activation, would not be the right choice to leave it just open for further queries? Maybe this approach would generate an inconvenient situation (and possible security risks) when multiple users are HTTP-requesting pages from a web site which keeps MySQL connections alive? I would like to know more. Thank you.

someOne
  • 1,975
  • 2
  • 14
  • 20
Stencil
  • 1,833
  • 1
  • 17
  • 19
  • 1
    I also would like to know how this would impact performance and security :), upvoted the question – Webeng Apr 23 '16 at 12:53
  • Each request is a new life cycle for PHP, it doesn't have a persistant state like you may get elsewhere. If you were to use the same connection for multiple requests/users then you'd might see race conditions (which queries finish first), getting the "last inserted id" might give you the id another user have inserted, etc – JimL Apr 23 '16 at 13:27
  • It's not possible to serialize (save a mysqli connection) to a session, so the example code won't work as expected. – drew010 Apr 23 '16 at 17:54

1 Answers1

3

There's overhead to connecting to MySQL or any database, although it isn't typically large. This overhead can be greater when the MySQL service is running on a different server, or depending on the authentication method and init commands needed.

More, MySQL connections may have associated caches. So reusing a connection may be able to reuse these caches.

But saving a resource in the session doesn't work. Session data is serialized, and stored in e.g. a file between requests. That's why the persistent connect methods have to be used.

The reason is that the connection is ultimately a resource, or a socket connection on an internal class, and this can't be "saved" without special handling. Try it, and you'll see that you get an error (with PDO and mysqli.)

mysqli::query(): Couldn't fetch mysqli

I don't think there's any way to get session-specific connection reuse without writing an extension to implement it. Theoretically it's possible, though, and you could theoretically implement a method to pull a connection from the pool by session id.

There are a lot of potential drawbacks and risks to persistent db connections, though:

  1. If your application creates TEMPORARY TABLEs, those tables will still exist on the next run, since it's the same MySQL session.

  2. If you set any variables (SET SESSION or etc.), they will be retained.

  3. Depending on how transactions are handled, it's theoretically possible a persistent connection may have an in-progress transaction.

  4. If the application issues any LOCK commands or uses user-locks, the locks could be held between requests and unknowingly held in new requests.

  5. On a multi-tenant (shared) webserver, the risk of another process somehow obtaining database access it shouldn't have is higher.

  6. This may leave large numbers of connections open for long periods of time, increasing resource usage on the database server.

  7. You still have to expect that the connection might be lost between requests.

Community
  • 1
  • 1
Todd Christensen
  • 1,297
  • 8
  • 11
  • Also note that identifying a *user* won't be enough to know whether this is a continuation of the same *session*. Any persistent approach would need to remember, per user, session id for any open session(s). If user starts a different session, you do *not* want to treat this as a continuation of the previous session - its like multi-threading, when you are writing scripts, you don't want to have to keep such a possibility constantly in mind. Likewise if user starts a second session (perhaps another device), you want to keep that separate. – ToolmakerSteve Aug 12 '20 at 21:50