Problem after Azure upgrades: Cannot insert the value NULL into column 'ContextInfo'

Aug 27, 2015 at 10:28 PM
Hello,

We have our site hosted on DNN's Azure cloud hosting and on August 24, they performed some kind of database maintenance. Since then, we have been unable to edit or add any records or settings in our instances of 2sxc, which is heavily used throughout the site. We already have a ticket open with DNN support but they are, of course, refusing to help with 3rd party modules and pushing it back to 2sxc. We are running 2sxc 6.6.2 on DNN 7.3.1. I am hoping an upgrade to the latest 2sxc 7.2.2 will help, but I figured I'd check here to see if this problem was known. Any advice is vastly appreciated. Thanks!

AssemblyVersion:7.3.1
ActiveTabID:671
ActiveTabName:Component Decontamination Services
RawURL:/Operating-Plants/Outage-Services/Component-Decontamination-Services?ctl=editcontentgroup&mid=5630&returnUrl=http%3A%2F%2Fxxxxxxx%2FDefault.aspx%3Ftabid%3D671%26error%3DAn%2Berror%2Boccurred%2Bwhile%2Bexecuting%2Bthe%2Bcommand%2Bdefinition.%2BSee%2Bthe%2Binner%2Bexception%2Bfor%2Bdetails.&sortOrder=6&contentGroupId=5630
AbsoluteURL:/Default.aspx
AbsoluteURLReferrer:/Operating-Plants/Outage-Services/Component-Decontamination-Services?ctl=editcontentgroup&mid=5630&returnUrl=http%3A%2F%2Fxxxxxxxxxx%2FDefault.aspx%3Ftabid%3D671%26error%3DAn%2Berror%2Boccurred%2Bwhile%2Bexecuting%2Bthe%2Bcommand%2Bdefinition.%2BSee%2Bthe%2Binner%2Bexception%2Bfor%2Bdetails.&sortOrder=6&contentGroupId=5630
UserAgent:Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/44.0.2403.157 Safari/537.36
DefaultDataProvider:DotNetNuke.Data.SqlDataProvider, DotNetNuke
ExceptionGUID:d90d6549-c815-4cd1-b3bf-1fceaca7104f
InnerException:An error occurred while executing the command definition. See the inner exception for details.
FileName:
FileLineNumber:0
FileColumnNumber:0
Method:System.Data.SqlClient.SqlConnection.OnError
StackTrace:
Message:
DotNetNuke.Services.Exceptions.PageLoadException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ContextInfo', table 'dnna1ct2b.dbo.ToSIC_EAV_ContextInfo'; column does not allow nulls. INSERT fails.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource
1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
--- End of inner exception stack trace ---
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, ReadOnlyMetadataCollection1 entitySets, EdmType[] edmTypes, MergeOption mergeOption)
at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter[] parameters)
at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ObjectParameter[] parameters)
at ToSic.Eav.EavContext.GetChangeLogId(String userName)
at ToSic.Eav.EavContext.AddEntity(AttributeSet attributeSet, Int32 attributeSetId, IDictionary values, Nullable
1 configurationSet, Nullable1 keyNumber, Nullable1 keyGuid, String keyString, Int32 assignmentObjectTypeId, Int32 sortOrder, Nullable1 entityGuid, ICollection1 dimensionIds, List1 updateLog, Boolean isPublished)
at ToSic.Eav.EavContext.AddEntity(Int32 attributeSetId, IDictionary values, Nullable
1 configurationSet, Nullable1 key, Int32 assignmentObjectTypeId, Int32 sortOrder, Nullable1 entityGuid, ICollection`1 dimensionIds, Boolean isPublished)
at ToSic.Eav.ManagementUI.ItemForm.Insert()
at ToSic.Eav.ManagementUI.ItemForm.Save()
at ToSic.SexyContent.EditContentGroupItem.Save()
at ToSic.SexyContent.EditContentGroup.btnUpdate_Click(Object sender, EventArgs e)
at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
--- End of inner exception stack trace ---
Source:
Server Name: RD000D3A6015B0
Aug 27, 2015 at 10:57 PM
I dont know if it is the same issue but i recently ran into other issues like this and it all had to do with cannot inser null value

try opening the url from the log but remove the returnurl section this solved various issues for me ( not realy solved, but made it possible to work with it until there is a better solution ) as there are issues with dnn and it seeing attributes in the return url as actual quesrystring variables

Armand
Coordinator
Aug 28, 2015 at 7:40 AM
Hi Lindsey

Some background: the EAV (the entity-attribute-value store) tracks every change it makes, and bundles these into packages - because otherwise each update (changing various fields, values, etc.) would be hard to undo - since nobody would know what was done together.

For this, it needs to know when updates belong together - this is a bit tricky, but can be solved. Basically the EAV will declare when it starts.
  • this is done in SQL using the stored procedure ToSIC_EAV_ChangeLogSet
  • the code has a nicer name, SetChangeLogIdInternal
I checked the code of that SP and discovered that it has 2 behaviours, one for Azure and one for normal SQL - here's the code of the current version - I'll assume it hasn't changed but could be wrong:

ALTER PROCEDURE [dbo].[ToSIC_EAV_ChangeLogSet]
  @ChangeID int
AS

SET NOCOUNT ON

-- Remove all context items older than an 5 minutes ago
DELETE FROM [dbo].[ToSIC_EAV_ContextInfo] WHERE [UpdatedAt] < DATEADD(mi, -5, GETUTCDATE())

IF SERVERPROPERTY('edition') <> 'SQL Azure'
BEGIN
    DECLARE @b varbinary(128)
    SET @b = CONVERT(varbinary(128),newid())
    EXEC sp_executesql @statement=N'SET CONTEXT_INFO @b',@params=N'@b varbinary(128)',@b=@b
    print @b
END

DECLARE @ContextInfo varbinary(128)
SELECT @ContextInfo = CONTEXT_INFO()

IF EXISTS (SELECT * FROM [dbo].[ToSIC_EAV_ContextInfo] WHERE [ContextInfo] = @ContextInfo)
    UPDATE [dbo].[ToSIC_EAV_ContextInfo]
    SET
        [ChangeID] = @ChangeID,
        [UpdatedAt] = GETUTCDATE()
    WHERE
        ContextInfo = @ContextInfo
ELSE
    INSERT INTO [dbo].[ToSIC_EAV_ContextInfo] ([ContextInfo], [ChangeID], [UpdatedAt]) VALUES (@ContextInfo, @ChangeID, GETUTCDATE());
The reason azure works differently, is that the CONTEXT_INFO() doesn't really exist there - theoretically each sql-request could be handled by a different sql-server (not likely, but possible), so they removed the context. We implemented this workaround based on various blogs a long time ago - like here:
Replacement of context info sql azure

So my best guess is that you'll have to drill down into this stored procedure and debug what's going wrong. I don't have a quick answer, but I believe this background should help you get started.
We could also look into it for you, but would need high level access (host-user) and it would take a few hours of support.
Aug 31, 2015 at 8:22 PM
I will wait to see what DNN support has to say, but I may have to get some help looking into it, because all of that is a little beyond my current skill set. :)
Sep 1, 2015 at 7:18 PM
Here is the response that I have received from the Cloud Team.

The error you have listed indicates that the row couldn’t be inserted because the SQL tried to insert a NULL into a column which doesn’t allow NULL. Any table created has each column marked NULL or NOT NULL – this has always been the case since v1 of SQL Server. Nothing in the upgrade would have changed that.

As unlikely as it seems, it would appear to be a coincidence if this error started happening after the V12 upgrade. In any case, there is nothing we can do because the issue is with the data being inserted, which might be a problem with the way the module is being used, or a problem with the module itself. Additionally, we are unable to roll back the change and previous versions of SQL Azure will not be supported by Microsoft in two weeks. So the issue will need to be fixed.

So I would recommend that you have the developer take a look at the error messages that you are seeing as the issue appears to be at the module level.
Coordinator
Sep 2, 2015 at 12:40 PM
So what's your plan? Will you look into it or would you prefer us to do it? If you need support, contact us through 2sxc.org --> Contact/Support
Sep 2, 2015 at 8:52 PM
I will contact you for support. Thanks
Sep 4, 2015 at 8:07 PM
One quick note. My fellow developer here read your post above and said:

"If this is how the SP still is then the IF SERVERPROPERTY('edition') <> 'SQL Azure' will always run. This doesn’t look like a valid return from any of the SQL MSDN docs regardless of version: https://msdn.microsoft.com/en-us/library/ms174396.aspx

ContextInfo was also the offending issue in the module installation/upgrade and the .NET errors."
Sep 8, 2015 at 2:13 PM
Also...DNN support just told me this:

Regarding the maintenance, it was to upgrade SQL Azure to V12. Here is more information on that upgrade:

https://azure.microsoft.com/en-us/documentation/articles/sql-database-v12-whats-new/