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

Question

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.

The mySql database probably allowed NULL for the lastUpdatedDate column. The DB on the server doesn't. Either make it allow null, or set lastUpdatedDate to DateTime.Now in your update code before you call Save(). You can make the server automatically calculate last updated date if you prefer, just google how to change the server's schema for that. — Steve Lillis, Nov 26 '14 at 15:00
Hi @Steve Lillis, 'lastUpdatedDate' column is set to Non-Null on both my local machine and the hosted database. The schema between the two pretty much match exactly. And, in the ProfileBase class, the 'lastUpdatedDate' variable is read only so I can't even set it before calling save(). It's defined as `public DateTime LastUpdatedDate { get; }` — ChrisJ, Nov 26 '14 at 15:13
Try manually inserting the data in your local DB and on the server. If both work, make sure you don't have any weird mappings in your domain model in Visual Studio. — Steve Lillis, Nov 26 '14 at 15:29
I'll try that. but this is a table that's automatically generated when using the MySQL providers, so I don't do any mapping for these in my code. The other tables in my database, that weren't created by the providers are mapped correctly and all is fine with them. — ChrisJ, Nov 26 '14 at 15:36
Hi @Steve Lillis, I managed to find [this link](http://mysql-connector-net.sourcearchive.com/documentation/6.4.3-1/classMySql_1_1Web_1_1Profile_1_1MySQLProfileProvider_a0fb82378ea98a5fb8bfd7bb68fa8703c.html) defining the SetPropertyValues. I;m not sure how up-to-date it is, though. However, in this you can see that in the `INSERT INTO db VALUES(...)..` the last value is NULL, which should be the 'lasUpdatedDate' value... When I run this on my local DB, it works fine. When I run it on my hosted DB, I get the same error. — ChrisJ, Nov 26 '14 at 15:49
Edited. MySqlProvider SetPropertyValues is not the same as ProfileBase.SetPropertyValue (http://referencesource.microsoft.com/#System.Web/Profile/HttpProfileBase.cs,00d09e49cfe7b3ff) :) — Steve Lillis, Nov 26 '14 at 16:04
I think you may have misunderstood, and it's my fault for not being clear, I think. The definition of the function I found is the `MySQLProfileProvider::SetPropertyValues()`. This is called after Save() (see stack trace in original post). The `profile.SetPropertyValue(...)` I use to store the property values in MY code, only saves the properties in the local instance of the profile. See [MSDN page](http://msdn.microsoft.com/en-us/library/system.web.profile.profilebase.setpropertyvalue(v=vs.110).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-2) — ChrisJ, Nov 26 '14 at 16:12
And just to be certain, the DB on the server is *also* MySQL? — Steve Lillis, Nov 26 '14 at 16:14
Yes, they are both MySQL! I'd be in real trouble if it wasn't :P — ChrisJ, Nov 26 '14 at 16:14
Might be unrelated, but this might help? http://stackoverflow.com/questions/3384668/mysql-cannot-insert-null-value-in-column-but-i-have-a-default-value-specified Also, are they the same version of MySQL? — Steve Lillis, Nov 26 '14 at 16:15
Ok, sorry, I'm with you now. The MySqlProvider is trying to send NULL instead of not specifying anything for that property and this is upsetting the DB on the server but not the localhost. — Steve Lillis, Nov 26 '14 at 16:16
That's exactly it, Steve. It seems like it could be a bug with MySql to me? Although I haven't been able to find anything else on this issue, so if it is a bug I doubt I would be the only person to run into it — ChrisJ, Nov 26 '14 at 16:19
Usually with generated models you can still edit the XML representation to make a column read only, but yeah, it looks like there's a conflict here. Are you using the latest version of the MySql bits you're using? — Steve Lillis, Nov 26 '14 at 16:21
You might be suffering because of this: http://stackoverflow.com/questions/3454788/any-reference-to-profilecommon-causes-error-value-cannot-be-null-parameter-name — Steve Lillis, Nov 26 '14 at 16:24
I think that @HoXa's answer in the other comments explains why its happening on one and not the other. Let me know if not and I'll keep trying to figure it out. — Steve Lillis, Nov 26 '14 at 16:36
Steve, yeah, seems like it explains the inconsistency between the two! Cheers for the help so far! — ChrisJ, Nov 26 '14 at 16:47

score 3 · Accepted Answer · edited May 23 '17 at 10:30

3

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

edited May 23 '17 at 10:30

Community

1
1

answered Nov 26 '14 at 15:13

HoXa

153
9

Thanks for the link, but the default value for the column is already 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'... – ChrisJ Nov 26 '14 at 15:28
Is your hosted db version >= 5.6.5? – HoXa Nov 26 '14 at 15:40
Hosted DB is 5.6.19, local DB is 5.6.21 – ChrisJ Nov 26 '14 at 15:44
Could you make the LastUpdatedDate property return DateTime.Now? – HoXa Nov 26 '14 at 15:50
In my app code? no I don't think so. It is a read only property of the ProfileBase class. – ChrisJ Nov 26 '14 at 15:51
1

http://bugs.mysql.com/bug.php?id=68472 Run select @@explicit_defaults_for_timestamp; on your server to see if it is enabled. – HoXa Nov 26 '14 at 16:23
On my hosted DB, this is enabled. On my local DB, it is not. So this must be what is causing the issue! Is it possible to disable this on my hosted DB by just setting the value to 0, or is this variable set when creating the DB? – ChrisJ Nov 26 '14 at 16:45
You can change it in your config file (my.ini), find the line explicit_defaults_for_timestamp = 1 and set it to 0. – HoXa Nov 26 '14 at 16:50

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

1 Answers1