Support for MariaDB: Looking for volunteers

Hi @marioareias, could you please give me a link to the tests?

I changed BaseContextSensitiveTest in order to use a memory DB depending on a system property. The property is useInMemoryDatabase. If it doesn’t exist or is true, it uses the H2 database. If it’s false, uses the DB configured in the openmrs-runtime.properties file.

Unless in the memory case, the DB is not initialized and no test data is inserted (see initializeInMemoryDatabase method). Therefore the DB must be already initialized (all tables created, and a few of them with data) and the properties file should point at it.

1 Like

@lluismf,

Is that for the MariaDB docker tests on CI? I ran the tests some few days ago and got no failed tests. So If you made some updates I’ll re-run the tests and see.

This thread is long (and I’m not going to go back and read it all), so let’s take a step back:

Our goal for the upcoming OpenMRS release is to commit to support for MariaDB, in addition to MySQL. The way we intend to do this is to have a regularly-running CI plan testing OpenMRS against MariaDB.

My suggestion had been that we run our functional tests against a MariaDB back end (in addition to the MySQL one we’re already testing against). Lluis had proposed that we run the context-sensitive tests in the usual build against MariaDB on CI (instead of H2).

So, help me understand where we’re at now, and let’s try to gather the current status here:

  1. I think that Cintia and Mario configured CI so that you can run the functional tests against MariaDB in a docker container. Is that true? And what’s the URL of that CI plan?
  2. I gather from people’s comments that this plan is not running automatically. Correct? If not, is there some reason we haven’t set it up yet?
  3. Per Lluis’s post, we can run context-sensitive tests against MariaDB. But we are not doing this yet, correct?
  4. Cintia, Mario, would it be easy to use the same MariaDB docker container in a new openmrs-core “build against mariadb” plan?
  5. Do we want to do this?

We should have CI plans that run the context sensitive tests against h2, MariaDB and MySQL(we historically skipped this). I believe the UI tests need to be run against just one DB and not all because if they are testing functionality that hits the DB, most likely that was already covered by the context sensitive tests in the platform

Context sensitive tests means unit tests? If so, I did a small change but was reverted due to line endings problems. The idea is to use a system property to specify wich kind of DB use in testing.

Okay, getting info from the thread…

  1. The URL for the CI plan is here: https://ci.openmrs.org/browse/RELEASE-MDT

  2. It is not being running automatically because it is not done yet. @cintiadr has put some points that needs neither be addressed or answered. More information here: Support for MariaDB: Looking for volunteers

  3. I don’t know about these context-sensitive tests. Currently the CI plan starts two dockers, one with openmrs running on 8080 and another one with running MariaDB. Both docker containers are already connected, which means there is a OpenMRS instance connected with MariaDB using docker containers. 1) I don’t see why not 2) We probably won’t have time available to work on this until the end of year, so it is up to you.

@lluismf context sensitive tests in OpenMRS are technically not really unit tests but rather more like integration tests. That system property shouldn’t never be required by pure unit tests

Thanks @marioareias, this is helpful. I had originally misread @cintiadr’s comment, and I thought that it was done, but just needed refactoring.

From peeking at the CI build it seems to me that:

  • there’s a great start here showing how to start up linked docker containers for mariadb and openmrs
  • we are using the OpenMRS SDK to set up the reference application from an empty DB (and therefore we are actually testing first-time setup)
  • we are not actually running the UI tests yet, and this needs to be added as another task in bamboo
  • at this point it will be “complete” but perhaps need refactoring

So…someone needs to do actual work on this, that involves configuring bamboo.

1 Like

Hi!

I was asked to try out OpenMRS as an EMR for our project. The thing is, we have a CentOS 7.3 on our server with MariaDB. I’ve looked through documentation and messages here, some people reported they had made openmrs work with mariadb. Unfortunately, it didn’t work for me.

So here are few lines how I finally started it up. At first, installation wizard couldn’t connect to the database. MariaDB has its own driver, so I just downloaded it, placed it to /openmrs/WEB-INF/lib/ folder and set a classpath. And really, connection errror log didn’t lie, it should really be stored in that particular folder.
echo $CLASSPATH
/opt/tomcat/webapps/openmrs/WEB-INF/lib/mariadb-java-client-2.2.0.jar

Connection string was changed from jdbc:mysql: to jdbc:mariadb:. These steps got me through right to tables creation. It failed immediately with some syntax errors. So I had to get some Java IDE and look at code. Managed to trace sql queries code, there was a line replacing single quotes with slashes. That really is not necessary for mariadb, so I changed if-statement.

From:
if (wizardModel.databaseConnection.contains(“mysql”)) {
  Class.forName(“com.mysql.jdbc.Driver”).newInstance();
} else {
  replacedSql = replacedSql.replaceAll("`", “”");
}

To:
if (wizardModel.databaseConnection.contains(“mysql”)) {
  Class.forName(“com.mysql.jdbc.Driver”).newInstance();
} else if (wizardModel.databaseConnection.contains(“mariadb”)) {
  Class.forName(“org.mariadb.jdbc.Driver”).newInstance();
} else {
  replacedSql = replacedSql.replaceAll("`", “”");
}

Driver name was taken from mariadb documentation page. It also stated that driver could be simply set with Class.forName() function, which is great.

Build with Maven, uploaded to server, and now we can test OpenMRS with mariadb. Hope it helps!

2 Likes

This is really cool, I hope this will help when attempting to do the same thing with Postgresql. Is Postgresql by any chance in your scope as well?

Not in scope. But I’m pretty sure PostgreSQL also has a JDBC driver, so steps could be repeated.

Good luck

FYI we are running UI tests against Reference Application on both MySQL and MariaDB, see https://travis-ci.org/openmrs/openmrs-distro-referenceapplication/

Interestingly in tests we use MySQL driver to connect to MariaDB. However, we do not install RA from scratch rather upgrade from platform 1.9.x. I do think using MariaDB driver should be preferred, but I am curious what issues you experienced using MySQL driver? It seems our tests do not cover your case.

1 Like

@aloginov, would you be willing to file a ticket for this a create a pull request?

It looks like mariadb is missing from DatabaseUtil.loadDatabaseDriver(…) as well.

Would it be overkill to adopt the strategy pattern for database-specific features – i.e., create an interface enumerating the database-specific tasks needed from OpenMRS and then implement that interface for each supported database (to avoid a growing stack of if…else that is repeated in multiple places)?

Anyway, let’s not let perfect be the enemy of the good. If you could create a TRUNK ticket for your change and throw in a pull request, we’d appreciate it (as would the next person using mariadb)! :slight_smile:

Thanks ALOT for this work , Am also trying to replace the embedded mysql database with mariaDB4J but according to the @aloginov description, it seems

To: if (wizardModel.databaseConnection.contains(“mysql”)) { Class.forName(“com.mysql.jdbc.Driver”).newInstance(); } else if (wizardModel.databaseConnection.contains(“mariadb”)) { Class.forName(“org.mariadb.jdbc.Driver”).newInstance(); } else { replacedSql = replacedSql.replaceAll("`", “”"); }

It seems this is the same creteria i need to figure out from, am working with @ibacher,@dkayiwa about this however am would like to hear from your views thanks

That seems like it would work, but as @burke said, there’s probably a better way to do it. In particular, I actually think constructions like this:

if (isCurrentDatabase(DATABASE_MYSQL)) {
	Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
} else if (isCurrentDatabase(DATABASE_POSTGRESQL)) {
	Class.forName("org.postgresql.Driver").newInstance();
	replacedSql = replacedSql.replaceAll("`", "\"");
} else {
	replacedSql = replacedSql.replaceAll("`", "\"");
}

are outdated.

DriverManager can use the Java Service Provider mechanism to already identify the correct driver given a database URL, as long as it’s on the classpath, and this has been implemented in all of MySQL Connector / J 5.x, the PostgreSQL JDBC connector and MariaDB.

This means we should just be able to do this:

DriverManager.getConnection(url, user, password);

and get a valid database connection without all the Class.forName() calls.

That said, we’d still need to differentiate between how to generate the right format for certain queries…

Thanks alot for your clarification, i agree with you for us not going deep in postgreSQL at this time

Using something like this?

String dbName = connection.getMetadata().getDatabaseProductName();
switch(dbName.toUpperCase()) {
  case "MARIADB":
    // do the mariadb thing
  case "POSTGRES"
    // do the postgres thing
  default:
    // do the mysql thing
}
1 Like

Yes, but I’d imagine this is actually a good place for the strategy pattern you were recommending above since there are a range of queries that need to be determined on a per-database thing :slight_smile:. I also suspect the MariaDB and MySQL implementations would be largely identical.

1 Like

Thanks @ibacher @burke this is how i tried to implement it, ticket, the problem is that, mysql.exe gets stopped i cant tell why this happens which breaks script the setup-openmrs-user.sql from being executed