XF 2.3 Unable to Connect to Managed MYSQL Digital Ocean via SSL

[twaxter]

Hello, I've been trying to connect to my MySQL managed database over SSL that is un in digital ocean. This code is run in a droplet.

I am able to connect to mySQL using mysql client directly and inside of php using msqli ie. like this:

mysql -u XXXXX -h mysql-db.XXXXXXX.db.ondigitalocean.com -P 25060 -D defaultdb --ssl-ca=ca-certificate.crt

I get this in my root url:

An exception occurred: [XF\Db\Exception] Cannot connect to MySQL using SSL in src/XF/Db/Mysqli/Adapter.php on line 172

1. XF\Db\Mysqli\Adapter->makeConnection() in src/XF/Db/Mysqli/Adapter.php at line 31
2. XF\Db\Mysqli\Adapter->getConnection() in src/XF/Db/AbstractAdapter.php at line 62
3. XF\Db\AbstractAdapter->connect() in src/XF/Db/Mysqli/Adapter.php at line 116
4. XF\Db\Mysqli\Adapter->escapeString() in src/XF/Db/AbstractAdapter.php at line 518
5. XF\Db\AbstractAdapter->quote() in src/XF/Db/AbstractAdapter.php at line 496
6. XF\Db\AbstractAdapter->quote() in src/XF/DataRegistry.php at line 172
7. XF\DataRegistry->readFromDb() in src/XF/DataRegistry.php at line 102
8. XF\DataRegistry->get() in src/XF/DataRegistry.php at line 271
9. XF\DataRegistry->offsetGet() in src/XF/App.php at line 2178
10. XF\App->XF\{closure}() in src/XF/Container.php at line 33
11. XF\Container->offsetGet() in src/XF/App.php at line 2799
12. XF\App->setupAddOnComposerAutoload() in src/XF/App.php at line 2276
13. XF\App->setup() in src/XF/Pub/App.php at line 116
14. XF\Pub\App->setup() in src/XF.php at line 779
15. XF::setupApp() in src/XF.php at line 802
16. XF::runApp() in index.php at line 23

In my installer, I get:
Cannot connect to MySQL using SSL

Here is my config.php, this is a managed mySQL database with digital ocean

<?php

$config['db']['host'] = 'mysql-db-XXXXX-db.ondigitalocean.com';
$config['db']['port'] = 25060;
$config['db']['username'] = 'XXXX';
$config['db']['password'] = 'XXXXX';
$config['db']['dbname'] = 'defaultdb';
$config['db']['socket'] = null;

$config['db']['ssl']['enabled'] = true;
$config['db']['ssl']['ca'] = 'ca-certificate.crt';

$config['fullUnicode'] = true;
$config['searchInnoDb'] = true;

 

[ForumDevs]

Cannot connect to MySQL using SSL

If you are sure that CA certificate is the correct for the database instance you should verify if SSL is enabled in MySQL:

mysql -u root -p
SHOW VARIABLES LIKE '%ssl%';

 

[twaxter]

Thanks for replying.

Yes I downloaded it directly from digital ocean, and I was able to use it to connect to the database via the mysql client directly.

Upon running that query I see:

SHOW VARIABLES LIKE '%ssl%';

| have_openssl | YES |
| have_ssl | YES |

I also see, in my connection settings for the managed database on digital ocean.
sslmode = REQUIRED

 

[twaxter]

Okay I believe you are right.
When I connect to the database, and I don't enable SSL or pass the CA (I comment out the lines in the config.php), I am able to connect!

 

[ForumDevs]

Thanks for replying.

Yes I downloaded it directly from digital ocean, and I was able to use it to connect to the database via the mysql client directly.

Upon running that query I see:

SHOW VARIABLES LIKE '%ssl%';


I also see, in my connection settings for the managed database on digital ocean.
sslmode = REQUIRED

Humm, the "Cannot connect to MySQL using SSL" error typically indicates an issue with the client configuration. Can you try to set the full path of the CA certificate? It should look like this:

mysql -u username -p -h your-db-host --ssl-ca=/path/to/ca-certificate.crt --ssl-mode=REQUIRED

 

[twaxter]

mysql -u username -p -h your-db-host --ssl-ca=/path/to/ca-certificate.crt --ssl-mode=VERIFY_CA

This does work. I set --ssl-mode=VERIFY_CA since I got this warning:

WARNING: no verification of server certificate will be done. Use --ssl-mode=VERIFY_CA or VERIFY_IDENTITY.

But it worked even without any SSL options.. confused. I did add this droplet to the trusted servers on the managed database, maybe that is why it's able to go through?