This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi, Ivan, I have problem deleting an entity. The error occurs on SaveChanges right after calling Remove. (Error detail attached) Do you have any idea? And here is the call stack.. Thanks. ============================================================================================================
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)
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 Mindscape.LightSpeed.Data.CommandRunner.<>cDisplayClass7. |
|
|
This appears to occur when an UPDATE does a SET whose value is a subquery (http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html). I'm not sure why we would ever do such a thing, especially during a Remove -- a Remove could result in a set to NULL (cascade delete for nullable associations) or a set of the DeletedOn column (for soft-deleted entities) but both of those should be for the table at hand, not for a subquery. Could you post a minimal but complete repro project (minimal set of entities, console application or NUnit test) that exhibits the error, and ideally also the SQL you're seeing generated (which you can get by setting LightSpeedContext.Logger = new TraceLogger())? Thanks! |
|
|
Hi, Ivan, I sent a sample project and mysql data to John as I don't have your email address. I told him to forward the email to you. Thanks for your help. -chris |
|
|
This looks like a MySQL limitation which is broader than the example I cited earlier: It occurs because you have a cascade delete across a self-joining table. This will be a bit tricky to resolve. You may have to turn off cascade delete, then manually soft-delete the child entities before deleting the parent. We will investigate whether we can work around this limitation (for example using the INNER JOIN trick mentioned in that Stack Overflow post) but I can't commit to anything so I'd encourage you to pursue the "turning off cascade delete" approach regardless. |
|
|
Thanks for the explanation. It will be very inconvenient to "soft delete" all children if there are many cascading children. If this is due to the limitation of MySQL DB that cannot be resolved easily, I might have to switch DB. Please let me know how you decide on the workaround. Thanks. -chris. |
|
|
Hi, Ivan, Please let me know if this limitation will not be fixed. It won't be easy but I'll have to plan on moving to SQL Server. Thanks. -chris |
|
|
Hi Chris, We believe this would require significant effort and we don't have the resources to do it right now unless it affects a larger number of customers. You should plan on the basis that we will not be able to work around the MySQL limitation. Sorry. We hope that the LightSpeed database creation features would take care of a good deal of the migration to another database -- they won't handle all of the nitty-gritty details, but should give you a big head start in terms of porting your schema. |
|
|
That's fine. It might be good to migrage SQL Server for hosting on Azure later on anyway. I'll give it a try, however, let me know if there are known limitations on SQL Server. Thanks. -chris |
|
|
Ivan, BTW, I get email notification of my own reply but not yours since recently. I'm not sure what's wrong. hmm... |
|
|
We have just done a release which hopefully will resolve the weird email issue. It should be that you get emailed after each reply/edit as long as you're not the author of that reply/edit. Thank you for drawing attention to this. |
|
|
Thanks John, I was wondering if it was filtering problem. |
|
|
I just noticed the following on the latest blog post. "Fix for issue with joined queries on soft-deleted entities" Does that mean Ivan implemented the workaround? If he did, silently, what a pleasant surprise! I don't have to migrate to other DB.. at least for a while.. ^^ thanks -chris |
|
|
No, this refers to a fix for loading soft-deleted entities in a join scenario, not to the deleting of self-joined entities. Sorry! We would certainly have let you know if we had been able to crack the MySQL issue. |
|