Thursday, 8 December 2016

NHibernate.StaleStateException : Unexpected row count

Situation:
Console application with NHibernate and a MySQL database. The application read several (large) files with data and process it to the database. For several reasons, all entries that are read from the files needs to be compared with existing entries in the database. New one's should be added, existing entries should be updated (if needed) and entries that does not exist anymore in the import files needs to be update (property set to inactive = true). So far so good.

Problem:
I ran several times into the NHibernate.StaleStateException with the unexpected row count message.
I changed from session.save() and session.flush() to a transaction with commit way of dealing with the objects. I also started to use Parallel.ForEach when processing the read entries for performance reasons. During testing (with smaller files) everything went well. Till I started to read files from over 15Mb (I got even files of > 60Mb). That was the moment that those NHibernate.StaleStateException's were fired.

Solution:
After searching a lot and reading a lot of flushing, saves, transactions and versions I still got not the correct answer for my problem.
However, I've found the solution. It was not really an Nhibernate issue, but a MySQL issue. I had to change a few database parameters:
  • innodb_buffer_pool_size. I changed the default value of 8M to 512M. This speed up the entire process a lot. On production server(s) this value can be set higher as well (depending on the available RAM of the machine).
  • innodb_log_file_size. I changed this one to 128M
  • max_allowed_packet. If this value is to small, NHibernate will fire those StaleStateExceptions if you are processing such amount of data(files). So I changed it to 100M
Those parameter you can find in the my.ini file of MySQL (location of my file is at C:\ProgramData\MySQL\MySQL Server 5.7\my.ini). More info of those parameters can be found in the my.ini file.
After changing those parameters, you need to restart the MySQL service.

For me, those 3 db-parameters did the trick.

No comments: