Registration failing: Cannot insert the value NULL into column 'PwdFormat'

If you have questions about using mojoPortal, you can post them here.

You may want to first review our site administration documentation to see if your question is answered there.

This thread is closed to new posts. You must sign in to post in the forums.
6/30/2015 12:41:14 PM
Gravatar
Total Posts 115

Registration failing: Cannot insert the value NULL into column 'PwdFormat'

Hi, we have a site that has been running for a couple of years ...

mojoPortal Version 2.4.0.3 MSSQL
Operating System Microsoft Windows NT 6.1.7601 Service Pack 1
ASP.NET Info v4.0.30319 Running in Full Trust

and suddenly when a new user registers it fails with this in the system log:

2015-06-30 18:21:20,028 ERROR (null) - (null) - (null) - mojoPortal.Web.Global -  Referrer(http://www.lincshar.org/Secure/Register.aspx?returnurl=/) useragent Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.124 Safari/537.36
System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'PwdFormat', table 'mojoPortal_LHaR.dbo.mp_Users'; column does not allow nulls. INSERT fails.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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)
   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.ExecuteScalar()
   at mojoPortal.Data.SqlHelper.ExecuteScalar(String connectionString, CommandType commandType, String commandText, Int32 commandTimeout, SqlParameter[] commandParameters)
   at mojoPortal.Data.SqlHelper.ExecuteScalar(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
   at mojoPortal.Data.DBSiteUser.AddUser(Guid siteGuid, Int32 siteId, String fullName, String loginName, String email, String password, String passwordSalt, Guid userGuid, DateTime dateCreated, Boolean mustChangePwd, String firstName, String lastName, String timeZoneId, DateTime dateOfBirth)
   at mojoPortal.Business.SiteUser.Create()
   at mojoPortal.Web.mojoMembershipProvider.CreateUser(String userName, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status)
   at System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser()
   at System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e)
   at System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e)
   at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
ClientConnectionId:79951ddc-6036-40ec-9c1b-1428e22d2c07
Error Number:515,State:2,Class:16

The last successful registration was back in February, but we do not know when it started failing.

In inspection, the structure of the mp_users table is the same as in other sites where registration works:

CREATE TABLE [dbo].[mp_Users](

[UserID] [int] IDENTITY(1,1) NOT NULL,

[SiteID] [int] NOT NULL,

[Name] [nvarchar](100) NOT NULL,

[LoginName] [nvarchar](50) NULL,

[Email] [nvarchar](100) NOT NULL,

[LoweredEmail] [nvarchar](100) NULL,

[PasswordQuestion] [nvarchar](255) NULL,

[PasswordAnswer] [nvarchar](255) NULL,

[Gender] [nchar](10) NULL,

[ProfileApproved] [bit] NOT NULL,

[RegisterConfirmGuid] [uniqueidentifier] NULL,

[ApprovedForForums] [bit] NOT NULL,

[Trusted] [bit] NOT NULL,

[DisplayInMemberList] [bit] NULL,

[WebSiteURL] [nvarchar](100) NULL,

[Country] [nvarchar](100) NULL,

[State] [nvarchar](100) NULL,

[Occupation] [nvarchar](100) NULL,

[Interests] [nvarchar](100) NULL,

[MSN] [nvarchar](50) NULL,

[Yahoo] [nvarchar](50) NULL,

[AIM] [nvarchar](50) NULL,

[ICQ] [nvarchar](50) NULL,

[TotalPosts] [int] NOT NULL,

[AvatarUrl] [nvarchar](255) NULL,

[TimeOffsetHours] [int] NOT NULL,

[Signature] [nvarchar](max) NULL,

[DateCreated] [datetime] NOT NULL,

[UserGuid] [uniqueidentifier] NULL,

[Skin] [nvarchar](100) NULL,

[IsDeleted] [bit] NOT NULL,

[LastActivityDate] [datetime] NULL,

[LastLoginDate] [datetime] NULL,

[LastPasswordChangedDate] [datetime] NULL,

[LastLockoutDate] [datetime] NULL,

[FailedPasswordAttemptCount] [int] NULL,

[FailedPwdAttemptWindowStart] [datetime] NULL,

[FailedPwdAnswerAttemptCount] [int] NULL,

[FailedPwdAnswerWindowStart] [datetime] NULL,

[IsLockedOut] [bit] NOT NULL,

[MobilePIN] [nvarchar](16) NULL,

[PasswordSalt] [nvarchar](128) NULL,

[Comment] [nvarchar](max) NULL,

[OpenIDURI] [nvarchar](255) NULL,

[WindowsLiveID] [nvarchar](36) NULL,

[SiteGuid] [uniqueidentifier] NULL,

[TotalRevenue] [decimal](15, 4) NULL,

[FirstName] [nvarchar](100) NULL,

[LastName] [nvarchar](100) NULL,

[Pwd] [nvarchar](1000) NULL,

[MustChangePwd] [bit] NULL,

[NewEmail] [nvarchar](100) NULL,

[EditorPreference] [nvarchar](100) NULL,

[EmailChangeGuid] [uniqueidentifier] NULL,

[TimeZoneId] [nvarchar](32) NULL,

[PasswordResetGuid] [uniqueidentifier] NULL,

[RolesChanged] [bit] NULL,

[AuthorBio] [nvarchar](max) NULL,

[DateOfBirth] [datetime] NULL,

[PwdFormat] [int] NOT NULL,

[EmailConfirmed] [bit] NOT NULL,

[PasswordHash] [nvarchar](max) NULL,

[SecurityStamp] [nvarchar](max) NULL,

[PhoneNumber] [nvarchar](50) NULL,

[PhoneNumberConfirmed] [bit] NOT NULL,

[TwoFactorEnabled] [bit] NOT NULL,

[LockoutEndDateUtc] [datetime] NULL,

CONSTRAINT [PK_mp_Users] PRIMARY KEY CLUSTERED

(

[UserID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[mp_Users] ADD CONSTRAINT [DF_Users_ProfileApproved] DEFAULT ((1)) FOR [ProfileApproved]

GO

ALTER TABLE [dbo].[mp_Users] ADD CONSTRAINT [DF_Users_Approved] DEFAULT ((1)) FOR [ApprovedForForums]

GO

ALTER TABLE [dbo].[mp_Users] ADD CONSTRAINT [DF_Users_Trusted] DEFAULT ((0)) FOR [Trusted]

GO

ALTER TABLE [dbo].[mp_Users] ADD CONSTRAINT [DF_mp_Users_DisplayInMemberList] DEFAULT ((1)) FOR [DisplayInMemberList]

GO

ALTER TABLE [dbo].[mp_Users] ADD CONSTRAINT [DF_Users_TotalPosts] DEFAULT ((0)) FOR [TotalPosts]

GO

ALTER TABLE [dbo].[mp_Users] ADD CONSTRAINT [DF_mp_Users_AvatarUrl] DEFAULT ('blank.gif') FOR [AvatarUrl]

GO

ALTER TABLE [dbo].[mp_Users] ADD CONSTRAINT [DF_mp_Users_TimeOffSetHours] DEFAULT ((0)) FOR [TimeOffsetHours]

GO

ALTER TABLE [dbo].[mp_Users] ADD CONSTRAINT [DF_Users_DateCreated] DEFAULT (getdate()) FOR [DateCreated]

GO

ALTER TABLE [dbo].[mp_Users] ADD CONSTRAINT [DF_mp_Users_UserGuid] DEFAULT (newid()) FOR [UserGuid]

GO

ALTER TABLE [dbo].[mp_Users] ADD CONSTRAINT [DF_mp_Users_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]

GO

ALTER TABLE [dbo].[mp_Users] ADD CONSTRAINT [DF_mp_Users_IsLockedOut] DEFAULT ((0)) FOR [IsLockedOut]

GO

ALTER TABLE [dbo].[mp_Users] ADD DEFAULT ((0)) FOR [PwdFormat]

GO

ALTER TABLE [dbo].[mp_Users] ADD DEFAULT ((1)) FOR [EmailConfirmed]

GO

ALTER TABLE [dbo].[mp_Users] ADD DEFAULT ((0)) FOR [PhoneNumberConfirmed]

GO

ALTER TABLE [dbo].[mp_Users] ADD DEFAULT ((0)) FOR [TwoFactorEnabled]

GO


All other rows in the table have PwdFormat=2, and in Site Settings the password format is set to "Encrypted in DB" (I'm not sure why, but that's how it is - does that equate to 2?).

The whole site was moved from one server to another some weeks back, but otherwise I cannot think what might have changed.

Any ideas?


 

1/28/2016 5:29:45 AM
Gravatar
Total Posts 115

Re: Registration failing: Cannot insert the value NULL into column 'PwdFormat'

For what it's worth, we solved this one, though do not know why it arose.

NULL values were being passed (on registration submission) to the the mp_users_insert and mp_users_update stored procs in these four variables: @PwdFormat, @EmailConfirmed, @PhoneNumberConfirmed, @TwoFactorEnabled

so the operations were failing as the mp_Users table had these fields not nullable. The solution was to modify these two stored procs to wrap each of the 4 offending nulls like this ISNULL(@PwdFormat,2) using the default values on the table designs, and now registration works.

Why the mojo code was passing nulls, and why we have not seen similar on other sites, I don't know (a quick check of current source code in mojoPortal.Data.MSSQL/dbSiteUser.cs suggests this is impossible, but that files was modified after our affected build). 

You must sign in to post in the forums. This thread is closed to new posts.