Avoiding some destination script stop your channel by running the task in another thread

[davutg]

We've some DB Writer channels, a few times a year the channel was stucking on a message and further messages were not being processed due to that reason until we reset the channel.

I think it was happening due to some DB related issues, maybe a record was locked for update or kinda reason. I've developed below method in order to process destination task in a different thread to avoid this issue. More than a year we've never faced any issue for the channels we applied this.

You can use it at your own risk, not only for DB writing JS channel tasks but for some others as well.

var dbConn;
var executor = java.util.concurrent.Executors.newCachedThreadPool();
var task = new java.lang.Runnable() {
	run(){
		try {
			dbConn =...
                        //your code goes here
               } finally {
			if (dbConn) { 
				dbConn.close();
			}
		}
       }

Running the task with task executor

try {
	logger.info("Future task started: "+connectorMessage.getMessageId()+" , "+ new Date());
	var future = executor.submit(task);
        var result = future.get(5, java.util.concurrent.TimeUnit.SECONDS); 
} catch (error) {
       // handle the timeout
       logger.error("Timeout occured " + error);
} finally {
       //future.cancel(true); // may or may not desire this
       logger.info("Future task ended: "+connectorMessage.getMessageId()+" , "+ new Date());
}

[pacmano1]

As you mentioned "use at your own risk". One risk is that a user should know if their data is expected to process in order both for a single destination and potentially all destinations. Just a note, not a criticism.

[davutg]

This is the reason I put that note, more precisely use it if you know what you're doing :) This will not solve the actual problem with the message, but avoid the entire channel hanged due to a single message. I'm open to any comment including criticism. We can only make progress when there's some..

[pacmano1]

For sure. In my particular case, db calls use a code template that in turn uses a shared dbconn in $g. I can't remember where I got this tempalte from - someone said it is shared during mirth advanced class. Inline dbConn creation IMHO is somewhat of a pain from a code maintenance perspective and/or "did I close the thing". This is a particularly interesting topic to me - since I nearly never have to worry about db contention or failured and I am always curious if that is an artifact of some fundamental design aspects for integration. For example, in nearly all work we do, mirth "owns" (not a db term, I mean mirth is the only writer/updater/deleter) the tables from which data is sourced from external system - and other procsses (like the application that uses that data) only link via FK to that data, perhaps copying some of it as needed. Part of that design is the inherent problem of maintaining data as received from the inbound system for subsequent delivery back vs the representation of the patient within the application being integrated, with the latter possibly have a newer representation of the patient due to patient engagment than the original sending system.

/**
	Executes a SQL statement using a shared database connection stored in the Global Map. If the
	connection does not exist, creates it.

	@param {String} statement - The SQL statement to execute
	@param {Boolean} isQuery - true if the statement is a SELECT, otherwise false
	@param {List} paramList - A Java List object containing the parameters for the statement
		(optional)
	@return {Any} 
*/
function executeSharedDBStatement(statement, isQuery, paramList) {
    var result;
    try {
        var dbConn = globalMap.get("dbConn");
        if(dbConn == null || dbConn.getConnection().isClosed()) {
            dbConn = DatabaseConnectionFactory.createDatabaseConnection(configurationMap.get('v3_pg_driver'), configurationMap.get('v3_pg_url'), configurationMap.get('v3_pg_username'), configurationMap.get('v3_pg_password'));
            globalMap.put("dbConn", dbConn);
        }
        if(paramList == undefined) {
            paramList = new Packages.java.util.ArrayList();
        }
        if(isQuery) {
            result = dbConn.executeCachedQuery(statement, paramList);
        } else {
            result = dbConn.executeUpdate(statement, paramList);
        }
    } catch (e) {
        logger.error(e);
    } finally {
        return result;
    }
}

That is the original template - over time I have grown that a bit such that there are more shared dbconns in $g depending on traffic volume and other factors.

[davutg]

I haven't tested by creating test cases, but it's happening twice/thrice annually before. The channel was hanging for some reason. It requires further inspection and testing.
By assuming it's because of some record locking/open transaction or DB failure. I would generate below test cases;

1. Oracle, Locking a record with SELECT...FOR UPDATE; https://www.techonthenet.com/oracle/cursors/for_update.php
2. SQL Server, beginning a transaction on the record
   ... similar cases and while the record is locked trying to do CRUDL ops on the record via Mirth.
3. Disconnecting/Disrupting DB while Mirth DB Writer (+ use js mode) processing messages

[MichaelLeeHobbs]

https://github.com/MichaelLeeHobbs/connect-pgpool

[davutg]

Thanks for sharing ! I'll have a look into this.

[jonbartels]

@davutg - What database engine do you usually use?

[davutg]

I'm connecting to an SQL server DB, thru JDBC. The DBA is dealing with SQL Server Database Engine component if you specifically asked for the DB engine. In another solution we've Oracle as well. I've also implemented a MongoDB writer, it uses WiredTiger storage DB engine.

Once we noticed it works for one we propagated the same to others as well. As @pacmano1 stated it depends on your concerns as you also know. My concern was while getting my car washed in a station I was getting a call because of a hanged channel and I hate carrying my laptop with me everywhere, so I found this workaround :)

[pacmano1]

Ah! Wash the car less then, that should fix it up.

[tonygermano]

I have a comment about the syntax. The suggestion may not be future proof as it does not use standard javascript syntax, and the rhino project is looking to eliminate some of the non-standard legacy syntax in future versions. Also, some older mirth versions won't support the shortened ES6 way of defining a function property on the object. This would be the most compatible way to write it for old and future mirth version. Note this appears to use an interface as a constructor that takes an object as the implementation rather than mimicking the java anonymous class syntax. Under the hood, both syntax options do the same thing.

var task = new java.lang.Runnable({
    run: function run() {
        try {
            dbConn =...
            //your code goes here
        } finally {
            if (dbConn) { 
                dbConn.close();
            }
        }
    }
});