Bug #62134 Connector/Net Generates Incorrect SELECT Clause after UPDATE
Submitted: 10 Aug 2011 0:15 Modified: 29 Feb 2012 21:01
Reporter: Alexander Nagy Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.4.3 OS:Any
Assigned to: Fernando Gonzalez.Sanchez CPU Architecture:Any

[10 Aug 2011 0:15] Alexander Nagy
Description:
Currently Connector/Net generates incorrect returning SELECT SQL statements if a value in the SELECT WHERE clause is not also present in the UPDATE SET clause.

Debugged this and the problem lies in UpdateGenerator.cs on lines 43-58 and then the following assumption in SqlGenerator.cs on line 460.

UpdateGenerator is not populating the values member with any parameters from the WHERE clause, just the SET clause.

The high level impact is that any time Entity Framework passes down such a query the query will return unpredictable data or fail outright.  

There is no easy workaround unless one manually executes SQL, negating the value of the framework.

Specifically this happens whenever there is a ConcurrencyCheck being used by Entity Framework.

Unit tests and output below:

How to repeat:
Here is an NUnit unit test to repro:

Current bad SQL:

UPDATE `Dogs` SET `Name`=@gp1 WHERE (`Id` = 1) AND (`Name` = @gp2);
SELECT
`Timestamp`
FROM `Dogs`
 WHERE  row_count() > 0 AND `Id`=last_insert_id() <-- ERROR HERE

Expected SQL:

UPDATE `Dogs` SET `Name`=@gp1 WHERE (`Id` = 1) AND (`Name` = @gp2);
SELECT
`Timestamp`
FROM `Dogs`
 WHERE  row_count() > 0 AND `Id`=1

Entity Code-First definition (requires Entity Framework 4.1):

    public class Dog
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
        public virtual int Id { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public virtual DateTime Timestamp { get; set; }

        [ConcurrencyCheck, Required, MaxLength(45)]
        public virtual string Name { get; set; }
    }

    public class DogDBContext : DbContext
    {
        public DbSet<Dog> Dogs { get; set; }
    }

App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="DogDbContext" connectionString="server=localhost;User Id=root;database=test" providerName="MySql.Data.MySqlClient" />
  </connectionStrings>
</configuration>

Unit test:

        [Test]
        public void SimpleConcurrencyCheck()
        {
            using (var db = new DogDBContext())
            {
                db.Database.ExecuteSqlCommand(
    @"DROP TABLE IF EXISTS `test`.`dogs`");

                db.Database.ExecuteSqlCommand(
@"CREATE TABLE `dogs` (
  `Id` int(11) NOT NULL,
  `Name` varbinary(45) NOT NULL,
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary");

                try
                {
                    var dog = db.Dogs.Create();
                    dog.Id = 1;
                    dog.Name = "Woofy";

                    db.Dogs.Add(dog);

                    db.SaveChanges();

                    dog.Name = "Sparky";

                    db.SaveChanges(); // Fails here with DB Update Exception
                }
                finally
                {
                    db.Database.ExecuteSqlCommand(@"DROP TABLE IF EXISTS `dogs`");
                }
            }
        }

Suggested fix:
Fix update generator to correctly interpret WHERE clauses.
[16 Aug 2011 10:10] Bogdan Degtyariov
Hi Alexander,

I installed Entity Framework 4.1 and tried to make a sample test case using your instructions. Visual Studio displayed the error: 

The type or namespace name 'ConcurrencyCheck' could not be found (are you missing a using directive or an assembly reference?) for 

[ConcurrencyCheck, Required, MaxLength(45)]

Is that a part of your code? I tried looking into the docs for more details about ConcurrencyCheck and could not find anything relevant to our case.
Thanks.
[16 Aug 2011 16:56] Alexander Nagy
Hi Bogdan, the error you are seeing is when the assembly System.ComponentModel.DataAnnotations.dll needs to be added to the project.

ConcurrencyCheckAttribute was added in .Net 4, here is the MSDN link: http://msdn.microsoft.com/en-us/library/system.componentmodel.dataannotations.concurrencyc....

The general DataAnnotations namespace can be found here: http://msdn.microsoft.com/en-us/library/cc490428.aspx (Entity Framework 4.1 and later use these).

All the best,

Alex
[22 Aug 2011 9:26] Bogdan Degtyariov
Alexander,

Thanks for your reply.
I had System.ComponentModel.DataAnnotations in using section, but forgot to add to the project references.

Now I am able to reproduce the exception:

{"Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries."}

Uploading the test project to avoid further confusions.
[22 Aug 2011 9:32] Bogdan Degtyariov
Test project

Attachment: bug62134.zip (application/zip, text), 16.22 KiB.

[17 Jan 2012 16:50] Fernando Gonzalez.Sanchez
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

This fix will appear in versions 6.3.9, 6.4.5 & 6.5.1.
[29 Feb 2012 21:01] John Russell
Added to changelog for 6.3.9, 6.4.5, 6.5.1: 

After an UPDATE statement, Connector/Net would generate incorrect
SELECT SQL statements if a value in the WHERE clause was not also
present in the SET clause of the UPDATE.