MySQL 8.0 TLS connection issue

MySQL 8.0 TLS connection issue

This arose with an upgrade to MySQL 8.0.4 on my server, which caused OpenSim (0.9.2.0 in this case) to be unable to connect via TLS (“SSL”) to Robust.

Here follows the text from https://oceangrid.net/news/107-mysql-8-0-tls-connection-issue.html

——

An upgrade to MySQL caused the grid to fail to connect to the database, since OpenSim 0.9.2.0 was not properly configured to connect to MySQL 8.0 by TLS, which is what MySQL 8.0 now assumes by default unless instructed otherwise, unlike in earlier versions.

There is a Mantis report that outlines the issue. However, default-authentication-plugin=mysql_native_password was already set in /etc/mysql/mysql.conf.d/mysqld.cnf and no change was required (Ubuntu 21.04.3 LTS).

There is no need for the overhead of TLS since the connection is being made on localhost, so the solution was to connect without TLS and thus without needing to configure the certificates by changing a line in Robust[.HG].ini as follows (with the parts in bold added):

[DatabaseService]
; MySQL
; Uncomment these lines if you want to use MySQL storage
    ; Change the connection string to your db details
    ; Remove SslMode=None if you need secure connection to the local MySQL
; If using MySQL 8.0.4 or later, check that default-authentication-plugin=mysql_native_password rather than caching_sha2_password is set in /etc/mysql/mysql.conf.d/mysqld.cnf (not applicable to MariaDB).
; In most cases ssl is just a pure waste of resources, specially when MySQL is on same machine, and closed to outside
StorageProvider = “OpenSim.Data.MySQL.dll”
ConnectionString = “Data Source=localhost;Database=opensim;User ID=opensim;Password=*****;Old Guids=true;SslMode=None;

You will notice that this hasn’t been documented, whereas it has been documented in GridCommon.ini and StandaloneCommon.ini as follows (with the suggested extra documentation added):

And also there needs to be a change to GridCommon.ini

[DatabaseService]
; MySQL
; Uncomment these lines if you want to use MySQL storage
; Change the connection string to your db details
; Remove SslMode=None if you need secure connection to the local MySQL
; If using MySQL 8.0.4 or later, check that default-authentication-plugin=mysql_native_password rather than caching_sha2_password is set in /etc/mysql/mysql.conf.d/mysqld.cnf (not applicable to MariaDB).
; In most cases ssl is just a pure waste of resources, specially when MySQL is on same machine, and closed to outside
StorageProvider = “OpenSim.Data.MySQL.dll”
ConnectionString = “Data Source=localhost;Database=opensim;User ID=opensim;Password=***;Old Guids=true;SslMode=None;”

; Uncomment this line if you are using MySQL and want to use a different database for estates
; The usual application for this is to allow estates to be spread out across multiple simulators by share the same database.
; Most people won’t need to do this so only uncomment if you know what you’re doing.
;EstateConnectionString = “Data Source=localhost;Database=opensim;User ID=opensim;Password=***;Old Guids=true;SslMode=None;”

This requires a patch to the documentation so that the same problem won’t affect others.

Patch suggested: http://opensimulator.org/mantis/view.php?id=8966

Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: