36

I get this error when I try to connect to the mysql database using php mysqli class. Using following code:

$db = new MySQLi("localhost","kamil","*****");

password is * for security.

I have created user kamil with all privileges on external ip address and localhost. When I run: select user,host from mysql.user it properly displays those two users.

I did some research and used this benchmark: https://stackoverflow.com/a/2183134/1839439 to see what it connects to. As it turns out it is only able to connect to 127.0.0.1 and 127.0.0.1:3306 which is localhost, however when I supply localhost it throws out this error.

My question is why does it only allow me to connect to DB using localhost ip address and not the name or external ip. Do I need a different host if I want to be able to use mysql on website or if I can use 127.0.0.1?

hosts file

127.0.0.1       localhost
::1             localhost ip6-localhost ip6-loopback
fe00::0         ip6-localnet
ff00::0         ip6-mcastprefix
ff02::1         ip6-allnodes
ff02::2         ip6-allrouters

127.0.1.1       raspberrypi
Mysql user table results for this user: 
| kamil            | 109.255.177.28 |
| kamil            | localhost      |
Dharman
  • 30,962
  • 25
  • 85
  • 135
  • well localhost connects via a socket, and 127.0.0.1 connects via TCP, so it seems like some sort of bad configuration is present. – thatidiotguy Dec 07 '12 at 19:11
  • I would appreciate if someone could tell me what is this bad configuration. I can supply all information requested – Dharman Dec 07 '12 at 19:12
  • `socket MySQL` sounds suspect. Typically that would be more like `socket /tmp/mysql.sock` or `/var/run/mysql.sock` .Find out what the actual connected socket is, and correct it in my.cnf – Michael Berkowski Dec 07 '12 at 19:12
  • 1
    If you are on a Unix system `netstat -a | grep mysql` to find the name of the socket – Michael Berkowski Dec 07 '12 at 19:14

5 Answers5

84

When you use just "localhost" the MySQL client library tries to use a Unix domain socket for the connection instead of a TCP/IP connection. The error is telling you that the socket, called MySQL, cannot be used to make the connection, probably because it does not exist (error number 2).

From the MySQL Documentation:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option.

There are a few ways to solve this problem.

  1. You can just use TCP/IP instead of the Unix socket. You would do this by using 127.0.0.1 instead of localhost when you connect. The Unix socket might by faster and safer to use, though.
  2. You can change the socket in php.ini: open the MySQL configuration file my.cnf to find where MySQL creates the socket, and set PHP's mysqli.default_socket to that path. On my system it's /var/run/mysqld/mysqld.sock.

  3. Configure the socket directly in the PHP script when opening the connection. For example:

    $db = new MySQLi('localhost', 'kamil', '***', '', 0, 
                              '/var/run/mysqld/mysqld.sock')
Joni
  • 108,737
  • 14
  • 143
  • 193
5

If it's a PHP issue, you could simply alter the configuration file php.ini wherever it's located and update the settings for PORT/SOCKET-PATH etc to make it connect to the server.

In my case, I opened the file php.ini and did

mysql.default_socket = /var/run/mysqld/mysqld.sock
mysqli.default_socket = /var/run/mysqld/mysqld.sock

And it worked straight away. I have to admit, I took hint from the accepted answer by @Joni

Vijay Kumar Kanta
  • 1,111
  • 1
  • 15
  • 25
2

If 'localhost' doesn't work but 127.0.0.1 does. Make sure your local hosts file points to the correct location. (/etc/hosts for linux/mac, C:\Windows\System32\drivers\etc\hosts for windows).

Also, make sure your user is allowed to connect to whatever database you're trying to select.

Ethan
  • 87
  • 1
  • 7
castis
  • 8,154
  • 4
  • 41
  • 63
1

My question is why does it only allow me to connect to DB using localhost ip address and not the name or external ip.

As per docs for mysqli_connect():

Parameters: hostname: Can be either a host name or an IP address. The local host is assumed when passing the null value or the string "localhost" to this parameter. When possible, pipes will be used instead of the TCP/IP protocol. The TCP/IP protocol is used if a host name and port number are provided together e.g. localhost:3308.

So basically if you specify a localhost or null value, it enforces named pipes/socket connections (see: mysqli.default_socket), otherwise (e.g. when you specify a port or non-localhost host) it's going to use the TCP/IP protocol (e.g. localhost:3308).

kenorb
  • 155,785
  • 88
  • 678
  • 743
0

Please check the following file

%SystemRoot%\system32\drivers\etc\host

The line which bind the host name with ip is probably missing a line which bind them togather

127.0.0.1  localhost

If the given line is missing. Add the line in the file

Could you also check your MySQL database's user table and tell us the host column value for the user which you are using. You should have user privilege for both the host "127.0.0.1" and "localhost" and use % as it is a wild char for generic host name.

Badal Singh
  • 918
  • 1
  • 6
  • 13