Difference between revisions of "Install MySQL"

From FMR Knowledge Base
Jump to navigation Jump to search
(Installing MySQL 5.7)
(Installing MySQL 5.7)
(19 intermediate revisions by 2 users not shown)
Line 2: Line 2:
 
[[Category:How_To V11]]
 
[[Category:How_To V11]]
 
=Overview=
 
=Overview=
Fusion Metadata 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 Metadata Registry to use.
+
MySQL may be used to act as the persistant store for Registry information. However the JDBC driver that communicates from the Registry to a MySQL instance is '''not''' provided within FMR (since FMR version 11.4.0) and must be obtained by a Systems Administrator.
  
Note that MySQL or equivalent services like MariaDB must be MySQL 5.7 compatible.  
+
Version 8 of MySql may be used with FMR, and it is permitted to use a MySQL equivalent service like MariaDB, provided it is MySQL compatible.
  
[[File:Warning.PNG|Warning|30px]] Fusion Metadata Registry is not compatible with MySQL 8.
+
=Installing MySQL=
 +
It is beyond the scope of this article to explain how to install MySQL, but at the current time of writing the community edition of MySQL Server may be [https://dev.mysql.com/downloads/windows/installer/5.7.html downloaded for free].
  
For those without an existing database service, the rest of this guide explains how to install and configure MySQL 5.7 on Windows.
+
The MySQL instance must be accessible to the FMR and FMR will need an database account that can create, update and modify tables.
  
=Installing MySQL 5.7=
+
= Obtaining and Specifying the MySQL JDBC Driver =
Download and install [https://dev.mysql.com/downloads/windows/installer/5.7.html MySQL 5.7 Community].
+
To get FMR to communicate succesfully with a MySQL database, there are three tasks to perform:
 +
# Obtain the correct dependency file
 +
# Supply this to your Java Web Server (e.g. Tomcat) that you are using to run FMR.
 +
# Specify the appropriate Credentials to FMR.
  
== Using FMR 11.4 or later versions? ==
+
==Obtaining the MySQL Connector Dependency==
 +
To enable FMR 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'''
  
As from Verion 11.4, if you are using MySQL you will needto obtain the MySQL connector as discussed [[Upgrading_to_Version_11.4#Obtain_the_mySql_connector| in this article.]]
+
This jar can be obtained from the MySQL Web site. At the time of writing this article [https://dev.mysql.com/downloads/connector/j/ this link will take you to the appropriate page] or alternatively you can obtain it from [https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.30 Maven Central].
  
Having obtained the connecter you will need to make a change to the fine sertenv.bat [[Upgrading_to_Version_11.4#Change_setenv.bat | as detailed in this article.]]
+
=== Obtaining the Jar file from the MySQL website ===
 +
 
 +
From the dropdown list, select ''''Platform Independen'''t' as shown in the image below and then select which type of compressed file you would like to downlaod.
 +
 
 +
These instructions are based on downloading the Zip file.
 +
 
 +
[[File:Download Connector.PNG|Download Connector|600px]]<br>
 +
 
 +
If you are prompted as shown below, there is no need to Login or Sign Up, simply click on ''''No thanks'''' to download the relevant files.
 +
 
 +
[[File:Download Connector Confirm.PNG|600px]]<br>
 +
 
 +
 
 +
Once the zip file has been downloaded you need to unzip it in the usual way to a folder which will always be available to the application.
 +
 
 +
 
 +
Extracting the files:
 +
 
 +
[[File:Extract SQL Connector.PNG|600px]]<br>
 +
 
 +
In this example, I have downloaded the files to a sub folder on my C drive as shown below.
 +
 
 +
[[File:SQL Connector Target.PNG|300px]]<br>
 +
 
 +
Make a note of the full location path (including the file name) for the 'jar' file, in this example: ''''C:\SQL_Connector\mysql-connector-java-8.0.30\mysql-connector-java-8.0.30.jar''''.
 +
 
 +
[[File:Jar file location.PNG|600px]]<br>
 +
 
 +
==Driver Installation==
 +
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
 +
 
 +
= Connection Details =
 +
FMR requires a database to be specified on the first page of the Install Wizard.  This can be changed at a later from the Administrator "Database Settings" page.
 +
 
 +
Oracle by default uses port 1521.
 +
 
 +
If you wish to make a custom connection to MySQL, the information you will likely need is:
 +
 
 +
{| class="wikitable" style="vertical-align:top; text-align:left;"
 +
! Connection String
 +
|| Of the form:
 +
jdbc:mysql://<server>:<server port>/<database name>
 +
<br/>
 +
e.g. jdbc:mysql://localhost:3306/fmr_test_v11?serverTimezone=GMT
 +
|-
 +
! Dialect
 +
|| org.hibernate.dialect.MySQL55Dialect
 +
|-
 +
! Class Name
 +
|| com.mysql.cj.jdbc.Driver
 +
|}
  
 
=Creating the Fusion Metadata Registry schema=
 
=Creating the Fusion Metadata Registry schema=
Line 30: Line 94:
 
It's usual to give the schema a name like <code>fusion_metsdata_registry</code>, but there's no restrictions so call it what you like. You'll tell Fusion Metadata Registry the name of the schema to use during the install process.
 
It's usual to give the schema a name like <code>fusion_metsdata_registry</code>, but there's no restrictions so call it what you like. You'll tell Fusion Metadata Registry the name of the schema to use during the install process.
  
=New Installations of FMR=
+
==Troubleshooting==
If you are implementing a new installation of Fusion Metadata Registry, having installed MySQL the next step is to to complete the installation process [[Install Fusion Metadata Registry|More]].
+
If FMR fails to start, inspect the Web Server logs.  If you encounter the following error:
 +
 
 +
<pre>
 +
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)
 +
        ...
 +
</pre>
 +
 
 +
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.

Revision as of 00:38, 26 October 2022

Overview

MySQL may be used to act as the persistant store for Registry information. However the JDBC driver that communicates from the Registry to a MySQL instance is not provided within FMR (since FMR version 11.4.0) and must be obtained by a Systems Administrator.

Version 8 of MySql may be used with FMR, and it is permitted to use a MySQL equivalent service like MariaDB, provided it is MySQL compatible.

Installing MySQL

It is beyond the scope of this article to explain how to install MySQL, but at the current time of writing the community edition of MySQL Server may be downloaded for free.

The MySQL instance must be accessible to the FMR and FMR will need an database account that can create, update and modify tables.

Obtaining and Specifying the MySQL JDBC Driver

To get FMR to communicate succesfully with a MySQL database, there are three tasks to perform:

  1. Obtain the correct dependency file
  2. Supply this to your Java Web Server (e.g. Tomcat) that you are using to run FMR.
  3. Specify the appropriate Credentials to FMR.

Obtaining the MySQL Connector Dependency

To enable FMR 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.

Obtaining the Jar file from the MySQL website

From the dropdown list, select 'Platform Independent' as shown in the image below and then select which type of compressed file you would like to downlaod.

These instructions are based on downloading the Zip file.

Download Connector

If you are prompted as shown below, there is no need to Login or Sign Up, simply click on 'No thanks' to download the relevant files.

Download Connector Confirm.PNG


Once the zip file has been downloaded you need to unzip it in the usual way to a folder which will always be available to the application.


Extracting the files:

Extract SQL Connector.PNG

In this example, I have downloaded the files to a sub folder on my C drive as shown below.

SQL Connector Target.PNG

Make a note of the full location path (including the file name) for the 'jar' file, in this example: 'C:\SQL_Connector\mysql-connector-java-8.0.30\mysql-connector-java-8.0.30.jar'.

Jar file location.PNG

Driver Installation

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

Connection Details

FMR requires a database to be specified on the first page of the Install Wizard. This can be changed at a later from the Administrator "Database Settings" page.

Oracle by default uses port 1521.

If you wish to make a custom connection to MySQL, the information you will likely need is:

Connection String Of the form:

jdbc:mysql://<server>:<server port>/<database name>
e.g. jdbc:mysql://localhost:3306/fmr_test_v11?serverTimezone=GMT

Dialect org.hibernate.dialect.MySQL55Dialect
Class Name com.mysql.cj.jdbc.Driver

Creating the Fusion Metadata Registry schema

We recommend that you create a dedicated schema for Fusion Metadata 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.

MySql Workbench

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

Troubleshooting

If FMR 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.