Support MariaDB JDBC url and MariaDB driver

jonathan · May 26, 2025, 11:20am

Hi Everyone

As part of improving standalone efforts (GSOC 2025) within the OpenMRS, I would like to propose that we officially support the MariaDB JDBC URL and Driver in addition to the existing MySQL setup.

Problem:

Currently, OpenMRS primarily configures JDBC connections using MySQL conventions

jdbc:mysql://localhost:3306/openmrs

However, when using MariaDB, using the MySQL JDBC URL has lead to compatibility issues with recent versions of the MariaDB server (11.4.5). For instance, am encounter errors such as:

java.sql.SQLSyntaxErrorException: Unknown column ‘RESERVED’ in ‘WHERE’

which call for a solution discussed here

This arises because MySQL and MariaDB have diverged slightly in metadata handling, and the MySQL Connector/J driver does not guarantee compatibility with newer MariaDB versions.

Proposed Solution:

Allow specifying MariaDB-compliant JDBC URLs in runtime properties and configuration wizards

jdbc:mariadb://localhost:3306/openmrs

Include the official MariaDB JDBC Driver (com.mysql.cj.jdbc.Driver) as a supported dependency during setup (openmrs core).
Detect database type (MariaDB vs MySQL) early in setup to avoid known issues with reserved word lookups and changelog parsing in Liquibase.

For the existing drivers supported u can check out here

github.com/openmrs/openmrs-core

api/src/main/java/org/openmrs/util/DatabaseUtil.java

4a61d8a01


      
          	public static String loadDatabaseDriver(String connectionUrl, String connectionDriver) throws ClassNotFoundException {
          		if (StringUtils.hasText(connectionDriver)) {
          			Class.forName(connectionDriver);
          			log.debug("set user defined Database driver class: " + connectionDriver);
          		} else {
          			if (connectionUrl.contains("mysql")) {
          				Class.forName("com.mysql.cj.jdbc.Driver");
          				connectionDriver = "com.mysql.cj.jdbc.Driver";
          			} else if (connectionUrl.contains("hsqldb")) {
          				Class.forName("org.hsqldb.jdbcDriver");
          				connectionDriver = "org.hsqldb.jdbcDriver";
          			} else if (connectionUrl.contains("postgresql")) {
          				Class.forName("org.postgresql.Driver");
          				connectionDriver = "org.postgresql.Driver";
          			} else if (connectionUrl.contains("oracle")) {
          				Class.forName("oracle.jdbc.driver.OracleDriver");
          				connectionDriver = "oracle.jdbc.driver.OracleDriver";
          			} else if (connectionUrl.contains("jtds")) {
          				Class.forName("net.sourceforge.jtds.jdbc.Driver");
          				connectionDriver = "net.sourceforge.jtds.jdbc.Driver";

This file has been truncated. show original

I’d love to hear your feedback or if others have run into this issue.

cc @ibacher @wikumc @dkayiwa @burke @mseaton @grace

sharif · May 26, 2025, 11:28am

MariaDB 11.4.5 of recent introduced changes that are incompatible with MySQL 8 drivers. So i think for some reasons, It makes sense to include it.

mherman22 · May 26, 2025, 12:04pm

Definitely worth supporting, but we should also be keen just incase of Liquibase compatibility issues, driver dependencies, and ensuring backward compatibility with MySQL users. Happy to help verify this across test setups.

dkayiwa · May 26, 2025, 1:23pm

What are your thoughts on having MariaDB4J support a lower version of MariaDB? At least the one we support in O3?

jonathan · May 27, 2025, 12:08pm

Oh yes, that would have been a great idea. However, earlier versions had compatibility issues with M1 and M2 Mac silicon chips. That’s why they opted for a newer version (11.4.5).

Note: Older versions of MariaDB4j lacked support for Apple Silicon (M1, M2+), which remains a critical issue on those machines.

jonathan · May 28, 2025, 3:33pm

On trying the lower version of MariaDB4j (3.1.0) using MariaDB binaries 10.11.5 on a linux machine 20.0.4 using mysql jdbc url and driver

Am getting liquibase errors INFO - Slf4JLogger.log(43) |2025-05-28T18:19:14,864| Marking ChangeSet: "liquiba - Pastebin.com still connected to mariadb

cc @dkayiwa @wikumc

jonathan · June 1, 2025, 1:50pm

@dkayiwa this makes sense now … Have used the lower version of MariaDB4j (3.1.0) using MariaDB binaries 10.11.5 on a linux machine 20.4.6 using mysql jdbc url and driver The persistent error was

???There was an error while updating the database to the latest. file: org/openmrs/liquibase/updates/liquibase-update-to-latest-2.0.x.xml. Error: liquibase.exception.LiquibaseException: Unable to execute change set: liquibase-update-to-latest.xml::20090402-1519-concept_map::bwolfe???
Unable to update the database. See server error logs for the full stacktrace.

After I clone the core , applied this changes to 20090402-1519-concept_map changeset to check if concept_map exists or else to skip this changeset and this solves the error

Initially was

github.com/openmrs/openmrs-core

api/src/main/resources/org/openmrs/liquibase/updates/liquibase-update-to-latest-2.0.x.xml

bcbbce41b


      
          	<changeSet id="20090402-1519-concept_map" author="bwolfe">
          		<preConditions onFail="MARK_RAN"><not><indexExists indexName="concept_map_uuid_index"/></not></preConditions>
          		<comment>Creating unique index on concept_map.uuid column</comment>
          		<createIndex tableName="concept_map" unique="true" indexName="concept_map_uuid_index" >
          			<column name="uuid" />
          		</createIndex>

After change

	<changeSet id="20090402-1519-concept_map" author="bwolfe">
		<preConditions onFail="MARK_RAN">
			<and>
				<tableExists tableName="concept_map"/>
				<not><indexExists indexName="concept_map_uuid_index"/></not>
			</and>
		</preConditions>
		<comment>Creating unique index on concept_map.uuid column</comment>
		<createIndex tableName="concept_map" unique="true" indexName="concept_map_uuid_index">
			<column name="uuid" />
		</createIndex>
	</changeSet>

This solves the liquidbase errors … and able to load all the Demostration mode, Ciel, and Expert mode

Note:

This does not bring the mariadb liquidbase warnings

INFO - Slf4JLogger.log(43) |2025-05-28T18:19:14,867| Marking ChangeSet: "liquibase-update-to-latest.xml::20090402-1516-program_workflow_state::bwolfe" as ran despite precondition failure due to onFail='MARK_RAN': 
          liquibase-update-to-latest.xml : DBMS Precondition failed: expected mysql, got mariadb
          liquibase-update-to-latest.xml : DBMS Precondition failed: expected oracle, got mariadb

After Tried it on the Latest MariaDB (3.2.0) with M1 and M2+ chips support using Mariadb binaries 11.4.5

On a happy note this when i use the updated packaged war file, this also is able to load the home page without errors

However in the logs Initial configuration neededDeleting active databaseUnzipping database from - Pastebin.com these warnings still remain

 java.sql.SQLSyntaxErrorException: Unknown column 'RESERVED' in 'WHERE'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.30.jar:?]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.30.jar:?]
at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) ~[mysql-connector-java-8.0.30.jar:?]
at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.getSQLKeywords(DatabaseMetaDataUsingInfoSchema.java:1194) ~[mysql-connector-java-8.0.30.jar:?]
at liquibase.database.jvm.JdbcConnection.attached(JdbcConnection.java:57) [liquibase-core-4.31.1.jar:?]
at liquibase.database.AbstractJdbcDatabase.setConnection(AbstractJdbcDatabase.java:162) [liquibase-core-4.31.1.jar:?]
at liquibase.database.DatabaseFactory.findCorrectDatabaseImplementation(DatabaseFactory.java:133) [liquibase-core-4.31.1.jar:?]

Which was also happening on the distro side (tried out with same mariadb version 11.4.5)

cc @dkayiwa @wikumc Should I create a ticket and a pull request for this fix on the Core side? (liquidbase failures on standalone)

dkayiwa · June 2, 2025, 12:05pm

Why would this be a bug in core that only manifests itself when running the standalone? Do you mind explaining this a bit more? And what exact changes do you want to introduce in core?

jonathan · June 2, 2025, 7:01pm

This is typically because for other Openmrs installations like sdk and docker (3.x), openmrs loads reference application SQL or demo data or a module during setup which on my assumptions these already contain or create the concept_map table and by the time the liquibase runs, the table is there.

However for the standalone we start with a clean schema which runs and by the time the changeset id="20090402-1519-concept_map" runs there isn’t any table for concept_map which fails

The changes I want to bring attempts to first check if the table exists before attempting to create uuid column in the concept_map table with the precondition <tableExists tableName="concept_map"/>


<changeSet id="20090402-1519-concept_map" author="bwolfe">
		<preConditions onFail="MARK_RAN">
			<and>
				<tableExists tableName="concept_map"/>
				<not><indexExists indexName="concept_map_uuid_index"/></not>
			</and>
		</preConditions>
		<comment>Creating unique index on concept_map.uuid column</comment>
		<createIndex tableName="concept_map" unique="true" indexName="concept_map_uuid_index">
			<column name="uuid" />
		</createIndex>
	</changeSet>

dkayiwa · June 2, 2025, 8:30pm

How do i reproduce this with the SDK?

jonathan · June 2, 2025, 9:09pm

Clone the openmrs-core and apply the liquibase changes for id="20090402-1519-concept_map" located at openmrs-core/api/src/main/resources/org/openmrs/liquibase/updates/liquibase-update-to-latest-2.0.x.xml at bcbbce41be3a516eea4bbb54cf29b0307e4653aa · openmrs/openmrs-core · GitHub

<changeSet id="20090402-1519-concept_map" author="bwolfe">
		<preConditions onFail="MARK_RAN">
			<and>
				<tableExists tableName="concept_map"/>
				<not><indexExists indexName="concept_map_uuid_index"/></not>
			</and>
		</preConditions>
		<comment>Creating unique index on concept_map.uuid column</comment>
		<createIndex tableName="concept_map" unique="true" indexName="concept_map_uuid_index">
			<column name="uuid" />
		</createIndex>
	</changeSet>

Build the war file with mvn clean package which is created in the webapp/target/openmrs.war
Go to openmrs-standalone-xxx/tomcat/webapps and delete that existing data (openmrs.war) from the standalone folder
After copy that openmrs.war file from webapp/target/openmrs.war in core to the openmrs-standalone-xxx/tomcat/webapps
After run openmrs-standalone.jar

Note: This works with the changes from this Pull request STAND-109: Replace the embedded mysql with mariadb4j by Muta-Jonathan · Pull Request #68 · openmrs/openmrs-standalone · GitHub