upgrade mysql version for mdsr public databases?

[homerhanumat]

On Ubuntu 20.04 commands like

library(mdsr)
con <- dbConnect_scidb("airlines")

generate an error containing the text:

SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol

The issue appears to relate to improvements in security levels in Ubuntu 20.04 vs. the old-ish version of MySQL at mdsr.cdc7tgkkqd0n.us-east-1.rds.amazonaws.com, see here.

Connections from the terminal works, provided that one disables certificate-checking in ssl:

$ mysql -h mdsr.cdc7tgkkqd0n.us-east-1.rds.amazonaws.com -u mdsr_public --ssl-mode="DISABLED" -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 238030
Server version: 5.6.40-log Source distribution ...

DBI::dbConnect() appears to have offer no way to pass in the equivalent of the --ssl-mode option, so if we want to use it we have to alter our openssl config file.

Perhaps upgrade to MySQL 8? Or (see link above) configure the database to accept TLSv1.2 connections?

[nicholasjhorton]

@beanumber are you able to replicate this? What version of Ubuntu are you running?

[homerhanumat]

Ubuntu version:

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.1 LTS
Release:        20.04
Codename:       focal

For now our workaround is to modify /etc/ssl/openssl.cnf to set the default minimum version TLS version down, from 1.2 to 1.0. Hopefully we aren't messing up something else.

On your end I would worry that upgrading to 1.2 might shut out people who work from other systems.

[beanumber]

Thanks for alerting us to this @homerhanumat !

I'm still running 18.04.5 LTS. I try to avoid upgrading during the semester.

Perhaps we should put a note on the book website for now and worry about upgrading during the summer.

@homerhanumat can you post what you changed in /etc/ssl/openssl.cnf?

Could the ssl-mode option be placed in a MySQL options file?

[homerhanumat]

OS Changes/Upgrades

We got switched over involuntarily from Centos7 to Ubuntu over the winter break. Much hassle, but a good move in the long run I hope. (Among other things, we never could figure out how to install system dependencies on Centos for R package sf.)

Use an Options File?

So far I'm find nothing that works, and it seems that others have also had no luck as well: see this discussion.

Current Best Workaround

As of version 20.04, Ubuntu by default won't work with ssl connection that use TLS below v1.2. The idea, borrowed from the link in my original post above, is to modify the open-ssl configuration file so as to permit connection using TLSv1.1, the latest version supported by the version of MySQL currently in use by the MDSR public database.

First, determine what file the mysql client is consulting in order to deal with ssl connections. In the terminal, run openssl version -d, like this:

openssl version -d

You get the directory where the config file lives. (I got /usr/lib/ssl.)

Next, check out the contents of this directory, e.g.:

$ls -l /usr/lib/ssl
total 4
lrwxrwxrwx 1 root root   14 Apr 20  2020 certs -> /etc/ssl/certs
drwxr-xr-x 2 root root 4096 Dec 16 18:39 misc
lrwxrwxrwx 1 root root   20 Dec  2 14:52 openssl.cnf -> /etc/ssl/openssl.cnf
lrwxrwxrwx 1 root root   16 Apr 20  2020 private -> /etc/ssl/private

Sure enough, there is the config file: openssl.cnf. But observe that it's really a soft link to /etc/ssl/openssl.cnf, where the information "actually" resides. That's the file you want to modify.

Start by making a backup of the file:

sudo cp /etc/ssl/openssl.cnf /etc/ssl/openssl.cnf.bak

Next open your editor:

sudo nano /etc/ssl/openssl.cnf

At the top of the file add:

# workaround for connection to older MySQL RMDs
# remove after upgrades

openssl_conf = default_conf

At the bottom of the file add:

# mysql workaround continued

[ default_conf ]

ssl_conf = ssl_sect

[ssl_sect]

system_default = ssl_default_sect

[ssl_default_sect]
MinProtocol = TLSv1
CipherString = DEFAULT:@SECLEVEL=1

Ctrl-X to exit, and say Y at the prompt to write your changes to disk.

Now the following in R should work:

library(mdsr)
con <- dbConnect_scidb("airlines")

Caveat: This solution in principal affects all open-ssl connections undertaken by the system, not just mysql connections or the specific mysql connection to the MDSR public database.

[homerhanumat]

I seems it will not be possible for you to set options on your end to enable connections via TLSv1.2. In the MySQL documentation I find:

If you cannot or prefer not to change the host system TLS configuration, change MySQL applications to use higher (more secure) TLS protocols that are permitted by the host system. This may not be possible for older versions of MySQL that support only lower protocol versions. For example, TLSv1 is the only supported protocol prior to MySQL 5.6.46, so attempts to connect to a pre-5.6.46 server fail even if the client is from a newer MySQL version that supports higher protocol versions. In such cases, an upgrade to a version of MySQL that supports additional TLS versions may be required.

You are at version 5.6.40. (And we don't want to try upgrading MySQL in the middle of a semester, for sure!)

[beanumber]

Like clockwork, I got a message from AWS that MySQL 5.6 will be EOL'd starting in February and continuing until August.

I'll plan to upgrade this to MySQL 8.0 in late May or early June.

[homerhanumat]

Suddenly I can connect to the mdsr public database on Ubuntu 20.04, without disabling ssl or downgrading the default TSL version to 1.1. Did you change something on your end, @beanumber ?

Strangely, I can do this with mysql in the terminal, but NOT with dbConnect(). (Same old error there.)

I did make a change on my end: I'm now using mysql from the MariaDB client. But that should not make a difference.

Specifically here is what happens in the terminal:

mysql -h mdsr.cdc7tgkkqd0n.us-east-1.rds.amazonaws.com -u mdsr_public -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 238791
Server version: 5.6.40-log Source distribution 
...

And when I check to see if TLS is being used i see that the connection is not secured at all:

MySQL [(none)]> SHOW SESSION STATUS LIKE 'Ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher    |       |
+---------------+-------+
1 row in set (0.007 sec)

It seems that my MariaDB connector ships with a default to non-secure connections (?).

dbConnect() through RMariaDB::MariaDB() gives the original error, so there must be some difference in the configuration between MariaDb connectors in the R package vs. the connector one installs for command-line use.

[beanumber]

Nope.

On Thu, Jan 28, 2021 at 6:16 PM Homer White notifications@github.com wrote:

Suddenly I can connect to the mdsr public database on Ubuntu 20.04, without disabling ssl or downgrading the default TSL version to 1.1. Did you change something on your end, @beanumber https://github.com/beanumber ?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/mdsr-book/mdsr/issues/57#issuecomment-769462127, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAVFJQ7JOUABBDWGUASA35TS4HV55ANCNFSM4WUSR7VA .

[beanumber]

We are now at 5.7.31 on this server. I think that fixed the TLS issue. Can you confirm that this is still working for you @homerhanumat ?

[homerhanumat]

It still works for us, but with the workaround decribed above. It will be a few more days before I can remove the workaround and check whether the upgrade fixed the TLS issue.