Can't connect to local MySQL server through socket

[stoneyd]

PHP Warning:  mysql_connect(): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (11) in 'some/file/on/my/web/server.php'

 

The file name just changes but I get a ton of these. it seems like the server cant connect to the server at all times.

 

MySQL Server version: 5.1.49-3

PHP Version 5.3.3-7+squeeze1

 

Since most of the files I have troubles with are encrypted I can't really give you a query statement. And I dont think its specific to a query.

i can cat you anything from my.cnf or php.ini. Not sure what kind of info you need. Is it possible that there is a limit how many connections any unix socket can have?

[Maq]

Can you post your my.cnf?

[stoneyd]

Here ya go.

 

[attachment deleted by admin]

[Maq]

Does this file exist?

/var/run/mysqld/mysqld.sock

[betterphp]

You could just use 127.0.0.1

 

mysql_connect('127.0.0.1', 'user', 'pass');

for example.

 

I have to do this on my server because its not easy to move a socket file (or what ever their real name is) and I run apache in a chroot.

[stoneyd]

I tried that. It bogged down the site. Apparently using the regular 127.0.0.1:3306 causes too much lag and the site massively slows down.

[betterphp]

I tried that. It bogged down the site. Apparently using the regular 127.0.0.1:3306 causes too much lag and the site massively slows down.

How slow ? It should not be noticeably any different unless your server is at its bandwidth limit all of the time.

[stoneyd]

the other sysop was the one who noticed the slow down. i was asleep when it started slowing down. I'll try switching it back. vnstat for today reports avg rate at 4.41mb/s so far. I'll try switching it back.

 

EDIT: Well I tried switching to 127.0.0.1:3306 but apparently the php file i dont have access to editing (they're encrypted) don't like the new config and it breaks the site.

 

 

define('MYSQL_HOST', 'localhost');

tried changing to

define('MYSQL_HOST', '127.0.0.1:3306');

and it breaks my site

[betterphp]

if you are the server admin then you can put the socket back be forcefully reinstalling the mysql-server package.

 

I had a problem where the socket went missing before after a reboot and that's how I got it back.

 

4.41 as an average is very high, although saying that I don’t imagine mysql uses huge amounts of bandwidth.

[stoneyd]

if you are the server admin then you can put the socket back be forcefully reinstalling the mysql-server package.

 

The socket file is still there. far as i can tell it doesnt go away unless i shutdown the mysql server. So i'm not sure how forcefully reinstall the mysql-server package would help. Could you clarify?

 

Also 4.41 is both rx and tx. yesterday's average is 5.03Mbit/s

We have about 25k users and average user daily is just over 3500.

[betterphp]

Hmm, well if the file is there then it probably would not help that much at all

 

by reinstall I meant

 

apt-get remove mysql-server
apt-get purge mysql-server
apt-get install mysql-server

 

the package name might be different.

 

If you do end up trying that make sure to backup the db first.

 

I guess it could be a permissions thing too. I don't really know to be honest, I just used 127.0.0.1 I'll have a look at your my.cnf now and see if anything there could be the problem.

 

It would be nice if it told you why it cant connect eh.

 

EDIT: actually check /var/log/mysql/mysql.err (I think it that) might tell you why.

 

EDIT2: general_log_file        = /var/log/mysql/mysql.log logging all queries is a major performance killer.

[stoneyd]

It would be nice if it told you why it cant connect eh.

 

rofl. Yeah right. Like it ever would tell me what the problem is.

 

However instead of using 127.0.0.1 i decided to try using the real ip of the server. and the site isnt broken. we'll see how this goes.

*stoneyd crosses fingers

[gizmola]

the other sysop was the one who noticed the slow down. i was asleep when it started slowing down. I'll try switching it back. vnstat for today reports avg rate at 4.41mb/s so far. I'll try switching it back.

 

EDIT: Well I tried switching to 127.0.0.1:3306 but apparently the php file i dont have access to editing (they're encrypted) don't like the new config and it breaks the site.

 

 

define('MYSQL_HOST', 'localhost');

tried changing to

define('MYSQL_HOST', '127.0.0.1:3306');

and it breaks my site

 

I'll be the first to say that this seems like you're chasing your tail.  You could be getting the error intermittently because you are running out of mysql connections. 

 

In terms of the networking:

 

define('MYSQL_HOST', 'localhost');
tried changing to
define('MYSQL_HOST', '127.0.0.1:3306');
and it breaks my site 

 

You would not want to set the MYSQL_HOST to the ip/port.  Mysql already is configured to use its well known port '3306'.  If you really want to try that change the host to 127.0.0.1, which should be the same as localhost if your box is configured correctly.

 

However, I suspect that your real problem, since it's intermittent, is that you are running out of mysql connections.  If the application is using persistent connections (mysql_pconnect) this is a frequent problem.  If it's just simply load related, then take a look at upping the number of mysql connections allowed.

 

Login to your mysql server using the mysql command line client as the root user, and run

 

show processlist

 

show variables

 

Will show you all the current configuration settings for the running server, but in this case:

 

select @@max_connections;

 

If you're even coming close, this might be a good indicator that you're running out of connections.

 

 

Cross reference your max_connections setting from your /etc/my.cnf. 

 

 

[stoneyd]

mmk well even with just 127.0.0.1 it had issues.

my.cnf max connections is set to 10k

processlist is minimal like 3 entries

 

[betterphp]

mmk well even with just 127.0.0.1 it had issues.

 

what were those issues ? And was there anything useful in the error logs ? I think they should have the reason that the connection fails in.

 

You could do it the lazy way and try setting the limit to 100k and see if that helps.

[stoneyd]

mmk well even with just 127.0.0.1 it had issues.

 

what were those issues ? And was there anything useful in the error logs ? I think they should have the reason that the connection fails in.

 

You could do it the lazy way and try setting the limit to 100k and see if that helps.

 

Orignally the max connection was set to about 1000. so i think 10k is pretty high comparatively.

 

/var/log/mysql.error.log logs only the startup info then nothing.

syslog doesnt do much for me either.

php and lighttpd logging doesnt have much

[betterphp]

if nothing is going in the logs that means you are not actually getting to the server, so yeah its not a max connection issue.

 

could you use a test script in the same folder as your site to try the connections

 

<?php

mysql_connect('localhost', 'user', 'pass');

mysql_connect('127.0.0.1', 'user', 'pass');

?>

 

and see if either work ?

[stoneyd]

if nothing is going in the logs that means you are not actually getting to the server, so yeah its not a max connection issue.

 

could you use a test script in the same folder as your site to try the connections

 

<?php

mysql_connect('localhost', 'user', 'pass');

mysql_connect('127.0.0.1', 'user', 'pass');

?>

 

and see if either work ?

 

 

See it is a intermittent issue. i've got the host set to the the actual ip. the localhost works but the 127.0.0.1 returns

'Warning: mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet', system error: 111 in /var/www/dev/test.php on line 4'

[betterphp]

what’s the load average like on the server ? I guess it might just not be able to respond to the request some of the time. Effectively a hardware limit on the number of connections.

 

Although you said there were only 3 connections ? so its not that likely to be that :?

 

I'm out of things to suggest to be honest  :-\

[stoneyd]

what’s the load average like on the server ? I guess it might just not be able to respond to the request some of the time. Effectively a hardware limit on the number of connections.

 

Although you said there were only 3 connections ? so its not that likely to be that :?

 

I'm out of things to suggest to be honest  :-\

 

Well, the average load on the server is usually under 5. tho i think i know why 127.0.0.1 isnt working. the bind in my.cnf is set to my actual ip and not the localhost or 127.0.0.1. I think i set it up like that because from my understanding 127.0.0.1 would have prevented outside connections from a diff server connecting to mysqld. Well since switching to the IP i havent gotten anymore of those errors about cant find mysqld.sock. who knows maybe this fixed it.

[betterphp]

5 is pretty high, unless you have like 10 CPU cores.

 

I think localhost and 127.0.0.1 are available regardless of the bind setting but don't quote me on that.

 

If it seems to be working, I say just go with it

[gizmola]

what’s the load average like on the server ? I guess it might just not be able to respond to the request some of the time. Effectively a hardware limit on the number of connections.

 

Although you said there were only 3 connections ? so its not that likely to be that :?

 

I'm out of things to suggest to be honest  :-\

 

Well, the average load on the server is usually under 5. tho i think i know why 127.0.0.1 isnt working. the bind in my.cnf is set to my actual ip and not the localhost or 127.0.0.1. I think i set it up like that because from my understanding 127.0.0.1 would have prevented outside connections from a diff server connecting to mysqld. Well since switching to the IP i havent gotten anymore of those errors about cant find mysqld.sock. who knows maybe this fixed it.

 

Well you probably should listen on all ports, or include 127.0.0.1.  That way you don't add overhead to your nic when the server really only wants to talk to itself over the loopback.  Or as you say, if you don't need this server to talk to any other servers, you should change it to 127.0.0.1.

[stoneyd]

Well you probably should listen on all ports, or include 127.0.0.1.  That way you don't add overhead to your nic when the server really only wants to talk to itself over the loopback.  Or as you say, if you don't need this server to talk to any other servers, you should change it to 127.0.0.1.

 

Well if i could have it bind to something without it blocking others then i'd be happy. but from my understanding localhost blocks everything but local connections and 127.0.0.1 as a synonym for localhost would prolly do the same thing. Can someone confirm this?

[stoneyd]

PHP Warning:  mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet', system error: 110

 

And now I get this error. Lovely the first problem disappear and now this shows up. We got 500'd and started getting those errors.

[betterphp]

PHP Warning:  mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet', system error: 110

 

And now I get this error. Lovely the first problem disappear and now this shows up. We got 500'd and started getting those errors.

 

are you sure that's not the same problem, it was intermittent.