Sql Error: Null Id INSERT for IdBag many to many table.

onevextchuck · **Posted:** Tue Apr 24, 2007 11:24 am

Hello,

The basic problem that we seem to be having is that we have a many to many relationship between a Library and an Asset modeled with Library containing a non-inverse IdBag with a guid.comb generator. A regular bag is used for the inverse collection in Asset. We have a setup routine that creates a Library and an Asset and establishes the relationship. In certain cases however, the default relationship is not correct and so the Asset is moved to the correct Library. When the session flushes, the Asset is successfully saved associated with the second(correct) Library. However, the first Library is also flushed along with its collection. Another SQL statement is run containing an Insert into the many-to-many relationship table with the old(incorrect) Library Id, the Asset Id and a null value for the IdBag Id(primary key).

In addition to the standard requested information included below, I can show a piece of NHibernate code that we found that seems to be causing our immediate problem. In NHibernate.Collection.Generic.PersistentIdentifierBag there is the following function:

Code:

private void BeforeRemove(int index)
{
   // Move the identifier being removed to the end of the list (i.e. it isn't actually removed).

   object removedId = identifiers[index];
   int last = values.Count - 1;
   for (int i = index; i < last; i++)
   {
      object id = identifiers[i + 1];
      if (id == null)
      {
         identifiers.Remove(i);
      }
      else
      {
         identifiers[i] = id;
      }
   }
   identifiers[last] = removedId;
}

The same function is also in NHibernate.Collection.PersistentIdentifierBag, but we only use generics and so have only found this problem in the generic implementation.

The problem seems to be the last line of code. When the function is called, 'values' contains one item because the item has not yet been removed from the collection. The item is removed in the line of code in 'RemoveAt' immediately after 'BeforeRemove' is called. Also index = 0. The identifiers are only added during 'PreInsert' as far as I can tell. However, since this collection has not yet been flushed, 'PreInsert' has not yet been called. Therefore identifiers does not contain anything, that is identifiers.Count == 0 -> identifiers[0] == null (no, it doesn't raise an exception, it returns null, at least on our computers around here) -> removedId == null. So when the last line of the method is hit, an identifier is inserted into the collection with a null value, identifiers.Count == 1. This causes the collection be flushed on the next session flush with a null value (guid in our case) as the Id for the element. Hence the not-null violation.

Our stop-gap was to throw an if (identifiers.Count > 0) around the rest of the method contents.

Code:

private void BeforeRemove(int index)
{
   // Move the identifier being removed to the end of the list (i.e. it isn't actually removed).

   if (identifiers.Count > 0)
   {
      object removedId = identifiers[index];
      int last = values.Count - 1;
      for (int i = index; i < last; i++)
      {
         object id = identifiers[i + 1];
         if (id == null)
         {
            identifiers.Remove(i);
         }
         else
         {
            identifiers[i] = id;
         }
      }
      identifiers[last] = removedId;
   }
}

This doesn't appear to be the best solution to me. Does it cover all the cases? Does this keep the identifiers collection in synch with the values collection? Is there a particular reason for this behavior? Would flushing the session before removing the Asset from the first Library fix the problem? I don't know. All I do know is that my unit tests are working again. Any other insights into this problem would be greatly appreciated. If this is a bug, please let me know and I will log it in Jira.

Thanks again for the great ORM and the help.

Hibernate version:
1.2.0.CR2

Mapping documents:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Savo.SAM.Business" assembly="Savo.SAM.Business">
   <joined-subclass name="Asset" table="`Asset`" extends="TaggableThing" lazy="false">
      <key column="taggable_thing_id" foreign-key="thing_id"/>
      <property name="AverageRating" access="field.camelcase-underscore">
         <column name="average_rating" not-null="true" />
      </property>
      <property name="AverageRatingCeiling" access="field.camelcase-underscore">
         <column name="average_rating_ceiling" not-null="true" />
      </property>
      <property name="Hot" access="field.camelcase-underscore">
         <column name="hot" not-null="true" />
      </property>
      <property name="HotPoints30DaysTrailing" access="field.camelcase-underscore">
         <column name="hot_points_30_days_trailing" not-null="true" />
      </property>
      <property name="HotPoints365DaysTrailing" access="field.camelcase-underscore">
         <column name="hot_points_365_days_trailing" not-null="true" />
      </property>
      <property name="HotPoints7DaysTrailing" access="field.camelcase-underscore">
         <column name="hot_points_7_days_trailing" not-null="true" />
      </property>
      <property name="HotPoints90DaysTrailing" access="field.camelcase-underscore">
         <column name="hot_points_90_days_trailing" not-null="true" />
      </property>
      <property name="HotPointsLifetime" access="field.camelcase-underscore">
         <column name="hot_points_lifetime" not-null="true" />
      </property>
      <property name="HotRank30DaysTrailing" access="field.camelcase-underscore">
         <column name="hot_rank_30_days_trailing" not-null="true" />
      </property>
      <property name="HotRank365DaysTrailing" access="field.camelcase-underscore">
         <column name="hot_rank_365_days_trailing" not-null="true" />
      </property>
      <property name="HotRank7DaysTrailing" access="field.camelcase-underscore">
         <column name="hot_rank_7_days_trailing" not-null="true" />
      </property>
      <property name="HotRank90DaysTrailing" access="field.camelcase-underscore">
         <column name="hot_rank_90_days_trailing" not-null="true" />
      </property>
      <property name="HotRankLifetime" access="field.camelcase-underscore">
         <column name="hot_rank_lifetime" not-null="true" />
      </property>
      <property name="StoredRatingCount" access="field.camelcase-underscore">
         <column name="stored_rating_count" not-null="true" />
      </property>
      <property name="Title" access="field.camelcase-underscore">
         <column name="title" not-null="true" />
      </property>
      <bag name="LibraryCollection" table="`Library_Asset`" lazy="true" inverse="true" access="field.camelcase-underscore">
         <key column="asset_id" />
         <many-to-many class="Library" column="library_id" />
      </bag>
      <bag name="RatingCollection" inverse="true" lazy="true" access="field.camelcase-underscore" cascade="delete">
         <key column="asset_id" />
         <one-to-many class="Rating" />
      </bag>
   </joined-subclass>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Savo.SAM.Business" assembly="Savo.SAM.Business">
   <class name="Library" table="`Library`" lazy="true">
      <id name="Id" access="field.camelcase-underscore">
         <column name="id" not-null="true" />
         <generator class="identity"/>
      </id>
      <property name="AllowAssetCreationThroughEmail" access="field.camelcase-underscore">
         <column name="allow_asset_creation_through_email" not-null="true" />
      </property>
      <property name="AllowPublicComments" access="field.camelcase-underscore">
         <column name="allow_public_comments" not-null="true" />
      </property>
      <property name="Created" access="field.camelcase-underscore">
         <column name="created" not-null="true" />
      </property>
      <property name="IsDeleted" access="field.camelcase-underscore">
         <column name="is_deleted" not-null="true" />
      </property>
      <property name="LibraryType" access="field.camelcase-underscore">
         <column name="library_type_id" not-null="true" />
      </property>
      <property name="Modified" access="field.camelcase-underscore">
         <column name="modified" not-null="true" />
      </property>
      <property name="Name" access="field.camelcase-underscore">
         <column name="name" not-null="true" />
      </property>
      <property name="NewPeriod" access="field.camelcase-underscore">
         <column name="new_period" not-null="true" />
      </property>
      <property name="PhotoFileName" access="field.camelcase-underscore">
         <column name="photo_file_name" />
      </property>
      <property name="ShortName" access="field.camelcase-underscore">
         <column name="short_name" not-null="true" />
      </property>
      <idbag name="AssetCollection" table="`Library_Asset`" lazy="true" inverse="false" access="field.camelcase-underscore">
         <collection-id column="id" type="guid">         
         <generator class="guid.comb"/>
        </collection-id>
         <key column="library_id" />
         <many-to-many class="Asset" column="asset_id" />
      </idbag>         
      <bag name="AssetPermissionCollection" inverse="true" lazy="true" access="field.camelcase-underscore" cascade="delete">
         <key column="library_id" />
         <one-to-many class="AssetPermission" />
      </bag>
      <many-to-one name="Creator" column="creator_id" class="User" access="field.camelcase-underscore" not-null="true" />
      <bag name="DefaultedUserCollection" inverse="true" lazy="true" access="field.camelcase-underscore" cascade="delete">
         <key column="default_library_id" />
         <one-to-many class="User" />
      </bag>
      <many-to-one name="FileType" column="file_type_id" class="FileType" access="field.camelcase-underscore" />
      <many-to-one name="Modifier" column="modifier_id" class="User" access="field.camelcase-underscore" not-null="true" />
      <many-to-one name="Owner" column="owner_id" class="User" access="field.camelcase-underscore" not-null="true" />
      <many-to-one name="Tenant" column="tenant_id" class="Tenant" access="field.camelcase-underscore" not-null="true" />
   </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
The code is extremely complex spanning many files. If a detailed example that causes the exception to occur is needed, please let me know and I will attempt to provide a simple reproduction.

Full stack trace of any exception that occurs:
at NHibernate.Persister.Collection.AbstractCollectionPersister.InsertRows(IPersistentCollection collection, Object id, ISessionImplementor session)
Savo.SAM.Business.UnitTest.AssetFixture.AssetTaggedActionTest : NHibernate.ADOException : could not insert collection rows: [Savo.SAM.Business.Library.AssetCollection#3]
----> System.Data.SqlClient.SqlException : Cannot insert the value NULL into column 'id', table 'SHAMU_601.dbo.Library_Asset'; column does not allow nulls. INSERT fails.
The statement has been terminated.
at NHibernate.Impl.ScheduledCollectionUpdate.Execute()
at NHibernate.Impl.SessionImpl.Execute(IExecutable executable)
at NHibernate.Impl.SessionImpl.ExecuteAll(IList list)
at NHibernate.Impl.SessionImpl.Execute()
at NHibernate.Impl.SessionImpl.AutoFlushIfRequired(ISet querySpaces)
at NHibernate.Impl.SessionImpl.GetQueries(String query, Boolean scalar)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters, IList results)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters)
at NHibernate.Impl.QueryImpl.List()
at Savo.SAM.Data.NHibernateProvider.Repositories.AssetPermissionRepository.CreatePermissionHash(User user) in C:\SourceCode\Shamu\trunk\Savo.SAM.Data.NHibernateProvider\Repositories\AssetPermissionRepository.vb:line 20
at Savo.SAM.Business.AssetPermission.CreatePermissionHash(User user) in C:\SourceCode\Shamu\trunk\Savo.SAM.Business\AssetPermission.vb:line 44
at Savo.SAM.Business.Permissions.PermissionCache.RefreshCache(Boolean override) in C:\SourceCode\Shamu\trunk\Savo.SAM.Business\Permissions\PermissionCache.vb:line 64
at Savo.SAM.Business.Permissions.PermissionCache.RefreshCache() in C:\SourceCode\Shamu\trunk\Savo.SAM.Business\Permissions\PermissionCache.vb:line 52
at Savo.SAM.Business.Permissions.PermissionCache..ctor(Int32 expirationSeconds) in C:\SourceCode\Shamu\trunk\Savo.SAM.Business\Permissions\PermissionCache.vb:line 22
at Savo.SAM.Business.Permissions.PermissionFactory.CreatePermissions(User user) in C:\SourceCode\Shamu\trunk\Savo.SAM.Business\Permissions\PermissionFactory.vb:line 15
at Savo.SAM.ApplicationContext.WindowsApplicationContext.SetPermissions(User user) in C:\SourceCode\Shamu\trunk\Savo.SAM.ApplicationContext\WindowsApplicationContext.vb:line 8
at Savo.SAM.ApplicationContext.WindowsApplicationContext.OverrideAuthenticatedUser(User user) in C:\SourceCode\Shamu\trunk\Savo.SAM.ApplicationContext\WindowsApplicationContext.vb:line 18
at Savo.SAM.Business.UnitTest.EntityManager.ResetAuthorizedUser() in C:\SourceCode\Shamu\trunk\Savo.SAM.Business.UnitTest\EntityManager.vb:line 38
at Savo.SAM.Business.UnitTest.EntityManager.CreatePost(User user, Boolean useUnitTestUser) in C:\SourceCode\Shamu\trunk\Savo.SAM.Business.UnitTest\EntityManager.vb:line 415
at Savo.SAM.Business.UnitTest.EntityManager.CreatePost() in C:\SourceCode\Shamu\trunk\Savo.SAM.Business.UnitTest\EntityManager.vb:line 371
at Savo.SAM.Business.UnitTest.AssetFixture.AssetTaggedActionTest() in C:\SourceCode\Shamu\trunk\Savo.SAM.Business.UnitTest\AssetFixture.vb:line 168
--ADOException
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Collection.AbstractCollectionPersister.InsertRows(IPersistentCollection collection, Object id, ISessionImplementor session)

Name and version of the database you are using:
Sql Server 2005

The generated SQL (show_sql=true):
This is only the offending statement. If more is needed, please let me know.
NHibernate: INSERT INTO dbo.[Library_Asset] (library_id, id, asset_id) VALUES (@p0, @p1, @p2); @p0 = '3', @p1 = '', @p2 = '6031'

Debug level Hibernate log excerpt:
Having trouble with log4net at the moment. Though if a better example is needed, I can get this going as well.

onevextchuck · **Posted:** Tue Apr 24, 2007 4:05 pm

I have made a change to our stop-gap code from before. The current code looks like this.

Code:

private void BeforeRemove(int index)
{
   // Move the identifier being removed to the end of the list (i.e. it isn't actually removed).

   if (index < identifiers.Count)
   {
      object removedId = identifiers[index];
      int last = values.Count - 1;
      for (int i = index; i < last; i++)
      {
         object id = identifiers[i + 1];
         if (id == null)
         {
            identifiers.Remove(i);
         }
         else
         {
            identifiers[i] = id;
         }
      }
   identifiers[last] = removedId;
   }
}

The change is that instead of checking if the identifiers.Count > 0, we are now checking that the index < identifiers.Count. This prevents assigning a new value after the end of the current identifiers collection.

I believe that we are seeing this error again because changes were made to our code that caused a persisted collection to get loaded from the DB thereby containing valid values and identifiers. An item is added to the collection and then removed before a flush. When RemoveAt is called, the same problem as before occurs.

I feel a little bit better about the current fix we have. This only appears to be a problem with new values added to the collection that have not yet been persisted so we can ignore those identifiers (which don't exist yet anyway) when removing items from the value collection. I am still nowhere near 100% on this though and am really not even sure that this is not just a problem with the way we are using NHibernate.

Again thanks for any help you might have.