I'm moving an account to a new server (both semi-managed, and I have root access to both). For now, I'm trying to keep the database on the old server, but eventually I'll move it to the new server.

I'm trying to connect to the server in PHP. Here's what I'm using:

$server = "12.34.56.78";
$user = "my_username";
$pass = "my_password";

$dbh = mysql_connect($server, $user, $pass, false, MYSQL_CLIENT_COMPRESS);

On the old server, this worked correctly if I used $server = "localhost", and I didn't have "false, MYSQL_CLIENT_COMPRESS" when it was being loaded from the local server.

But when I do it this way, I'm getting the following error:

Access denied for user 'nobody'@'localhost' (using password: NO)

I'm guessing that the problem is with the $server variable, but I'm not sure how it's supposed to be written. Is it supposed to be just the IP (which is what PHP.net shows), or http://12.34.56.78, or what?

TIA,

Jason

Jason C <jwcarlton (AT) gmail (DOT) com> wrote:

If this is the code, then this

cannot be the error message. This login attempt used a locally
running MySQL server and no password. Check your code.

Just the IP address.

If you do have root access to the machines, you better test the
connection with the 'mysql' client tool. On the machine where PHP
is running, try

mysql -h 12.34.56.78 -u my_username -p

If this works, then PHP shall work too.


XL

on the old server, but eventually I'll move it to the new server.

I have an include file with just this sort of stuff in it, plus a
database name. It avoids scattering login credentials all over
multiple scripts, and it's easier when passwords get changed or the
code gets moved to a different PHP server.

it was being loaded from the local server.

Is $server set to an IP address other than "127.0.0.1" or "localhost"?
Is $user equal to "nobody"? Is $pass an empty string or null? It
would seem that you are attempting connection to the *WRONG SERVER*,
possibly because PHP will automatically connect with defaults if
you start running queries before opening a connection. If it says
"using password: NO" and you think you supplied a password (not ''
or null), this also suggests that it's not your call to mysql_connect
that's getting the error.

Does the error message give a file name and line number? Is that
the file name and line number where you call to mysql_connect() is?
Do you run any database queries *BEFORE* the call to mysql_connect()?
Don't. Or is there another left-over call to mysql_connect()? Move
the call to mysql_connect() earlier if needed.

just the IP (which is what PHP.net shows), or http://12.34.56.78,
or what?

My guess is that there's a > 50% chance that it's not YOUR call to
mysql_connect() that's failing. It might be PHP calling it for you
if you call mysql_query() or some other related functions without
opening a connection with mysql_connect() first. Change (temporarily)
your call from mysql_connect() to mysql_fail() (Hint: there is no
such function. Trying to run it will provoke an error message about
an undefined function.) and see if you still get the same error message.

The IP address is supposed to be just an IP address, or a host name
resolvable in DNS. No slashes or colons. MySQL does not use the
http protocol.

The fact that you got an "Access denied" error indicates that you
successfully connected to *some* server, possibly the wrong one.

Can you temporarily shut down the server you don't think you are
using? If you do, does the error message change to one of not
being able to connect?

Correction to my own post: if the IP address is an *IPv6* IP
address, you will need colons. But the colons don't go where the
OP put them, as in http: .

Same here. I have a variables.php with all of the common variables, and I connect to MySQL at the very beginning of the script. This script is included on every page of the site, very first thing.


I have $server set to the IP of the server (which is different from the server with the PHP script). If you just went to the IP in your browser, though, it's not going to take you to the account; you would have to go to http://12.34.56.78/~myacct.

$user and $pass are definitely correct, because if I change $server to "localhost" (on the old server) then everything loads correctly.


Unfortunately, no, it doesn't give me any error messages or line numbers; what I pasted before is the entire error, verbatim.

After "password:No)", the rest of the page stops loading. So when I view source, there's nothing past "No)".


No, this is the very first thing in the script.


Well, apparently I have something turned off to prevent PHP errors from showing on the screen; all I get was a "server error 500" page. But, since thecurrent error doesn't give a line number, I really wouldn't have anything to compare.


I've tried both the IP (no "http://") and the host name ("server2.example.com"), with the same error. Both the IP and host name resolve to the same location (if I ping "server2.example.com", it returns the IP address).


I DID discover that if I turn off the firewall on the server with the database (using ConfigServer Firewall, aka CSF), then I get the error a lot faster (immediately, versus a wait of 45 seconds or so). But still, completely disabling the firewall on both servers didn't fix the problem.

I added the IP of the new server to the "Allow" file, and now it seems to go through immediately each time I refresh.


Shutting down the server itself is a bit of a pain, but I can shut down MySQL easily enough. Doing so didn't change the error message at all, which leads me to believe that I'm ever making it that far.

Other than the firewall, would there be another setting somewhere that would prevent remote access to the database? If it matters, I'm using Apache 2.2 on a RHEL 5 operating system (the database server has RHEL 4), and both servers have WHM / cPanel.

On Wednesday, October 5, 2011 2:45:29 AM UTC-4, Axel Schwenke wrote:
Well, XL, I do believe you're on to something here.

For a little further info, the old server DID have all of the data, and I'm moving it over to the new server. To avoid DNS delays, I'm setting up the old server to proxy the new one, using Apache's mod_proxy module.

So, these files are on both Server A (old) and Server B (new):

/includes/variables.php
/includes/header.php
index.php

When I go to www.example.com/index.php, I know for a fact that it's loading from Server B, because I added some dummy code on that page.

In PHP, this page loads header.php as:

include "includes/header.php";

And, it's definitely loading header.php from Server B, because I added some dummy code there, too.

Here's the interesting thing. The header.php file is what includes variables.php, with:

include "variables.php";

For whatever reason, it looks like THIS include is loading variables.php from Server A, not Server B. Server A is the one that loads localhost.

I know this has nothing to do with MySQL, so I'll take it to alt.apache.configuration now. But, I wanted you guys to know that this was the apparent culprit.

On Wednesday, October 5, 2011 2:45:29 AM UTC-4, Axel Schwenke wrote:
Well, I spoke too soon! Using this, I get the following error:

ERROR 1130 (HY000): Host '12.34.56.78' is not allowed to connect to this MySQL server

How do I give the new server permission?

Jason C <jwcarlton (AT) gmail (DOT) com> wrote:
It would be much clearer if you would use real ip addresses here.
But lets assume the MySQL server runs on 1.2.3.4 and PHP runs on
5.6.7.8. Then the thest would be run on 5.6.7.8 like so:

mysql -h 1.2.3.4 -u my_username -p

and /might/ return an error "Host '5.6.7.8' is not allowed ..."

The GRANT on 1.2.3.4 should look like so

GRANT ALL ON my_database.*
TO 'my_username'@'5.6.7.8'
IDENTIFIED BY 'my_password';

Hint: probably you should not grant ALL permissions, but only those
needed by your application. I.e. SELECT, INSERT, UPDATE, DELETE.
Certainly you should not GRANT ALL ON *.*

With the above GRANT in place (check with SHOW GRANTS !) the connect
should succeed. Of course the MySQL server on 1.2.3.4 must listen on
it's outbound network interface. Look out for bind-address=... and
skip-networking in my.cnf. Also the MySQL server obeys /etc/hosts.deny
and /etc/hosts.allow. And you might have a firewall (netfilter) there.

You can even use

telnet 1.2.3.4 3306

to test the connectivity. It should print some garbage characters and
then close the connection after 10 seconds.

Only if the test connection works, it makes sense to test with PHP again.


XL

On Wednesday, October 5, 2011 6:12:34 PM UTC-4, Axel Schwenke wrote:
<snip all of the great info>
You know, honest to God, sometimes I just feel so freakin' STUPID! LOL After I get it all figured out, it's obvious what was needed, but until you know...

I get that hindsight is 20/20, but seriously, I've been working on this for 2 days! My server management company couldn't figure it out, either, which leads me to believe that they made a minimal attempt, and just blew me off.

Alright, I'm gonna go beat my head against the wall for awhile. Thanks for the help, all.

But, since the current error doesn't give a line number, I really
wouldn't have anything to compare.

Turn on PHP error logging (at least for debugging purposes, not in
production code). Things you should include in php.ini include:

error_reporting = E_ALL
display_errors = On

OR figure out where the errors are currently being logged, and read
them.

It is possible you are getting piles of errors which are not getting
logged, or are getting logged somewhere you are not reading, then
the error you are seeing is generated from something PHP is doing
internally, perhaps to clean up what you did.

error a lot faster (immediately, versus a wait of 45 seconds or
so). But still, completely disabling the firewall on both servers
didn't fix the problem.

This suggests that a TCP/IP connection to the database was failing
with the firewall on. And you aren't seeing the error for it.