CODEWITHSUNDEEP
c#mysqlasp.net
ChrisJ
ChrisJ

Reputation: 201

Attempting to update user profile throws MySqlException: column 'lastUpdatedDate' cannot be NULL

I have a web service application written in asp.net c# and with a MySQL database. I am using the MySQL providers (v6.9.4.0) for memberships, roles and profiles. For the profile, I am using this to store 7 properties, name, company etc.

I am able to connect and access the database fine, read all users and all other information so far. I am able to create user accounts and delete them with no issues, and all the profile properties are saved correctly in 'my_aspnet_profiles' table.

However, when I try to change some of the properties on an existing account, I get the following exception (including stack trace):

System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Configuration.Provider.ProviderException: Profile update failed. ---> MySql.Data.MySqlClient.MySqlException: Column 'lastUpdatedDate' cannot be null

   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at MySql.Web.Profile.MySQLProfileProvider.SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection)
   --- End of inner exception stack trace ---
   at MySql.Web.Profile.MySQLProfileProvider.SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection)
   at System.Configuration.SettingsBase.SaveCore()
   at System.Configuration.SettingsBase.Save()
   at System.Web.Profile.ProfileBase.SaveWithAssert()
   at System.Web.Profile.ProfileBase.Save()
   at Cylon.QuoteEngine.Application.Web.AccountManager.SaveUserProfile(String username, CylonProfile customProfile) in C:\Work\Web\AccountManager.cs:line 86

This works when I am using a MySQL instance hosted on my local machine, but when I attempt to run the app with the database hosted on a server, I am getting the exception.

Here is where I am attempting to save the user profile:

public void SaveUserProfile(string username, CylonProfile customProfile)
    {
        var profile = ProfileBase.Create(username);
        profile.SetPropertyValue("Name", customProfile.Name);
        profile.SetPropertyValue("Company", customProfile.Company);
        ...

        profile.Save();
    }

In my web.config file, the providers are declared as:

MembershipProvider:

<membership defaultProvider="MySqlMembershipProvider">
  <providers>
    <clear />
    <add name="MySqlMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web,Version=6.9.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="ApplicationServices" enablePasswordRetrieval="true" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="true" passwordFormat="Encrypted" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" autogenerateschema="true" /> 
  </providers>
</membership>

ProfileProvider:

<profile enabled="true" defaultProvider="MySqlProfileProvider">
  <properties>
    <add name="Name" type="String" />
    <add name="Company" type="String" />
    ...
  </properties>
  <providers>
    <clear />
    <add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.9.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" applicationName="/" connectionStringName="ApplicationServices" autogenerateschema="True" description="" writeExceptionsToEventLog="False" enableExpireCallback="False" />
  </providers>
</profile>

RoleProvider:

<roleManager enabled="true" defaultProvider="MySqlRoleProvider">
  <providers>
    <clear />
    <add connectionStringName="ApplicationServices" applicationName="/" name="MySqlRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web,Version=6.9.4.0, Culture=neutral,PublicKeyToken=c5687fc88969c44d" autogenerateschema="true" />
  </providers>
</roleManager>

I was under the assumption that the 'lastUpdatedDate' column would be updated automatically and I wouldn't have to do any coding to set this value, or do I? Can anyone explain why it's happening on the hosted server, and not on my local machine? What can I do to fix it?

If you need any more info, let me know and I can add it.

Upvotes: 1

Views: 938

Answers (1)

HoXa
HoXa

Reputation: 153

Sorry not enough reputation to comment, but you could have look here, it looks related: How do you set a default value for a MySQL Datetime column?

Update
Could be related to a bug in MySql v5.6.19 bugs.mysql.com/bug.php?id=68472
If select @@explicit_defaults_for_timestamp; returns 1 try changing the value to 0 in my.ini file.
The file is usually located at C:\Program Files\MySQL\MySQL Server x.xx\my.ini

Upvotes: 3

Related Questions