Confusing PDO-only problem : Can't connect through socket/Access denied/Can't connect to server (shared host)

Question

So the problem changed from what it was, i'll leave the original question below to prevent bad reviews on answers like I had after someone editing his question I answered :

So I am working on a (really lame) shared hosting which has PDO installed, but it doesn't work. With default parameters

<?php
try {
    $dbh = new PDO('mysql:host=localhost;dbname=THE_DB_NAME', 'THE_USER', 'THE_PASSWORD');
    echo 'Connected to database';
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>

it throws this message :

SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

With a simple mysql_connect, it works.

And the socket path seems correct (both phpinfo and this query :

show variables like 'socket';

confirm.

Localhost redirects to 10.103.0.14 (this data comes from mysql_get_host_info() and in phpMyAdmin)

In the PDO, if i replace localhost by 127.0.0.1 i will get

SQLSTATE[HY000] [2003] Can't connect to MySQL server on '127.0.0.1' (111) 

And if i replace localhost by 10.103.0.14 :

Access denied for user 'USER_NAME'@'10.103.0.14' (using password: YES

Both IP adress (127.0.0.1 and 10.103.0.14) work with mysql_connect.

So apparently the problem comes from the PDO connection.

Does somebody knows where this could come from, or/and any way to fix it ?

Some server datas :

The PHP Version : 5.2.10 You can see the server's phpinfo : http://web.lerelaisinternet.com/abcd.php?v=5 No command line possible. (i know it should be the tech suport's job, but they're reaaaaaly slow)

Thanks

Previous question :

How to find the mysql.sock on a shared host (tricky way needed...)

So today's problem is : The PDO connection doesn't work on a shared host, and it's supposed to (it's installed on the server). Just a basic PDO connection :

<?php
try {
    $dbh = new PDO('mysql:host=localhost;dbname=THE_DB_NAME', 'THE_USER', 'THE_PASSWORD');
    echo 'Connected to database';
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>

throws this message :

SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

A regular mysql connection :

mysql_connect("localhost", "THE_USER", "THE_PWD") or die(mysql_error()); 
mysql_select_db("24DLJLRR1") or die(mysql_error());;
echo 'Connected to database <br/>';

works fine.

So apparently it cannot find the .sock. I think specifying the correct address should work, i tried some "classic" mysql path that I found on internet, without success. The phpinfo says it is at this adress (/var/lib/mysql/mysql.sock) (The PHP Version is 5.2.10) You can see the server's phpinfo : http://web.lerelaisinternet.com/abcd.php?v=5

So i am trying to figure out where the hell it is !!! I tried to look in the phpMyAdmin interface, but i couldn't find the info, plus it seems that phpMyAdmin connects to a different server (it has a different IP adress, and trying to connect to it with php gives a "Wrong password" error). The mysql_connect also connects to this adress, i think it redirects to a different server with some internal password/login.

Well if you have any idea of how to obtain this info (the provider's technical support is "fixing the problem"... it's been 1 month...). Also maybe the problem comes from somewhere else, but the same stuff works on other shared hosts...

The need of PDO is because I use the Symfony framework with Doctrine for this website, and the Doctrine plugin needs PDO... I don't want to redo the website from scratch !

Thanks for your help !

Osteo

It could be that the socket exists, but you don't have the necessary rights to access it. Anyway, isn't this a question for your provider's tech support? – Barcelona

I guess it's there job !they're too lame, we've been waiting for more than a month, plus it's in France, it takes 30 minutes to get the right person. The problem is that it's an important customer and we have to ship... (the website is working on a mirror right now, but we the customer wants it on his host and it belongs to the french telecom company, so we can't say it's lame). Moreover we can not talk directly to the server tech support. So how come i wouldn't have the rights to access it ? I mean if PDO is installed and i can connect by mysql_connect i should have them no ? – Osteo

as far as i understand it, you are out of luck. the maped localhost to a diffrent ip. but it seems the mysql server itself only allow access from localhost (which in their case resolves to your 10.103.0.14). pdo seems to use sockets if the host is localhost. (see php.net documentation, pdo - connection second comment) but why mysql_connect works with 127.0.0.1 is very strange. in this case pdo connect with host=127.0.0.1 should work too. – Endear

Answer 1

One year later, I found a solution for this issue : using a SQLite database. PDO worked fine, but not with MySQL

** EDIT ** as everyone is downvoting this: This solved my issue (I'm the OP). I was using Doctrine, so switching RDBMS was easy and quick. Also the website was some a home made CMS, with very few trafic, so SQLite was fine.

I know it's not a real "Answer" to the problem, but if someone is in the same context: a crappy shared hosting which you can't change with this weird PDO-MySQL bug AND is using doctrine. This IS a solution. I can delete this answer, but if I had thought of this at the time of the OP, I would have saved a lot of time.

Osteo

This worked for me, but Brent's option looks more interesting – Osteo

Your solution to the OP was to switch RDBMS? IMO this isn't a solution. – Saragossa

Not a solution when the OP problem is still there – Veridical

Well, I was stuck, the customer was angry, my boss yelling at me. I switched RDBMS, now the customer is happy, and my boss is smiling. So I guess that can be called a solution. – Osteo

This is a commercial solution, not a technical solution. – Cornstalk

Answer 2

This was already marked as answered, but not really solved (without changing databases). So, just in case someone like me also experiences this problem...

The easiest way to fix this is to first get the socket path (either by looking in the php.ini file or by using: phpmyadmin or the console (or construct it in mysql or mysqli)

...to run the following query (anything but PDO):

show variables like 'socket';       //as mentioned by symcbean

THEN, in the PDO connection string, change it to use the socket instead of a hostname:

$dbc = new PDO("mysql:unix_socket=/var/run/mysqld/mysqld.sock;dbname=$DBName", $User, $Password, array(PDO::ATTR_PERSISTENT => true)); // using persistent connections

This worked for me.

Pipette

Answer 3

FWIW, I had this issue and changed my host from 'localhost' to '127.0.0.1'.

I have no clue why localhost wasn't working, but that did the trick.

Odd thing is, we have tons of servers and it works on almost every one using 'localhost'

Helenehelenka

Same here. Drop me a line on gplus.to/swader if you find out why please. My hosts file is set up to direct 127.0.0.1 to localhost so that's alright. Odd. – Lawana

I saw the same error on a server and there was a second mysql-server active at this time (ps aux | grep mysql) ... the connection via "127.0.0.1" was still working but via "localhost" (unix-socket) wasn't working: "Can't connect to local MySQL server through socket ..." -> we had to "kill -9" and restart mysql :/ – Durand

Answer 4

Is your server running with SeLinux enabled (enforcing)? If it is, try running as root:

# setsebool -P httpd_can_network_connect on

Reputation

Answer 5

Can you try 127.0.0.1 as the server name instead of localhost?

IIRC, with some mySQL drivers / adapters, this decides whether the socket is used for establishing the connection or not.

Barcelona

I get a different error : SQLSTATE[HY000] [2003] Can't connect to MySQL server on '127.0.0.1' (111) – Osteo

Is there a mySQL server running on localhost? Do you know for sure? – Barcelona

Ah, sorry, you say above that there is. – Barcelona

Well it's a bit confusing, because mysql_get_host_info() function gives me an ip address (10.103.0.14), so not 10.0.0.127. If i try this IP address from the PDO i get : Access denied for user 'USER_NAME'@'10.103.0.14' (using password: YES) – Osteo

I was already beginning to have the feeling that your provider is mapping localhost to a different IP (not sure yet, though). Can you try my comment from symcbean's answer? – Barcelona

this comment helped me alot and saved me hours of time. Thanks Pekka! :) – Batey

Answer 6

Using the connection which works, run the query:

show variables like 'socket';

(this behaves just like a select statement)...and you'll get the path of the running socket.

Then check the file permissions.

Duque

+1 the socket path quoted above does definitely not exist (Error 2 = No such file or directory). This sounds like the best way to go. – Barcelona

Thank's a lot ! The problem now is I get : Array ( [0] => socket [1] => /var/lib/mysql/mysql.sock ), so the socket IS at the right place... I think i'm going to kill myself. How can i check the file permission ? (which i think should have, if it works with mysql_connect) Maybe it's just a problem with their PDO installation... – Osteo

I think the mysql_connect connection is not using the socket, which is why it works. Just for laughs, what happens if you replace localhost by 127.0.0.1 in the working mysql connection? – Barcelona

This is strange. I think PDO uses different client libraries than classic mysql_connect() but there is something different wrong here... I have no idea what this could be. – Barcelona

Yeah, i feel more and more confused, i changed the topic because the socket seems at the right place... hope someone will know. I didn't want to create a new question, because lots of elements are in the comments, but if nobody answers i'll mark this one as the correct answer (cause it made me find out the socket was at the right place...) – Osteo

Yup. You might really have to change providers or to increase pressure on the support staff :( – Barcelona

To find out the permissions, depending on how the server is configured you could just ls -l $socket or print_r(stat($socket)) (and decode the numbers) – Duque

Answer 7

I had the problem that production version worked just fine and a test version wasn't able to connect PDO :/ both versions was located at same servers, test in a sub directory.

The fix was replacing in DSN the localhost for ip.

'mysql:host=localhost;dbname=db'

became

'mysql:host=127.0.0.1;dbname=db'

Feudal

Yes. Fixed it BUT WHY? Ping localhost gives me 127.0.0.1 as the IP so why can localhost not be used?? – Garboard

it seems something wrong with host resolve on machines. – Feudal

Answer 8

try:

exec('`which mysql_config` --socket');

this should show you the configured socket.

Endear

If he can run external programs, good idea. It also may be an idea to find for any mysql.sock files. – Barcelona

Well seems i can't Warning: exec() has been disabled for security reasons Thanks thought :) – Osteo

Answer 9

I found the reason for the strange behaviour. If bind-address is different to 127.0.0.1 or 0.0.0.0 (all addresses) PDO can't connect to 127.0.0.1.

Bunton

Answer 10

For what it's worth, I found this page after having the exact same issue. I am on a server running Apache & PHP only - MySQL is installed on another machine. I tried both the DNS name of the server and its IP and confirmed I could ping it. A PHP app on the same machine is talking to the database fine, using old syntax mysql_connect( ). But PDO from the CLI was throwing this error.

The solution for me was to check my DSN. Any typo in the DSN itself is ignored silently, and PDO assumes you mean localhost. My issue was I had "name=" instead of "dbname=" in the DSN.

Vector

Answer 11

The Issue In the Mysql configuration It you need to disable the option of skip-networking in my.conf configuration file this should work fine reference http://www.wolfcms.org/forum/post7098.html#p7098

Brie

Answer 12

I just solved a similar issue. My guess is you probably replaced your mysql_connect() statement with the PDO equivalent. Don't forget you still have lots of other code dependent on that old connection statement. Try keeping the mysql_connect in place while writing in the PDO code.

Whitening

Answer 13

What worked for me was specifying the port number like so:

mysql:hostname;port=3306;dbname=dbname;

This got it to work when connecting to a local database. Now I'm working on getting it to work with a remote db.

Ruskin

Answer 14

My problem may be different to the OP, but I thought it was worth posting. I did a software upgrade on a VM, then rebooted and got the OP's error message. It turned out to be an out-of-memory problem preventing mysql from starting. Deleting a few large files made the problem go away.

Hypertensive