Generating Wrong Columns on Queries

Question

We are having an intermittent problem with NHibernate where it will occasionally generate a query with a wrong column on the SQL. If we restart the application the problem ceases to happen (sometimes it requires more than one restart). When the problem occurs, during the lifetime of that process, it always produces the wrong SQL for the affected entity. It´s not always the same affected entity.

It´s an ASP.NET application where the SessionFactory is created during the Application_Start event. All the configuration and mapping are done by code.

We don´t have any more ideas how to test or debug the application, and I´m starting to assume there´s some bug in NHibernate, since the application fixes itself upon restart. Any ideas/tips will be much appreciated!

Here´s an example:

Entity

namespace Example.Clinicas
{
    public partial class Clinica : Entidade   // Abstract base class that has a property Handle
    {
        public virtual string Ddd { get; set; }
        public virtual string Ddd2 { get; set; }
        public virtual long? Duracao { get; set; }
        public virtual string Numero { get; set; }
        public virtual string Numero2 { get; set; }
        public virtual string Prefixo { get; set; }
        public virtual string Prefixo2 { get; set; }
        public virtual long? HandlePrestador { get; set; }
        public virtual Example.Prestadores.Prestador Prestador { get; set; }
    }
}

Mapping

namespace Example.Clinicas.Mappings
{
    public class ClinicaMapping : ClassMapping<Clinica>
    {
        public ClinicaMapping() 
        {
            Table("CLI_CLINICA");

            Id(x => x.Handle, map => 
            {
                map.Column("HANDLE");
                map.Generator(Generators.Sequence, g => g.Params(new { sequence = "SEQ_AUTO1816" }));
            });
            Property(x => x.Ddd, map => map.Column( c=> 
            {
                c.Name("DDD1");
                c.Length(4);
            }));
            Property(x => x.Ddd2, map => map.Column( c=> 
            {
                c.Name("DDD2");
                c.Length(4);
            }));
            Property(x => x.Duracao, map => map.Column("INTERVALOAGENDA"));
            Property(x => x.Numero, map => map.Column( c=> 
            {
                c.Name("NUMERO1");
                c.Length(5);
            }));
            Property(x => x.Numero2, map => map.Column( c=> 
            {
                c.Name("NUMERO2");
                c.Length(5);
            }));
            Property(x => x.Prefixo, map => map.Column( c=> 
            {
                c.Name("PREFIXO1");
                c.Length(5);
            }));
            Property(x => x.Prefixo2, map => map.Column( c=> 
            {
                c.Name("PREFIXO2");
                c.Length(5);
            }));
            Property(x => x.HandlePrestador, map => map.Column("PRESTADOR"));
            ManyToOne(x => x.Prestador, map => 
            { 
                map.Column("PRESTADOR");
                map.Insert(false);
                map.Update(false);
            });
        }
    }
}

Command

Session.Query<Clinica>().FirstOrDefault();

Generated SQL

select HANDLE489_,
       DDD2_489_,
       DDD3_489_,
       INTERVAL4_489_,
       NUMERO5_489_,
       NUMERO6_489_,
       PREFIXO7_489_,
       FATURADE8_489_,
       PRESTADOR489_
  from (select clinica0_.HANDLE               as HANDLE489_,
               clinica0_.DDD1                 as DDD2_489_,
               clinica0_.DDD2                 as DDD3_489_,
               clinica0_.INTERVALOAGENDA      as INTERVAL4_489_,
               clinica0_.NUMERO1              as NUMERO5_489_,
               clinica0_.NUMERO2              as NUMERO6_489_,
               clinica0_.PREFIXO1             as PREFIXO7_489_,
               clinica0_.FATURADEPARCELAMENTO as FATURADE8_489_,
               clinica0_.PRESTADOR            as PRESTADOR489_
          from CLI_CLINICA clinica0_)
 where rownum <= 1

Exception

ORA-00904: "CLINICA0_"."FATURADEPARCELAMENTO": invalid identifier

Interesting Observations:

• It is more likely to affect bigger entities (that has a higher number of properties), but also affects smaller entities occasionally;
• The generated SQL always have the same number of columns as mapped properties;
• The columns on the SQL are in the same order as the mapped properties on the mapping class;
• The wrong column will replace an existing one;
• The wrong column is a valid column in a different mapped entity;
• There is no relationship between the affected entity and the one that has the wrong column;

Other Details:

• .NET Version: 4.0
• NHibernate Version: 3.3.3.400
• Mapping by Code: NHibernate.Mapping.ByCode
• Configuration by Code: NHibernate.Cfg

Load Mappings

var mapper = new ModelMapper();

foreach (var assembly in resolver.GetAssemblies()) // resolver is a class that gets all the assemblies for the current application
    mapper.AddMappings(assembly.GetExportedTypes());

var mapping = mapper.CompileMappingForAllExplicitlyAddedEntities();

return mapping;

SessionFactory Configuration

var configure = new Configuration();
configure.DataBaseIntegration(x =>
                                  {
                                      x.Dialect<Oracle10gDialect>();  // Custom class
                                      x.ConnectionString = ConnectionString;
                                      x.BatchSize = 100;
                                      x.Driver<OracleMultiQueryDataClientDriver>();  // Custom class
                                      x.MaximumDepthOfOuterJoinFetching = 10;
                                      x.Timeout = 250;
                                      x.PrepareCommands = true;
                                      x.HqlToSqlSubstitutions = "true 'S', false 'N', yes 'S', no 'N'";
                                      x.LogFormattedSql = true;
                                      x.LogSqlInConsole = true;
                                      x.AutoCommentSql = true;
                                      x.IsolationLevel = IsolationLevel.ReadCommitted;
                                      x.ConnectionProvider<ConnectionProvider>();  // Custom class
                                  });
configure.Properties.Add(new KeyValuePair<string, string>("hibernate.command_timeout", "250"));
configure.Proxy(x => x.ProxyFactoryFactory<NHibernate.Bytecode.DefaultProxyFactoryFactory>());
configure.LinqToHqlGeneratorsRegistry<LinqToHqlGeneratorsRegistry>();
configure.CurrentSessionContext<NHibernate.Context.WebSessionContext>();
var mapping = GetMappings(); // Method showed above
mapping.autoimport = false;
configure.AddMapping(mapping);
var listener = new AuditEventListener();
configure.EventListeners.PostInsertEventListeners = new IPostInsertEventListener[] { listener };
configure.EventListeners.PostUpdateEventListeners = new IPostUpdateEventListener[] { listener };
configure.SessionFactory().GenerateStatistics();
return configure;

Hollar

Do you have more than one entity mapped to the same database table? – Johnjohna

There are some, but not in this case. Clinica is the only mapped entity to CLI_CLINICA table. – Hollar

Does it always reference the same invalid column? – Aqaba

More questions: Can you post the code for resolver? Do you subclass CLINICA? – Johnjohna

@mxmissile, apparently yes. We have noticed situations where an entity has produced a different SQL with another invalid columns, but we are unsure there weren't changes between the situations (it's a large code base, so it's likely a developer edited at least one entity in the project). What we can affirm is that for the cases where we've monitored and we are sure there weren't code modification, it always produced the same invalid column. – Hollar

@rivarolle, I don't have access to my development machine right now, but it's very simple, it just gets all assemblies in the AppDomain that have our base namespace. We don't subclass Clinica. – Hollar

You are using Nhibernate's Linq Query<>. What is the result if you use a QueryOver<>? try: Session.QueryOver<Clinica>().SingleOrDefault(); – Flay

TedOnTheNet, we just tried. Since there's more than one row in the table, using SingleOrDefault directly raises an exception, so we had to use .QueryOver<Clinica>.Take(1).SingleOrDefault(). Doing so gave us the same exception. – Hollar

Have exactly same issue. Did you find a solution? – Stockton

you should post/lodge this as a bug/thread to the sdk support site, porbably other encountering same issue may have workaround for it. – Franza

NHibernate is an opensource framework, you can try to debug – Earthen

Answer 1

I asked the same question on the NHibernate Users Google Groups forum, and someone thinks they have worked out the root cause (and have also proposed a solution):

https://groups.google.com/forum/#!topic/nhusers/BZoBoyWQEvs

The problem code is in PropertyPath.Equals(PropertyPath) which attempts to determine equality by only using the hash code. This works fine for smaller code bases as the default Object.GetHashCode() returns a sequential object index. However, after garbage collection, these indices get reused as finalized objects are removed and new objects are created...which results in more than one object getting the same hashcode...Once garbage collection kicks in, property paths have a chance to share the same hashcode which means they will ultimately mix up their customizers for the colliding properties, thus the wrong column names...

If you want to fix this the bug, you can patch the NH source code:

If you have your own copy of the NH source, you can fix the bug by changing NHibernate/Mapping/ByCode/PropertyPath.cs line #66 from:

return hashCode == other.GetHashCode();

To:

return hashCode == other.GetHashCode() && ToString() == other.ToString();

Please check out the Google Group for full details of the issue.

Threw

Answer 2

Check your querylog to see what type of query its runnig, in your sql from there you, can spot the problem.

Diegodiehard

Answer 3

it looks like the "creditcard payments" FATURADEPARCELAMENTO is a property on your "lender" object PRESTADOR, if this is the case it needs to be a reference and NOT a property in the mapping. Hope that helps or at least gets you pointed in the correct direction

the reference would take the place of your line Property(x => x.HandlePrestador, map => map.Column("PRESTADOR")); and would be something close to References(x => x.HandlePrestador)

Catabolite