MySQL Bugs: #73271: Incorrect Sql generated for EF with ConcurrencyCheck and DatabaseGenerated

Bug #73271	Incorrect Sql generated for EF with ConcurrencyCheck and DatabaseGenerated
Submitted:	11 Jul 2014 5:54	Modified:	12 Jun 2018 20:01
Reporter:	Ross Knudsen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.8.3.2, 6.9.9, 7.0.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ConcurrencyCheck, DatabaseGenerated, entity framework

Description:
SO question:  http://stackoverflow.com/questions/24645180/dbupdateconcurrencyexception-using-entity-fram...

Trying to implement optimistic concurrency checking using Entity Framework and MySql generates exception.  Inspection of generated Sql seems to be incorrect.  After updating a record it correctly tries to select the updated concurrency field but includes a condition in the where clause on this field requiring it to have the same value as pre-update.  So we are querying for a new value but requiring the value to be the same.

Running against the equivalent Sql Server set up works without error and does not include the condition in the where clause.

How to repeat:
Nuget packages installed:

EF 6.0.1
MySql.ConnectorNET.Data 6.8.3.2
MySql.ConnectorNET.Entity 6.8.3.2

DROP DATABASE IF EXISTS `bugreport`;
CREATE DATABASE IF NOT EXISTS `bugreport`;
USE `bugreport`;

DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
  `TestId` int(10) NOT NULL AUTO_INCREMENT,
  `AStringField` varchar(50) DEFAULT NULL,
  `DateModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`TestId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO `test` (`TestId`, `AStringField`, `DateModified`) VALUES
    (1, 'Initial Value', '2014-07-11 09:15:52');

using System;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace BugReport
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new BugReportModel())
            {
                context.Database.Log = (s => Console.WriteLine(s));

                var firstTest = context.tests.First();
                firstTest.AStringField = "First Value";

                // Exception is thrown when changes are saved.
                context.SaveChanges();            

                Console.ReadLine();
            } 
        }
    }

    public class BugReportModel : DbContext
    {
        public BugReportModel()
            : base("name=Model1")
        {

        }

        public virtual DbSet<test> tests { get; set; }
    }

    [Table("test")]
    public class test
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int TestId { get; set; }

        [StringLength(50)]
        public string AStringField { get; set; }

        [ConcurrencyCheck()]
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        [Column(TypeName = "timestamp")]
        public System.DateTime DateModified { get; set; }
    }
}

Suggested fix:
Concurrency check fields should be queried from the database after updating but do not include the condition on the concurrency field.

I would expect that the DatabaseGenerated attribute wouldn't be necessary as it seems to be implied by the ConcurrencyCheck attribute (unless it is possible for this field to be updated client side).  Excluding the DatabaseGenerated attribute causes EF to not query the database for the updated value and hence fails on subsequent modifications to the record.

Now with a bit more playing around I think that my approach here was wrong.  What I would like to implement is something like Sql Server's RowVersion/Timestamp feature.  Where the database maintains a field which changes on every update to a record so that EF can use it to catch concurrency errors.

I created a binary field in a table and set up a trigger to increment the value in the binary field on every update.  Then I tried to set the System.ComponentModel.DataAnnotations.TimeStampAttribute for the corresponding field in my POCO but EF threw an error complaining the the data types didn't match.  

So I'm not sure if there is a way to create a field that EF/ADO.Net thinks is a Sql Server Timestamp field.

Also affecting 6.9.3.0 on MySQl 5.6

MySql
ModifyDT column: timestamp (CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

C#
[ConcurrencyCheck]
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime ModifyDT { get; set; }

The logged sql query as shown below where "AND (`ModifyDT` = @gp5)" causes the exception System.Data.Entity.Infrastructure.DbUpdateConcurrencyException.  The reason is that ModifyDT has changed after the update statement due to "ON UPDATE CURRENT_TIMESTAMP".

==========================================
UPDATE `Registrations` SET `RegistrationGUID`=@gp1, `FormGUID`=@gp2, `Email`=@gp3 WHERE (`RegistrationID` = 2) AND (`ModifyDT` = @gp4);
SELECT
`CreateDT`, 
`ModifyDT`
FROM `Registrations`
 WHERE  row_count() > 0 and (`RegistrationID` = 2) AND (`ModifyDT` = @gp5)

-- @gp1: '1b284114-d244-402e-9f28-b71e51a820de' (Type = Guid, IsNullable = false)

-- @gp2: '498cb05d-88aa-11e3-bf0e-74d7dac137cd' (Type = Guid, IsNullable = false)

-- @gp3: '2@2.com' (Type = String, IsNullable = false, Size = 7)

-- @gp4: '20/09/2014 02:34:33' (Type = DateTime, IsNullable = false)

-- @gp5: '20/09/2014 02:34:33' (Type = DateTime, IsNullable = false)

-- Executing at 22/09/2014 00:04:58 +08:00

-- Completed in 1 ms with result: EFMySqlDataReader

Fix #73271, base on 6.9.6

Attachment: UpdateGenerator.patch (application/octet-stream, text), 907 bytes.

I have attached an patch try to fix this bug, Would you please check this for me, Thanks.

Here is what I thought, when return some computed values, we just need key Members to identify this entity like InsertGenerator.

http://bugs.mysql.com/bug.php?id=80033 marked as duplicate of this one.

Hello Ross Knudsen,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.

http://bugs.mysql.com/bug.php?id=81477 marked as duplicate of this one.

http://bugs.mysql.com/bug.php?id=80578 marked as duplicate of this one.

This issue is now more than 2.5 years and still unresolved. Are there any plans to fix it?

I have submitted a PR to github that provides a workaround solution for this issue. 

The workaround avoids use of TIMESTAMP or DATETIME values to perform optimistic locking using a safer `BIGINT RowVersion` value that is incremented via a BEFORE UPDATE trigger.  This will now support optimistic locking with an external (non-EF) application.

https://github.com/mysql/mysql-connector-net/pull/23

I'm also investigating how to performing optimistic locking with a TIMESTAMP field.

Firstly, you need to use a more fine grained timestamp value.

So for example if you use the following, your timestamp value will be truncated to the nearest second (not very safe for optimistic locking).

    UpdatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIME ON UPDATE CURRENT_TIME

Instead you should use:

    UpdatedAt TIMESTAMP(6) NOT NULL DEFAULT NOW(6) ON UPDATE NOW(6)

Secondly, I'm observing a bug that I'm reproducing within the environment of the MySQL .NET Connector unit test suite combined with the PR patch I've just submitted. EF6 optimistic locking now generates the correct SQL to perform an UPDATE followed by the SELECT (now fixed) that returns the updated TIMESTAMP field.  However the MySQL connector returns a ZERO TIMESTAMP (0000-00-00 00:00:00.000000) even though executing the exact same UPDATE and SELECT in MySQL Workbench it returns a valid non-zero TIMESTAMP value. I've observed the packets read via the connection socket return the string '0000-00-00 00:00:00.000000' so its probably related to the MySQL session configuration in some way.  Hints welcome! I'm currently testing this with MySQL v5.6.26 (Windows).

In our case, we have a legacy MS-Access app that uses a TIMESTAMP in most tables to perform optimistic locking. This is a convenient solution for MS-Access as it detects the presence of any TIMESTAMP column and automatically applies optimistic locking to this column when it finds one.  

Since we currently don't have optimistic locking working with EF6 for TIMESTAMP columns we've added a second optimistic lock column on each table we care about by creating a BIGINT RowVersion column as that is incremented via a BEFORE INSERT trigger. So now for each UPDATE both the existing TIMESTAMP column and the new RowVersion column are always changed so either can be used to detect a change. Not ideal but it works! 

Hopefully I'll soon have a fix for EF6 / TIMESTAMP optimistic locking as well.