Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Exception Alert for Social.Messaging.MessageRecipient #3624

Closed
1 of 10 tasks
james7342 opened this issue Mar 12, 2020 · 27 comments · Fixed by #4777
Closed
1 of 10 tasks

Exception Alert for Social.Messaging.MessageRecipient #3624

james7342 opened this issue Mar 12, 2020 · 27 comments · Fixed by #4777

Comments

@james7342
Copy link

Description of bug

Since upgrade to DNN 9.5.0 I've noticed a new error in the admin log.

Steps to reproduce

Not clear at the moment. I think it's when a user archives messages in Message Center.

Screenshots

screenshot_1189
screenshot_1188

Error information

From Log File:
2020-03-11 00:29:32,742 [generic262][Thread:57][ERROR] DotNetNuke.Services.Exceptions.Exceptions - System.IndexOutOfRangeException: Archived
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at DotNetNuke.Services.Social.Messaging.MessageRecipient.Fill(IDataReader dr)
at DotNetNuke.Common.Utilities.CBO.FillObjectFromReader(Object objObject, IDataReader dr)

2020-03-11 10:15:49,658 [generic262][Thread:12][ERROR] DotNetNuke.Services.Exceptions.Exceptions - System.IndexOutOfRangeException: Archived
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at DotNetNuke.Services.Social.Messaging.MessageRecipient.Fill(IDataReader dr)
at DotNetNuke.Common.Utilities.CBO.FillObjectFromReader(Object objObject, IDataReader dr)

Affected version

9.5.0 is all I have to test with at the moment.

  • 10.0.0 alpha build
  • 9.5.1 alpha build
  • 9.5.0 latest supported release
  • 9.4.4

Affected browser

N/A

  • Chrome
  • Firefox
  • Safari
  • Internet Explorer 11
  • Microsoft Edge (Classic)
  • Microsoft Edge Chromium
@thabaum
Copy link
Contributor

thabaum commented Mar 14, 2020

What version did you upgrade from?

@james7342
Copy link
Author

9.4.4

@stale
Copy link

stale bot commented Jun 12, 2020

We have detected this issue has not had any activity during the last 90 days. That could mean this issue is no longer relevant and/or nobody has found the necessary time to address the issue. We are trying to keep the list of open issues limited to those issues that are relevant to the majority and to close the ones that have become 'stale' (inactive). If no further activity is detected within the next 14 days, the issue will be closed automatically.
If new comments are are posted and/or a solution (pull request) is submitted for review that references this issue, the issue will not be closed. Closed issues can be reopened at any time in the future. Please remember those participating in this open source project are volunteers trying to help others and creating a better DNN Platform for all. Thank you for your continued involvement and contributions!

@stale stale bot added the stale label Jun 12, 2020
@stale
Copy link

stale bot commented Jun 26, 2020

This issue has been closed automatically due to inactivity (as mentioned 14 days ago). Feel free to re-open the issue if you believe it is still relevant.

@stale stale bot closed this as completed Jun 26, 2020
@roman-yagodin
Copy link
Contributor

Same here after upgrading from DNN 8.0.4 to 9.8.1. Please reopen!

@bdukes bdukes reopened this Feb 17, 2021
@stale stale bot removed the stale label Feb 17, 2021
@roman-yagodin
Copy link
Contributor

The error messages stopped to appear in the logs after moving some (or all) message recipients into archive:

update dbo.CoreMessaging_MessageRecipients set Archived = 1 where Archived = 0

Still have no idea what would be causing this.

@james7342
Copy link
Author

james7342 commented Feb 18, 2021

Wonder if this is due to messages not archived possibly from users that may have been deleted. IE: Recipient received a message from sender. Later the sender or recipient is deleted. The non delete sender or recipient later archives the message.

On a site the mimics my production I ran a "Select * from dbo.CoreMessaging_MessageRecipients". The list contains UserID's that when looking at the limited number of users on this site don't match except for a 1 or 2.

UserIDs.pdf is from running "select UserID from users"

Query 1.pdf
UserIDs.pdf

I tried creating this on a local test/dev site (DNNdev.me) running 9.8.1 with symbols and was unable to duplicate it. Of course kind of hard to recreate if the condition is not known.

On my production site I only seen it once in a while. Messaging is not widely used on it.

If the above is of any significance I can run the same on my production site to see if the same correlation between messages and userIDs.

@roman-yagodin
Copy link
Contributor

roman-yagodin commented Feb 18, 2021

I've checked dbo.CoreMessaging_MessageRecipients for non-existent users using this query:

select count(*) from dbo.CoreMessaging_MessageRecipients where UserID not in (select UserID from dbo.Users)

But there are none in my case.

@james7342
Copy link
Author

james7342 commented Feb 18, 2021

Well, That might rule that theory out. Just for info reasons this is what my environments look like.

FYI: I think there is an error in your SQL "is not in"
select count(*) from dbo.CoreMessaging_MessageRecipients where UserID not in (select UserID from dbo.Users)

Test site the Mimics production: 13
Production: 542

select count(*) from dbo.CoreMessaging_MessageRecipients where CreatedByUserID not in (select UserID from dbo.Users)

Test site the Mimics production: 13
Production: 499

Looking at my production site with users that have been deleted over the years. Not sure of the full impact of this, but maybe this is something that should be looked into. Does this pose any issues with the old orphaned data points? Seems kind of concerning that there doesn't appear to be any cleanup or purging of data. I would think if user is deleted the archived or stored messages references should be purged.

Considering I only have 1004 messages in the list. Oldest one being from 2012.
select count(*) from dbo.CoreMessaging_MessageRecipients

@roman-yagodin
Copy link
Contributor

roman-yagodin commented Feb 19, 2021

FYI: I think there is an error in your SQL "is not in"

You are right, error corrected.

@markmcavoy
Copy link
Contributor

I'm having the same issue coming from an original install of 6.0.0 and moving to 9.8.1

I applied Roman's fix to stop the logs getting flooded with these message

update dbo.CoreMessaging_MessageRecipients set Archived = 1 where Archived = 0

@james7342
Copy link
Author

james7342 commented Feb 21, 2021

A user was messaging me earlier today. Went in a looked at the logs and I don’t think this has anything to do with actual archiving of messages. The reason being I have these message now in the log that seem to correlate to myself and this user. It seems like it might be happening during creating of the messaging interface/view. The reason for this thought is looking in the logs and can see my login and then shortly after the error event. Which would correlate to logging and immediately click the message notification in the menu bar.

2021-02-21 10:27:26,135 [generic262][Thread:52][ERROR] DotNetNuke.Services.Exceptions.Exceptions - System.IndexOutOfRangeException: Archived
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at DotNetNuke.Services.Social.Messaging.MessageRecipient.Fill(IDataReader dr)
at DotNetNuke.Common.Utilities.CBO.FillObjectFromReader(Object objObject, IDataReader dr)
2021-02-21 10:56:50,444 [generic262][Thread:97][ERROR] DotNetNuke.Services.Exceptions.Exceptions - System.IndexOutOfRangeException: Archived
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at DotNetNuke.Services.Social.Messaging.MessageRecipient.Fill(IDataReader dr)
at DotNetNuke.Common.Utilities.CBO.FillObjectFromReader(Object objObject, IDataReader dr)
2021-02-21 11:34:41,817 [generic262][Thread:11][ERROR] DotNetNuke.Services.Exceptions.Exceptions - System.IndexOutOfRangeException: Archived
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at DotNetNuke.Services.Social.Messaging.MessageRecipient.Fill(IDataReader dr)
at DotNetNuke.Common.Utilities.CBO.FillObjectFromReader(Object objObject, IDataReader dr)
2021-02-21 11:40:59,865 [generic262][Thread:65][ERROR] DotNetNuke.Services.Exceptions.Exceptions - System.IndexOutOfRangeException: Archived
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at DotNetNuke.Services.Social.Messaging.MessageRecipient.Fill(IDataReader dr)
at DotNetNuke.Common.Utilities.CBO.FillObjectFromReader(Object objObject, IDataReader dr)

@bdukes
Copy link
Contributor

bdukes commented Feb 22, 2021

@sleupold it looks like this issue may have been introduced in #3540. That script edited CoreMessaging_GetNextMessagesForDigestDispatch and CoreMessaging_GetNextMessagesForInstantDispatch to use an explicit column list instead of SELECT *.

Previously:

SELECT *
FROM {databaseOwner}[{objectQualifier}CoreMessaging_MessageRecipients] CMR
INNER JOIN {databaseOwner}[{objectQualifier}CoreMessaging_Messages] CMM ON CMR.MessageID = CMM.MessageID
WHERE EmailSent = 0
AND EmailSentDate IS NULL
AND [Read] = 0
AND Archived = 0
AND ( EmailSchedulerInstance = @SchedulerInstance )
ORDER BY UserID ,
CMM.CreatedOnDate DESC

As of 9.5.0:

SELECT M.[PortalID],
M.[NotificationTypeID],
M.[To],
M.[From],
M.[Subject],
M.[Body],
M.[SenderUserID],
M.[ExpirationDate],
M.[Context],
R.[RecipientID],
R.[MessageID],
R.[UserID]
FROM {databaseOwner}[{objectQualifier}CoreMessaging_MessageRecipients] R
JOIN {databaseOwner}[{objectQualifier}CoreMessaging_Messages] M ON R.MessageID = M.MessageID
WHERE [EmailSent] = 0 -- Filter these columms 4 to use proper index
AND [Read] = 0
AND [Archived] = 0
AND [EmailSchedulerInstance] = @SchedulerInstance
ORDER BY --[PortalID],
[UserID],
[RecipientID]

However, that SQL is only called by code which materializes that data into MessageRecipient instances:

/// <inheritdoc/>
public IList<MessageRecipient> GetNextMessagesForInstantDispatch(Guid schedulerInstance, int batchSize)
{
return CBO.FillCollection<MessageRecipient>(this._dataService.GetNextMessagesForInstantDispatch(schedulerInstance, batchSize));
}

MessageRecipient requires the Archived and Read columns, as well as the audit columns CreatedByUserID, CreatedOnDate, LastModifiedByUserID, and LastModifiedOnDate, which aren't supplied by the above stored procedure:

public void Fill(IDataReader dr)
{
this.RecipientID = Convert.ToInt32(dr["RecipientID"]);
this.MessageID = Convert.ToInt32(dr["MessageID"]);
this.UserID = Convert.ToInt32(dr["UserID"]);
this.Archived = Null.SetNullBoolean(dr["Archived"]);
this.Read = Null.SetNullBoolean(dr["Read"]);
// add audit column data
this.FillInternal(dr);
}

@james7342
Copy link
Author

Just a little bump on this so Stale Bot doesn't close it out and it dies on the vine.

@prjrvp
Copy link
Contributor

prjrvp commented Apr 19, 2021

I added four missing columns to the procedure and we will see if it fixes the errors.

@james7342
Copy link
Author

@prjrvp Did your test work?

@prjrvp
Copy link
Contributor

prjrvp commented May 1, 2021

@prjrvp Did your test work?

I had to add every single remaining field R.(fieldname) before it worked. I will note that while this is failing messages are NOT going out.

@james7342
Copy link
Author

Weird as have not noticed messages not working. I have received messages and responded to messages without issue. Just each one creates a log entry.

@prjrvp
Copy link
Contributor

prjrvp commented May 1, 2021

My system sends out heavy messages (about 35 a day) which dropped to a trickle of 1 to 2 a day. After I finally fixed it, the number went back up to 35 a day. In the meantime, I learned that users who were to receive these message had received nothing from us.

If the issue was failing and restarting from where it left off, the messages would eventually have been sent. This did not happen. It moved forward in the queue without actually sending the message to the users. Now, if your users do not use the email and simply look at their message queue on the website, they will not miss messages. They are on the website, just not being sent.

I can not tell you why some messages went out while others did not.

@james7342
Copy link
Author

Our site is minimal messages but I was getting the emails also when I was testing and outside of testing. Weird we're having different results. Maybe it has a message volume component also. Would be nice to get this defect resolved though.

@prjrvp
Copy link
Contributor

prjrvp commented May 1, 2021

Agreed. It is an easy fix on two procedures that you can do live in the production environment using SSMS.

@stale
Copy link

stale bot commented Jul 30, 2021

We have detected this issue has not had any activity during the last 90 days. That could mean this issue is no longer relevant and/or nobody has found the necessary time to address the issue. We are trying to keep the list of open issues limited to those issues that are relevant to the majority and to close the ones that have become 'stale' (inactive). If no further activity is detected within the next 14 days, the issue will be closed automatically.
If new comments are are posted and/or a solution (pull request) is submitted for review that references this issue, the issue will not be closed. Closed issues can be reopened at any time in the future. Please remember those participating in this open source project are volunteers trying to help others and creating a better DNN Platform for all. Thank you for your continued involvement and contributions!

@stale stale bot added the stale label Jul 30, 2021
@james7342
Copy link
Author

Seems like the fix is known for this. I'm not a developer or I would do a PR to resolve. Would be nice if someone could take a look at this and provide a fix.

@stale stale bot removed the stale label Jul 30, 2021
@bdukes
Copy link
Contributor

bdukes commented Jul 30, 2021

@prjrvp can you share the changes you did to resolve this?

@prjrvp
Copy link
Contributor

prjrvp commented Jul 31, 2021

Yes, these are the changes required to fix this issue once and for all:
Change Stored Procedure CoreMessaging_GetNextMessagesForDigestDispatch to:

/****** Object:  StoredProcedure [dbo].[CoreMessaging_GetNextMessagesForDigestDispatch]    Script Date: 7/31/2021 6:40:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CoreMessaging_GetNextMessagesForDigestDispatch]
    @Frequency         INT,
    @SchedulerInstance UNIQUEIDENTIFIER,
    @BatchSize         INT
AS 
BEGIN
    UPDATE R
     SET   [EmailSchedulerInstance] = @SchedulerInstance,
           [LastModifiedOnDate]     = GetDate()
     FROM  dbo.[CoreMessaging_MessageRecipients] R
     JOIN  (SELECT TOP (@BatchSize)
                   UserID
             FROM  dbo.[vw_MessagesForDispatch] 
             WHERE [EmailSchedulerInstance] IS NULL
             AND   [EmailFrequency] = @Frequency
             GROUP BY UserID
             ORDER BY UserID) D ON R.UserID = D.UserID 

    SELECT M.[PortalID],
           M.[NotificationTypeID],
           M.[To],
           M.[From],
           M.[Subject],
           M.[Body],
           M.[SenderUserID],
           M.[ExpirationDate],
           M.[Context],
           R.[RecipientID],
           R.[MessageID],
           R.[UserID],
           R.[EmailSent],
           R.[Read],
           R.[Archived],
           R.[EmailSchedulerInstance],
		   R.[CreatedByUserID],
		   R.[CreatedOnDate],
		   R.[LastModifiedByUserID],
		   R.[LastModifiedOnDate],
		   R.[SendToast]
     FROM  dbo.[CoreMessaging_MessageRecipients] R
     JOIN  dbo.[CoreMessaging_Messages]          M ON R.MessageID = M.MessageID
     WHERE [EmailSent] = 0 -- Filter these 4 columms to use proper index
       AND [Read]      = 0
       AND [Archived]  = 0
       AND [EmailSchedulerInstance] = @SchedulerInstance
     ORDER BY --[PortalID],
              [UserID],
              [RecipientID] DESC
END; -- Procedure

Change Stored procedure CoreMessaging_GetNextMessagesForInstantDispatch to:

/****** Object:  StoredProcedure [dbo].[CoreMessaging_GetNextMessagesForInstantDispatch]    Script Date: 7/31/2021 6:44:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CoreMessaging_GetNextMessagesForInstantDispatch]
    @SchedulerInstance UNIQUEIDENTIFIER,
    @BatchSize         INT
AS 
BEGIN
    -- reset possibly remaining records from any previous run of this SchedulerInstance:
    UPDATE dbo.[CoreMessaging_MessageRecipients]
       SET [EmailSchedulerInstance] = Null,
           [LastModifiedOnDate]     = GetDate()
     WHERE [EmailSchedulerInstance] = @SchedulerInstance
       AND [EmailSent] = 0 AND [Read] = 0 AND [Archived] = 0;
       
    -- reset possibly remaining outdated records from other instances:
    UPDATE dbo.[CoreMessaging_MessageRecipients]
     SET   [EmailSchedulerInstance] = Null
     WHERE [EmailSent] = 0 AND [Read] = 0 AND [Archived] = 0
       AND [EmailSchedulerInstance] Is Not Null AND [LastModifiedOnDate] < DateAdd(hh, -2, GetDate());
       

    -- mark messages for dispatch, so they won't be handled by another SchedulerInstance:
    UPDATE TOP (@BatchSize) R
     SET   [EmailSchedulerInstance] = @SchedulerInstance,
           [LastModifiedOnDate]     = GetDate()
     FROM       dbo.[CoreMessaging_MessageRecipients] AS R 
     INNER JOIN dbo.[CoreMessaging_Messages]          AS M ON R.MessageID = M.MessageID
     LEFT  JOIN dbo.[CoreMessaging_UserPreferences]   AS P ON R.UserID    = P.UserID    AND M.PortalID = P.PortalID
     WHERE R.[EmailSent] = 0 AND R.[Read] = 0 AND R.[Archived] = 0 AND EmailSchedulerInstance IS NULL
       AND CASE 
            WHEN M.NotificationTypeID IS Null 
            THEN IsNull(P.[MessagesEmailFrequency],      0) -- direct mails are sent immediately by default
            ELSE IsNull(p.[NotificationsEmailFrequency], 2) -- notifications are sent as daily digest by default
           END = 0;

    SELECT M.[PortalID],
           M.[NotificationTypeID],
           M.[To],
           M.[From],
           M.[Subject],
           M.[Body],
           M.[SenderUserID],
           M.[ExpirationDate],
           M.[Context],
           R.[RecipientID],
           R.[MessageID],
           R.[EmailSent],
           R.[Read],
           R.[Archived],
           R.[EmailSchedulerInstance],
           R.[UserID],
		   R.[CreatedByUserID],
		   R.[CreatedOnDate],
		   R.[LastModifiedByUserID],
		   R.[LastModifiedOnDate],
		   R.[SendToast]
     FROM  dbo.[CoreMessaging_MessageRecipients] R
     JOIN  dbo.[CoreMessaging_Messages]          M ON R.MessageID = M.MessageID
     WHERE [EmailSent] = 0 -- Filter these columms 4 to use proper index
       AND [Read]      = 0
       AND [Archived]  = 0
       AND [EmailSchedulerInstance] = @SchedulerInstance
     ORDER BY --[PortalID],
              [UserID],
              [RecipientID]
END; -- Procedure

bdukes added a commit to bdukes/Dnn.Platform that referenced this issue Aug 9, 2021
Fixes dnnsoftware#3624

Co-authored-by: @prjrvp <9609997+prjrvp@users.noreply.github.com>
@Saeedalhs
Copy link

Saeedalhs commented Jan 15, 2022

In addition to modifying the two procedures mentioned above, and updating table CoreMessaging_MessageRecipients setting Archived = 1, for an ordinary user it's OK; but entering the site with host or admin user, it logs again the error. It seems this is caused by other type of notifications like from Feedback and other modules.

AbsoluteURL:/Default.aspx
DefaultDataProvider:DotNetNuke.Data.SqlDataProvider, DotNetNuke
ExceptionGUID:5a0cd914-9f5a-4js0-ae2b-4376e84fc43f
AssemblyVersion:
PortalId:-1
UserId:-1
TabId:-1
RawUrl:
Referrer:
UserAgent:
ExceptionHash:Nk4I1Bqc86r3ndSp4HxRGtk2XjB34M=
Message:IsSystem
StackTrace:
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at DotNetNuke.Entities.Tabs.TabInfo.Fill(IDataReader dr)
at DotNetNuke.Common.Utilities.CBO.FillObjectFromReader(Object objObject, IDataReader dr)
InnerMessage:
InnerStackTrace:
Source:System.Data
FileName:
FileLineNumber:0
FileColumnNumber:0
Method:

Also from other sources:

  • AbsoluteURL:/API/PersonaBar/Pages/SavePageDetails when updating a page settings
  • AbsoluteURL:/API/PersonaBar/Extensions/GetInstalledPackages when installing or upgrading a pckage

@Saeedalhs
Copy link

Yes, these are the changes required to fix this issue once and for all: Change Stored Procedure CoreMessaging_GetNextMessagesForDigestDispatch to:

/****** Object:  StoredProcedure [dbo].[CoreMessaging_GetNextMessagesForDigestDispatch]    Script Date: 7/31/2021 6:40:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CoreMessaging_GetNextMessagesForDigestDispatch]

What is the purpose of SELECT query at the end of these procedures. How it's result will be used? Although modified the two procedures, the DNN log continuously records the same error message of this issue.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

7 participants