Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11

    Intermittent can't connect to mysql socket


    Hi folks,

    I wasn't sure if this should go in the mysql forum or the php forum, but I've been searching Google for answers, but haven't found anything for an issue like this one.

    PHP version: PHP 5.3.10-1ubuntu3.4 with Suhosin-Patch (cli) (built: Sep 12 2012 18:59:41)
    MySQL version: 5.5.28-0ubuntu0.12.04.2-log

    I just upgraded my 64-bit server from Ubuntu 10.04 to 12.04.1 not quite two weeks ago. Since then this issue has only come up twice in the Apache error log and no logs seem to have any entries pointing to a cause:

    PHP Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

    A mysql restart solves it, but I'd rather not have it happen at all.

    There were some grumblings in the mysql error log for "Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT" but that was an insert query that used a subselect on a different table to get the insert value. I split it up into two queries. Other than that, the mysql error log only shows entries for when I restarted mysql.

    I have a second server configured the exact same and it doesn't have the issues, but it was upgraded over a month ago and doesn't have the user or data volume.

    No other logs in /var/log mention anything for the time when the db was unavailable to php. Mysql was still running, though. Php just couldn't get to it.

    Is there anywhere else I can look to see what causes this?

    Thanks,
    Mike
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,538
    Rep Power
    595
    Since you didn't show any code and we are not clairvoyant, it is impossible to say what might be wrong from a PHP perspective. The obvious guess is that there is a network or MySQL server problem.

    P.S. You need to consider rewriting your code to use PDO rather than the depreciated MySQL extensions. That may also give you better and more comprehensive trouble shooting capabilities.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11
    sorry...didn't figure I needed to post code since the issue is intermittent on only one of my identical servers. (I also have a dev box that's the same, but w/o mysql replication).

    This is in the session/db handler code included in the header of each page of the CMS:
    PHP Code:
    include_once(INCLUDE_PATH 'config.inc.php');
    $db mysql_connect(DB_HOST ':' DB_PORTDB_USERDB_PASSWORDTRUE);
    mysql_select_db(DB_NAME$db); 
    I pass TRUE as the 4th param, as the code sometimes connects to a remote database for importing content.

    All queries use (with different vars for $result):
    $result=mysql_query($SQL, $db);

    I also have a dashboard site that uses the PhpLens adodb library, with the connection and query functions straight out of the examples.

    Perhaps this should be moved to the mysql forum after all. I started in the PHP section, as the PHP error was the only thing I could find in the server logs.

    How do you move a thread?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,538
    Rep Power
    595
    Click on the red triangle in the upper right and select wrong forum under report type. A mod will move it.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    The fact that a MySQL restart solves the problem makes me suspect a problem with MySQL rather than PHP. When this problem happens, are you able to connect to MySQL manually via the command line?

    I pass TRUE as the 4th param, as the code sometimes connects to a remote database for importing content.
    The fourth parameter to mysql_connect has nothing to do with connecting to remote databases. It could be the source of your problem, but only if your code is calling mysql_connect more than once in a request.

    Also note that PHP's mysql library is deprecated, although at the moment still supported. This is probably not related to the problem either, but switching to a more modern library might solve the problem.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11
    Originally Posted by E-Oreo
    The fact that a MySQL restart solves the problem makes me suspect a problem with MySQL rather than PHP. When this problem happens, are you able to connect to MySQL manually via the command line?


    The fourth parameter to mysql_connect has nothing to do with connecting to remote databases. It could be the source of your problem, but only if your code is calling mysql_connect more than once in a request.

    Also note that PHP's mysql library is deprecated, although at the moment still supported. This is probably not related to the problem either, but switching to a more modern library might solve the problem.
    The code does sometimes connect to more than one database server on a single page, that's why I use the fourth param. I just didn't word that bit very clearly. The code is the same on all of my live servers, however.

    I have thought about updating to use mysqli, but haven't had the time to test. PDO seems to be a bit slower that mysqli and mysql_* in benchmarks, but the benchmark tests I've seen were a couple of years old.

    Our Zabbix server didn't report mysql as down and, if I recall correctly, I could connect to mysql via the command line.

    Thanks,
    Mike
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11
    it happened again late last night and lasted for 6 minutes. I did nothing to resolve it. It resolved itself. As before, there are no entries in other system logs pointing to an issue.

    I'd move this to the mysql forum, but I don't appear to have proper privs to do so. Can someone help me with moving this?

    Thanks,
    Mike
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11

    Intermittent can't connect to mysql socket


    (This was in the PHP section, but I moved it here. Original thread: http://forums.devshed.com/mysql-help...et-935305.html )

    ### Original post from 11/30/2012

    PHP version: PHP 5.3.10-1ubuntu3.4 with Suhosin-Patch (cli) (built: Sep 12 2012 18:59:41)
    MySQL version: 5.5.28-0ubuntu0.12.04.2-log

    I just upgraded my 64-bit server from Ubuntu 10.04 to 12.04.1 not quite two weeks ago. Since then this issue has only come up twice in the Apache error log and no logs seem to have any entries pointing to a cause:

    PHP Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

    A mysql restart solves it, but I'd rather not have it happen at all.

    There were some grumblings in the mysql error log for "Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT" but that was an insert query that used a subselect on a different table to get the insert value. I split it up into two queries. Other than that, the mysql error log only shows entries for when I restarted mysql.

    I have a second server configured the exact same and it doesn't have the issues, but it was upgraded over a month ago and doesn't have the same user or data volume.

    No other logs in /var/log mention anything for the time when the db was unavailable to php/Apache. The Mysql process was still running, though. Php just couldn't get to it.

    Is there anywhere else I can look to see what causes this?


    ### Update from 1/2/2013

    Well, it's happened a few times since my last post. It happened at 9AM today and I got en error number in the Apache error log, but haven't been able to find much to help me with it:

    PHP Warning: mysql_connect(): Lost connection to MySQL server at 'waiting for initial communication packet', system error: 95

    Some sites suggest changing the mysql wait_timeout value to a lower number than the default of 28800.

    After this happened, I ran mysqlcheck to check and auto-repair the tables.

    The latest incident was a minute or two ago. I was watching the Apache error log, so I saw it as it happened. I tried to connect to mysql from the command line (#mysql -u root -p), but couldn't:

    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)

    Since I was there, I restarted Apache first to see if it was the culprit (unclosed handles), but it didn't help. A mysql restart did the trick, but i could have just waited a few minutes for it to resolve itself like it has done every other time. I didn't think to make sure that the socket file was still there.

    This is getting serious. Does anyone have any suggestions?

    Thanks,
    Mike
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11
    it just happened again. This time, I checked for the socket file and it was there:

    /var/run/mysqld/mysqld.sock

    Still researching "'waiting for initial communication packet' system error: 95"
  18. #10
  19. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,180
    Rep Power
    9398
    Assuming 2 is file not found, 95 is operation not supported (which may not be an error), and 111 is connection refused, it sounds like MySQL isn't keeping the socket alive.

    Can you change the database connection settings to use 127.0.0.1 instead of localhost? As a test.

    [edit] I assume that you've been checking each time that mysqld was still running?
    Last edited by requinix; January 2nd, 2013 at 06:06 PM.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11
    Originally Posted by requinix
    Assuming 2 is file not found, 95 is operation not supported (which may not be an error), and 111 is connection refused, it sounds like MySQL isn't keeping the socket alive.

    Can you change the database connection settings to use 127.0.0.1 instead of localhost? As a test.

    [edit] I assume that you've been checking each time that mysqld was still running?
    Thanks for the reply, requinix!

    I tested on a dev server and then on the affected server and 127.0.0.1 worked just as well as localhost in the connection params for both.

    You know, I don't think I'd checked for the process. I may have, but I don't recall. I've been logged into the mysql prompt when it started and was able to hit enter for a new line, but further testing shows me that I'll get a new line even if I stop mysqld while at the mysql prompt. I'll check for the process the next time I see this happening.

    Wonder why MySQL wouldn't be keeping the socket alive. It seems to take a bit more than 6 minutes for it to resolve itself when I'm not on top of it.
  22. #12
  23. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,180
    Rep Power
    9398
    Originally Posted by storemike
    I tested on a dev server and then on the affected server and 127.0.0.1 worked just as well as localhost in the connection params for both.
    Meaning... didn't work, right?

    Originally Posted by storemike
    but further testing shows me that I'll get a new line even if I stop mysqld while at the mysql prompt
    As opposed to the Windows cmd.exe, with bash even when something is running in the foreground you can still type and it will appear on screen (unless the program actively prevents that).
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11
    127.0.0.1 worked just fine when I used it in the connection params. To make sure the new params were being used, I set it to 127.0.0.11 and it didn't connect:

    PHP Warning: mysql_connect(): Can't connect to MySQL server on '127.0.0.11' (111)

    Sorry that I wasn't clear in my reply about that.
  26. #14
  27. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,180
    Rep Power
    9398
    Right, but does it work when localhost does not?
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11
    I don't know. I'll have to remember to try the next time this happens. I'll leave the change to 127.0.0.1 in there, but commented out for quick testing.

    Where did you find the description of the error numbers? I tried the mysql error docs, but didn't get the info you posted. The descriptions sure do help.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo