Laravel 5.2 + MySQL 8.0 On RDS - Connection Errors Out With "SQLSTATE[HY000] [2002]"

Question

I have an application that runs on PHP 5.6 and Laravel 5.2. It's hosted in AWS on an AWS Linux 1 EC2 machine. This web server connects to a RDS MySQL instance. The RDS instance has been running MySQL 5.7 for several years.

MySQL 5.7 passed its end of life at the end of 2023 and Amazon is phasing out standard support for MySQL 5 in RDS. I updated my RDS instance to MYSQ 8.0 this week taking the precautions laid out in this guide.

Here are the things I've done:

-In my Laravel config/database.php file, I have the following:

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],

-In RDS, I've attached a parameter group with the following overwrites:

After saving the above database config values in the parameter group, I've made sure to allow my RDS MySQL instance to finish "modifying" and then restarted the instance via the AWS console.

-I've also made sure the MySQL user that's connecting from the Laravel app has the authentication plugin mysql_native_password (and not the new plugin caching_sha2_password).

-I've tested that I am able to connect to the RDS MySQL instance with the username / password I've provided to the Laravel app via MySQL Workbench

After the above steps, I am receiving the following error:

In addition to the above config (which aligns with all the MySQL 5 to 8 upgrade guides and all questions and answers I've seen around the internet), I've also tried:

-Setting all character_set_* values in my RDS parameter group to utf8mb4 (the default character set in MySQL 8) and collation_* values in the parameter group to utf8mb4_0900_ai_ci (the default collation in MySQL 8).

-Setting my Laravel database config to:

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_0900_ai_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],

to match the new MySQL 8 character set and collation defaults.

-Mixing and matching the above configs in every possible iteration

I haven't been able to get past the SQLSTATE[HY000] [2002] error I posted above.

Has anyone gone through the same MySQL 5 to 8 upgrade process with an older version of Laravel (preferably where the MySQL instance was hosted on RDS) and had to work through the same error?

You want to get the rest of the error message as there will be further details what caused the error. But basically, your code can't evem connect to the mysql instamce specified. Most of the time this error is caused by providing incorrect connection parameters. — Shadow
– Shadow, Commented Jan 31, 2024 at 3:44
Does the error appear almost instantaneously or does it take a few seconds? — Salman Arshad
– Salman Arshad, Commented Feb 2, 2024 at 8:09
Can you get in with this cimmandline: mysql -u {username} -p{password} -h {remote server ip} ? — Rick James
– Rick James, Commented Feb 4, 2024 at 19:10

OzzieFZI · Accepted Answer · 2024-02-06 23:04:57Z

-2

It may be a TLS issue, some details on how RDS configures TLS certificates for instances here. You may need to provide database ssl config to the app and add the RDS CA cert to the server. Instructions for downloading the ca cert can be found here.

Below is a sample config with the RDS CA cert located in database/certs/rds-combined-ca-bundle.pem;

    'mysql_ssl' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => false,
        'engine' => null,
        'sslmode' => 'verify_identity',
        'options' => [
            PDO::MYSQL_ATTR_SSL_CA => base_path('database/certs/rds-combined-ca-bundle.pem')
        ],
    ],

answered Feb 6, 2024 at 23:04

OzzieFZI

1127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lloyd Banks Over a year ago

There is already a RDS CA cert referenced in the Laravel config. It's the latest version from AWS. This same RDS cert works when the RDS instance is running MySQL 5

Vaibhav Nampalliwar · Accepted Answer · 2024-02-07 06:19:17Z

1)Verify that the MySQL server is configured to accept connections from the Laravel app IP address. This can be done by checking the bind-address setting in the MySQL server configuration file (example my.cnf) and ensuring that the server firewall allows incoming connections on port 3306.

2)Check the security groups associated with the RDS instance to ensure that they allow incoming connections from the Laravel app IP address on port 3306.

3)Verify that the MySQL user specified in the Laravel app configuration file has the correct authentication plugin (mysql_native_password).

4)Verify that the MySQL server is running and listening on the correct port (3306 by default). You can check this using the netstat command or by checking the MySQL server logs.

mysql configuration file

'mysql' => [ 'driver' => 'mysql', 'host' => env('DB_HOST', '127.0.0.1'), 'port' => env('DB_PORT', '3306'), 'database' => env('DB_DATABASE', 'forge'), #from RDS setup 'username' => env('DB_USERNAME', 'forge'), #from RDS setup 'password' => env('DB_PASSWORD', ''), #from RDS setup 'unix_socket' => env('DB_SOCKET', ''), 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'strict' => false, 'engine' => null, ],

6)Verify that you have set environment variables for your database connection details in your .env file, such as DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME, and DB_PASSWORD.

7)Check that the RDS endpoint is correct . as you have recently modified your RDS instance, the endpoint may have changed.

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Collectives™ on Stack Overflow

Laravel 5.2 + MySQL 8.0 On RDS - Connection Errors Out With "SQLSTATE[HY000] [2002]"

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related