Replace the embedded mysql with MariaDB4J

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

There should be a slight change to made in StopEmbeddedMYSQL specifically this

	for (int i = 0; i < args.length; i++) {
		ServerLauncherSocketFactory.shutdown(new File(args[i]), null);
   }.

here are the logs https://pastebin.com/0BD6p3BP

So… a fundamental problem here is that the MariaDB4j wrapper doesn’t support a launch from a special JDBC URL the way that the MXJ connector did. This means that the URL for all the empty-db-* executions can be simplified to just this:

jdbc:mysql://127.0.0.1:33326/openmrs?autoReconnect=true&sessionVariables=storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8

And similarly for all the demo-db-* executions all we need is this:

jdbc:mysql://127.0.0.1:33328/openmrs?autoReconnect=true&sessionVariables=storage_engine=InnoDB&useUnicode=true&characterEncoding=UTF-8

Now we just need to ensure that MariaDB4j is actually running before those liquibase plugins attempt to execute. Fortunately, MariaDB4j has a maven plugin we can take advantage of. Things get a little complicated, however, as we want to start two database before the liquibase plugins run and stop them after they run. The liquibase plugins are configured to run during the generate-resources phase.

So, I would suggest we add this to the POM before any of the liquibase plugins:

<plugin>
	<groupId>ch.vorburger.mariaDB4j</groupId>
	<artifactId>mariaDB4j-maven-plugin</artifactId>
	<configuration>
		<databaseName>openmrs</databaseName>
		<baseDir>${project.build.directory}</baseDir>
		<dataDir>${project.build.directory}/emptydatabase/data</dataDir>
		<port>33326</port>
		<scripts>
			<script>src/main/resources/setup-openmrs-user.sql</script>
		</scripts>
		<args>
			<arg>--basedir=${project.build.directory}/emptydatabase</arg>
			<arg>--datadir=${project.build.directory}/emptydatabase/data</arg>
			<arg>--character-set-server=utf8</arg>
			<arg>--collation-server=utf8_general_ci</arg>
			<!-- 33554432 ~= 32M -->
			<arg>--max-allowed-packet=33554432</arg>
		</args>
	</configuration>
	<executions>
		<execution>
			<id>start-empty-db</id>
			<phase>generate-resources</phase>
			<goals>
				<goal>start</goal>
			</goals>
		</execution>
		<execution>
			<id>stop-empty-db</id>
			<!-- This is a bit hacky... process-resources should be run after generate-resources -->
			<phase>process-resources</phase>
			<goals>
				<goal>stop</goal>
			</goals>
		</execution>
	</executions>
</plugin>
<plugin>
	<groupId>ch.vorburger.mariaDB4j</groupId>
	<artifactId>mariaDB4j-maven-plugin</artifactId>
	<configuration>
		<databaseName>openmrs</databaseName>
		<baseDir>${project.build.directory}/demodatabase</baseDir>
		<dataDir>${project.build.directory}/demodatabase/data</dataDir>
		<port>33328</port>
		<scripts>
			<script>src/main/resources/setup-openmrs-user.sql</script>
		</scripts>
		<args>
			<arg>--basedir=${project.build.directory}</arg>
			<arg>--datadir=${project.build.directory}/emptydatabase/data</arg>
			<arg>--character-set-server=utf8</arg>
			<arg>--collation-server=utf8_general_ci</arg>
			<!-- 33554432 ~= 32M -->
			<arg>--max-allowed-packet=33554432</arg>
		</args>
	</configuration>
	<executions>
		<execution>
			<id>start-empty-db</id>
			<phase>generate-resources</phase>
			<goals>
				<goal>start</goal>
			</goals>
		</execution>
		<execution>
			<id>stop-empty-db</id>
			<!-- This is a bit hacky... process-resources should be run after generate-resources -->
			<phase>process-resources</phase>
			<goals>
				<goal>stop</goal>
			</goals>
		</execution>
	</executions>
</plugin>

We then also need the src/main/resources/setup-openmrs-user.sql file that probably just looks something like this:

CREATE USER 'openmrs'@'%' IDENTIFIED BY 'test';
GRANT ALL PRIVILEGES ON openmrs.* to 'openmrs'@'%';
FLUSH PRIVILEGES;

That probably won’t make everything work, but maybe it gets a bit closer to a working setup.

1 Like

True thanks for clarifying on this, i also at some point landed into it,This means we are also migrating from depending on mjx connectors, dont that have any effect on mysql connectors.

hey actually like how you said above there is a way forward about the changes you provided, however here are the logs i get after packaging the standalone https://pastebin.com/ykQ3BBUD, Am still looking through the logs thanks

Could you try after deleting these lines and the corresponding ones for the demodatabase directory?

1 Like

ok Let me delete them

There is a very good progress so fur https://pastebin.com/Qgxn2bmp, however server stopping from this script setup-openmrs-user.sql, MariaDB4J can be accessed now but the only problem is connection to the openmrs standalone

i have been able to connect to the databases using mariaDB4J, take a look at screen shot

cc @dkayiwa @ibacher

@sharif: That’s not quite connecting to MariaDB. Among other things, notice the Server version: 5.6.45-log MySQL Community Server. What you’ve done is use the MariaDB4j bundle command line client to connect to an already existing MySQL instance on your machine (which is fine; it’s very intentional that you’re able to do that. To really try things out you’d have to in one command window run:

C:\Users\SHARIF\Tickets\openmrs-standalone\target\bin\mysqld.exe, --no-defaults, --console, --skip-grant-tables, --max_allowed_packet=64M, --basedir=C:\Users\SHARIF\Tickets\openmrs-standalone\target, --datadir=C:\Users\SHARIF\Tickets\openmrs-standalone\target\emptydatabase\data, --port=33328, --basedir=C:\Users\SHARIF\Tickets\openmrs-standalone\target, --datadir=C:\Users\SHARIF\Tickets\openmrs-standalone\target/emptydatabase/data, --character-set-server=utf8, --collation-server=utf8_general_ci, --max-allowed-packet=33554432

And in another run something like:

mysql --port 33328

To connect.

Also, could you try re-running the failed Maven build with -e so we can see the full error trace.

1 Like

Thanks @ibacher i will be trying to connect the port, i think i have been missing that

Here is the full log after running with -e -X https://pastebin.com/K34X1Fm6

@sharif: Could you please make sure that all instances of MariaDB are stopped on your machine and try re-running the Maven build?

I’m kind of thinking things are going to get more complex because I’m not sure we can support two instances of MariaDB with the Maven plugin, but we should at least be able to get things working with one…

Sure thanks i will make try making sure we are running on one mariaDB4J

Hey @ibacher what could be your suggestion of stopEmbeddedMYSQL class, actually i tried re running but similar error, i was thinking may be this class may be somehow need to be changed like ServerLauncherSocketFactory.connect(port number, ip address). Any suggestion about this ideal

@sharif I’m not sure I exactly understand the question. Could you point me to some code that shows what you are trying to do that fails?

Here is the ticket here where am working all the work from , Am sorry for late reply

So the StopEmbeddedMySQL isn’t really going to work the ways it’s setup. We’d need to have some equivalent code for MariaDB.

True, thanks for clarification , am looking forward for this,Does that mean we get rid of stopping the embedded mysql, or we are to create the code that connects to mariaDB4J.

Well, the previous code worked on the principle that we can launch MySQL from the JDBC URL and provide a utility to shut it down. I think we need to:

  1. Overhaul this class to support MariaDB4J
  2. Provide a class that launches MariaDB4J on standalone startup
1 Like