Real time messages refuses to be processed and are stuck in process mode.

[thibaultb473199]

Hello everyone,

Since this Monday, I have some issues with one particular RTM (ACCOUNT_ACTIVATION_FR) that refuses to be "processed" and stays in status "in process". Before that day, that RTM was working fine as seen on this screenshot below:

Starting on Monday at midnight, this RTM refuses to pass the "in process" status.

This is what I have right now for the deliveries that supposed to be send, checking the tracking logs, they exist and recipients are opening their emails.

Also here are the delivery logs :

Can there be something wrong with the mid-sourcing ?

Since that, I also notice an issue on Adobe itself with a pop-up window that mention that when I try to configure a list on the deliveries :

Do you have any idea why that specific RTM refuses to pass the in process mode ? This was going fine before Monday. I already restart the workflow that run the specific JS to process those RTM and also the Campaign Jobs workflow but nothing much happen.

If you need more information about this issue, feel free to ask me and I will provide you that.

Thank you for your help.

Kind regards,

Down below is the workflow using a JS that updates all the real time messages.

// Run an update of alert status every 30 seconds.

// Leave the script activity after one hour so to avoid creating too many activity and log records.

try

{

          var iRound = 0;

          while( iRound < 120 )

          {

            iRound++;

            sqlExec("truncate table wkAlertStatus");

            // Get processed alerts from broadlogs

            var iProcessedCount = sqlExec(<![CDATA[

            insert /*+ APPEND */  into wkAlertStatus

            select

            bl.iRtMsgId, max(bl.tsEvent), 2

            from

            RtmBroadLogRtm bl, RtmAlert rm

            where

            rm.iStatus = 1 and

            rm.iAlertId = bl.iRtMsgId and

            bl.iStatus in (1, 2) and

            bl.tsLastModified > SYSDATE -1

            group by

            bl.iRtMsgId

            ]]>);

            // Get ignored alerts from broadlogs

            // - if the preparation rejected them (quarantine...)

            var iIgnoredCount = sqlExec(<![CDATA[

            insert /*+ APPEND */  into wkAlertStatus

            select

            bl.iRtMsgId, max(bl.tsEvent), 3

            from

            RtmBroadLogRtm bl, RtmAlert rm

            where

            rm.iStatus = 1 and

            rm.iAlertId = bl.iRtMsgId and

            bl.iStatus = 0 and

            bl.tsLastModified > SYSDATE -1

            group by

            bl.iRtMsgId

            ]]>);

            if( iProcessedCount+iIgnoredCount > 0 )

            {

              // Update date and status in RtmAlert

              sqlExec(<![CDATA[   

              UPDATE

                RtmAlert rm

              SET

                iStatus = (select w.iStatus from wkAlertStatus w where rm.iAlertId = w.iAlertId),

                tsProcessed = (select w.tsProcessed from wkAlertStatus w where rm.iAlertId = w.iAlertId)

              WHERE

                iAlertId in (select iAlertId from wkAlertStatus)

                ]]>);

              if( iProcessedCount > 0 )

              {

                if( iIgnoredCount > 0 )

                logInfo("Flag " + iProcessedCount + " alert(s) as processed and "+iIgnoredCount+" as ignored.");

                else

                logInfo("Flag " + iProcessedCount + " alert(s) as processed.");

              }

              else if( iIgnoredCount > 0 )

                logInfo("Flag " + iIgnoredCount + " alert(s) as ignored.");

            }

      }

      //  Wait 30 seconds

      return pause(30000)

}

      catch (e)

      {

      sendAlert('RTMon4');

      /*

        var iNotifId = nms.delivery.SubmitNotification

            ('RTMon4',

              <delivery>

                <targets _keepScenarioTarget='true'/>

              </delivery>

            );

        //  Wait 1 minute

        return pause (60000)   */

      }

function pause(duration)

{

  var now = getCurrentDate();

  //logInfo(now);

  var tmNextStart= now.setTime(now.getTime() + duration);

  //var tmNextStart= now.setSeconds(now.getSeconds() + duration);

  task.setNextProcessingDate(new Date(tmNextStart))

  return 0;

}

function sendAlert(errModel)

{

  try

  {

    var iNotifId = nms.delivery.SubmitNotification

        (errModel,

          <delivery>

            <targets _keepScenarioTarget='true'/>

          </delivery>

        );

    //  Wait 1 minute

    return pause(60000);

  }

  catch(e)

  {

    return pause(60000);

  }

  //task.postEvent(task.transitionByName("error"))

}

return 0

Down below is the workflow using a JS that process all the real time messages:

//----------------------------------------------------------------------------

  // alert.js

  // (c) Neolane 2010

  //----------------------------------------------------------------------------

  //changes needed for compatibility with version 6.1 are indicated with //6.1:

  try

  {

    // Do not format XML when serialized to save space

    XML.prettyPrinting = false;

    //----------------------------------------------------------------------------

    // Helper function to capture timing information

    //----------------------------------------------------------------------------

    function logDate(strMsg)

    {

    var d = new Date();

    logInfo(d.getHours() + ":" + d.getMinutes() + ":" + d.getSeconds() +

        ":" + d.getMilliseconds() + "  "+strMsg);

    }

    //----------------------------------------------------------------------------

    // Espace a text value to be usable as an SQL litteral

    //----------------------------------------------------------------------------

    function escapeSql(strSql)

    {

    return "'"+strSql.replace("'", "''")+"'";

    }

    //----------------------------------------------------------------------------

    // Create alertProcessor object and the needed variables

    //----------------------------------------------------------------------------

    function alertProcessor()

    {

    // Cache for alert parameters

    this.vecAlertParams = [];

    // Processing statistics

    this.m_dtNextLog = new Date();

    this.m_strTemplates = "";

    this.m_iAlertCount = 0;

    this.m_iBlackListCount = 0;

    }

    //----------------------------------------------------------------------------

    // Function to dump alert processing statistics

    // - one log every minute, for all alert types

  //----------------------------------------------------------------------------

  //6.1: NOT tested in 6.1

    alertProcessor.prototype.logStatistics = function()

    {

    var dtNow = new Date;

    if( dtNow > this.m_dtNextLog )

    {

      var strMsg = "";

      if( this.m_iAlertCount > 0 )

      strMsg = this.m_iAlertCount+" alert(s) processed";

      if( this.m_iBlackListCount > 0 )

      {

      if( strMsg != '' )

        strMsg += ', ';

      strMsg += this.m_iBlackListCount + " alert(s) ignored";

      }  

      if( strMsg != '' )

      {

      strMsg += " for template(s) "+this.m_strTemplates;

      logInfo(strMsg);

      // Reset statistics

      this.m_strTemplates = "";

      this.m_iAlertCount = 0;

      this.m_iBlackListCount = 0;

      }

      // Next expiration in one minute

      this.m_dtNextLog.setTime(dtNow.getTime()+1000*60);

    }

    }

    //----------------------------------------------------------------------------

    // Find parameters for a template

    //----------------------------------------------------------------------------

    alertProcessor.prototype.findAlertParams = function(sTemplate)

    {

    var strKey = sTemplate;

  //6.1: using vecAlertParams[0] instead of [strKey]

    this.vecAlertParams[0] = strKey;

    var alertParams = this.vecAlertParams[0];

    //6.1: IF next 3 lines not commented workflow does not work!

    /*if( alertParams != null )

      //## check last time it was checked and reload if too old

      logInfo("alarmParams is not null, return alertParams");

      return alertParams;*/

    // Load parameters for this template

    var queryDef = xtk.queryDef.create(

    <queryDef schema="rtm:param" operation="getIfExists">

      <select>

      <node expr="[scenario/@internalName]"/>

      </select>

      <where>

      <condition expr={ "@name='" + sTemplate + "'"}/>

      </where>

    </queryDef>

    );

    var alertParamRes = queryDef.ExecuteQuery(); 

    if( alertParamRes.scenario.@internalName.toString() == '' )

    { // Alert param does not exist

    return null;

    }

    //Create new alertParams and put the scenario name in this variable

    var alertParams = new Object;

    alertParams.scenarioName = alertParamRes.scenario.@internalName.toString();

  //6.1: again [0] instead of [strKey]

    this.vecAlertParams[0] = alertParams;

    return alertParams;

    }

    //----------------------------------------------------------------------------

    // Process a batch of eligible alerts for a given template

    //----------------------------------------------------------------------------

    alertProcessor.prototype.processTemplateAlerts = function(sTemplate)

    {

    //call function with template name

    var alertParams = this.findAlertParams(sTemplate);

    if( alertParams == null )

    {

    //_________IGNORE______________________________

    // Template not known => set alerts as ignored   

    var iCount = sqlExec("UPDATE RtmAlert SET iStatus=3, tsProcessing=SYSDATE, tsProcessed=SYSDATE "+

           "WHERE iStatus=0 AND tsRequiredProcessing<SYSDATE AND "+

           "sTemplate='" + sTemplate +"'");

       // vérifier ligne ci dessous

    logWarning("Template name "+sTemplate+" not defined. "+iCount + " pending alert(s) for this template have been flagged as ignored.");

    sendAlert('RTMon3');

    return;

    }

    else{

    //## temporary for test

    logInfo("Processing alerts with template = " + sTemplate);

    }

    // Get a batch of the oldest eligible alerts for this template

  //6.1: query is rewrited. boolOperator="AND" is used for clear and easy understandable code

    qryDef = xtk.queryDef.create

    (

      <queryDef schema="rtm:alert" operation="select" lineCount="100">

      <select>

      <node expr="@id"/>

      <node expr="@address"/>

      <node expr="ctx"/>

      </select>

      <where>

      <condition boolOperator="AND" expr={ "@template='" + sTemplate +"'"}/>

      //PCA 2017-04-05 <condition boolOperator="AND" expr="@created &gt;= DaysAgo(7)"/>

      <condition boolOperator="AND" expr="@created &gt;= SubDays(ToDate( GetDate()) , 7)"/>

      <condition boolOperator="AND" expr="@status = 0"/>

      <condition expr="@requiredProcessing &lt;= GetDate()"/>

      </where>

      </queryDef>

    );

    // ## See which alert requests need to be enriched from a call to the cache database

    var iBlackListCount = 0; 

    var strBlacklistList = "";

    var strAlertIdList = "";

    var iAlertCount = 0;

    var alertList = qryDef.ExecuteQuery();

    for each (var alert in alertList.alert)

    {

    var ctx = alert.ctx;

    if( ctx.child("*").length() > 0 )

    {//------ the context is valid => the alert may be processed

      // this section determines whether each alert has to be

      // blacklisted or processed, depending on the given context

      var strSql = "UPDATE RtmAlert SET ";

      var bBlackList = ctx.@blacklist.toString() == "1";

      if( bBlackList )

      { // Alert is blacklisted by context

      if( strBlacklistList != '' )

        strBlacklistList += ',';

      strBlacklistList += alert.@id;

      alert.@ignore = "1"; // Flags alert as ignored to avoid adding its 'id' in strAlertIdList

      strSql += "iBlacklist=1";

      }

      else

      { // Alert is NOT blacklisted by context

      strSql += "iBlacklist=0";

      }

      strSql += " WHERE iAlertId="+alert.@id;

      sqlExec(strSql);

    }

    else

    {//------ the context is NOT valid => the alert won't be processed

      logInfo("alert ignored");

      //_________IGNORE_________________________

      // empty context => alert set as "ignored"

      sqlExec("UPDATE RtmAlert SET iStatus=3, tsProcessing=SYSDATE, tsProcessed=SYSDATE WHERE iAlertId = $(l)",alert.@id);

      alert.@ignore = "1"; // Flags alert as ignored to avoid adding its 'id' in strAlertIdList

    }  

    // Will process this alert

    if( alert.@ignore.toString() != "1" ) // Using flag @ignore

    {

      if( strAlertIdList != '' )

      strAlertIdList += ',';

      strAlertIdList += alert.@id;

      iAlertCount++;

    } 

    }

    if( strBlacklistList != '' )

    {

    // ?? There are some alerts to postpone ??

    //_________IGNORE_____________________________

    // alert blacklisted => alert set as "ignored"   

    iBlackListCount = sqlExec('UPDATE RtmAlert SET iStatus=3, tsProcessing=SYSDATE, tsProcessed=SYSDATE WHERE iAlertId IN ('+strBlacklistList+')');

    }

    try  

    {

        if( strAlertIdList != '' )

        {

        // Now perform the submit notification

        var iNotifId = nms.delivery.SubmitNotification

        (alertParams.scenarioName,

          <delivery>

          <targets>

          <deliveryTarget>

          <targetPart id="1">

            <where id="1">

            <condition internalId="1" expr={"@id IN (" + strAlertIdList + ")"}/>

            </where>

          </targetPart>

          </deliveryTarget>

          </targets>

          </delivery>

        );

        //-------------------- IN PROCESS ! PROCESSING DATE -----------------

        // Update requests as in process

        sqlExec("UPDATE RtmAlert SET iStatus = 1, tsProcessing = SYSDATE, iDeliveryId=" +

          iNotifId + " WHERE iAlertId IN (" + strAlertIdList + ")");

        }         

    }

    catch(e)

    {

    //_________IGNORE_________________________________________________

    // nms.delivery.SubmitNotification FAILED => alerts set as ignored

      sqlExec('UPDATE RtmAlert SET iStatus=3, tsProcessing=SYSDATE, tsProcessed=SYSDATE WHERE iAlertId IN ('+strAlertIdList+')');

      logError("An error occured when trying to execute delivery with Scenario: " + alertParams.scenarioName + ". All alerts belonging to this scenario are flagged as ignored.");

      sendAlert('RTMon1');  

      //task.postEvent(task.transitionByName("error"))

    }

    /* Use fot diagnosis, commented since too many logs in production (>15K/day)

      if( iBlackListCount > 0 )

      logInfo(iAlertCount+" alert(s) processed and "+iBlackListCount+" ignored due to blacklist for template "+sTemplate);

      else

      logInfo(iAlertCount+" alert(s) processed for template "+sTemplate);

    }

    else if ( iBlackListCount > 0 )

      logInfo(iBlackListCount + " alert(s) ignored due to blacklist for template "+sTemplate);

    */

    // Accumulate statistics

    var strKey = sTemplate;

    if( this.m_strTemplates.indexOf(strKey) < 0 )

    {

      if( this.m_strTemplates != "" )

      this.m_strTemplates += ', ';

      this.m_strTemplates += strKey;

    }

    this.m_iAlertCount += iAlertCount;

    this.m_iBlackListCount += iBlackListCount;

    //logInfo("____/!\____iBlackListCount : "+iBlackListCount);

    }

    //----------------------------------------------------------------------------

    // Process a batch of new eligible alerts

    //----------------------------------------------------------------------------

    alertProcessor.prototype.processAlerts = function()

    {

    // Find oldest alert request to process

    // - ignore requests too old (more than 4 days old)

   //6.1: query is rewrited. boolOperator="AND" is used for clear and easy understandable code

   var qryDef = xtk.queryDef.create

    (<queryDef schema="rtm:alert" operation="getIfExists">

      <select>

        <node expr="@template"/>

        <node expr="@requiredProcessing"/>

        <node expr="@id"/>

      </select>

    <where>

      <condition boolOperator="AND" expr="@requiredProcessing <= GetDate()"/>

      //PCA 2017-04-05 <condition expr="@created &gt;= DaysAgo(4)"/>

      <condition expr="@created &gt;= SubDays(ToDate( GetDate()) , 4)"/>

      <condition expr="@status=0"/>

      </where>

      <orderBy>

        <node expr="@requiredProcessing"/>

      </orderBy>

      </queryDef>

    );

  var eAlert = qryDef.ExecuteQuery();   

    if( eAlert.@template.toString() == '' )

      // nothing to process

      return false;

    // Process a batch of alerts for this template

    var sTemplate = eAlert.@template.toString();

    this.processTemplateAlerts(sTemplate);

    return true;

    }

    var ap = new alertProcessor();

    ap.processAlerts();

    // Loop at most 120 times (one hour if nothing happens)

    // so that the activity stops form time to time

    var iRound = 0;

    while( iRound < 120 )

    {

    iRound++;

    var bHasAlert = ap.processAlerts();

    //6.1: TODO testing logStatistics

    ap.logStatistics();

    if( !bHasAlert )

      return pause(8000);

    if (iRound == 60)

      return -67;

    }

    //  Wait 6 seconds

    return pause(8000);

  }

  catch(e)

  {

   //commented for testing purposes

   sendAlert('RTMon2')

   //logInfo("CATCHED, error in workflow");

  }

  function pause(duration)

  {

    //  Wait X seconds

    var now = getCurrentDate();

    //logInfo(now);

    var tmNextStart= now.setTime(now.getTime() + duration);

    //var tmNextStart= now.setSeconds(now.getSeconds() + duration);

    task.setNextProcessingDate(new Date(tmNextStart))

    return 0

  }

  function sendAlert(errModel)

  {

    try

    {

    var iNotifId = nms.delivery.SubmitNotification

      (errModel,

        <delivery>

        <targets _keepScenarioTarget='true'/>

        </delivery>

      );

    //  Wait 1 minute

    return pause(60000);

    }

    catch(e)

    {

    return pause(60000);

    }

    //task.postEvent(task.transitionByName("error"))

  }

  return 0