0

I'm doing some testing (on my dev server) to help me figure out what is causing the ever growing list of connections on my production server. My assumption is that there are certain pages that are not closing the connection using mysql_close(). So I'm trying to first of all confirm what is creating the connections, and then confirm how to make sure they're closed.

To do this I created a simple script to create a connection, followed by a mysql_close() to close the connection. Unfortunately, my testing is at a standstill because I can't even confirm that I'm creating connections.

When I execute the following code (notice mysql_close()) is commented out, I would expect to see a new connection in my client connections list, and I would expect it to kill itself after 20 seconds since I've set the wait_timeout to 20. However, no connection even seems to be created.

<?php

$dblocation = "127.0.0.1";
$dbusername = "blah_dev";
$dbpassword = "test";
$dbname = "blah_dev";
$dbdescription = "";

$conn = mysql_connect($dblocation, $dbusername, $dbpassword) or die ("<span style='color:red'>Unable to connect!  Press F5 to try again.</span>");
mysql_select_db($dbname, $conn) or die ("Unable to select database!");

$sql = "select fullname from months";
$result = mysql_query($sql);
if($row = mysql_fetch_array($result)){

  echo "<table border=1>";
  do{
    $month = $row["fullname"];
    echo "<tr><td>".$month."</td></tr>";
  }while ($row = mysql_fetch_array($result));

  echo "</table>";
}

//mysql_close();

?>

I even added a simple select statement to confirm that I'm making a connection, and I am. Why does this connection not show in my Client Connections list in MySQL Workbench? I'm also using another tool (MySQL Diagnostic Manager) to show me the current threads and it's not showing there either.

What am I missing here?

Vincent
  • 736
  • 1
  • 7
  • 11
  • Does any layer in your stack provide "connection pooling"? – Rick James Aug 29 '21 at 18:22
  • Unless the Workbench is connected as "root", you can only see connections coming from the same "user". – Rick James Aug 29 '21 at 18:24
  • Use of General Log is a great way to confirm what is going on. Remember to SET GLOBAL general_log=OFF; to avoid filling your storage media when you are through with the investigation. – Wilson Hauck Aug 29 '21 at 20:35
  • Vincent, Have you considered using mysqli_connect rather than mysql_connect. This url may be helpful. https://www.php.net/manual/en/mysqli.error.php – Wilson Hauck Aug 29 '21 at 20:53
  • @RickJames I am connected as Root. – Vincent Aug 30 '21 at 15:12
  • @WilsonHauck I'm actually in the process of switching all of my code over to DBO, and I am actually using a mixture of both, but I still have over 2000 mysql_query statements so it's a big job and a long process to switch every statement over. At this point I'm just doing my testing with mysql_connect because I had to pick one. – Vincent Aug 30 '21 at 15:14
  • @WilsonHauck just to be clear, I am connecting because if I wasn't I wouldn't be able to execute my select statement and display the results. The connection just isn't showing in my connections list and that makes testing connection management impossible. Also, it seems random. Sometimes I do see two connections in my connections list after executing this code (never one) and sometimes I see none. – Vincent Aug 30 '21 at 15:17
  • Vincent, Please post from production to pastebin.com and share the links. After 48 hours of uptime the following information would make analysis possible. Login as root and from MySQL Command Prompt, SHOW GLOBAL VARIABLES; and SHOW GLOBAL STATUS; to detect possible causes of growing connections. Include complete SHOW FULL PROCESSLIST; also. Thanks for considering posting this info. – Wilson Hauck Aug 30 '21 at 18:04
  • @WilsonHauck Not sure what you want me to post exactly. – Vincent Aug 30 '21 at 19:42
  • Look at our website 'Data We Need' page for how to get data to us. Thanks, Wilson – Wilson Hauck Aug 30 '21 at 21:29
  • @RickJames how would I know if I'm using connection pooling? I installed MySQL with the default settings, so I'm guessing this is something that needs to be enabled explicitly, but how can I confirm? – Vincent Aug 31 '21 at 16:04
  • Never mind y'all. I figured it out. The connection is created but closed immediately after so it does not appear to be creating a connection. My bad. – Vincent Aug 31 '21 at 16:48

1 Answers1

0

Turns out, this code does create a client connection, but because it closes it as soon as the script finishes running it does not appear to be creating a connection. :/

Vincent
  • 736
  • 1
  • 7
  • 11