Asked  7 Months ago    Answers:  5   Viewed   92 times

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","*****");
if (mysqli_connect_errno())
{
    echo "An error occured. Please try again later.";
    exit();
}

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?

EDIT:
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      |

 Answers

81

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')
    
Tuesday, June 1, 2021
 
Anand
answered 7 Months ago
95

ok, facepalm moment... after this hitting me for ages I finally found why mysql_error() was reporting the socket error. in my code there was a mysql_select_db() that I didnt notice and it was that, thats reporting the socket error as there is no open connection for it to use.

actual error the mysql_connect is reporting is (2003) Can't connect to MySQL server on 'mysql.hostname.tld'. Im still leaning towards some sort of DNS lookup/network traffic issue, Im currently using the IP address to connect to see if that errors.

But as far as this question is concerned, the answer is "Im a numpty".

Saturday, May 29, 2021
 
Markol
answered 7 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 7 Months ago
24

I don't think it will work this way. When you close the statement (e.g. $menu_stmt->close();) you also deallocate the statement handle. So the second time through the loop you don't have the prepared statements available to work with anymore.

Try closing the statements after the loop has finished executing.

Friday, August 6, 2021
 
NIKHIL
answered 4 Months ago
37

Here is what fixed for me with Mariadb

First find out if your mysql config file ,most likely located at /etc/mysql/my.cnf or /etc/my.cnf has the correct mysql.sock entry or not

You can find out where mysql.sock file is located by running find / -type s , if the entry is not correct in your mysql config, file fix it , also make sure mysql.pid has the correct path as well.

Now try to start mysql server , if it starts fine and you can see all DB's and tables within , you are good and dont need to follow rest of the post .

If mysql fails to start after fixing mysql.sock path , read on

Add the following to your my.cnf file ,

innodb_force_recovery = 1 inside the mysqld block , and restart mysql.

If it fails to start , increment the number by one each time , but keep in mind , after 3 , their might be some data loss (not happened to me , but i have only read about anything above 3 being a salvaging measure , than a recovery)

Dont panic if some of the tables you previously had , stop showing that they dont exist in the engine . Ones mysql starts successfully (if you dont have a backup yet , better make one now , just in case things go south from here on) , remove innodb_force_recovery = 1 from my.cnf file and restart mysql again , all your tables should be available again.

Posts that helped me

  • https://stackoverflow.com/a/20617180/3117013
  • https://stackoverflow.com/a/11990813/3117013
Sunday, November 7, 2021
 
Yuriy Faktorovich
answered 4 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share