Lost connection to MySQL

Question

I'd installed Apache, mysqld under Slackware. Apache working. But script:

$dblocation="127.0.0.1";
    $dbuser="root";
    $dbpasswd="unfriendly_possum";
    $dbcnx=mysql_connect($dblocation, $dbuser, $dbpasswd);
    if(!$dbcnx)
    {
        echo("Error");
        exit();
    }
    $dbq=mysql_query("select version();");
    echo(mysql_result($dbq,0));

returns an error

Warning: mysql_connect() [function.mysql-connect]: Lost connection to MySQL server at 'reading initial communication packet', system error: 111 in /usr/local/httpd/htdocs/mysql.php on line 8
Error

Help (i'm crying)

UPD i've corrected $dblocation="127.0.0.1" to "localhost", but another error happens:

Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2) in /usr/local/httpd/htdocs/mysql.php on line 8
Error

php config fragment

[SQL]
sql.safe_mode = Off

[ODBC]
;odbc.default_db    =  Not yet implemented
;odbc.default_user  =  Not yet implemented
;odbc.default_pw    =  Not yet implemented

; Allow or prevent persistent links.
odbc.allow_persistent = On

; Check that a connection is still valid before reuse.
odbc.check_persistent = On

; Maximum number of persistent links.  -1 means no limit.
odbc.max_persistent = -1

; Maximum number of links (persistent + non-persistent).  -1 means no limit.
odbc.max_links = -1

; Handling of LONG fields.  Returns number of bytes to variables.  0 means
; passthru.
odbc.defaultlrl = 4096

; Handling of binary data.  0 means passthru, 1 return as is, 2 convert to char.
; See the documentation on odbc_binmode and odbc_longreadlen for an explanation
; of uodbc.defaultlrl and uodbc.defaultbinmode
odbc.defaultbinmode = 1

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = On

; Maximum number of persistent links.  -1 means no limit.
mysql.max_persistent = -1

; Maximum number of links (persistent + non-persistent).  -1 means no limit.
mysql.max_links = -1

; Default port number for mysql_connect().  If unset, mysql_connect() will use
; the $MYSQL_TCP_PORT or the mysql-tcp entry in /etc/services or the
; compile-time value defined MYSQL_PORT (in that order).  Win32 will only look
; at MYSQL_PORT.
mysql.default_port =

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
mysql.default_socket =

; Default host for mysql_connect() (doesn't apply in safe mode).
mysql.default_host =

; Default user for mysql_connect() (doesn't apply in safe mode).
mysql.default_user =

; Default password for mysql_connect() (doesn't apply in safe mode).
; Note that this is generally a *bad* idea to store passwords in this file.
; *Any* user with PHP access can run 'echo get_cfg_var("mysql.default_password")
; and reveal this password!  And of course, any users with read access to this
; file will be able to reveal the password as well.
mysql.default_password =

; Maximum time (in seconds) for connect timeout. -1 means no limit
mysql.connect_timeout = 60

; Trace mode. When trace_mode is active (=On), warnings for table/index scans and
; SQL-Errors will be displayed.
mysql.trace_mode = Off

[MySQLi]

; Maximum number of links.  -1 means no limit.
mysqli.max_links = -1

; Default port number for mysqli_connect().  If unset, mysqli_connect() will use
; the $MYSQL_TCP_PORT or the mysql-tcp entry in /etc/services or the
; compile-time value defined MYSQL_PORT (in that order).  Win32 will only look
; at MYSQL_PORT.
mysqli.default_port = 3306

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
mysqli.default_socket =

; Default host for mysql_connect() (doesn't apply in safe mode).
mysqli.default_host =

; Default user for mysql_connect() (doesn't apply in safe mode).
mysqli.default_user =

; Default password for mysqli_connect() (doesn't apply in safe mode).
; Note that this is generally a *bad* idea to store passwords in this file.
; *Any* user with PHP access can run 'echo get_cfg_var("mysqli.default_pw")
; and reveal this password!  And of course, any users with read access to this
; file will be able to reveal the password as well.
mysqli.default_pw =

; Allow or prevent reconnect
mysqli.reconnect = Off

[mSQL]
; Allow or prevent persistent links.
msql.allow_persistent = On

; Maximum number of persistent links.  -1 means no limit.
msql.max_persistent = -1

; Maximum number of links (persistent+non persistent).  -1 means no limit.
msql.max_links = -1

my.cnf part:

[mysqld]
log             =/var/log/mysqld/mysqld.log
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Disable Federated by default
skip-federated

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

score 1 · Answer 1 · answered Feb 21 '10 at 02:37

1

Check the MySQL log file if there is something about this error. If you want to connect using MySQL Unix socket, change this line :

socket          = /tmp/mysql.sock

to

socket          = /var/run/mysql/mysql.sock

That should fix this problem.

answered Feb 21 '10 at 02:37

Kedare

1,786
4
20
37

score 0 · Answer 2 · answered Feb 20 '10 at 19:00

0

Try:

$dblocation="localhost";

answered Feb 20 '10 at 19:00

EEAA

109,363
18
175
245

Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2) in /usr/local/httpd/htdocs/mysql.php on line 8 Oshibka podklucheniya k base – Feb 20 '10 at 19:04
Please post the [mysqld] section of your my.conf. – EEAA Feb 20 '10 at 19:57

score 0 · Answer 3 · answered Feb 20 '10 at 20:34

0

Is MySql running? And is PHP correctly set to connect to it in its configuration file(/etc/php.ini)?

answered Feb 20 '10 at 20:34

Kevin M

2,312
1
16
21

running, i've posted config part – Feb 20 '10 at 21:07
Your php.ini isn't really relevant in this case. As I asked before, post the [mysqld] section of your my.cnf. – EEAA Feb 20 '10 at 21:12
ok, just posted – Feb 20 '10 at 21:20

score 0 · Answer 4 · answered Feb 21 '10 at 10:57

I'm basically going to parrot the good ideas already posted but I want you to try a couple of things in order in hopes of locating the specific culprit.

Your code is sound so try to test this in parts so you can cross off what's actually working. You should find that both 127.0.0.1 and localhost are fine in your example above. And it's odd that your error logs tells you that it can't find /var/run/mysql/mysql.sock since your config references /tmp/mysql.sock (have you attempted to install mysql over a default OS install?).

Verify that you can actually connect to mysql from a command line (everything I'm going to suggest is moot unless you have ssh)
Run your php code from your command line. You'll need php cli. Here's the install:

yum install php-cli (RedHat)

apt-get install php5-cli (Debian)

Let us know if either or both of these tests are successful.

score 0 · Answer 5 · answered Feb 22 '10 at 19:32

MySQL has opened a socket in /tmp/mysqld.sock, but PHP is looking for the socket /var/run/mysql/mysql.sock.

You either have to get PHP to look in /tmp/mysql.sock, or get MySQL to create the socket in /var/run/mysql/mysql/sock - as suggested by Kedare.

Changing PHP would be less disruptive. Your configuration file has two entries:

mysql.default_socket =

Change both of these (only one will be in effect, but I do not know which) to:

mysql.default_socket = /tmp/mysql.sock

MySQL is a bit strange: for MySQL clients, "localhost" means use a UNIX Domain socket to the database.

I have a feeling that fixing this part will only bring you back to your original problem:

Lost connection to MySQL server at 'reading initial communication packet', system error: 111

This sounds like incompatibility between client and server versions. Are you using 3.23 libraries to talk to a 5.1 server ?

Lost connection to MySQL

5 Answers5