PHP Versions Question posted in
You can find a list of all versions here.

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

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:

enter image description here

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:

enter image description here

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?

Tags:

2

Answers


  1. 0

    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')
        ],
    ],
    Login or Signup to reply.
  2. 0

    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.

    1. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search