Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

amordecosmos
Member
From: London (Hometown: Vancouver)
Registered: 2004-08-03
Posts: 71
Website

Can't connect to DigitalOcean managed databases

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)

Offline

#2 2020-02-16 21:39:52

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 9,481
Website

Re: Can't connect to DigitalOcean managed databases

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.


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#3 2020-02-16 23:12:05

jakob
Admin
From: Germany
Registered: 2005-01-20
Posts: 3,851
Website

Re: Can't connect to DigitalOcean managed databases

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.


TXP Builders – finely-crafted code, design and txp

Offline

#4 2020-02-17 08:53:18

gaekwad
Admin
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 2,996

Re: Can't connect to DigitalOcean managed databases

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)

Offline

#5 2020-02-17 13:54:22

amordecosmos
Member
From: London (Hometown: Vancouver)
Registered: 2004-08-03
Posts: 71
Website

Re: Can't connect to DigitalOcean managed databases

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.

Offline

#6 2020-02-17 15:27:09

gaekwad
Admin
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 2,996

Re: Can't connect to DigitalOcean managed databases

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.

Offline

#7 2020-02-17 20:39:17

amordecosmos
Member
From: London (Hometown: Vancouver)
Registered: 2004-08-03
Posts: 71
Website

Re: Can't connect to DigitalOcean managed databases

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)

Offline

#8 2020-02-17 20:59:23

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 9,481
Website

Re: Can't connect to DigitalOcean managed databases

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)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#9 2020-02-17 22:08:24

amordecosmos
Member
From: London (Hometown: Vancouver)
Registered: 2004-08-03
Posts: 71
Website

Re: Can't connect to DigitalOcean managed databases

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

Offline

#10 2020-02-17 23:11:59

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 9,481
Website

Re: Can't connect to DigitalOcean managed databases

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:

  1. Anything you don’t specify will default to null.
  2. In the case of the flags, the flag key must be the key itself without quotes round it.
  3. 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.
  4. 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.
  5. 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)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

Board footer

Powered by FluxBB