Database

When BridgeGate first starts it will run several databases connection checks.  If any fail it will report the error.   In the /bridgegate/logs/server.log

Search for checkDB() Starting DB check!  You shouldn’t see any errors after that statement.

Common causes of database connection errors:

1. Database is not started or is on a server that is blocking the bridgegate server port 3306
2. MySQL is configured with SSL as default and you are connecting unsecured. Add the following to the JDBC URL.  useSSL=false
3. The Timezone is not setup as default.  Add the following to the JDBC URL serverTimezone=EST
4. Check the mysql log for any mysql exceptions on startup

When BridgeGate is first started it check the connection to the database.  If you receive the following exception the version of MySQL has a parameter change.

[com.oidev.bridgegate.clustering.BridgeGateLoadBalancePolicy] checkDB() Error executing sql! java.sql.SQLNonTransientConnectionException: Cannot load connection class because of underlying exception: com.mysql.cj.exceptions.WrongArgumentException: The connection property ‘zeroDateTimeBehavior’ acceptable values are: ‘CONVERT_TO_NULL’, ‘EXCEPTION’ or ‘ROUND’. The value ‘convertToNull’ is not acceptable. at

Update the /bridgegate/conf/bridgegate.xml   Change zeroDateTimeBehavior=convertToNull  to zeroDateTimeBehavior=CONVERT_TO_NULL

<DATABASE_CONNECTION_POOL ACQUIRE_INCREMENT=”2″ AUTO_COMMIT_ON_CLOSE=”false” CHECKOUTTIME=”600″ DRIVER=”com.mysql.cj.jdbc.Driver” IDLETIME=”600″ IDLE_TEST_PERIOD=”300″ JDBCURL=”jdbc:mysql://localhost/bridgegate?useSSL=false&amp;autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;zeroDateTimeBehavior=CONVERT_TO_NULL&amp;serverTimezone=EST”   ….. />

WARN: 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 on the JDBC URL String or set useSSL=true and provide truststore for server certificate verification.

BridgeGate can be configured for mysql database failover with the following updates to the jdbc url in the bridgegate.xml

MySQL jdbc supports server failover. A failover happens when connection-related errors occur for an underlying, active connection.  ie Database down.  A connection using failover support works just like a standard connection: the client does not experience any disruptions in the failover process.

jdbc:mysql://[primary host][:port],[secondary host 1][:port][,[secondary host 2][:port]]...[/[database]]» [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

The host list in the connection URL comprises of two types of hosts, the primary and the secondary. When starting a new connection, the driver always tries to connect to the primary host first and, if required, fails over to the secondary hosts on the list sequentially when communication problems are experienced. Even if the initial connection to the primary host fails and the driver gets connected to a secondary host, the primary host never loses its special status: for example, it can be configured with an access mode distinct from those of the secondary hosts, and it can be put on a higher priority when a host is to be picked during a failover process.

Update the jdbc url to include the primary database IP and then a comma list of secondary IP address.

jdbc:mysql://192.168.10.17,192.168.10.18/bridgegate? …..

Below is an example of the bridgegate.xml configured with a primary db on IP 192.168.10.17  and the secondary db on IP 192.168.18

<DATABASE_CONNECTION_POOL ACQUIRE_INCREMENT=”2″ AUTO_COMMIT_ON_CLOSE=”false” CHECKOUTTIME=”600″ DRIVER=”com.mysql.cj.jdbc.Driver” IDLETIME=”600″ IDLE_TEST_PERIOD=”300″ JDBCURL=”jdbc:mysql://192.168.10.17,192.168.10.18/bridgegate?useSSL=false&amp;failOverReadOnly=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;zeroDateTimeBehavior=CONVERT_TO_NULL” MAXCONN=”300″ MAX_STATEMENTS=”0″ MINCONN=”10″ PASSWORD=”ENC3AMq%2BPLK4Q%2Bzvhu2oJhQvw%3D%3D” USERNAME=”bridgegate”/>

MySQL also supports load balancing across DBs.

Update bridgegate.xml to use the mysql loadbalance feature. Append loadBalanceStrategy=bestResponseTime to the existing URL. For URL jdbc:mysql:loadbalance://192.168.10.88,192.168.10.89/bridgegate?useUnicode=true&amp;characterEncoding=UTF-8&amp;zeroDateTimeBehavior=convertToNull&amp;loadBalanceStrategy=bestResponseTime

More details can be found on the mysql site.  See below

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-config-failover.html

While BridgeGate is logging transaction/workflow details to the database, if it experiences issues saving a SQL statement to the database the statements are saved in the following location and will be automatically retried every 20 minutes.

/bridgegate/server/sqlqueue.history

There are many connection options for SQL Server.  The quickest way is to configure a Predefined Connection using JDBC. BridgeGate includes a generic JDBC driver for SQL Server named jtds.

The JDBC Syntax

The general form of the connection URL is

jdbc:jtds:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

where:

• jdbc:jtds:sqlserver:// (Required) is known as the sub-protocol and is constant.
• serverName (Optional) is the address of the server to connect to. This could be a DNS or IP address, or it could be localhost or 127.0.0.1 for the local computer. If not specified in the connection URL, the server name must be specified in the properties collection.
• instanceName (Optional) is the instance to connect to on serverName. If not specified, a connection to the default instance is made.
• portNumber (Optional) is the port to connect to on serverName. The default is 1433. If you are using the default, you do not have to specify the port, nor its preceding ‘:’, in the URL.

Examples of connecting to a SQLServer database with the instance name of MSSQLSERVER and the database schema name of BillingTest1

You can find the instance name in the SQL Management or by looking at the Services Name

You can find the databasename in the SQL Management under Databases.

Once you have located the two parameters you can build the JDBC String on the Predefined Connection screen in BridgeGate.

In this example the full syntax is

jdbc:jtds:sqlserver://192.168.10.28;instance=MSSQLSERVER;databasename=BillingTest1

Backup MySql Database using MySqlAdmin or mysqldump (Instructions below)

• Open a Command prompt on the desktop of the BridgeGate server.
• Assuming BridgeGate 7.0 is installed on c:
• At a c:\bridgegate\mysql\bin> prompt
• Replace MYSQLPASSWORD in the below command with your current MySql password
• Run: ” mysqldump -u bridgegate -pMYSQLPASSWORD –routines –hex-blob bridgegate > c:\bridgegate\bg.sql”
  • If you have MySQL databases other than bridgegate, you can backup all of them together with the following:
    • Run: “mysqldump -u bridgegate -pMYSQLPASSWORD –routines –hex-blob –databases bridgegate db1 db2 db3 db4 > c:\bridgegate\bg.sql”
    • Replace db1 db2 db3 db4 with the other database names

Yes as long as you can access the DB via JDBC or ODBC

When backing up databases manually you should use the —hex-blob option  to ensure encrypted data is not corrupted.   We have seen issues in the past where an ascii dump has issues.

• Open a Command prompt on the desktop of the BridgeGate server.
• Assuming BridgeGate is installed on E:
• At a E:\bridgegate\mysql\bin> prompt
• Replace MYSQLPASSWORD in the below command with your current MySql password
• Run: “mysqldump -u bridgegate -p –add-drop-database –hex-blob –routines bridgegate > E:\bg_backup\bridgegate\bg.sql”
  • If you have MySQL databases other than bridgegate, you can back up all of them together with the following:
    1. Run: “mysqldump -u bridgegate -p –routines –add-drop-database –databases bridgegate db1 db2 db3 db4 > E:\bg_backup\bridgegate\bg.sql”
       • Example: “mysqldump -u bridgegate -p –routines –add-drop-database –databases bridgegate ccd cda mds mpi msg_tracking nemsis oasis > E:\bg_backup\bridgegate\bg.sql”