Databases
StackOverflow2013
Postsdbo
POMySQL and Entity Framework - Application calling a stored procedure returns 0 rows effected but the same procedure works in terminal
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL and Entity Framework - Application calling a stored procedure returns 0 rows effected but the same procedure works in terminal
    text
    copied!<p>I recently had to switch from using Microsofts SQL server to using MySQL. </p> <p>The application I wrote is using the Entity Framework 4.0. I really like MySQL but the process has been a bit of a pain. Most of the CRUD operations are done with stored procedures so I had fun in my encounter with <a href="http://bugs.mysql.com/bug.php?id=55778" rel="nofollow">MySQL bug 55778</a>.</p> <p>I got over that and manually edited the EDMX file.</p> <p>Everything worked well until in one scenario I started getting an <a href="http://msdn.microsoft.com/en-us/library/aa0416cz.aspx" rel="nofollow">optimistic concurrency exception</a>.</p> <p>This is where is happened:</p> <pre><code>using (var context = new DBEntities()) { context.Requests.AddObject(request); context.SaveChanges(); response.RequestId = request.Id; context.Responses.AddObject(response); context.SaveChanges(); } </code></pre> <p>The request is logged fine. The exception is thrown when I try to save changes on the response. RequestId is the primary key (not auto incremented) of the response table. I tried to use MySQL profiler to see what was happening but it didn't really work. I could only profile queries made through my terminal but not queries made by the application running on my local IIS.</p> <p>I ended up using <a href="http://www.wireshark.org/" rel="nofollow">Wireshark</a>. I found that when I went to save the response the stored procedure linked to Insert on the response Entity was being called. MySQL returned a response saying 0 rows were effected. This caused the exception. The application then called ROLLBACK removing any changes that should have been made.</p> <p>If I run that stored procedure in a terminal with the exact same values it works.</p> <p>So over the wire I see this being called:</p> <pre><code>CALL `DevDB`.`LogResponse` (1,'2012-03-06 12:30',1,1,'test','test','test','test',false,'test'); </code></pre> <p>Then 0 rows effected being returned and a ROLLBACK being called.</p> <p>Running the very same command from MySQL workbench works. I have tried this multiple times, dropping the database and starting over. I can't find a reason for this. The request is logged using a similar method and it works. </p> <p>The application has EXECUTE permissions on both stored routines.</p> <p>Has anyone come across something like this before?</p> <p>EDIT: I also use <a href="http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html" rel="nofollow">MySQL handlers</a> to catch any error I could think of and log it to an error log table. e.g.</p> <pre><code>DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; INSERT INTO `DevDB`.`tblDatabaseErrorLogs`(ERROR_MESSAGE, ERROR_CODE) VALUES( 'SQL Exception at LogResponse', 'Unknown' ); END; </code></pre> <p>So far none to the error codes I planned for - SQL Exception, SQL WARNING and a bunch of others - appear to have happened as the error log table is empty. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload