CODEWITHSUNDEEP
mysqllaravelsockets
Carol.Kar
Carol.Kar

Reputation: 5355

Laravel SQLSTATE[HY000] [2002] Connection refused with multiple connections

I am using Laravel 8.33.1 and mysql Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)) on my Ubuntu 20.04.1 LTS vps.

As during development I also need to access data from my production db. Within my code I do this the following way:

             $con = "mysql_prod";
            // $con = "mysql";

            // DB::connection($con)->enableQueryLog();
            $data = DB::connection($con)->table('test')->select('*')
                ->where('test.id', 'LIKE', '%' . $id. '%')
                ->get();

Therefore my prod .env file looks like the following:

DB_CONNECTION=mysql
DB_HOST_PROD=127.0.0.1
DB_PORT_PROD=3306
DB_DATABASE_PROD=test_db
DB_USERNAME_PROD=root
DB_PASSWORD_PROD=
DB_SOCKET=/var/run/mysqld/mysqld.sock

DB_CONNECTION_PROD=mysql_prod
DB_HOST_PROD=127.0.0.1
DB_PORT_PROD=3306
DB_DATABASE_PROD=test_db
DB_USERNAME_PROD=root
DB_PASSWORD_PROD=
DB_SOCKET_PROD=/var/run/mysqld/mysqld.sock

However, I get the following error in my laravel application:

Illuminate\Database\QueryException Object
(
    [sql:protected] => select * from `products` where `products`.`owners` LIKE ?
    [bindings:protected] => Array
        (
            [0] => %sha:asdfsdafwaeadfasdfsadr%
        )

    [message:protected] => SQLSTATE[HY000] [2002] Connection refused (SQL: select * from `products` where `products`.`owners` LIKE %sha:asdfsdafwaeadfasdfsadr%)
    [string:Exception:private] => 
    [code:protected] => 2002
    [file:protected] => /var/www/test_project/vendor/laravel/framework/src/Illuminate/Database/Connection.php
    [line:protected] => 678
    [trace:Exception:private] => Array

My /var/log/mysql looks like the following:

2021-05-03T12:01:53.909594Z 1 [Note] [MY-012356] [InnoDB] Validated 93/93  tablespaces
2021-05-03T12:01:54.067261Z 1 [Note] [MY-010006] [Server] Using data dictionary with version '80023'.
2021-05-03T12:01:54.131322Z 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. bind-address: '127.0.0.1' port: 33060'
2021-05-03T12:01:54.131553Z 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. socket: '/var/run/mysqld/mysqlx.sock''
2021-05-03T12:01:54.131709Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-05-03T12:01:54.306534Z 0 [Note] [MY-010902] [Server] Thread priority attribute setting in Resource Group SQL shall be ignored due to unsupported platform or insufficient privilege.
2021-05-03T12:01:54.323773Z 0 [Note] [MY-010855] [Server] Recovering after a crash using binlog
2021-05-03T12:01:55.191842Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2021-05-03T12:01:55.330794Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-05-03T12:01:55.455623Z 0 [Note] [MY-012487] [InnoDB] DDL log recovery : begin
2021-05-03T12:01:55.456032Z 0 [Note] [MY-012488] [InnoDB] DDL log recovery : end
2021-05-03T12:01:55.479173Z 0 [Note] [MY-011946] [InnoDB] Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-05-03T12:01:55.492071Z 0 [Note] [MY-011946] [InnoDB] Buffer pool(s) load completed at 210503 14:01:55
2021-05-03T12:01:55.679587Z 0 [Note] [MY-010913] [Server] You have not provided a mandatory server-id. Servers in a replication topology must have unique server-ids. Please refer to the proper server start-up parameters documentation.
2021-05-03T12:01:56.555335Z 0 [Note] [MY-010182] [Server] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2021-05-03T12:01:56.555636Z 0 [Note] [MY-010304] [Server] Skipping generation of SSL certificates as certificate files are present in data directory.
2021-05-03T12:01:56.562402Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-05-03T12:01:56.562710Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-05-03T12:01:56.562938Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory.
2021-05-03T12:01:56.563129Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
2021-05-03T12:01:56.569220Z 0 [Note] [MY-010252] [Server] Server hostname (bind-address): '*'; port: 3306
2021-05-03T12:01:56.575025Z 0 [Note] [MY-010253] [Server] IPv6 is available.
2021-05-03T12:01:56.575384Z 0 [Note] [MY-010264] [Server]   - '::' resolves to '::';
2021-05-03T12:01:56.575669Z 0 [Note] [MY-010251] [Server] Server socket created on IP: '::'.
2021-05-03T12:01:56.715459Z 0 [Note] [MY-011025] [Repl] Failed to start slave threads for channel ''.
2021-05-03T12:01:56.738313Z 0 [Note] [MY-011240] [Server] Plugin mysqlx reported: 'Using SSL configuration from MySQL Server'
2021-05-03T12:01:56.738855Z 0 [Note] [MY-011243] [Server] Plugin mysqlx reported: 'Using OpenSSL for TLS connections'
2021-05-03T12:01:56.737081Z 5 [Note] [MY-010051] [Server] Event Scheduler: scheduler thread started with id 5
2021-05-03T12:01:56.739219Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-0ubuntu0.20.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).

Any suggestions what I am doing wrong?

Why can`t I use two socket connections to my mysql database?

I appreciate your replies!

Upvotes: 0

Views: 804

Answers (2)

Adam Hopkinson
Adam Hopkinson

Reputation: 28795

You can't have duplicate keys in your .env file. In your example above, all your keys have the _PROD suffix, not just the prod ones.

For each key, phpDotEnv will take the value of the last one in the file.

Upvotes: 2

Gaurav Gusain
Gaurav Gusain

Reputation: 86

Can you check your database connection in config/database.php file.

There you might be seeing something like 'default' => env('DB_CONNECTION', 'mysql'), change this to

'dbConnection1' => env('DB_CONNECTION', 'mysql'),
'dbConnection2' => env('DB_CONNECTION_PROD', 'mysql_prod')

Then use these two connection in your Eloquent code like:

$con = 'dbConnection2'   // Anyone you want
$data = DB::connection($con)->table('test')->select('*')
                ->where('test.id', 'LIKE', '%' . $id. '%')
                ->get()

Note: Don't forget to run php artisan config:cache after making the changes.

Upvotes: 0

Related Questions