Category Archives: Software Projects

Applications, tools, & code snippets from my software projects.

PHP: mysql_connect query to Remote MySQL server

PHP: mysql_connect query to Remote MySQL server fails or is really slow.  Why?

I added the remaining charts to LMHist and found that they were not working.  Reload the browser and different charts would display.  I looked in /var/log/httpd/errors_log and found:

[Mon Aug 12 09:45:39 2013] [error] [client 10.3.1.1] PHP Warning: mysql_connect (): Can't connect to MySQL server on 'websrvr.davis.net' (4) in /var/www/html/lmhist-dev/davis186081MAXDES_2014_0F.php on line 9, referer: http://fawlty.us/lmhist-dev/lmhist.php 
[Mon Aug 12 09:45:39 2013] [error] [client 10.3.1.1] PHP Warning: mysql_select_ db() expects parameter 2 to be resource, boolean given in /var/www/html/lmhist-dev/davis186081MAXDES_2014_0F.php on line 10, referer: http://fawlty.us/lmhist-dev/lmhist.php

This about drove me nuts.  Since some of the charts would generate I knew it wasn’t a MySQL security issue.  I tried a bunch of things and finally decided to copy all the source to the server where MySQL is running.  When I load the page I get every chart.

Back to Basics

The problem turned out to be DNS.  The MySQL server is on an internal network behind a firewall and has the name websrvr.davis.net.  The Apache server is on the same network but is exposed through NAT on the firewall.  It is called Fawlty.us.

From Fawlty.us I could ping websrvr.davis.net no problem.  From websrvr.davis.net I could NOT ping fawlty.us.  It would resolve the external IP address from DNS.  I fixed on websrvr.davis.net by adding an entry to the /etc/hosts table for fawlty.us and provided the internal IP address.  Now MySQL resolves the internal address and I can ping fawlty.us successfully.

Then from a client browser the charts all worked fine.

What is MySQL Doing?

The PHP code opens mysql_connect($db-server, $db-username, $db-password) where $db-server is the fully qualified DNS name, websrvr.davis.net.  In MySQL I allowed the private IP address to connect to the database.  I found that MySQL is actually resolving the host name even when the IP address is what was added as the host the db-user is coming from.  Either the DNS or local hosts file must resolve the name to the correct IP address, or you have to turn off the name resolution.

–skip-name-resolve turns it off.  I chose to fix the hosts table.

Change MySQL Access

Along the way I found a suggestion to move mysql_connect() and mysql_select_db() to a function.  It seems simple enough.  Create a file called connect.php and add the connection info to it.

<?php
     $g_link = false;

     function GetMyConnection()
     {
         global $g_link;
         if( $g_link )
             return $g_link;
         $g_link = mysql_connect( 'host.name', 'user', 'password') or die('Could not connect to server.' );
         mysql_select_db('database_name', $g_link) or die('Could not select database.');
         return $g_link;
     }

     function CleanUpDB()
     {
         global $g_link;
         if( $g_link != false )
             mysql_close($g_link);
         $g_link = false;
     }

?>

Then require_once(‘connect.php’) in each file that is making the connection.  Then you hook to the function in your mysql_query like this:

<?php
     $res = mysql_query("SELECT ...", GetMyConnection() );
?>

And it works.  Once mysql_connect() can then be used for all your queries removing the overhead of building and destroying database connections.  But Chart.php doesn’t like it.  the image.php file finds the connection and queries the data just fine.  Chart builds the image and if caching is enabled the image is saved in the cache directory just fine.  But the original php script that embeds the image.php in an image tag will never display the image.  Grrr.

I think chart.php or image.php is waiting for the mysql_connect() to close which it doesn’t do.  I reverted back to having each image.php file create and destroy its own connection.  Then Chart builds the image and the browser displays them.

Future Design Problems for mysql_connect()

mysql_connect() deprecated

On the PHP documentation for mysql_connect I found that the function is now deprecated as of PHP 5.5.0.  I will now have to go back through and look at mysqli_connect() or PDO::__construct() and switch all the code to use the newer functions.

Since I am developing the current release of LMHist to PHP 5.3 I will add this to a future release.