Apache Guacamole Broken with MariaDB v11.0

Troubleshooting and resolving this issue took me the better part of a day. So, I’m documenting it below to hopefully help someone else with a similar environment and error.

TL;DR:

MariaDB 11.0 has a breaking change for clients trying to get or set the ‘transaction_isolation’ system variable. It bricked my Apache Guacamole instance. Downgrade to MariaDB 10 or Upgrade to MariaDB 11.1-rc.

[UPDATE 2023-08-25]

Maria DB 11.1 has now been released for general availability (v.11.1.2). You no longer need to specify a release candidate. I recommend using the tag mariadb:11.1 or mariadb:latest if you’re feeling adventurous!

Problem:

I am running containerized versions of both MariaDB and Apache Guacamole in my HomeLab environment. I discovered that the authentication screen on Guacamole was returning a generic error when I tried to log in. When I dug into the log files on the Guacamole container, I saw the following error:

### Error querying database.  Cause: java.sql.SQLException: Unknown system variable 'transaction_isolation'
### The error may exist in org/apache/guacamole/auth/jdbc/user/UserMapper.xml
### The error may involve org.apache.guacamole.auth.jdbc.user.UserMapper.selectOne
### The error occurred while executing a query
### Cause: java.sql.SQLException: Unknown system variable 'transaction_isolation'

This error effectively bricked my Guacamole instance as the application was unable to read anything from the auth database hosted on the MariaDB instance.

Environment:

MariaDB: I’m using the Official Docker Image “mariadb:latest” in my environment. At the time of this article, “latest” was MariaDB v.11.0.2 running on ARM64 (Raspberry Pi).

Guacamole: I’m using the Apache signed release image “guacamole/guacamole:latest”. At the time of this article, “latest” is version Guacamole v. 1.5.2 which comes with guacamole-auth-jdbc-mysql-1.5.2.jar and mysql-connector-j-8.0.32.jar libraries installed by default. I do have the TOTP one time password functionality enabled as well.

Root Cause:

The savvy docker user will recognize that running containers on the “latest” tag will occasionally introduce breaking changes when new versions are published and pulled down to your environment. As this is my HomeLab, that’s exactly what I’m hoping for. This is my development environment. I want to be able to identify breaking changes here, early, so I’m better prepared to support upgrades in my production environments elsewhere. That’s also why I’m documenting this, hoping that this short article will save your bacon if a similar error happens in your production environment!

MariaDB is a fork of the MySQL database ecosystem and works to maintain close compatibility with many of MySQL’s feature additions and changes. However, there can be gaps in that compatibility and it’s one of those recent changes that caused the problem here. MySQL and MariaDB have system variables that can be configured at startup, and in many cases, accessed dynamically by the client. Historically, the ‘tx_isolation‘ and the ‘transaction_isolation‘ system variables have been synonymous. However they were set differently and caused confusion. Between MySQL v5.7 and v5.8, and MariaDB’s v10 and v11 ‘tx_isolation’ is being deprecated in favor of the more unambiguous ‘transaction_isolation’. Both variables exist in MariaDB v10, but deprecation begins in MariaDB v11. Unfortunately in MariaDB v11.0.2 the ‘transaction_isolation’ system variable was not exposed to the client, creating the error at connection seen above. This has been corrected in MariaDB 11.1 and is highlighted at the top of the release notes. Version 11.1 is currently a release candidate as of June 6, 2023, and has not been released for general availability.

Solution:

I chose to upgrade to MariaDB v.11.1.1, even though it is still a release candidate and not recommended for production environments. Once I restarted my MariaDB container as mariadb:11.1-rc, I was able to use my Guacamole instance again without any further modifications. Once MariaDB moves beyond 11.1-rc, I’ll switch back to the mariadb:latest tag.

You can also try rolling back to MariaDB v10 as ‘transaction_isolation’ is also supported in that branch. However, as this involves crossing back over a major release milestone, it’s not recommended unless you restore your DB from backup.

I hope you found this article helpful. Leave a comment if you found it helpful or even if it sent you down the wrong rabbit hole and you ultimately solved it another way!

LED Neon Signage

My son’s high school jazz ensemble has a fundraiser coming up in the next month and needed some set pieces for the stage. This was a good excuse to play with some LED neon rope. Soldering is complete and I’m really happy with how this 2ft x 8ft sign came out.

“Northgate Music” in red and yellow neon.

Spoon Carving in 2022

I like to make stuff every chance I get. Making is a part of my identity and it goes a long way towards maintaining my mental health. Just before the COVID-19 pandemic began, I picked up some carving tools and attended a few spoon carving group activities. Originally I intended to use it to teach knife skills to our scouts and to have something to work on next to the campfire. I’ve carved a few times while quarantining, but finally had a chance to spoon carve socially in 2022. I won’t claim to be any good at it, but I’m certainly having fun learning.

Updates to my Minecraft Dashboard

Back in 2020, at the height of the COVID-19 pandemic, my kids and I set up a Minecraft server and had a blast building worlds together. Too much fun, in fact. It didn’t take us long before we started losing track of which day it was. To help, I built a Minecraft dashboard that we could use to track our time online. We still had trouble remembering which day of the week it was, but now it had much less to so with our time in-world.

This fall, I spent several nights and weekends updating my home lab. I containerized many of the tools and services I have running (much love for Docker and the CasaOS ecosystem πŸ’“). I relocated Minecraft to a new Docker host and needed to update the dashboard as well. Now that Minecraft had been absorbed by Microsoft, I refactored much of the code. Since I didn’t share this link before, I thought I’d do so now:

mcDashboard – A Simple Minecraft Time Tracking Dashboard for Family & Friends Servers

Halloween and the James Webb Space Telescope

In light of so many new space exploration milestones in the last year, I wanted to let my space geek flag fly this Halloween. My wife indulged me as we went to our parties as “Sexy James Webb Space Telescope and the Rocket Scientist”

@tinkeringrocks

Happy Halloween from the GhO’Steens! We had a lot of fun making our costumes this year with the help of @raspberry_pi, #pimoroni and the #nasa #jwst mission.

♬ Struttin – thumpasaurusmusic

New Microcontroller, Who Dis?!

This summer, I joined an amazing group of makers in the hills of Kentucky for 4 days of collaboration, celebration, and giving back to our community. One of our group outreach projects was to build a pinewood derby track for a local Cub Scout pack in need. I teamed up with Halbert Walston to design, build, assemble and program this remote controlled drag strip Christmas tree.

This project used the brand new Raspberry Pi Pico W microcontroller and some Neopixel LEDs from Adafruit. Check out the code and template files on GitHub.