Email Failed to Parse or Stuck in State 5 or Status 5

Applies to Moxie Email Versions 9.2 and 9.4

 

There are multiple circumstances where email in not properly processed by the Mail email subsystems, primary related to CommCore services.   The following will help identify such circumstances and offer remediation. Please contact Moxie Support if you have any questions or if the emails fails to get out of that state/status even after running the workaround.

1) Inbound email fails to parse

Moxie Email analyses inbound email to identify the email content for further processing such as routing. Some emails received may not be in a format the mail system can support, usually these are not in one of the industry email standards, known as RFCs. If email is received in a format that cannot be processes, they will remain left in the Moxie main database (in the MailFailedInParse table) and cannot sent to mailboxes. 

The following script should executed periodically to monitor the database table (MailFailedInParse) where emails that fail to properly parse the format.   

Select COUNT(1) from MailsFailedInParse with (NOLOCK) WHERE

(

DAY(RecvTime) = DAY(GETDATE())

AND

MONTH(RecvTime) = MONTH(GETDATE())

AND

YEAR(RecvTime) = YEAR(GETDATE())

)

If an email is found, the email may be extracted from the database and attempt to view the content with a desktop application such as a email application or text processor.

 

 

2) Email fails to process due to processing errors

In some circumstances, email may not fully process and appear in a Moxie mailbox or leave the Outbox. The primary situations where these occur are when Workflow processing errors occur or there is a database failure such as the database going into read-only mode due to space or other issues.

The following scripts will enable an automated process to identify and enable recovery of these emails.

Step1:   Create two separate tables for inbound and outbound MSGIDs using TableScript in the Channels main database

Step2:   Create a SQL job on the Channels main database with the JobScript and schedule it for every 30 minutes

The JobScript will execute as follows:

  1. Check the records in Mailsendqueue and Mailmessage  table for email that are there for more than 30 minutes. Emails should not be in these tables for an extended period of time

  2. If there are any records, then insert MSGIDs into tables which were created in the step 1 above

  3. Update the mailsendqueue table records that are set to state =5  and mailmessage table records with status =6

     

SQL Scripts for Email fails to process due to processing errors 

TableScript

 CREATE TABLE [gomoxie_schema].[Msgid_from_Mailmessage_inbound](

 

       [MsgID] [int] NOT NULL,

       [RouteDate] [datetime] NOT NULL,

      

       CONSTRAINT [PK_Msgid_from_Mailmessage_inbound] PRIMARY KEY CLUSTERED

(

       [MsgID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

 

CREATE TABLE [gomoxie_schema].[Msgid_from_Mailsendqueue_outbound](

      

       [firstattempttime] [datetime]NOT NULL,

       [msgid] [int] NOT NULL,

      

CONSTRAINT [PK_Msgid_from_Mailsendqueue_outbound] PRIMARY KEY CLUSTERED

(

       [MsgID] ASC

)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,

FILLFACTOR = 85) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

JobScript

 

declare @inbound_count int

SELECT @inbound_count=count(1)

from mailmessage where DATEDIFF(mi,routedate,getdate())>30 and status=5

 

if @inbound_count<>0

 

begin

 

INSERT INTO Msgid_from_Mailmessage_inbound (MSGID,Routedate)

SELECT MSGID,Routedate FROM mailmessage where DATEDIFF(mi,routedate,getdate())>30 and status=5

 

Update MailMessage Set Status = 6 where DATEDIFF(mi,routedate,getdate())>30 and status=5

 

end

 

declare @outbound_count int

SELECT @outbound_count=count(1) 

from MailSendQueue where DATEDIFF(mi,firstattempttime,getdate())>30

and state =5

 

if @cnt<>0

begin

 

INSERT INTO Msgid_from_Mailsendqueue_outbound (Msgid,firstattempttime)

SELECT Msgid,firstattempttime FROM mailsendqueue where DATEDIFF(mi,firstattempttime,getdate())>30 and status=5

 

Update Mailsendqueue Set State = 0 where DATEDIFF(mi,firstattempttime,getdate())>30 and status=5

 

end