3

situation as follows:

php script on server a, run by user 'web' (nginx + php-fpm) should access mysql on server b via pdo library, but gets SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (4)

server a centos 6.5, php 5.3; server b centos 6.2, mysql 5.1

however, mysql command line client - run as unprivileged user - can connect perfectly fine, reproducible and stable.

once mysql command line client established a successful connection between the servers, the php script runs successfully as well for about 5-7 minutes, presumably until the mysql command line client connection times out.

what am i missing?

here's the test script:

   <?php
   try {
   $dbh = new PDO('mysql:host=111.111.111.111;dbname=myname;port=3306', 'myuser',
    'mypass');
  echo 'Connected to database';
}
catch(PDOException $e)
{
echo $e->getMessage();
}
Andreas Wagner
  • 133
  • 1
  • 7

3 Answers3

1

So it turns out that this had been a routing issue after all.

We are currently using a statically routed network, and the mysql server a did not have a route defined back to server b.

the network setup is a bit messy, as the physical connection goes like this: server b (off-site) > vpn gw > internet > checkpoint fw > openwrt router > server a

Apparently the mysql command line client is more forgiving and was able to establish that connection anyway, but the pdo library was not.

After adding the route, the pdo library could successfully establish connections on its own and is happily running after since.

So, check your routes, even if ping/telnet/mysql connections are seemingly working.

By the way, would the error "Interrupted system call" cover this scenario?

Andreas Wagner
  • 133
  • 1
  • 7
0
$ perror 4
OS error code   4:  Interrupted system call

You should simply try again when receiving this particular error.

This may be an indication of a heavily loaded server/network. In this case you can try increasing the connect timeout as a short-term fix, and resolving the congestion issues long-term.

Community
  • 1
Michael Hampton
  • 237,123
  • 42
  • 477
  • 940
  • interesting. thank you for pointing out the last part, i was not aware of that. however, it hardly seems applicable to this setup: the server load is negligible, mysql transactions are practically non-existent at the moment, and the consumed bandwith is in the low kbits. moreover, it would not explain the fact that the connection is open and stable for a specific amount of time after an (basically always) successful mysql cl client connect. – Andreas Wagner Jul 02 '14 at 20:15
0

Just ran into this kind of problem, though it was a bit different in terms of servers, addresses etc. The important thing that helped me to resolve this is:

  1. Try accessing the DB in question through CLI from the same box your PHP runs on
  2. Try to connect the DB through PHP from a different box

If the first one fails and the second one works it can be that you've got an extra user account in your DB designated to the host you're connecting from.

Check the existing users with 

select host, user, password from mysql.user;

If you find there something like:

| host           | user           | password
+----------------+----------------+------------------------------------------
| 10.10.10.1     | myuser         | 
| %              | myuser         | *0803B925439C244BA857C3FD07A4F86A556F0925

this means you have that extra user attached to the PHP host you're connecting from. Either drop it or make proper password an privileges for this host-specific user.

Varrah
  • 1
  • 2