升级nHibernate v5.x后Oracle数据库DateTime错误解决方案问询

问题概述 #

我们近期将nHibernate从v4.x版本升级至v5.3.9版本后，出现日期时间字段相关异常。经排查定位，问题由用作无日期标识的DateTime.MaxValue引发：

• 升级前日期存入数据库时会自动截断毫秒，读写均正常
• 升级后该值可正常写入Oracle数据库，但读取时间戳字段时会抛出异常
• 问题仅在Oracle环境出现，Microsoft SQL Server不受影响

错误堆栈 #

2021-10-22 14:23:05,141 [9] ERROR EafManagement.Core.NHibernateExtensions - Error during transaction commit
NHibernate.Exceptions.GenericADOException: could not execute query
[ select id1_12_,isdeleted2_12_,islocked3_12_,uuid4_12_,creationdate5_12_,isretired6_12_,retiredate7_12_,........... where rownum <=1 ]
  Name:p1 - Value:Test  Name:p2 - Value:1  Name:p3 - Value:0  Name:p4 - Value:0
[SQL: select id1_12_,isdeleted2_12_,islocked3_12_,uuid4_12_,creationdate5_12_,isretired6_12_,retiredate7_12_,........ where rownum <=1] ---> System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime.
   at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)
   at System.DateTime..ctor(Int32 year, Int32 month, Int32 day, Int32 hour, Int32 minute, Int32 second, Int32 millisecond)
   at Oracle.ManagedDataAccess.Types.DateTimeConv.ToDateTime(Byte[] byteRep, Boolean isNotTimeStampTZ, Int32 offset, Int32 length)
   at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDateTime(Int32 i)
   at Oracle.ManagedDataAccess.Client.OracleDataReader.GetValue(Int32 i)
   at NHibernate.Type.AbstractDateTimeType.GetDateTime(DbDataReader rs, Int32 index, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Type\AbstractDateTimeType.cs:line 80
   at NHibernate.Type.AbstractDateTimeType.Get(DbDataReader rs, Int32 index, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Type\AbstractDateTimeType.cs:line 59
   at NHibernate.Type.NullableType.NullSafeGet(DbDataReader rs, String name, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Type\NullableType.cs:line 235
   at NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(DbDataReader rs, Object id, Object obj, String[][] suffixedPropertyColumns, ISet`1 fetchedLazyProperties, Boolean allProperties, Int32[] indexes, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:line 2848
   at NHibernate.Persister.Entity.LoadableExtensions.Hydrate(ILoadable loadable, DbDataReader rs, Object id, Object obj, String[][] suffixedPropertyColumns, ISet`1 fetchedLazyProperties, Boolean allProperties, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Persister\Entity\ILoadable.cs:line 101
   at NHibernate.Loader.Loader.LoadFromResultSet(DbDataReader rs, Int32 i, Object obj, ILoadable persister, EntityKey key, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1301
   at NHibernate.Loader.Loader.InstanceNotYetLoaded(DbDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1164
   at NHibernate.Loader.Loader.GetRow(DbDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session, Boolean mustLoadMissingEntity, Action`2 cacheBatchingHandler) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1041
   at NHibernate.Loader.Loader.GetRowFromResultSet(DbDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder, Action`2 cacheBatchingHandler) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 405
   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 558
   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 303
   at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1972
   --- End of inner exception stack trace ---
   at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1981
   at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1837
   at NHibernate.Loader.Hql.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Hql\QueryLoader.cs:line 325
   at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Hql\Ast\ANTLR\QueryTranslatorImpl.cs:line 119
   at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Engine\Query\HQLQueryPlan.cs:line 116
   at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results, Object filterConnection) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Impl\SessionImpl.cs:line 559
   at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Impl\SessionImpl.cs:line 524
   at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Impl\AbstractSessionImpl.cs:line 172
   at NHibernate.Impl.AbstractQueryImpl2.List() in D:\BuildAgent\work\30546188361a242\src\NHibernate\Impl\AbstractQueryImpl2.cs:line 78
   at NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Linq\DefaultQueryProvider.cs:line 226
   at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Linq\DefaultQueryProvider.cs:line 96
   at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Linq\DefaultQueryProvider.cs:line 101
   at .....

根因分析 #

该问题由nHibernate版本破坏性变更中的时间戳精度调整导致，版本变更说明标注：

Oracle 9g+方言现在对所有日期时间类型统一使用timestamp(7)，替代此前的timestamp(4)。

目前观测到现象：无论是应用程序还是Oracle SQL Developer工具，都无法读取该存储值的世纪字段，仅能返回无世纪的格式，显示值为01.01.00 00:00:00,00000000，推测Oracle将DateTime.MaxValue高精度值向上取整后超出了合法日期范围，导致后续读取失败。

参考数据 #

真实DateTime.MaxValue值为12/31/9999 23:59:59.999999999

• Microsoft SQL DB存储值：
  • 升级前：12/31/9999 23:59:59.000
  • 升级后：12/31/9999 23:59:59.997
• Oracle DB存储值：
  • 升级前：31.12.99 23:59:59,000000（日期格式化后显示年份为9999）
  • 升级后：01.01.00 00:00:00,0000000

待解决问题 #

该问题已影响生产环境运行，将日期字段切换为null值的改造需要周期，且版本降级不是理想选择，因此急需临时解决方案，核心咨询两个问题：

1. 如何在nHibernate v5.x版本中配置，将日期时间精度回退到升级前的水平？
2. 在完成日期时间戳字段全量改造为null值前，还有哪些安全的快速修复方案？

内容的提问来源于stack exchange，提问作者btomas