Can't connect to DigitalOcean managed databases

amordecosmos · 2020-02-16 19:09:39

Digital Ocean now has MySQL available as a cloud service.

But I cannot for the life of me get TXP to connect to it. Not 4.7, not 4.8. (They work fine, of course, with localhost)

Is it because it’s MySQL 8? Is it the requirement for SSL required?

The info below is the real info – I know that’s not secure but this is only for testing and no one but me will get hurt. When it’s solved, I’ll delete the managed database.

username = doadmin
password = ne3kb0ex54ckv4d9
host = db-mysql-lon1-22948-do-user-6926819-0.db.ondigitalocean.com
port = 25060
database = defaultdb
sslmode = REQUIRED

Last edited by gaekwad (2020-02-17 08:47:37)

Bloke · 2020-02-16 21:39:52

My guess is it’s the SSL. We support MySQL 8 in 4.8.0. Does it work if you open a tunnel at the command line to the server first? e.g. (untested):

ssh doadmin@db-mysql-lon1-22948-do-user-6926819-0.db.ondigitalocean.com -L 3306:localhost:25060 -N

And then connect to defaultdb through port 3306 from your Txp setup?

If not that, maybe something along those lines? I’m not exactly sure of the syntax as tunnelling like this is pretty new to me.

jakob · 2020-02-16 23:12:05

Out of interest, is there a particular advantage to using an external MySQL hosted service? It seems a fairly expensive option compared to a regular droplet with LAMP or LEMP stack.

gaekwad · 2020-02-17 08:53:18

jakob wrote #321693:

Out of interest, is there a particular advantage to using an external MySQL hosted service? It seems a fairly expensive option compared to a regular droplet with LAMP or LEMP stack.

I was thinking the same.

amordecosmos, given you’re running Ubuntu, this bash will install Percona Server 8 (if you’re set on using that, there are no obvious performance advantages I’m aware of over Percona Server 5.7, to be frank) and secure it:

distribcodename=$(grep DISTRIB_CODENAME /etc/lsb-release | cut -f2 -d'=') \
&& rm -f ~/percona-release_latest.$distribcodename_all.deb \
&& wget -O ~/percona-release_latest.$distribcodename_all.deb https://repo.percona.com/apt/percona-release_latest.$(echo $distribcodename)_all.deb \
&& sudo dpkg -i ~/percona-release_latest.$distribcodename_all.deb \
&& sudo percona-release setup ps80 \
&& sudo apt update \
&& sudo apt install -y \
percona-server-client \
percona-server-common \
percona-server-server \
percona-toolkit \
percona-xtrabackup \
&& rm ~/percona-release_latest.$distribcodename_all.deb \
&& sudo mysql_secure_installation

Way cheaper than managed SQL, much quicker (uses a local socket, not a different server), and I’ve battle-tested it in production.

Edit: variation on the above, but for Percona Server 5.7:

distribcodename=$(grep DISTRIB_CODENAME /etc/lsb-release | cut -f2 -d'=') \
&& rm -f ~/percona-release_latest.$distribcodename_all.deb \
&& wget -O ~/percona-release_latest.$distribcodename_all.deb https://repo.percona.com/apt/percona-release_latest.$(echo $distribcodename)_all.deb \
&& sudo dpkg -i ~/percona-release_latest.$distribcodename_all.deb \
&& sudo apt update \
&& sudo apt install -y \
percona-server-client-5.7 \
percona-server-common-5.7 \
percona-server-server-5.7 \
percona-toolkit \
percona-xtrabackup \
&& rm ~/percona-release_latest.$distribcodename_all.deb \
&& sudo mysql_secure_installation

Last edited by gaekwad (2020-02-17 11:32:31)

amordecosmos · 2020-02-17 13:54:22

The attraction to the managed databases is literally the managed part.

Digital Ocean maintains the databases, you can one-click upgrade to more memory/space, the backups are per-instance: you can roll back to any point in time for 7 days. It makes the databases not my problem.

I have clients that are have hundreds of thousands of articles on TXP with thousands of concurrent users. The database quality is important and worth paying for.

gaekwad · 2020-02-17 15:27:09

amordecosmos wrote #321707:

The attraction to the managed databases is literally the managed part.

<snip>

I have clients that are have hundreds of thousands of articles on TXP with thousands of concurrent users. The database quality is important and worth paying for.

Makes total sense, I appreciate the clarification.

amordecosmos · 2020-02-17 20:39:17

Looking at some instructions on how to get Wordpress working with Managed Databases, can someone point me in the direction of changing this for TXP:

$con = mysqli_init();
$con->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$con->ssl_set(NULL, NULL, “ca-certificate.crt”, NULL, NULL);
$con->real_connect(‘host’, ‘your_user’, ‘your_password’, ‘your_database’, 25060);
$con->close();

It’s these three lines that I think need to be modded to TXP:

$con->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$con->ssl_set(NULL, NULL, “ca-certificate.crt”, NULL, NULL);
$con->real_connect(‘host’, ‘your_user’, ‘your_password’, ‘your_database’, 25060);

(The ca-certificate is provided by DO with a download)

Bloke · 2020-02-17 20:59:23

You can get closer by using this construct in config.php:

$txpcfg['host'] = 'hostname:port';
$txpcfg['client_flags'] = MYSQLI_CLIENT_FOUND_ROWS | MYSQLI_OPT_SSL_VERIFY_SERVER_CERT;

But since we don’t support mysqli_ssl_set() in the connection params, the last piece of the puzzle is out of reach.

If that’s all it takes, though, it should be fairly easy to add support for that to config.php so it’ll read the values in and configure them on connection. I can squeeze that in for you to test if you like.

Last edited by Bloke (2020-02-17 21:00:23)

amordecosmos · 2020-02-17 22:08:24

Thanks, Bloke. If you build it, I’ll test it.

Bloke · 2020-02-17 23:11:59

Okay then, please try SSL connection support. It’s totally untested as I don’t have such a managed MySQL connection lying around.

Modify your config.php to include the various things you want in a new ‘ssl’ block, and ensure your ['host'] entry includes the :port. Example:

$txpcfg['db'] = 'defaultdb';
$txpcfg['user'] = 'doadmin';
$txpcfg['pass'] = 'your-pass';
$txpcfg['host'] = 'some.db.ondigitalocean.com:25060';
$txpcfg['table_prefix'] = '';
$txpcfg['txpath'] = '/path/to/textpattern';
$txpcfg['dbcharset'] = 'utf8';
$txpcfg['ssl']['cert'] = 'ca-certificate.crt';
$txpcfg['ssl']['flags'][MYSQLI_OPT_SSL_VERIFY_SERVER_CERT] = 'true';

Entries you can add in the ssl block:

['key'] = ‘/optional/path/to/keyfile.pem’;
['cert'] = ‘/optional/path/to/cert.pem’;
['ca'] = ‘/optional/path/to/ca_cert.pem’;
['capath'] = ‘/path/to/cert/directory’;
['ciphers'] = ‘list of acceptable ciphers’;
['flags'][SOME_FLAG] = ‘true|false|some-value’;

Notes:

Anything you don’t specify will default to null.
In the case of the flags, the flag key must be the key itself without quotes round it.
You don’t have to specify the MYSQLI_CLIENT_SSL flag in the ['client_flags'] parameter as it’s automatically added for you if you configure an ['ssl'] block. Without that, it defaults to the usual MYSQLI_CLIENT_FOUND_ROWS. You can, of course, override this if you want by specifying your own ['client_flags'] in config.php.
No idea how to pass in the list of ciphers. Could be comma-separated. Might need to be an array. It’s largely a mystery. All examples I found seemed to ignore this parameter. The closest I got was a bunch of examples but they only ever use one at a time.
According to various reports around the internet (maybe outdated), MYSQLI_OPT_SSL_VERIFY_SERVER_CERT does nothing. So try it with and without this to see if it makes a difference.

Please see how you get on with that and let me know if it’s successful and, if so, whether you think it’s flexible enough to make a variety of different connection types.

EDIT: if you’re specifying a bunch of SSL params, you can do it in array format if you prefer:

$txpcfg['ssl'] = array(
    'cert' => 'ca-certificate.crt',
    'flags' => array(
        MYSQLI_OPT_SSL_VERIFY_SERVER_CERT => 'true',
    ),
    'capath' => '/path/to/certificates',
);

Last edited by Bloke (2020-02-17 23:21:59)

amordecosmos · 2020-05-15 21:59:44

I’ve come back to this.

I can now connect to the Digital Ocean Managed Database using a stock Textpattern 4.8 (no need to edit the config) as long as I create a MySQL 5 user on their MySQL 8 setup.

So I go through the setup as usual – then this happens:

Storage engine MyISAM is disabled (Table creation is disallowed). in /var/www/html/textpattern/lib/txplib_db.php on line 450

Digital Ocean Managed Database only uses the InnoDB engine for reasons.

Can I get past this?

gaekwad · 2020-05-16 10:02:53

What happens if you replace the two occurrences of MyISAM to InnoDB /textpattern/lib/txplib_db.php and re-run the setup?

Textpattern CMS

Textpattern CMS support forum

#1 2020-02-16 19:09:39