search

rbeckman-nextgen / test-mc

test-migration
1 stars 0 forks source link

Setting to allow message pruning to prune ERRORED messages #3069

Open rbeckman-nextgen opened 5 years ago

rbeckman-nextgen commented 5 years ago

Introduced in Mirth 3.0, MIRTH-2235 changes the message pruner behavior so that it no longer prunes ERRORED messages.

We would like to have the ability to toggle, per channel, whether or not pruning will delete ERRORED messages instead of leaving them in the database.

Imported Issue. Original Details: Reporter: daigoba66 Created: 2014-01-16T04:34:02.000-0800

rbeckman-nextgen commented 5 years ago

[http://www.mirthcorp.com/community/forums/showthread.php?t=14220]

Imported Comment. Original Details: Author: narupley Created: 2015-05-11T08:10:59.000-0700

rbeckman-nextgen commented 5 years ago

This is a serious issue for my shop as we have 8 boxes with perhaps 1000 channels and I really don't want to have to manually prune the old error messages. Has anyone come up with a workaround? Perhaps a SQL query that can get to the DB directly?

Imported Comment. Original Details: Author: jtheune Created: 2017-09-01T12:49:06.000-0700

rbeckman-nextgen commented 5 years ago

We created a channel that goes through the MirthDB and sets the status from ERROR to SENT after a configurable interval; Then the regular Mirth Pruner picks them up to be deleted.

Imported Comment. Original Details: Author: bradcrawford Created: 2017-10-31T07:02:43.000-0700

rbeckman-nextgen commented 5 years ago

Can you post that channel?

Imported Comment. Original Details: Author: jtheune Created: 2017-10-31T07:14:54.000-0700

rbeckman-nextgen commented 5 years ago

I can't post the entire channel because we have other proprietary items present but below is the important part. Update the DB connection info and set the 'PurgeDays' Put this into a Javascript reader and schedule it to run nightly.

var dbConn = DatabaseConnectionFactory.createDatabaseConnection(dbDriver, dbDatabase, dbUser, dbPassword); var Query = "SELECT LOCAL_CHANNEL_ID FROM D_CHANNELS"; var result = dbConn.executeCachedQuery(Query);

while (result.next()) { var channelID = result.getString("LOCAL_CHANNEL_ID").trim(); var Query3 = "SELECT MESSAGE_ID FROM D_MM" + channelID + " WHERE STATUS = 'E' AND DATEDIFF(d, RECEIVED_DATE, GETDATE()) > " + purgeDays; var result2 = dbConn.executeCachedQuery(Query3); var i = result2.size();

//LOOP UNTIL RECORD COUNT FOR TABLE IS ZERO
while ( i > 0 )
{
        //Update error messages in batches
        var Query2 = "UPDATE TOP (1000) D_MM" + channelID +  " SET STATUS = 'S' WHERE STATUS = 'E' AND DATEDIFF(d, RECEIVED_DATE, GETDATE()) > " + purgeDays;

        dbConn.executeUpdate(Query2);

        java.lang.Thread.sleep(5000);

        var result3 = dbConn.executeCachedQuery(Query3);
        i =  result3.size();

        result3.close();
}
result2.close();

}

result.close(); dbConn.close();

Imported Comment. Original Details: Author: bradcrawford Created: 2017-10-31T10:06:27.000-0700

rbeckman-nextgen commented 5 years ago

I would recommend the priority on this issue be raised to high. Anyone with multiple Mirth instances or many channels would find management very difficult without this being fixed. Thank You

Imported Comment. Original Details: Author: bradcrawford Created: 2017-10-31T10:10:45.000-0700