NonUniqueObjectException error inserting multiple rows, LAST_INSERT_ID () returns 0
I am using NHibernate / Fluent NHibernate in an ASP.NET MVC application with a MySQL database. I am working on an operation that reads quite a lot of data (as to how much is inserted), processes it, and ends up inserting (currently) about 50 records. I have a single ISession for a request that is created / destroyed in the request start / end event handlers (just like http://ayende.com/Blog/archive/2009/08/06/challenge-find-the- bug-fixes.aspx ) and I am reading data and adding new objects (as in section 16.3 at https://www.hibernate.org/hib_docs/nhibernate/html/example-parentchild.html ) and finally calling Flush ( ) in the session actually start all inserts.
Fetching data and lazy loading works fine and when I call Flush two new records are inserted (I manually check the table to find out) and then I get the following error:
NonUniqueObjectException: Another entity with the same id value was already associated with session: 0, from entity: ...
I'm new to NHibernate and while looking for a solution tried to explicitly set the Id property generator for both Native and Identity (this is a MySQL database and the Id column is an int with auto_increment) and explicitly setting the unsaved value for the Id property to 0. I'm all I get an error message.
I've also tried calling Flush at different times (effectively once per INSERT) and then getting the same error, but for an identity value other than 0, and at seemingly random points in the process (sometimes I don't get it all in this scenario, but sometimes I do at different points).
I'm not sure where to go from here. Any help or understanding would be appreciated.
EDIT: See answer below.
source to share
EDIT: I originally posted another "answer" that didn't actually solve the problem, but I want to document my results here for anyone else who might come across it.
After several days of trying to figure out the problem and fix it and being extremely frustrated that the problem seemed to go away for a while and then came back intermittently (making me think many times that the change I fixed when on in fact it is not), I believe that I have traced the real problem.
Several times after I enabled the log4net level for NHibernate before DEBUG, the problem went away, but I was finally able to get an error with this log level. The log includes the following lines:
Building an IDbCommand object for the SqlString: SELECT LAST_INSERT_ID()
...
NHibernate.Type.Int32Type: 15:10:36 [8] DEBUG NHibernate.Type.Int32Type: returning '0' as column: LAST_INSERT_ID()
NHibernate.Id.IdentifierGeneratorFactory: 15:10:36 [8] DEBUG NHibernate.Id.IdentifierGeneratorFactory:
Natively generated identity: 0
And looking at a few lines, I saw:
NHibernate.AdoNet.ConnectionManager: 15:10:36 [8] DEBUG NHibernate.AdoNet.ConnectionManager: aggressively releasing database connection
NHibernate.Connection.ConnectionProvider: 15:10:36 [8] DEBUG NHibernate.Connection.ConnectionProvider: Closing connection
It seems that by clearing the session and doing INSERT, NHibernate was closing the connection between the INSERT statement and "SELECT LAST_INSERT_ID ()" to get the ID that MySQL generated for the INSERT statement. Rather, I have to say that this would sometimes close the connection, which is one of the reasons I believe the problem was intermittent. I cannot find the link now, but I believe I also read in all my searches that MySQL will sometimes return the correct value from LAST_INSERT_ID () even if the connection is closed and reopened, which is another reason I believe that it is intermittent. Most of the time, however, LAST_INSERT_ID () returns 0 if the connection is closed and reopened after the INSERT.
There seem to be two ways to fix this problem. First of all, there is a patch that looks like it will evolve in NHibernate 2.1.1 or that you can use to create your own NHibernate assembly that makes INSERT and SELECT LAST_INSERT_ID () work together. Secondly, you can set connection.release_mode to on_close, as described in this blog post , which prevents NHibernate from closing the connection until the ISession is explicitly closed.
I used the last approach, which is done in FluentNHibernate like this:
Fluently.Configure()
...
.ExposeConfiguration(c => c.Properties.Add("connection.release_mode", "on_close"))
...
It also had the side effect of drastically speeding up my code. The time it took to execute 20-30 seconds (when it just happened before I made this change) now works after 7-10 seconds, so it does the same job in ~ 1/3 of the time.
source to share