PHP + MYSQL + Laravel - "SQLSTATE[HY000] [2002] Connection refused "

Question

Getting below db connection issue from PHP laravel project dashboard which is backend,

"SQLSTATE[HY000] [2002] Connection refused (SQL: select * from `sample` where `column_name` = 92ac89e3-91d1-4aee-a046-8b4e6c79e99a limit 1)"

from file:

/var/www/laravel-boilerplate/vendor/laravel/framework/src/Illuminate/Database/Connection.php

my docker-compose.yml config have:

services:
  mysql:
    image: mysql:latest
    volumes:
      - "./data/db:/var/lib/mysql"
    ports:
      - "8880:8880"
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=password
      - MYSQL_DATABASE=test
      - MYSQL_USER=root
      - MYSQL_PASSWORD=password

.env file have below configurations: DATABSE

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=8880
DB_DATABASE=test
DB_USERNAME=root
DB_PASSWORD=password

Any Idea on what is missing.

Tries(EDIT): after setting DB_HOST=mysql the error changed as below,

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (2054)
PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] (0)

showing users,

mysql> select user,plugin,host from mysql.user;
+------------------+-----------------------+-----------+
| user             | plugin                | host      |
+------------------+-----------------------+-----------+
| root             | caching_sha2_password | %         |
| root             | mysql_native_password | 127.0.0.1 |
| mysql.infoschema | caching_sha2_password | localhost |
| mysql.session    | caching_sha2_password | localhost |
| mysql.sys        | caching_sha2_password | localhost |
| root             | mysql_native_password | localhost |
| root             | mysql_native_password | mysql     |
+------------------+-----------------------+-----------+

have ran below suggested step too, but no luck,

mysql> alter USER 'root'@'mysql' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> alter USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.01 sec)

Any Idea?

What PHP versions are you using? The following error seems relevant: https://stackoverflow.com/questions/50026939/php-mysqli-connect-authentication-method-unknown-to-the-client-caching-sha2-pa — MaartenDev, Feb 08 '21 at 19:13

score 1 · Accepted Answer · answered Feb 07 '21 at 11:49

1

thats mean your data in env not correct , try to change host from 127.0.0.1 to be mysql. its works with me like this :-

DB_CONNECTION=mysql
DB_HOST=mysql
DB_PORT=8880
DB_DATABASE=test
DB_USERNAME=root
DB_PASSWORD=password

answered Feb 07 '21 at 11:49

Mohamed Ahmed

760
1
4
11

Thanks @Mohamed Now it shows below error, "SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: select * from `tablename` where `uuid` = *** limit 1) ◀" – Satscreate Feb 08 '21 at 18:06
1

By default and for some reason, mysql 8 default plugin is auth_socket. Applications will most times expect to log in to your database using a password so you should run this command :- ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your password'; – Mohamed Ahmed Feb 08 '21 at 22:22
Does the trick after cleaning up the users and alter with same commands. – Satscreate Feb 09 '21 at 07:16

PHP + MYSQL + Laravel - "SQLSTATE[HY000] [2002] Connection refused "

1 Answers1