1

In my php script, When I am executing query on certain PDO mysql connection, on checking mysql logs, I am not able to see the connection getting closed.

Php Code:

?php
$db = new PDO('mysql:host=HOST;dbname=DB',USER,PASSWORD);
$db->exec("SHOW TABLES");
$db = null;
?>

Mysql logs:

180312 18:31:45 9048429 Connect USER@HOST on DB
        9048429 Query   SHOW TABLES

Though, when I remove query, I can see the mysql connection closed on the Mysql log.

php code:

?php
$db = new PDO('mysql:host=HOST;dbname=DB',USER,PASSWORD);
$db = null;
?>

Mysql log:

180312 18:33:54 9048515 Connect USER@HOST on DB
        9048515 Quit

I have to close mysql connection explicitly on my script to prevent too many connections. How can I do the same?

Tanu Gupta
  • 602
  • 1
  • 11
  • 26
  • I'd say the connection is not closed because you have unfetched data. – Dormilich Mar 12 '18 at 13:17
  • I think you need to close the result set also. However, your current code doesn't use a result set. '$results = $db->exec("SHOW TABLES");' then '$results = null;' then '$db = null;' – CharlesEF Mar 12 '18 at 13:19
  • 2
    Have a read of http://php.net/manual/en/pdo.connections.php – Nigel Ren Mar 12 '18 at 13:19
  • 1
    Especially this comment from the docs may be useful: http://php.net/manual/en/pdo.connections.php#114822 – enricog Mar 12 '18 at 13:54

2 Answers2

0

With the above code, Mysql connection was getting closed successfully. Its just that quitting connection was not appearing on Mysql logs.

When I checked Mysql processlist, It was verified that connection was closing successfully.

In addition to the above problem, if there are query statements to be executed using pdo, then pdo statement handler object need to be destroyed too to close mysql connection as stated in php-mysql-pdo-connection-not-closing-without-destroying-statement-handler.

Tanu Gupta
  • 602
  • 1
  • 11
  • 26
-1

As stated in the documentation: "Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted—you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends."

So in your case setting $db=null; should do the trick

Kai Adelmann
  • 199
  • 6
  • 15