Page 1 of 1

How to failover to another DB in PHP trigger.

Posted: Fri Sep 20, 2019 5:36 am
by Maldayne
Hi everyone.

We currently have two database (DB) connections set up in ProcessMaker.

1. MSSQL : Database from an external software solution
2. MySQL : Duplication of the first Database on the same machine as ProcessMaker.

The system administrator set it up to have the data from the first DB duplicated to the second DB. This was because the first DB was regularly down due to circumstances out of our control.
This allowed ProcessMaker to continue pulling information as it was still available in the PHP Triggers (Simple select statements usually).

The issue with this is that the second DB is not always as up to date as the first DB. Either due to speed of the duplication tool or its failure.

Is there a simple way to have ProcessMaker in a PHP trigger check in the if the first DB connection is up and if so run the MSSQL query. Else use the second connection along with the MySQL query.

:?: Something along the lines of :
Code: Select all

@@db_1 = '11111111101234567890123456789012';
@@db_2 = '22222222201234567890123456789012';

if (@@db_1 can connect){

     $result = executeQuery($mssql_query, @@db_1);

}else if (@@db_2 can connect){

     $result = executeQuery($mysql_query, @@db_1);

}


Any assistance would be greatly appreciated :D

Re: How to failover to another DB in PHP trigger.

Posted: Fri Sep 20, 2019 1:47 pm
by amosbatto
You can try using this:
Code: Select all
$mssqlId = '123456789abcdef134567890abcdef'; //set to ID of the database connection for MSSQL
$mysqlId = 'abcdef123456789abcdef134567890'; //set to ID of the database connection for MSSQL

try {
   $result = executeQuery($sql, $mssqlId);
}
catch (Exception $e) {
   $result = executeQuery($sql, $mysqlId);
}

Another way is to try establishing a connection to the database with Propel::getConnection( $db )
See:
https://www.pmusers.com/index.php/Escap ... se_queries
Code: Select all
$mssqlId = '123456789abcdef134567890abcdef'; //set to ID of the database connection for MSSQL
$mysqlId = 'abcdef123456789abcdef134567890'; //set to ID of the database connection for MSSQL

try {
   $con = Propel::getConnection( $db );
   $db = $mssqlId;
}
catch (Exception $e) {
   $db = $mysqlId;
}
   
$result = executeQuery($sql, $db);

Re: How to failover to another DB in PHP trigger.

Posted: Mon Sep 23, 2019 10:27 am
by Maldayne
Hi Amosbatto.

I tried the first code and it works great. It was much simpler than I expected :roll:

Thank you for the assistance.

I have tested it with no issues and currently having the users test the change to see all is working on their side.