How to use MySQL to read xml dataset for integration test instead of H2 database

Hi there, we are developing a reporting module that uses specific MySQL functions in queries. When we run the queries in H2 database for integration test it complains the syntax.

I tried to replace the functions for equivalent ones so that both H2 and MySQL can understand. But when I do that the results are not the same in MySQL.

There are some functions that I changed to use in both databases:

    • date_add() replaced with timestampadd()
    • datediff() replaced with timestampdiff()

As the module uses MySQL in production I would like to know how to configure project to use MySQL + DbUnit + XML Dataset. May be there are other solutions to do that, If you guys know please let me know

1 Like

In your test, override the useInMemoryDatabase() method and return false. Then supply the connection information as system properties. This will give you some context:

Thank you for your reply. Does this configuration require a physical instance of MySQL database in the machine?

Depends on your databaseUrl value

ok I understand. let me try it

by this it means mysql can not be used in memory for unit tests?

MySQL is not primarily an in memory database.

the idea is to run those SQL functions in database server who understands the syntax… or use mysql in memory.

Tell me something, how do I load xml dataset in MySQL physical instance for tests using dbunit ? May be that is the solution

Did you try doing what i advised above? It should work for MySQL.

Yes sure. But when do that I get the below error:

Looks like there is required privilege missing to stick the data in the database.

Bellow my initial dataset:

<!-- user -->
<users user_id="1" system_id="admin" creator="1"
	date_created="2008-10-13 11:32:52" person_id="1" retired="0"
	uuid="1c3db49d-440a-11e6-a65c-00e04c680037" />

<!-- role -->
<role role="System Developer"
	description="Developers of the OpenMRS .. have additional access to change fundamental structure of the database model."
	uuid="8d94f852-c2cc-11de-8d13-0010c6dffd0f" />

<role role="Authenticated CDaccess"
	description="For Concept Dictionary Synchronization only"
	uuid="e2f0a906-1d5f-11e0-b929-000c29ad1d07" />
	<role role="Anonymous"
	description="Privileges for non-authenticated users."
	uuid="774b2af3-6437-4e5a-a310-547554c7c65c" />
	<role role="Provider"
	description="General privileges held by all providers"
	uuid="8d94f280-c2cc-11de-8d13-0010c6dffd0f" />
<!-- role_role -->
<role_role parent_role="System Developer"
	child_role="Authenticated CDaccess" />

<!-- user_role -->
<user_role user_id="1" role="System Developer" />
<user_role user_id="1" role="Provider" />

<!-- privilege -->
<privilege privilege="Get Patients"
	description="Able to get patients"
	uuid="d05118c6-2490-4d78-a41a-390e3596a244" />

<!-- role_privilege -->
	<role_privilege role="Provider"
	privilege="Get Patients" />

<!-- user_property -->
<user_property user_id="1" property="loginAttempts"
	property_value="0" />

Can you edit your post and replace the stack trace with a link to where your paste is?

Yes sure

You need to add the required privilege.

thank you! that’s was what I was thinking, any privilege for writing? or all of them mentioned in here

Doesn’t the stack trace have the missing privilege?

yes it does, Let me look for what privilege is and then I will come back

Hi @dkayiwa , when I did what you sggested (overrided useinmemorymethod) I got null pointer exception:

public IntegrationModuleDaoTest() throws ManagedProcessException {
		Properties props = getRuntimeProperties();
		if (!useInMemoryDatabase()) {
					DBConfigurationBuilder config = DBConfigurationBuilder.newBuilder();
					config.setPort(0); // 0 => autom. detect free port 
					DB db = DB.newEmbeddedDB(;
					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 
					String url = config.getURL(dbName);
					props.setProperty(Environment.URL, url);
					props.setProperty(Environment.USER, "root");
					props.setProperty(Environment.PASS, "");
	public Boolean useInMemoryDatabase() {
			return false;