PHP refuses to connect to remote MySQL server

[kaigoh]

Hi there.

 

First, let me appologise in advance if this question has been answered elsewhere, I have been looking for the solution for this since 8 this morning and I'm getting fed up with trawling through forums!

 

Ok, here is the problem:

 

PHP will talk to a local database (through socket) but will not talk to a remote one. I have added all necasary permissons to allow a user to connect to the db remotely on the server I am trying to connect to. I can connect to the database remotely using mysql client on the web box, which is why I do not understand why PHP will not connect to it? I have trapsed through loads of websites and forums and IRC but everything just draws a blank?!

 

I hope someone can help me, this truly is driving me nuts.

 

Just so you know:

 

Linux (CentOS 5)

Apache 2

PHP 5

MySQL 5

 

Remote end, firewall is allowing the correct ports.

 

Thanks,

 

Kai.

[Chris92]

There should be no reason for it not to connect unless you are filling in the wrong details. Maybe you should try putting the port at the end of the ip if you already haven't:

mysql_connect("127.0.0.1:3306","username","password");

[kaigoh]

There should be no reason for it not to connect unless you are filling in the wrong details. Maybe you should try putting the port at the end of the ip if you already haven't:

mysql_connect("127.0.0.1:3306","username","password");

 

I have tried it already. I know it should work, I really just don't understand why it wont?

[Chris92]

Can you connect through other ports to the server? Make sure port 3306 is being forwarded.

[kaigoh]

I have tried to connect to the server both with the firewall on and off. The server has 3306 open. The wierd thing is I can connect to the server with the mysql -h <SERVERIP> -u blah blah -p command and manipulate the database. It is just PHP that won't connect.

[PFMaBiSmAd]

If a mysql client will connect, php should be able. Post your code (xxxxx'ing out any sensitive information but don't change any punctuation or syntax.)

 

How do you know it is not connecting? What errors or other symptoms? Anyway, posting your code will help in finding out what it is doing that is not working.

[kaigoh]

<?php

if(ISSET($_SERVER["HTTPS"])) {

$server = gethostbyname('<HIDDEN>');

//Remote <HIDDEN> connection
$link = mysql_connect($server.':3306', '<HIDDEN>', '<HIDDEN>') or die('Could not connect 

to <HIDDEN> server! Connection Error: '.mysql_error());
mysql_select_db('<HIDDEN>') or die('Could not connect to <HIDDEN> server! Error: DBSELECTERROR');
$query = " SELECT * FROM activation WHERE ACTIVATION_CODE = '".$pbx_local_data["ACTIVATION_CODE"]."' LIMIT 1 ; 

";
$result = mysql_query($query) or die('Could not connect to <HIDDEN> server! Query Error: ' . mysql_error());
$pbx_remote_data = mysql_fetch_array($result, MYSQL_ASSOC);
mysql_free_result($result);

 

Thanks.

[Chris92]

Try getting rid of the gethostbyname function

 

$link = mysql_connect('<HIDDEN>:3306', '<HIDDEN>', '<HIDDEN>') or die('Could not connect to <HIDDEN> server! Connection Error: '.mysql_error());

[kaigoh]

I have done already. Same problem. (P.S. I need that in there in case the IP address of the server changes)

[Chris92]

Is it giving a mysql error?

[kaigoh]

The only output is:

 

"Connection Error: Can't connect to MySQL server on '84.xxx.xxx.xxx' (13)"

[effigy]

Does your_user_name@your_host have an account set up in the remote database with the proper permissions?

[kaigoh]

Yes, first thing I looked at

[effigy]

Is the machine you're working on in the /etc/hosts file of the other?

[kaigoh]

No. The code I am writing is destined to be installed on clients machines. It will be impossible to include all the hosts. The permisions for mySQL are set to user@% so that a connection can be established from any host.

[PFMaBiSmAd]

Is the host name that you are putting into - gethostbyname(); the host name that corresponds to the IP address of the database server? Have you checked that the IP address it returns is the IP address that the database server is listening to.

 

Basically, the error message indicates that there is no server listening at the IP : PORT combination.

[effigy]

Is the server using bind-address or skip-networking in its configuration?

[kaigoh]

Yes, the server is setup to bind to address 0.0.0.0 because it has two IP addresses, both a private and a public one.

 

I have tried connecting to another sql database on a completely different server with the same results.

 

And yes, gethostbyname() is returning the correct IP.

[effigy]

Is sql.safe_mode off in your php.ini?

[kaigoh]

yup 

[PFMaBiSmAd]

See if php will give you any more information about why it is not connecting. Add the following after your first <?php tag -

 

ini_set ("display_errors", "1");
error_reporting(E_ALL);

[kaigoh]

Nope

 

Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on '84.xxx.xxx.xxx' (13) in /var/www/html/index.php on line 11

 

[effigy]

Does PHP see the other box? For example:

<pre>
<?php
echo `ping the_other_box_ip`;
?>
</pre>

[kaigoh]

Yes, it can ping the other box.