skip to Main Content
[Ubuntu]: 18.04.4 LTS
[Apache]: Apache/2.4.29 (Ubuntu)
[MySQL]: Ver 14.14 Distrib 5.7.29, for Linux (x86_64) 
[PHP]: 7.2.24-0ubuntu0.18.04.3 (cli) 

MySQL Does Not Reuse Exisitng Connection.

SELECT * 
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE DB="mySchema" and state <>"executing" order by id;    

Upto 10 Connections are created.
Once these connections are created, the connections begin to be reused and the TIME column is reset .
Surely 1 connection should be created and reused.

Replicated on GCloud LAMP Install and Local Machine LAMP Install.
Not able to replicate on Local Machine WAMP Install.

To replicate:

  1. Create a PHP script to connect to a MYSQL table, for example the one below.
  2. Run the script 10 times, wait for 10 seconds between each execution.
  3. Note that upto 10 connections are created.
    4 .Note that once these connections are created, the connection is reused, and the TIME column is reset.

my code:

<?php
$options = [
PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
PDO::ATTR_PERSISTENT=>true
];
$conn = new PDO("mysql:host=localhost;dbname=mySchema", "myUser", "myPassword", $options);
$stmt = $conn->query("SELECT * FROM myTable;");
$row=$stmt->fetch(PDO::FETCH_NUM);
$conn = null;
?>

2

Answers


  1. Chosen as BEST ANSWER

    Removed the default database from the DSN. This ensures the 10 connections can be reused across the multiple schemas in the CRUD application.


  2. Surely 1 connection should be created and reused

    Surely? Nothing in the data you provide in your question points at any reuse of connections.

    If there is a connection pool, it will make sure that eventually connections are reused, but there’s no guarantee that the same connection will be used over and over and over until a second one is required concurrently. I’m assuming that your environment behaves correctly based on its (undisclosed) configuration.

    As you say you can replicate this on one environment, but not on another: That points to different configurations, not at any wrong behavior.

    And it doesn’t look like anything you need to worry about.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search