Running a Ruby application with MariaDB on Windows 11

Ruby as a platform, wasn't really made to run in a Windows environment. The natural environment to develop and deploy Ruby apps is Linux or some variant of Unix with GNU tools

This is mostly because, many libraries (gems) need to have some part natively compiled when installed or linked to some other native library in order to function.

I am developing an application that has a dependency on MariaDB. After some searching, I found a library named 'mysql2' that presents a simple interface to the database

But this gem compiles a native module that links to the mysql client library on the platform where it is built.

Problem is, I am on Windows 11, and the formats and tools are totally different. So I had to investigate what is the way to do this on this operating system.

First, the Ruby implementation has to be specific to Windows. I went with Ruby Installer (linked here)

The version to download is the one that also includes the DevKit. It is needed to build native windows libraries

Since I am going to use MariaDB, I used the locally installed instance on my development machine to get the library files that are needed.

Make a directory on your disk named for example mariadblib. From the MariaDB install root copy the lib and include folders to this new directory.

Now the following part is described using the project I am developing. The detail relevant to this is that at least one source file of the app, requires the mysql2 library.

The GemFile for the application looks like :

Open a command prompt and put the directory of the ruby interpreter on the path. Then run the command :

Now the mariadb libraries must be processed in order to produce a version that can be linked with the native library produced by the MSYS toolchain that is included in the DevKit of Ruby Installer.

locate the tools gendef.exe and dlltool.exe in the path msys64\ucrt64\bin under the Ruby Installer root. Open a command prompt with administrator priviliges and put that directory on the path. Now change to the directory where are the mariadb files under the lib subfolder in the folder created above. For example

cd c:\mariadblib\lib

Run gendef.exe on the dll :

gendef.exe libmariadb.dll

Now run dlltool.exe on the dll and the def file generated on the previous step :

dlltool -v --dllname libmariadb.dll --def libmariadb.def --output-lib libmariadb.lib

Copy the dll and the generated lib file to the ruby interpreter directory and rename them to libmysql.dll and libmysql.lib.

Now in the project command prompt install the mysql2 gem with the following command :

gem install mysql2 --platform=ruby -- '--with-mysql-dir=C:/mariadblib/'

The gem command will invoke MSYS to build a native shared library and install it as part of the gem.

The mysql2 gem requires TLS/SSL for communicating with the database. For MariaDB versions before 11, this comunication mode must be enabled by the database administrator.

To do this a certificate and a private key must be supplied to the database engine in order to encrypt the data. If you don't already have those on hand, you will have to genmerate them.

In my case I had to install openssl on the windows machine to do the creation of those files. By opening a power shell window and issuing the command:

winget search openssl

The available implementations will be shown. I went with FireDaemon. To install it do:

winget install --id=FireDaemon.OpenSSL -e

once installed put the bin directory on the path so you have access to the openssl command from anywhere

First generate a certificate authority dedicated to signing the database server keys. In some directory do:

openssl genrsa -out ca.key.pem 2048
openssl req -key ca.key.pem -new -x509 -out ca.cert.pem -days 7300

Generate the private key for the server:

openssl genrsa -out mariadb.key 2048

Generate a certificate signing request to sign the server certificate with the CA

openssl req -new -key mariadb.key -out mariadb.csr

Sign the server certificate with the CA certificate:.

openssl x509 -req -in mariadb.csr -CA ca.cert.pem -CAkey ca.key.pem -CAcreateserial -out mariadb.crt -days 365

Then find one of the my.cnf files used by the MariaDB server and put under the server section the following:

ssl_cert = <path to the server certificate>
ssl_key = <path to the server private key>
ssl_ca = <path to the root CA certificate>

Restart the server, log in to any account and do :

SHOW VARIABLES LIKE 'have_ssl';

The value shown should be YES.

I encountered one glitch using this method of installing mysql2 and compiling its native module.

Doing this procedure without change, the MariaDB libraries that I took from the server, cause the mysql2 native code to throw an exception related to the library's version number being mismatched with what was used to compile it.

What I did was to take the sources of the native module as they were along with the generated makefile from the subdirectory of the mysql2 gem, put a copy in a separate directory and comment the instruction that throws the version related error.

Then, in a msys2 command window launched from the msys2 subdirectory of the ruby installer installation, I ran make to clean and recompile the module. I had to put the gcc executable on the path first.

Since the Makefile is already generated from the first time the gem was installed, it should compile without errors. Then take the generated .so file and copy it over the one already present under the mysql2 subdirectory that is in the gem installation.

The error should be gone and the code should be able to connect to the database.