Implementing MySQLi SSL

MattW

MattW

Well-known member
I'm trying to get this working with the new Linode Beta MySQL managed databases.

I can connect via command line using the provided crt file:

mysql -h x.x.x.x -u xxxxxx -p --ssl-ca=mwsdb-ca-certificate.crt

When I try setting XF to use these, I can a DB error, but nothing logged. Would debug show any additional information?

PHP: 
$config['db']['ssl']['enabled'] = true;
$config['db']['ssl']['cert'] = '/full/path/to/mwsdb-ca-certificate.crt';

Tried it with:
PHP: 
$config['db']['ssl']['enabled'] = true;
$config['db']['ssl']['ca'] = 'mwsdb-ca-certificate.crt';
$config['db']['ssl']['capth'] = '/full/path/to';
 
Solution
VersoBit
We just configured Linode's DBaaS tonight and heres what our config looks like:
PHP: 
$config['db']['host'] = '<Linode Private DB URL>';
$config['db']['port'] = '3306';
$config['db']['username'] = '<db_user>';
$config['db']['password'] = '<db_password>';
$config['db']['dbname'] = '<dbname>';
$config['db']['ssl']['enabled'] = true;
$config['db']['ssl']['ca'] = 'src/db_ca.pem';

We are running php-fpm8.1; We placed the downloadable certificate in the /src folder named db_ca.pem.
In our case it is a symlink to our certificates folder.
Sort by date Sort by votes
Thanks Chris,

This is the error that's coming back
Code: 
An exception occurred: [XF\Db\Exception] in src/XF/Db/Mysqli/Adapter.php on line 165

XF\Db\Mysqli\Adapter->makeConnection() in src/XF/Db/Mysqli/Adapter.php at line 28
XF\Db\Mysqli\Adapter->getConnection() in src/XF/Db/AbstractAdapter.php at line 62
XF\Db\AbstractAdapter->connect() in src/XF/Db/AbstractAdapter.php at line 85
XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1354
XF\Mvc\Entity\Finder->fetchOne() in src/XF/Mvc/Entity/Manager.php at line 151
XF\Mvc\Entity\Manager->find() in src/XF/Repository/User.php at line 25
XF\Repository\User->getVisitor() in src/XF/App.php at line 1919
XF\App->getVisitorFromSession() in src/XF/Pub/App.php at line 158
XF\Pub\App->start() in src/XF/App.php at line 2347
XF\App->run() in src/XF.php at line 517
XF::runApp() in index.php at line 20
 
Upvote 0 Downvote
Okay, so one way we might be able to get more info out (and I think the end result of this should be us making changes to try to display more info in the event of an error) should be a quick file change.

Edit src/XF/Db/Mysqli/Adapter.php. Find:

PHP: 
$isConnected = @$connection->real_connect(
   $config['host'], $config['username'], $config['password'],
   $config['dbname'], $config['port'] ?: 3306, $config['socket']
);

And replace with:

PHP: 
$isConnected = $connection->real_connect(
   $config['host'], $config['username'], $config['password'],
   $config['dbname'], $config['port'] ?: 3306, $config['socket']
);

(Essentially just remove the @)

This will hopefully completely blow up but with a more sensible error.

I just fudged it on my local install (not set up for SSL) and managed to get:

Fatal error: Uncaught ErrorException: [E_WARNING] mysqli::real_connect(): Unable to set local cert chain file `/path/to/ssl'; Check that your cafile/capath settings include details of your certificate and its issuer
Click to expand...
 
Upvote 0 Downvote
Code: 
An exception occurred: [ErrorException] [E_WARNING] mysqli::real_connect(): Unable to set private key file `/PATH/I/SET/mwsdb-ca-certificate.crt' in src/XF/Db/Mysqli/Adapter.php on line 161

XF::handlePhpError()
mysqli->real_connect() in src/XF/Db/Mysqli/Adapter.php at line 161
XF\Db\Mysqli\Adapter->makeConnection() in src/XF/Db/Mysqli/Adapter.php at line 28
XF\Db\Mysqli\Adapter->getConnection() in src/XF/Db/AbstractAdapter.php at line 62
XF\Db\AbstractAdapter->connect() in src/XF/Db/AbstractAdapter.php at line 85
XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1354
XF\Mvc\Entity\Finder->fetchOne() in src/XF/Mvc/Entity/Manager.php at line 151
XF\Mvc\Entity\Manager->find() in src/XF/Repository/User.php at line 25
XF\Repository\User->getVisitor() in src/XF/App.php at line 1919
XF\App->getVisitorFromSession() in src/XF/Pub/App.php at line 158
XF\Pub\App->start() in src/XF/App.php at line 2347
XF\App->run() in src/XF.php at line 517
XF::runApp() in index.php at line 20

Linode didn't provide a private key, they only supplied the CA file I've grabbed.
 
Upvote 0 Downvote
Just want to rule something out, if you make a change to the same file and change:

PHP: 
$connection->ssl_set($config['ssl']['key'], $config['ssl']['cert'], $config['ssl']['ca'], $config['ssl']['capath'], $config['ssl']['cipher']);

To:

PHP: 
$connection->ssl_set($config['ssl']['key'], $config['ssl']['cert']);

Does this change anything? I'm just wondering if we're passing in a default value to one of the non-set ssl options and it's confusing mysqli.
 
Upvote 0 Downvote
We just configured Linode's DBaaS tonight and heres what our config looks like:
PHP: 
$config['db']['host'] = '<Linode Private DB URL>';
$config['db']['port'] = '3306';
$config['db']['username'] = '<db_user>';
$config['db']['password'] = '<db_password>';
$config['db']['dbname'] = '<dbname>';
$config['db']['ssl']['enabled'] = true;
$config['db']['ssl']['ca'] = 'src/db_ca.pem';

We are running php-fpm8.1; We placed the downloadable certificate in the /src folder named db_ca.pem.
In our case it is a symlink to our certificates folder.
 
Upvote 2 Downvote
Solution
Code: 
Fatal error: Uncaught ArgumentCountError: mysqli::ssl_set() expects exactly 5 arguments, 2 given in /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php:157 Stack trace: #0 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php(157): mysqli->ssl_set(NULL, '...') #1 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php(28): XF\Db\Mysqli\Adapter->makeConnection(Array) #2 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Error.php(87): XF\Db\Mysqli\Adapter->getConnection() #3 /home/nginx/domains/mattwservices.co.uk/public/src/XF/App.php(2362): XF\Error->logException(Object(ArgumentCountError), true, '') #4 /home/nginx/domains/mattwservices.co.uk/public/src/XF.php(201): XF\App->logException(Object(ArgumentCountError), true) #5 [internal function]: XF::handleException(Object(ArgumentCountError)) #6 {main} thrown in /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php on line 157

Fatal error: Uncaught ArgumentCountError: mysqli::ssl_set() expects exactly 5 arguments, 2 given in /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php:157 Stack trace: #0 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php(157): mysqli->ssl_set(NULL, '...') #1 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php(28): XF\Db\Mysqli\Adapter->makeConnection(Array) #2 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Error.php(87): XF\Db\Mysqli\Adapter->getConnection() #3 /home/nginx/domains/mattwservices.co.uk/public/src/XF/App.php(2362): XF\Error->logException(Object(ErrorException), true, '') #4 /home/nginx/domains/mattwservices.co.uk/public/src/XF.php(241): XF\App->logException(Object(ErrorException), true) #5 [internal function]: XF::handleFatalError() #6 {main} thrown in /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php on line 157
 
Upvote 0 Downvote
MattW said:
Code: 
Fatal error: Uncaught ArgumentCountError: mysqli::ssl_set() expects exactly 5 arguments, 2 given in /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php:157 Stack trace: #0 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php(157): mysqli->ssl_set(NULL, '...') #1 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php(28): XF\Db\Mysqli\Adapter->makeConnection(Array) #2 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Error.php(87): XF\Db\Mysqli\Adapter->getConnection() #3 /home/nginx/domains/mattwservices.co.uk/public/src/XF/App.php(2362): XF\Error->logException(Object(ArgumentCountError), true, '') #4 /home/nginx/domains/mattwservices.co.uk/public/src/XF.php(201): XF\App->logException(Object(ArgumentCountError), true) #5 [internal function]: XF::handleException(Object(ArgumentCountError)) #6 {main} thrown in /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php on line 157

Fatal error: Uncaught ArgumentCountError: mysqli::ssl_set() expects exactly 5 arguments, 2 given in /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php:157 Stack trace: #0 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php(157): mysqli->ssl_set(NULL, '...') #1 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php(28): XF\Db\Mysqli\Adapter->makeConnection(Array) #2 /home/nginx/domains/mattwservices.co.uk/public/src/XF/Error.php(87): XF\Db\Mysqli\Adapter->getConnection() #3 /home/nginx/domains/mattwservices.co.uk/public/src/XF/App.php(2362): XF\Error->logException(Object(ErrorException), true, '') #4 /home/nginx/domains/mattwservices.co.uk/public/src/XF.php(241): XF\App->logException(Object(ErrorException), true) #5 [internal function]: XF::handleFatalError() #6 {main} thrown in /home/nginx/domains/mattwservices.co.uk/public/src/XF/Db/Mysqli/Adapter.php on line 157
Click to expand...
Ah ok. That’s a good sign from our point of view. I think the values should be null (default) or overridden with an actual value as needed So you can definitely undo that one.
 
Upvote 0 Downvote
VersoBit said:
We just configured Linode's DBaaS tonight and heres what our config looks like:
PHP: 
$config['db']['host'] = '<Linode Private DB URL>';
$config['db']['port'] = '3306';
$config['db']['username'] = '<db_user>';
$config['db']['password'] = '<db_password>';
$config['db']['dbname'] = '<dbname>';
$config['db']['ssl']['enabled'] = true;
$config['db']['ssl']['ca'] = 'src/db_ca.pem';

We are running php-fpm8.1; We placed the downloadable certificate in the /src folder named db_ca.pem.
In our case it is a symlink to our certificates folder.
Click to expand...
This got it, it needed to be the 'ca' value not the 'cert' (and the 'capth' wasn't needed to be set).
 
Upvote 0 Downvote
VersoBit said:
We just configured Linode's DBaaS tonight and heres what our config looks like:
PHP: 
$config['db']['host'] = '<Linode Private DB URL>';
$config['db']['port'] = '3306';
$config['db']['username'] = '<db_user>';
$config['db']['password'] = '<db_password>';
$config['db']['dbname'] = '<dbname>';
$config['db']['ssl']['enabled'] = true;
$config['db']['ssl']['ca'] = 'src/db_ca.pem';

We are running php-fpm8.1; We placed the downloadable certificate in the /src folder named db_ca.pem.
In our case it is a symlink to our certificates folder.
Click to expand...
Just wanted to add after some discussion with Linode, a persistent MySQL connection may be more performant depending on your traffic levels; In our case we did shave a few microseconds off our transaction time.

To do this, simply add p: to the beginning of your ['db']['host'] string.
 
Upvote 0 Downvote
Mouth said:
Hmm .. quite a pricing premium over running it yourself on a node
Click to expand...
True, but you are paying for them managing everything. You click a button to deploy a HA cluster with everything already set up. That’s the point in it being “managed”, you pay for the managed part.
 
Upvote 0 Downvote
Mouth said:
Hmm .. quite a pricing premium over running it yourself on a node
Click to expand...
We used to be on DigitalOcean's offering and we switched to Linode because of the Cloudflare Bandwidth Alliance. In comparison for the same Master-Slave-Slave failover, you'd pay 70$ for the basic offering w/ DO.

Feature wise, DO Beats Linode (for now) in terms of statistics, query/log viewing, and backup offerings.

With DO, you get to the second restore points, where Linode is done daily (at this time); however I can understand if they keep it to daily backups considering the price difference (35 vs 70)
1646211934793.webp

However with Stats and Query/Log viewing, you will have to utilize third party tools to query your endpoint and see whats poppin' as Linode currently does not offer a comparable service.

At this time Linode does not charge for their service while it is in Beta.
 
Upvote 0 Downvote
VersoBit said:
We just configured Linode's DBaaS tonight and heres what our config looks like:
PHP: 
$config['db']['host'] = '<Linode Private DB URL>';
$config['db']['port'] = '3306';
$config['db']['username'] = '<db_user>';
$config['db']['password'] = '<db_password>';
$config['db']['dbname'] = '<dbname>';
$config['db']['ssl']['enabled'] = true;
$config['db']['ssl']['ca'] = 'src/db_ca.pem';

We are running php-fpm8.1; We placed the downloadable certificate in the /src folder named db_ca.pem.
In our case it is a symlink to our certificates folder.
Click to expand...
Thanks so much for sharing this. We had run into the same issue getting setup under Azure's hosted DB system, and needed the SSL flags. Hopefully this can be added to the documentation at some point.
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

Earl
  • Solved
XF 2.2 help with implementing new XenForo content vote system to an add-on, build link, route
Replies
1
Views
703
Jeremy P
Jeremy P
J
  • Question Question
XF 2.2 How to Implementing the search option of xenforo on a landing page on WordPress?
Replies
0
Views
381
joselaholla
J
SFFNetwork
  • Question Question
XF 2.1 Best practice for implementing banner and permissions for "new users"?
Replies
4
Views
438
SFFNetwork
SFFNetwork
Anatoliy
  • Question Question
XF 2.1 Implementing Responsive Images
Replies
0
Views
219
Anatoliy
Anatoliy
C
  • Suggestion Suggestion
Implemented Add MySQLi SSL support
Replies
4
Views
3K
Laetos
Laetos
Top Bottom