Install MySQL

Overview

Fusion Registry needs an operating database to store the structural metadata, configuration settings and other operating information like audit logs. MySQL, Oracle and SQL Server are supported. If you already have one of those services available, use that and simply create a new schema for Fusion Registry to use.

Important Note: From version 11.8.0 onwards, the Fusion Registry will no longer ship with a MySQL connector due to licensing issues. The Fusion Registry can still be used with a MySQL database, but you will need to manually supply the MySQL dependency JAR file. If you were using MySQL with an earlier version of Fusion Registry, then you will need to make this change in order to successfully start your Tomcat after installing Version 11.8.0 or later. If you do not supply this dependency then you will receive a "ClassNotFoundException: com.mysql.cj.jdbc.Driver" error when attempting to start the Registry and communicate with a MySQL database.

Versions and Permissions

For version 10.X of the Fusion Registry MySQL version 8 may not be used.

For versions of the Fusion Registry up to version 11, the MySQL database or eqivalent services like MariaDB must be MySQL compatible.

For versions of the Fusion Registry from version 11 onwards, MySQL version 8 may be used.

From Fusion Registry version 11.0.1 onwards, if you wish to use the Registry Managed or External Database feature of Fusion Registry, it is necessary to enable support for "LOAD DATA LOCAL INFILE" on your MySQL instance. This is due to a number of security issues applied by the MySQL team by default and an upgrade in a dependency in a third-party library. Please refer to your Database Administrator or follow the instructions in the Troubleshooting section below.

Installing MySQL 5.7

Download and install MySQL 5.7 Community.

Obtaining the MySql Connector Dependency

To enable the Registry and a MySQL database to communicate the depdendency MySQL Connector/J version 8.0.29 (or higher) is required. This is a Jar file of approximately 2.5 Mb in size and called: mysql-connector-java-8.0.29.jar

This jar can be obtained from the MySQL Web site. At the time of writing this article this link will take you to the appropriate page or alternatively you can obtain it from Maven Central.

Providing the JAR file to your Java Web Server

There are a number of ways that the JAR file can be supplied to your Web Server and these ways will depend on the Web Server you are using. We recommend the use of Apache Tomcat since it is quick and easy to configure.

For Apache Tomcat, the jar file can be added to the "lib" folder of the Tomcat instance. The jar could also be added to the WEB-INF\lib folder of FMR once FMR has been expanded under the "webapps" directory (this method is not recommended as the file is lost when the Registry is upgraded). Another method is to specify the jar via the "setenv" file which allows the setting of environment variables at Tomcat launch-time.

Specifying via setenv

setenv.bat (or setenv.sh for UNIX) is located in the Tomcat's bin folder. If the file does not exist simply create it.

In setenv, add the location of the mysql-connector jar file to the CLASSPATH. This can be achieved by use of the following line (which uses a location from the example above):

set CLASSPATH=C:\SQL_Connector\mysql-connector-java-8.0.30\mysql-connector-java-8.0.30.jar

Troubleshooting

If the Registry fails to start, inspect the Web Server logs. If you encounter the following error:

2022-09-30 23:59:59.999 WARN main org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator - HHH000342: Could not obtain connection to query metadata
java.sql.SQLException: Cannot load JDBC driver class 'com.mysql.cj.jdbc.Driver'
	at org.apache.commons.dbcp2.DriverFactory.createDriver(DriverFactory.java:54)
	at org.apache.commons.dbcp2.BasicDataSource.createConnectionFactory(BasicDataSource.java:459)
	at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:525)
        ...
Caused by: java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver
        at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1407)
        ...

This means that the JAR file has not been supplied correctly to your Web Server. Please review the steps you took in supplying the jar file to your Web Server and ensure that all locations and file names have been correctly specified.

Creating the Fusion Registry schema

We recommend that you create a dedicated schema for Fusion Registry to use. You can do that using the mysqlsh command line interface, but a simple option is to install the MySQL Workbench which provides a graphical user interface for Windows users.

MySQL Workbench GUI

Download and install MySQL Workbench.

Using MySQL Workbench, connect to your MySQL database service and create a new schema. There's a button on the toolbar to do that.

It's usual to give the schema a name like fusion_registry, but there's no restrictions so call it what you like. You'll tell Fusion Registry the name of the schema to use during the install process.

Troubleshooting MySQL

This section details issue you may encounter when using MySQL.

Error: bad SQL grammar: The used command is not allowed with this MySQL version

If you encounter an error like the following:

 org.springframework.jdbc.BadSqlGrammarException: StatementCallback; 
 bad SQL grammar [LOAD DATA LOCAL INFILE 'C:/tomcats/apache-tomcat-9.0.58/temp/matrixBL_1424624886700320079.tmp' INTO TABLE METATECH_DELETE_TEST_1_0_KEY FIELDS TERMINATED BY ','  ENCLOSED BY '"'   
   LINES TERMINATED BY  (  ID,FR_PROVIDER_ACY,FR_PROVIDER_ID,FR_PROVISION,FR_UPDATED_DATE,FR_GROUP_ID,DIM_A,DIM_B,SER_ATT_B,SER_ATT_A)  ]; 
 nested exception is java.sql.SQLSyntaxErrorException: The used command is not allowed with this MySQL version

This is where MySQL has been configured to not allow the command LOAD DATA LOCAL INFILE. By default, this is disabled as it is considered a security risk to be able to load data this way !! In particular:

 LOAD DATA LOCAL INFILE is disabled by default because it poses a security risk. A malicious server or proxy could send a fake “local infile request” packet to the client and read any file that the client has permission to open.

From: https://mysqlconnector.net/troubleshooting/load-data-local-infile/

MySQL 5.X appears to have the value of local_infile set to "true" or "ON" by default, whereas MySQL 8.X appears to have this value set to "false" or "OFF"

To enable the loading of data, you will need to set your MySql database to enable the loading of data via "LOAD DATA LOCAL INFILE". This is performed via the editing of the config file, my.cnf and add:

[mysqld]
local-infile 
[mysql]
local-infile 

Alternatively you may find the following works. If you run the following command:

 SHOW GLOBAL VARIABLES LIKE 'local_infile';

This should show a value of "ON"

If it doesn't run the following:

 SET GLOBAL local_infile = TRUE;