2

I'm totally stuck having spent a couple of days trying to figure this out...

I have set up an Apache web server (version 2.4.34) on my Mac OS running Mojave (10.14.3) and have installed the current version of MySQL Community Server (8.0.15). Both running perfectly as I can determine (from command line). I have PHP 7.1.23 installed. When I create a .php file with the following code and put it in my Sites directory and then in Safari go to http://localhost/~dave/hello.php I get this error:

"Warning: mysqli::__construct(): (HY000/2002): No such file or directory in /Users/dave/Sites/hello.php on line 7 Connection failed: No such file or directory".

Code in hello.php:

<?php
$servername = "localhost";
$username = "dave";
$password = "*****";

//Create connection
$conn = new mysqli($servername, $username, $password);

//Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

I have tried everything I can think of, including those suggestions in the other two similar posts (this one: My database user exists, but I still get an (HY000/2002): No such file or directory and this one: PHP Warning: mysqli::__construct(): (HY000/2002): No such file or directory (Debian GNU/Linux 9)), and I cannot connect to MySQl with php. In particular, I checked with the php info page and the socket for mysqli is given as var/mysql/mysql.sock. In the relevant table in mysql the value for socket is given as /tmp/mysql.sock. I tried changing the php.ini.default file by adding each of these sockets (in place of the empty value) and neither worked (still generates the same error). I thought that the 2002 MySQL socket error might be the problem, so I made a symlink by doing this:

sudo mkdir /var/mysql
sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

but all that did was generate a new error:

Warning: mysqli::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] in /Users/dave/Sites/hello.php on line 7

Warning: mysqli::__construct(): (HY000/2054): The server requested authentication method unknown to the client in /Users/dave/Sites/hello.php on line 7 Connection failed: The server requested authentication method unknown to the client

So I removed the symlink...and returned to the original error.

I also tried replacing "localhost" with the IP address 127.0.0.1 as well as "localhost:3306" as other question threads have suggested. Nothing worked here either.

Please help, I am new to this kind of use of the computer/web design and I'm out of obvious (googleable/understandable) options.

DJC
  • 29
  • 1
  • 1
  • 4
  • 1
    You haven't described the steps you've taken during installation. It's one thing to say "I've installed Apache and Mysql".. but there are various ways of installing and configuring those, so you need to describe your precise steps so we can see where you've gone wrong. Also why are you installing them? Why not just use MAMP? Also did you search StackOverflow / Google for that error? E.g. https://stackoverflow.com/questions/13769504/mysqlimysqli-hy000-2002-cant-connect-to-local-mysql-server-through-sock. You mention "two other similar posts" but you don't link to them. It would help if you did. – geoidesic Mar 05 '19 at 21:46
  • 1
    How have you verified that MySQL is actually running and accepting connections? Does it accept socket connections? Do you run stuff inside a docker container or everything on the same host? – ArSeN Mar 05 '19 at 22:17
  • @geoidesic: I will try to answer acurately. Apache: Actually, I think Apache and php came already installed when I upgraded to Mojave. Then, I set up Apache according to https://discussions.apple.com/docs/DOC-13841 and https://www.dyclassroom.com/howto-mac/how-to-install-apache-mysql-php-on-macos-mojave-10-14. MySQL. I downloaded the .dmg zipped folder and installed it following the MySQL user manual instructions. I checked the server was running in Mac preferences, then could use mysql from the command line, following along with the tutorial no problem. I could also LOAD DATA LOCAL INFILE OK. – DJC Mar 05 '19 at 22:30
  • @ArSeN. I don't understand your questions sorry, total novice here. – DJC Mar 05 '19 at 22:31
  • If your goal is to learn server admin, then you're on the right track. If your goal is to learn coding, then you'll have a much easier time with MAMP: https://www.mamp.info/en/ – geoidesic Mar 05 '19 at 22:32
  • 1
    Do a `phpinfo()` in your php file and show the contents of the MySQL and MySQLi blocks. – geoidesic Mar 05 '19 at 22:32
  • The sockets listed in MySQL and MySQLi, at least on my system, are the same. I don't think it's right for them to be different. A symlink probably isn't going to sort that out. Also generally speaking you need to install `php-mysql` in order to get php talking to mysql, so I don't think you've been following the best tutorials. – geoidesic Mar 05 '19 at 22:39
  • @geoidesic. There is only a MySQLi block that I can see, and in that the socket is given as /var/mysql/mysql.sock for both local and master. When I show variable in mysql (in the terminal) It says the socket value is /tmp/mysql.sock. How do I install php-mysql? – DJC Mar 05 '19 at 22:53
  • Some days ago I had a similar problem (on Debian, with Apache and [MariaDB](https://mariadb.org/about/)). I've tested a software, which, surprisingly, completely removed the directory containing the MariaDB log files (`var/log/mysql`). But this directory also contained a file named `mariadb-bin.index`. I recreated the directory and I gave it corresponding rights. Though, no matter what I've tried further, the .index file could not be (properly) created anymore. I had to reinstall the db server... So, check for your `var/log/mysql` directory. – PajuranCodes Mar 06 '19 at 00:58
  • Also, note that the default installed apache and php on MacOS are (somehow) tricky (as I recall from about six months ago). I, too, really recommend MAMP for developing on Mac - even if, on any other operating system, I always prefer to manually install and configure the web and db server myself. – PajuranCodes Mar 06 '19 at 01:13

2 Answers2

13

Use 127.0.0.1 instead of localhost.

Hamish
  • 2,763
  • 1
  • 17
  • 21
devbikash07
  • 161
  • 2
  • 4
  • Works for me on devilbox, where 'localhost' doesn't. – frumbert Aug 29 '20 at 13:33
  • Same here, had been poking around for a while after checking with php -m that mysqli was installed. Changed my hostname localhost to 127.0.0.1 and it worked. – loonighan Jan 28 '21 at 10:01
  • It also works for me by changing the host name "localhost" to "127.0.0.1". Would you explain more why using 127.0.0.1 works instead of localhost? – Aryo Jul 24 '21 at 14:47
  • found the answer of my own questions here: https://stackoverflow.com/questions/31413728/connect-mysql-through-localhost-not-working-but-127-0-0-1-is-working . It seems that in my case the PHP mysqli.default_socket directive points to wrong mysql.sock socket file location of MySQL server. Either change the mysqli.default_socket directive in php.ini file or change the MySQL socket configuration in my.cnf file, or, make a symbolic link to the correct MySQL socket file as specified in php.ini. – Aryo Jul 24 '21 at 15:01
0

I got some help from some local tech guys in my area and it turns out that fixing the socket error was the first step, like I tried to do above. I eventually did it this way, using the solution by noun:

Warning: mysql_connect(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in

The second error message was then generated (same one as in my question, here again):

mysqli::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] in /Users/dave/Sites/hello.php on line 7

Warning: mysqli::__construct(): (HY000/2054): The server requested authentication method unknown to the client in /Users/dave/Sites/hello.php on line 7

This was fixed by the SQL-based solution (June 9 2018 answer) to this question:

php mysqli_connect: authentication method unknown to the client [caching_sha2_password]

This SQL statement was simply run in MySQL from the terminal. After restarting the Apache server I reloaded the .php file in Safari and this resulted in a successful mysqli connection to MySQL with PHP. I also tried a PDO connection and this also worked.

DJC
  • 29
  • 1
  • 1
  • 4