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!