Databases
StackOverflow2013
Postsdbo
PONHibernate and MySQL Nullable object must have a value
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONHibernate and MySQL Nullable object must have a value
    primarykey
    data
    text
    <p>I'm having a problem that I would appreciate some guidance on where to start on. Let me first set the scene.</p> <p>I've recently migrated from SQL Server to MySQl database, all integration tests passed against SQL Server now one is failing since the migration with this exception:</p> <pre><code>NHibernate.Exceptions.GenericADOException : could not execute query [ select post0_.Id as Id1_, post0_.CreatedAt as CreatedAt1_, post0_.Title as Title1_, post0_.Body as Body1_, post0_.CreatedBy as CreatedBy1_, post0_.ParentId as ParentId1_ from Posts post0_, Users user1_ where post0_.CreatedBy=user1_.Id and ((user1_.Username is null) and (? is null) or user1_.Username=?) and (post0_.ParentId is null) order by post0_.CreatedAt desc ] Name:p1 - Value:username1 [SQL: select post0_.Id as Id1_, post0_.CreatedAt as CreatedAt1_, post0_.Title as Title1_, post0_.Body as Body1_, post0_.CreatedBy as CreatedBy1_, post0_.ParentId as ParentId1_ from Posts post0_, Users user1_ where post0_.CreatedBy=user1_.Id and ((user1_.Username is null) and (? is null) or user1_.Username=?) and (post0_.ParentId is null) order by post0_.CreatedAt desc] ----&gt; System.InvalidOperationException : Nullable object must have a value </code></pre> <p>.</p> <p>I'm using NHibernate v3 with POCO models and handcrafted SQL (using DBDeploy.NET to manage database versioning)</p> <p>It's possible, and most likely, that I've made an error when converting the schema creation SQL from SQL Server to MySQL but what is perplexing me is I get this error even when a query results in no records.</p> <p>Why is NHibernate trying to create any objects? What am I not understanding about how it works?</p> <p>This is an example of one of the queries. This raised the exception when rows are returned. The only column that returns null, by design, is the Post.ParentId column.</p> <pre><code>return GetSession() .Query&lt;Post&gt;() .Where(p =&gt; p.CreatedBy.Username == username &amp;&amp; p.Parent == null) .OrderByDescending(p =&gt; p.CreatedAt) .Take(count) .ToList(); </code></pre> <p>The map files and the classes haven't changed but have included them for reference below.</p> <p>Would really welcome any thoughts I where I should be looking.</p> <p>The Post map</p> <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt; &lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="clubrd.service" namespace="clubrd.service"&gt; &lt;class name="Post" table="Posts"&gt; &lt;id name="Id"&gt; &lt;generator class="guid.comb"/&gt; &lt;/id&gt; &lt;property name="CreatedAt"/&gt; &lt;property name="Title" column="Title"/&gt; &lt;property name="Body"/&gt; &lt;many-to-one name="CreatedBy" column="CreatedBy"/&gt; &lt;many-to-one name="Parent" column="ParentId"/&gt; &lt;set name="Children"&gt; &lt;key column="ParentId"/&gt; &lt;one-to-many class="Post"/&gt; &lt;/set&gt; &lt;/class&gt; &lt;/hibernate-mapping&gt; </code></pre> <p>The User map, referenced here as Post.CreatedBy</p> <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt; &lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="clubrd.service" namespace="clubrd.service"&gt; &lt;class name="User" table="Users"&gt; &lt;id name="Id"&gt; &lt;generator class="guid.comb"/&gt; &lt;/id&gt; &lt;property name="Username" not-null="true"/&gt; &lt;property name="Email" not-null="true"/&gt; &lt;property name="PasswordHash"/&gt; &lt;property name="PasswordSalt"/&gt; &lt;one-to-one name="Profile" class="Profile" /&gt; &lt;/class&gt; &lt;/hibernate-mapping&gt; </code></pre> <p>The Post class</p> <pre><code>public class Post : Entity { public virtual string Title { get; set; } public virtual string Body { get; set; } public virtual Post Parent { get; set; } public virtual ICollection&lt;Post&gt; Children { get; set; } } </code></pre> <p>The User class</p> <pre><code>public class User : Entity { public virtual string Name { get { return this.Username; } } public virtual string Username { get; set; } public virtual string PasswordHash { get; set; } public virtual string PasswordSalt { get; set; } public virtual string Email { get; set; } public virtual Profile Profile { get; set; } public virtual ICollection&lt;ObjectRole&gt; Roles { get; set; } } </code></pre> <p><strong>edit</strong> Full output at error point plus the preceding NHibernate logs showing the entity being constructed.</p> <pre><code>NHibernate.Cfg.SettingsFactory: 2010-12-18 15:56:20,159 INFO Default entity-mode: Poco NHibernate.Cfg.SettingsFactory: 2010-12-18 15:56:20,160 INFO Named query checking : enabled NHibernate.Impl.SessionFactoryImpl: 2010-12-18 15:56:20,211 INFO building session factory NHibernate.Impl.SessionFactoryObjectFactory: 2010-12-18 15:56:20,580 INFO no name configured NHibernate.Hql.Ast.ANTLR.Tree.FromElement: 2010-12-18 15:56:21,389 INFO handling property dereference [clubrd.service.Post (p) -&gt; CreatedBy (Class)] NHibernate.Hql.Ast.ANTLR.Tree.FromElement: 2010-12-18 15:56:21,406 INFO handling property dereference [clubrd.service.User () -&gt; Username (Class)] NHibernate.Hql.Ast.ANTLR.Tree.FromElement: 2010-12-18 15:56:21,416 INFO handling property dereference [clubrd.service.Post (p) -&gt; CreatedBy (Class)] NHibernate.Hql.Ast.ANTLR.Tree.FromElement: 2010-12-18 15:56:21,417 INFO handling property dereference [clubrd.service.User () -&gt; Username (Class)] NHibernate.Hql.Ast.ANTLR.Tree.FromElement: 2010-12-18 15:56:21,419 INFO handling property dereference [clubrd.service.Post (p) -&gt; Parent (Class)] NHibernate.Hql.Ast.ANTLR.Tree.FromElement: 2010-12-18 15:56:21,424 INFO handling property dereference [clubrd.service.Post (p) -&gt; CreatedAt (Class)] NHibernate.Util.ADOExceptionReporter: 2010-12-18 15:56:21,564 WARN System.InvalidOperationException: Nullable object must have a value. at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource) at System.Nullable`1.get_Value() at NHibernate.Dialect.MySQLDialect.GetLimitString(SqlString querySqlString, Int32 offset, Int32 limit, Nullable`1 offsetParameterIndex, Nullable`1 limitParameterIndex) in d:\CSharp\NH\nhibernate\src\NHibernate\Dialect\MySQLDialect.cs:line 197 at NHibernate.Loader.Loader.PrepareQueryCommand(QueryParameters queryParameters, Boolean scroll, ISessionImplementor session) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1119 at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 399 at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 236 at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1665 NHibernate.Util.ADOExceptionReporter: 2010-12-18 15:56:21,576 ERROR Nullable object must have a value. NHibernate.Util.ADOExceptionReporter: 2010-12-18 15:56:21,577 WARN System.InvalidOperationException: Nullable object must have a value. at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource) at System.Nullable`1.get_Value() at NHibernate.Dialect.MySQLDialect.GetLimitString(SqlString querySqlString, Int32 offset, Int32 limit, Nullable`1 offsetParameterIndex, Nullable`1 limitParameterIndex) in d:\CSharp\NH\nhibernate\src\NHibernate\Dialect\MySQLDialect.cs:line 197 at NHibernate.Loader.Loader.PrepareQueryCommand(QueryParameters queryParameters, Boolean scroll, ISessionImplementor session) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1119 at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 399 at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 236 at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1665 NHibernate.Util.ADOExceptionReporter: 2010-12-18 15:56:21,579 ERROR Nullable object must have a value. NHibernate.Exceptions.GenericADOException : could not execute query [ select post0_.Id as Id1_, post0_.CreatedAt as CreatedAt1_, post0_.Title as Title1_, post0_.Body as Body1_, post0_.CreatedBy as CreatedBy1_, post0_.ParentId as ParentId1_ from Posts post0_, Users user1_ where post0_.CreatedBy=user1_.Id and ((user1_.Username is null) and (? is null) or user1_.Username=?) and (post0_.ParentId is null) order by post0_.CreatedAt desc ] Name:p1 - Value:username1 [SQL: select post0_.Id as Id1_, post0_.CreatedAt as CreatedAt1_, post0_.Title as Title1_, post0_.Body as Body1_, post0_.CreatedBy as CreatedBy1_, post0_.ParentId as ParentId1_ from Posts post0_, Users user1_ where post0_.CreatedBy=user1_.Id and ((user1_.Username is null) and (? is null) or user1_.Username=?) and (post0_.ParentId is null) order by post0_.CreatedAt desc] ----&gt; System.InvalidOperationException : Nullable object must have a value. at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs: line 1674 at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs: line 1589 at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs: line 1583 at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Hql\Ast\ANTLR\Loader\QueryLoader.cs: line 298 at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Hql\Ast\ANTLR\QueryTranslatorImpl.cs: line 110 at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) in d:\CSharp\NH\nhibernate\src\NHibernate\Engine\Query\HQLQueryPlan.cs: line 105 at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs: line 658 at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs: line 634 at NHibernate.Impl.ExpressionQueryImpl.List() in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\ExpressionQueryImpl.cs: line 63 at NHibernate.Linq.NhQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) in d:\CSharp\NH\nhibernate\src\NHibernate\Linq\NhQueryProvider.cs: line 78 at NHibernate.Linq.NhQueryProvider.Execute(Expression expression) in d:\CSharp\NH\nhibernate\src\NHibernate\Linq\NhQueryProvider.cs: line 27 at NHibernate.Linq.NhQueryProvider.Execute(Expression expression) in d:\CSharp\NH\nhibernate\src\NHibernate\Linq\NhQueryProvider.cs: line 102 at Remotion.Data.Linq.QueryableBase`1.GetEnumerator() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList(IEnumerable`1 source) at clubrd.data.Repository.PostRepository.UsersLatestPosts(String username, Int32 count) in PostRepository.cs: line 13 at clubrd.data.integrationtest.Repository.PostRepositoryTests.UsersPosts() in PostRepositoryTests.cs: line 103 --InvalidOperationException </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USadambird
    1. USadambird
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCan you narrow it down, make the question a bit shorter? Also, please post the exception stack trace.
      singulars
      1. PONHibernate and MySQL Nullable object must have a value
      1. USMauricio Scheffer
    2. COI'm struggling to narrow it down looking for pointers of where to start really. Have appended the full output. It seems odd to me that I get this error in another section of the application when there are no results returned from the query. If there's no instance, why is an assignment being attempted?
      singulars
      1. PONHibernate and MySQL Nullable object must have a value
      1. USadambird
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload