In-memory MariaDB4j for testing Liquibase changesets

Development
,
Tags: #<Tag:0x00007fceb39f04c8> #<Tag:0x00007fceb39f0400>
#1

Dear all, I want to test liquibase changes throw Junit tests. I have used Mariadb4j like that :

Properties props = getRuntimeProperties();

  if (useInMemoryDatabase()) {
  	
  	DBConfigurationBuilder config = DBConfigurationBuilder.newBuilder();
  	config.setPort(0); // 0 => autom. detect free port
  	DB db = DB.newEmbeddedDB(config.build());
  	db.start();
  	
  	String dbName = "openmrs"; // or just "test"
  	if (!dbName.equals("test")) {
  		// mysqld out-of-the-box already has a DB named "test"
  		// in case we need another DB, here's how to create it first
  		db.createDB(dbName);
  	}
  	
  	String url = config.getURL(dbName);
  	
  	props.setProperty(Environment.URL, url);
  	props.setProperty(Environment.USER, "root");
  	props.setProperty(Environment.PASS, "");
  	
  }
  
  Context.setRuntimeProperties(props);

I can see in the logs that the mysql database is installed and running, However, I got this error in my test method :

DatabaseUpdater.executeChangelog(“liquibase.xml”, null);

The error is :

java.sql.SQLException: Unknown system variable 'REFERENTIAL_INTEGRITY'

Here is my pom :

         <dependency>
  	<groupId>ch.vorburger.mariaDB4j</groupId>
  	<artifactId>mariaDB4j</artifactId>
  	<version>2.4.0</version>
  	<scope>test</scope>
  </dependency>

  <dependency>
  	<groupId>mysql</groupId>
  	<artifactId>mysql-connector-java</artifactId>
  	<version>5.1.8</version>
  	<scope>test</scope>
  </dependency>

  <dependency>
  	<groupId>org.slf4j</groupId>
  	<artifactId>slf4j-simple</artifactId>
  	<version>1.7.30</version>
  	<scope>test</scope>
  </dependency>

Thanks a lot in advance, in cc @mksd @dkayiwa (I putted you in copy because I saw that you did some job with MariaDb4j :slight_smile: )

1 Like
#2

Does the full stack trace give more clues?

#3

here are full logs https://justpaste.it/2xh8z

#4

Anyway the quick guess is that the version of MariaDB you’re using doesn’t know of this system variable (REFERENTIAL_INTEGRITY). However it’s referenced here for all Spring tests:

if (useInMemoryDatabase()) {
  constraintsOnSql = "SET REFERENTIAL_INTEGRITY TRUE";
}
#5

Or rather this is for H2:

SET REFERENTIAL_INTEGRITY FALSE/TRUE;

… and apparently this is for MySQL/MariaDB:

SET @@foreign_key_checks = 0/1;

See here.

#6

Exactly, it looks like it is version problem. But, I tried many version dependencies but still getting the same error.

#7

Ok, very good to know, so it looks like we can use only H2 as in memory db, because it is almost hardcoded in openmrs core, right ?

#8

Right now yes, but the base class for Spring tests could be made smarter for those who need something more sophisticated than H2.

You should try first on a fork of Core that you can make things work by bypassing that MariaDB-incompatible statement. I mean, before doing the investment of suggesting a properly designed change of BaseContextSensitiveTest.

1 Like
#9

it makes sense , thanks a lot @mksd

#10

FWIW, @wolf is replacing H2 with mariadb4j while working on this ticket: https://issues.openmrs.org/browse/TRUNK-4830?focusedCommentId=261935&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-261935

2 Likes
#11

Well, it’s worth quite a lot in the context of what @amine’s up to :slight_smile: Great stuff.

#12

@dkayiwa this is a good news, thanks for the info.

By the way, I have found this dependency which allow to “hack” H2 database and add missed MySql functions.

However, in my test class I have to call this method to update my datasource :

H2FunctionsLoader.loadMysqlFunctions(datasource);

How can we get the DataSource object from _Test Class ?

Thanks again,

Regards

#13

update : just for info, it works like that :

Properties props = getRuntimeProperties();

JdbcDataSource dataSource = new JdbcDataSource();

dataSource.setURL(“jdbc:h2:mem:openmrs;MODE=MYSQL;DB_CLOSE_DELAY=30;LOCK_TIMEOUT=10000;LOCK_MODE=0”);

dataSource.setUser(props.getProperty(Environment.USER)); dataSource.setPassword(props.getProperty(Environment.PASS)); H2FunctionsLoader.loadMysqlFunctions(dataSource); DatabaseUpdater.executeChangelog(“liquibase.xml”, null);

However, not all mysql functions are covered, so if we complicated sql queries, you need to add some alias, or at least use functions which are compatible with both MySql and H2.

Regards

#14

Hello, Am trying to embed mysql with mariaDB4J in openmrs standalone via this ticket, with my new commits here however when am trying to test it, i fall into errors, As suggested by @dkayiwa to have changes in this file suggested here, after building the standalone the same logs https://pastebin.com/Et6ifavv. it seems that for us to run on mariaDB4J we need to replace this line https://github.com/openmrs/openmrs-standalone/blob/master/pom.xml#L179 with equivalent url for mariaDB4J . Am still blocked your help will be appreciated thanks cc @dkayiwa @ibacher @mksd

#15

Thanks @amine, a lot of your exploratory work reported here. It helped me a lot getting MariaDB up and running for context-sensitive tests with mariaDB4j :+1:

@dkayiwa I have been doing that in the context of the upcoming Iniz Validator that will allow to make dry runs on OpenMRS configs as a standalone fatjar tool. For this I needed MariaDB rather than H2 to be able to preload the official CIEL SQL dump before replaying the config.

Long story short, all works well but I found that BaseContextSensitiveTest could be more helpful by letting a couple of methods be (non-final) protected (rather than currently private), namely:

Any objections there? @ibacher @mseaton?

1 Like
#16

This is awesome! :slight_smile:

No objections from me.

1 Like
#17

Great, I created

  • TRUNK-5980: BaseContextSensitiveTest to allow further protected overrides

Cc @gcliff @sharif

1 Like
#18

Thanks @mksd Pr here