Laravel + MySQL + SSL-Mode - SQLSTATE[HY000] [3159]

Question

On Linode I've setup a MySQL Database Cluster and an Ubuntu server with Apache and PHP 8.1.

When I SSH onto the Ubuntu server I'm able to connect to the cluster:

mysql --host=lin-xxx-mysql-primary-private.servers.linodedb.net --user=xxx --password --ssl-mode=required

However, when I run php artisan migrate I get the following error:

   Illuminate\Database\QueryException 

  SQLSTATE[HY000] [3159] Connections using insecure transport are prohibited while --require_secure_transport=ON. (SQL: select * from information_schema.tables where table_schema = xxxrch  and table_name = migrations and table_type = 'BASE TABLE')

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

      +33 vendor frames 
  34  artisan:37
      Illuminate\Foundation\Console\Kernel::handle()

I have not setup any certificates/pem files which other answers reference, but it still works from the mysqlclient.

What would I add to my .env to config\database.php to get this working?

Answer 1

Here's how I addressed this:

1. Download the cert from the Linode dashboard.
2. Place it in resources\certificates

In config/database.php I added:

'sslmode' => env('DB_SSLMODE', 'prefer'),
'options' => extension_loaded('pdo_mysql') && env('APP_ENV') !== 'testing' && env('APP_ENV') !== 'local' ? array_filter([
    PDO::MYSQL_ATTR_SSL_CA => resource_path('certificates/certificate.crt'),
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
]) : [],

One thing that's important to note is that this will not work for phpunit tests.

This answer was helpful: https://stackoverflow.com/a/70468831/1343140