SQL Server "no count", and Hibernate StaleStateException

Recently an application my company wrote had suddenly stopped working. We were getting an exception from Hibernate in some maintenance tasks we were doing on server startup. The exception string was: org.hibernate.StaleStateException: Unexpected row count: 0; expected: 1

I wondered how this could even be possible since no one was in the DB at the time the server was starting up. The person who reported this error said it was only happening on this one instance of MS SQL server, and no where else.

So I proceeded to debug remotely from my workstation to the server instance that failing. I noticed that the message on the StaleStateException was that the number of updated rows "0" did not match the expected row count of "1". This was even more confounding to me as I could even see that the row existed in the database. Plus, Hibernate would re-inserted the row if I deleted it, and yet I would _still_ get the StaleStateException.

I should have listened to the person who reported the problem more closely and realized that this was a "server" related issue, and I might have saved myself some time.

I finally got around to checking the configuration of the DB server instance that was exhibiting this problem, and I noticed that someone had turned on "no count" as a default connection setting. Setting "no count" basically tells SQL server not to report back how many rows were affected by the last operation executed. Of course, this is a big problem for Hibernate which uses those row counts to determine if an update was unsuccessful so that it can try an insert on a session.saveOrUpdate() call.

You can find this "no count" setting by going to SQL Server Manager, right clicking on the DB Server instance you have registered in the Object Explorer, and selecting "Properties". Next, select the Connections page, and look in the "Default connection options" list for the "no count" setting. It must be unchecked for Hibernate to function properly.

There are of course lots of other reasons you can see this error with Hibernate, but this is certainly one I hadn't encountered before.

Comments

Sri Prasanna said…
Thanks a lot mate. You saved me from going mad!!!

Cheers,
Prasanna K Rao
December 06, 2011 8:10 PM
Pablo dos Reis said…
Thanks, It also helps me a lot.
But, do you have another solution for the problem? With out change the parameter?
May 23, 2013 10:40 AM
Post a Comment

Popular posts from this blog

Using Snapshot Isolation with SQL Server and Hibernate

In the course of working on some deadlock issues, I found that MS SQL Server exhibits some unexpected (to me, at least) locking behaviors that can affect performance and cause deadlocks. In the end, I found that SQL Server provides an isolation level called Snapshot isolation which removes the need for locks on rows and removes lock contention. To use this isolation level, you need to execute the following SQL on the database that you want to use the isolation level with (replacing MyDatabase with your database name, of course): ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON Next, you need to modify your hibernate.cfg.xml file to add a property to tell Hibernate to use the Snapshot Isolation level: <!-- The 4096 isolation level is the setting to use with the jTDS or Microsoft JDBC drivers --> < property name =”hibernate.connection.isolation”>4096</property> Snapshot isolation is not a panacea...
Read more

FreeNAS, CIFS, and user login problems

Image
I have been in the process of converting from the dead-end Windows Home Server product to a FreeNAS server for my home NAS.  I was trying to replicate the same sort of set up that WHS had for user directories.  In WHS, there was a share called "Users" and under that share there were various user directories corresponding to each user account.  I thought it would be pretty simple to replicate that same behavior to allow users to simply attach to \\nas-server-name\Users in Windows, and then navigate to their own user folder.  What follows is how I achieved this, as well as a problem I fought with for a while that prevented certain users from connecting via CIFS. First, I started off the process by installing FreeNAS to a USB stick, added a few disks in the server, plugged in the USB stick and booted up.  I performed basic config for the networking, and admin account passwords, and turned on the CIFS service. Next, I created a some user accounts fo...
Read more

Official Pandora Desktop and Hotkey Support?

EDIT: There is a slight problem occasionally with this script not being able to send keys to the Pandora Desktop Air app. I've got an updated version of the script that you can use instead of this one. Pandora is a different sort of online music service that uses the work of the Music Genome Project to automatically selects music similar to an artist or particular song, and then streams it over the Internet for your listening pleasure. You can't select individual songs, but you can skip ones you don't like and make as many music channels as you like to suit your particular tastes. Pandora provides a free version of the product that is supported by visual advertising, and a very inexpensive ($36 USD per year as of the creation of this entry) subscription supported version of the product that provides higher quality streams and no advertising. Traditionally, Pandora has been a Flash-based application that required you to have your bulky browser open to listen on the deskto...
Read more