JDBC Error: "Batch update returned unexpected row count" but only for specific user accounts. - by rkite

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 466473 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 6/11/2009 3:16:05 PM
Access Restriction Public


   In a nutshell here is the problem, the text below has more detail.  We have a defined database role that allows a mid level user to have permissions to 7 tables which a regular user can not access.  The insert seems to work but returns the incorrect row count when inserting into any of the 7 tables in the role.

    int resultCount =  prepareStatement.executUpdate(); 

    The JDBC prepared above returns -1 resultCount when when the statement appears to work.  The Hibernate ORM engine checks the result and raises an exception since it was expecting a count of 1 to be returned.  I realize this was fixed for the latest 2.0 release of the SQLServer JDBC driver but it still happens to us in special cases.  I can get the error to happen through EJB 3.0 or just submitting SQL through a connection from the JBoss connection pool.      

  We are using JBoss 4.2.3 and have it setup so the connection pool uses the authenticated users login when accessing the database.  For our super user account there are no problems.  For any account that has been granted a specific mid level user role with, INSERT, SELECT, UPDATE and DELETE permissions to 7 additional tables, the error happens for the first 15 or so attempted insert sql statements then it seems to correct itself.  When the application server is restarted the problem returns for the first 15 or so SQL statements then magically the insert starts returning a count of 1 which is correct.  If we add our mid level user role to the super user account the problem happens for that login as well.  Almost like adding a role has taken away a permission from the super user.  The error is happening when inserting into any of the 7 additional table that are contained in the mid level users role.

Here is the full error:
"javax.ejb.EJBException: javax.persistence.OptimisticLockException: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: -1; expected: 1"

  We are moving a large application from Sybase to SQL Server.   We did not have this issue with Sybase so I am assuming there is a bug in the JDBC driver.

Sign in to post a comment.
Posted by Microsoft on 6/23/2009 at 3:04 PM
Hi rkite,

Thank you for your interest in SQL Server and the Microsoft SQL Server JDBC Driver!

Given the potential number of variables involved (application server, connection pooling, roles, a new database and JDBC driver) and the apparent transient nature of the problem, I have to suggest that you would be better served by opening a case with Microsoft Customer Support to investigate. They have the personnel and expertise to work with you one on one to get the problem scoped, reproduced, investigated, and solved. The community feedback process (Microsoft Connect) unfortunately is not well-suited to an investigation that may require a lot of communication back and forth.

That said, I can offer up one thing to check for: SET NOCOUNT ON. If this is executed on the same connection (not necessarily on the same statement or the same thread) anywhere prior to the INSERT, then the executeUpdate() call will indeed return -1 as SQL Server does not return update counts after SET NOCOUNT ON has been executed.

--David Olix [SQL Server]
Posted by rkite on 6/12/2009 at 1:39 PM
Correction: I mean setting the value to greater than 0 causes the driver to use JDBC 2.0+ function calls.
Posted by rkite on 6/12/2009 at 1:37 PM
Added the following 2 properties to the persistence.xml file and the problem seems to have gone away.
<property name="hibernate.jdbc.batch_size" value="30"/>
<property name="hibernate.max_fetch_depth" value="5"/>
I am not sure why this fixed it. I read in a few places on the internet that not setting the hibernate.jdbc.batch_size property to a value greater than 0 forces hibernate to only use JDBC 2.0 or higher function calls.