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:
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.
org.hibernate.StaleStateException: Unexpected row count: 0; expected: 1I 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
Cheers,
Prasanna K Rao
But, do you have another solution for the problem? With out change the parameter?