CODEWITHSUNDEEP
phpmysqllinuxapache
Jack Smith
Jack Smith

Reputation: 11

mysql not reusing a connection Ver 14.14 Distrib 5.7.29, for Linux (x86_64)

[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;
?>

Upvotes: 0

Views: 125

Answers (2)

Olaf Kock
Olaf Kock

Reputation: 48087

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.

Upvotes: 0

Jack Smith
Jack Smith

Reputation: 11

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

Upvotes: 1

Related Questions