none
Exception in EF when using MySql foreign keys in one-to-one scenario RRS feed

  • Question

  • I get an exception in EF when I try to insert objects in the data model.  I am using MySql (the MySql Connector 6.4.5). The problem occurs when I have foreign keys setup in MySql to support cascade deletes.

    I have two tables A and B that both have a fk column that links to table C.  The data in table C provides additional information that could apply to a row in table A or table C.   You can think of that like a "has-a" relationship.  (An "A" can have-a "C" and a "B" can "have-a" C).   A row in table C will never be pointed to by both table A and table B.   My goal is to automatically delete the appropriate row in C when the associated row in A is deleted, and to do the same thing when I delete a row in table B.  Referential integrity would prevent you from deleting a row in table C.

    What I believe I have figured out is that:

    1. I need a non-cascade deleting foreign key in table A that references table C to provide the referential integrity.

    2. I need a cascade deleting foreign key in table C that references table A to provide the cascade delete.

    If I put the cascade delete on table A it doesn't do anything.  Deleting the row in table A leaves the row in table C.

    If I put those foreign keys in place, I get the behavior I want when I insert rows manually in the database.  But if I use code-first entity framework to manage the objects, I get an exception.

    This is a test case I setup.  In this case, I just have table A and table C for simplicity ("cars" and "cardetails"):

    DROP TABLE IF EXISTS `cascadetest`.`cars`;
    CREATE TABLE `cascadetest`.`cars` (
      `carid` INT(10) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
      `cardetailid` INT(10) NULL,
      PRIMARY KEY (`carid`),
      INDEX `ix_cardetailid` (`cardetailid`),
      CONSTRAINT `fk_cars_cardetails_cardetailid` FOREIGN KEY `fk_cars_cardetails_cardetailid` (`cardetailid`)
        REFERENCES `cascadetest`.`cardetails` (`cardetailid`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
    )
    ENGINE = INNODB;
    DROP TABLE IF EXISTS `cascadetest`.`cardetails`;
    CREATE TABLE `cascadetest`.`cardetails` (
      `cardetailid` INT(10) NOT NULL AUTO_INCREMENT,
      `test` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
      PRIMARY KEY (`cardetailid`),
      CONSTRAINT `fk_cardetails_cars_cardetailid` FOREIGN KEY `fk_cardetails_cars_cardetailid` (`cardetailid`)
        REFERENCES `cascadetest`.`cars` (`cardetailid`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
    )
    ENGINE = INNODB;

    My entity framework code is very simple:

    public class Car {
        public virtual int CarID { get; set; }
        public virtual string Name { get; set; }
        public virtual CarDetail CarDetail { get; set; }
    }
    public class CarDetail {
        public virtual int CarDetailID { get; set; }
        public virtual string Test { get; set; }
    }
    public class MyDatabase : DbContext {
        private static MyDatabase s_instance;
        public static MyDatabase Instance { get { return s_instance ?? (s_instance = new MyDatabase("MySqlDB")); } }
        public MyDatabase(string connectString) : base(connectString) {}
        public DbSet<Car> Cars { get; set; }
        public DbSet<CarDetail> CarDetails { get; set; }
    }
    class Program {
        static void Main(string[] args) {
            var db = MyDatabase.Instance;
            var toyotaDetails = db.CarDetails.Create();
            toyotaDetails.Test = "test1";
            var toyota = db.Cars.Create();
            toyota.Name = "Toyota";
            toyota.CarDetail = toyotaDetails;
            var fordDetails = db.CarDetails.Create();
            fordDetails.Test = "test2";
            var ford = db.Cars.Create();
            ford.Name = "Ford";
            ford.CarDetail = fordDetails;
            db.Cars.Add(toyota);
            db.Cars.Add(ford);
            db.SaveChanges();
                
            Console.WriteLine("Success");
            Console.ReadKey();
        }
    }

    Here's the exception that I get:

    System.Data.Entity.Infrastructure.DbUpdateException was unhandled
      Message=An error occurred while updating the entries. See the inner exception for details.
      Source=EntityFramework
      StackTrace:
           at System.Data.Entity.Internal.InternalContext.SaveChanges()
           at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
           at System.Data.Entity.DbContext.SaveChanges()
           at TestCascadeDelete.Program.TestInsertObjects() in C:\Projects\Experiments\MySql\CascadeDelete\EntityFramework\TestCascadeDelete\TestCascadeDelete\Program.cs:line 60
           at TestCascadeDelete.Program.Main(String[] args) in C:\Projects\Experiments\MySql\CascadeDelete\EntityFramework\TestCascadeDelete\TestCascadeDelete\Program.cs:line 9
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: System.Data.UpdateException
           Message=An error occurred while updating the entries. See the inner exception for details.
           Source=System.Data.Entity
           StackTrace:
                at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
                at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
                at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
                at System.Data.Entity.Internal.InternalContext.SaveChanges()
           InnerException: MySql.Data.MySqlClient.MySqlException
                Message=Cannot add or update a child row: a foreign key constraint fails (`cascadetest`.`cardetails`, CONSTRAINT `fk_cardetails_cars_cardetailid` FOREIGN KEY (`cardetailid`) REFERENCES `cars` (`cardetailid`) ON DELETE CASCADE ON UPDATE CASCADE)
                Source=MySql.Data
                ErrorCode=-2147467259
                Number=1452
                StackTrace:
                     at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
                     at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
                     at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& 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.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
                     at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
                     at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
                     at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
                InnerException:

    I suspect I might be able to use the Fluent API to make EF adhere to my data model, but I don't know that for sure.  I haven't used the Fluent API except for some very basic code snippets that I copied off the internet, so I'm not really sure where to start trying to figure out how to manipulate it (short of indulging in an in-depth crash course in researching the innards of EF, which I'm hoping to avoid).

    Any thoughts on what to do about this?

    Thanks,

    David Cater

    Friday, August 3, 2012 6:02 PM

All replies

  • Hi David,

    If the database is working as expected when you run queries against it then I would use a profiler to capture what SQL EF is actually sending to the server. Running that SQL should then give you the same error that EF is reporting and you can see what is wrong.

    It's been a while since I've used MySQL so I'm not sure if the functionality to do that comes out of the box, do you know how to capture the SQL that is being sent to MySQL?


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Friday, August 3, 2012 9:35 PM
    Moderator
  • No, I haven't looked into that yet.  I typically use Sql Server myself, so I'm used to using the Profiler.  I agree; that's probably the best approach, if I can figure out how to intercept the sql.

    Thanks,

    David

    Friday, August 3, 2012 9:43 PM
  • Let me know if you can't find anything and i'll see if I can find something.

    Alternatively if you re-post the contents of this question in Stackoverflow with both a MySQL and EntityFramework tag then you are more likely to get a MySQL guy looking at it. You can link to the StackOverflow question in this thread if it gives you the answer.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Friday, August 3, 2012 10:05 PM
    Moderator
  • Thanks for the advice about StackOverflow.  I might give that a shot.

    I turned on the MySql general log, which logs all sql queries (amongst over things).  It looks like this is the first query that runs is the one that fails:

    INSERT INTO `CarDetails`(`Test`) VALUES ('test1');

    I went back to my test sql, and the reason my test succeeded was that I had this line at the top of my sql:

    SET FOREIGN_KEY_CHECKS = 0;

    I did all of my test inserts like that, and then afterwards had:

    SET FOREIGN_KEY_CHECKS = 1;

    So it looks like I still don't have my foreign keys setup correctly in MySql. 

    Friday, August 3, 2012 10:30 PM
  • Hi David Cater,

    Welcome to MSDN Forum.

    Since MySQL is 3rd party database, I'm afraid I've no environment to help you solve the issue. I agree with @Glenn Condron, you'd better to post in stackoverflow with the tag of 'MySql' and 'EntityFramework', you're more likely to get help from MySQL experts. : )

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, August 6, 2012 3:08 AM
    Moderator