Configuration for DCC, IS and MWS to use MySQL Community edition (5.7) RDS on AWS

[Community-admin (Tech Community team)]

To use Integration Server and My webMethods Server with MySQL 5.7 RDS on AWS, we need to install products first, while installing products for we need to provide db details of MYSQL. Then we need to launch a database configuration to create tables.

Below are the steps to configure IS and MWS products with MySQL 5.7 community edition:

1. Configuring Database Configurator to Use MySQL Community Edition

1. Download MySQL native driver from the following location:
    
2. Copy the .jar file to the location: <Software AG directory>\common\lib\ext\
    
3. Open setEnv.bat file in the following location:

<Software AG directory>\common\db\bin

Or (in case Database Component Configurator installed separately)

<Database Component Configurator installation directory>\common\db\bin

1. Add class path for MySQL Community Edition driver jar in setEnv.bat file like the following:

set CLASSPATH=%CLASSPATH%;%DCI_HOME%\..\lib\ext\<MySQL Community Edition driver name>

Example: set CLASSPATH=%CLASSPATH%;%DCI_HOME%\..\lib\ext\mysql-connector-java.jar

1. Generic URL for MySQL Community Edition is:

jdbc:mysql://<server>:<3306|port>/databaseName

Example: jdbc:mysql://sample.eur.ad.sag:4306/sample

1. Configuring My webMethods Server to Use MySQL Community Edition

1. Download MySQL native driver from the following location:
   https://dev.mysql.com/downloads/connector/j/5.1.html
    
2. Copy the .jar file to the Software AG_directory \MWS\lib directory

1. Create a driver-name .bnd text file in the Software AG_directory \MWS\lib directory, where driver-name is the name of the .jar file, for example, mysql-connectorjava-version .bnd

1. Provide instructions for the OSGi bundle conversion in the .bnd text file, as described in the following example.

# attach as fragment to the caf.server bundle

Fragment-Host: com.webmethods.caf.server

Bundle-SymbolicName: mysql-connector-java-

Bundle-Version: 5.1.41

Include-Resource: mysql-connector-java-5.1.41 .jar

-exportcontents: *

Bundle-ClassPath: mysql-connector-java-5.1.41 .jar

Import-Package: *;resolution:=optional

1. At a command line prompt, type the following command to move to the server's bin directory and run the command:

cd Software AG_directory\MWS\bin

mws.bat -s serverName update

1. Start My webMethods Server

Important: If My webMethods Server failed to start with below error:

Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. 

If such an issue occurs, update URL parameter in mws.db.xml files:

<URL>jdbc:mysql: ://<server>:<3306|port>/databaseName?useSSL=false</URL>

Command to create MWS DB tables using Database configurator:

dbConfigurator.bat -a create -c MywebMethodsServer -v latest -d MySQL -l jdbc:mysql://mysqlrds57.amazonaws.com:3306/wmdbrds57 -u mysqlrds57user -p Disney123 -n wmdbrds57

If MywebMethodsServer table creation failed with error: “script failing because RDS does not have super privileges”
Workaround: Removed below line from following files: 

C:\SoftwareAG\common\db\scripts\mysql\mws\65\create\mys_mws_c_ddl_tables.sql 
C:\SoftwareAG\common\db\scripts\mysql\mws\65\create\mys_mws_c_ddl_socialbpm.sql 
C:\SoftwareAG\common\db\scripts\mysql\mws\65\create\mys_mws_c_ddl_taskorchestrator.sql 
C:\SoftwareAG\common\db\scripts\mysql\mws\65\create\mys_mws_c_ddl_taskengine.sql 

Line to remove:

set GLOBAL innodb_large_prefix = ON;

set GLOBAL innodb_file_format = BARRACUDA;

1. Configuring “Integration server” to Use MySQL Community Edition

a. In a text editor, open the ini.cnf file from the following directory:

<Software AG_directory>\IntegrationServer\instances\IS_instance_name\bin

b. Add the following entry to the application.classpath property:]

%COMMON_LIB_EXT%mysql-connector-java.jar

c. Restart Integration Server [If it’s already up].

d. Use the following URL to configure a JDBC connection pool alias for MySQL Community Edition 5.7 on the

    Settings > JDBC Pools screen in Integration Server Administrator:

jdbc:mysql://<server>:<3306|port>/<databaseName

Example:

jdbc:mysql://mysqlrds57.amazonaws.com:3306/wmdbrds57

Command to create IS DB tables using Database configurator:

dbConfigurator.bat -a create -c ISInternal -v latest -d MySQL -l jdbc:mysql://mysqlrds57.amazonaws.com:3306/wmdbrds57 -u mysqlrds57user -p Disney123 -n wmdbrds57