NHibernate.StaleStateException: Batch update returned unexpected row count from update; actual row count: -1; expected: 1


(Raj Shelar) #1

I am using below versions

NServiceBus : 5.2.14
NServiceBus.Host : 6.0.0
NServiceBus.NHibernate : 6.2.7

I am using NSB5 with Nibernate persistence and sql server transport. My service used to work fine before I move the queues to different the database, I created all queues in another database. Audit and Error queue remains on same database (Other NSB hosts are using same queues). I deleted all the old subscriptions entries from other endpoints, restarted services and check but subscription entries are not coming in new tables and give below error logs. What can be solution?

018-10-16 07:11:03.2878 Info NServiceBus.Unicast.Transport.TransportReceiver Failed to process message
NHibernate.StaleStateException: Batch update returned unexpected row count from update; actual row count: -1; expected: 1
   at NHibernate.AdoNet.Expectations.VerifyOutcomeBatched(Int32 expectedRowCount, Int32 rowCount)
   at NHibernate.AdoNet.SqlClientBatchingBatcher.DoExecuteBatch(IDbCommand ps)
   at NHibernate.AdoNet.AbstractBatcher.ExecuteBatchWithTiming(IDbCommand ps)
   at NHibernate.AdoNet.AbstractBatcher.ExecuteBatch()
   at NHibernate.Engine.ActionQueue.ExecuteActions()
   at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session)
   at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event)
   at NHibernate.Impl.SessionImpl.Flush()
   at NHibernate.Transaction.AdoTransaction.Commit()
   at NServiceBus.Unicast.Subscriptions.NHibernate.SubscriptionPersister.Subscribe(Address address, IEnumerable`1 messageTypes) in C:\BuildAgent\work\5135de308b2f3016\src\NServiceBus.NHibernate\Subscriptions\SubscriptionPersister.cs:line 41
   at NServiceBus.SubscriptionReceiverBehavior.Invoke(IncomingContext context, Action next) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Unicast\Subscriptions\MessageDrivenSubscriptions\SubscriptionReceiverBehavior.cs:line 76
   at NServiceBus.BehaviorChain`1.InvokeNext(T context) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 107
   at NServiceBus.BehaviorChain`1.<>c__DisplayClass4_0.<InvokeNext>b__0() in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 95
   at NServiceBus.UnitOfWorkBehavior.Invoke(IncomingContext context, Action next) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\UnitOfWork\UnitOfWorkBehavior.cs:line 42
   at NServiceBus.BehaviorChain`1.InvokeNext(T context) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 107
   at NServiceBus.BehaviorChain`1.<>c__DisplayClass4_0.<InvokeNext>b__0() in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 95
   at NServiceBus.Persistence.NHibernate.OpenSqlConnectionBehavior.Invoke(IncomingContext context, Action next) in C:\BuildAgent\work\5135de308b2f3016\src\NServiceBus.NHibernate\SharedSession\OpenSqlConnectionBehavior.cs:line 43
   at NServiceBus.BehaviorChain`1.InvokeNext(T context) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 107
   at NServiceBus.BehaviorChain`1.<>c__DisplayClass4_0.<InvokeNext>b__0() in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 95
   at NServiceBus.ChildContainerBehavior.Invoke(IncomingContext context, Action next) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Unicast\Behaviors\ChildContainerBehavior.cs:line 17
   at NServiceBus.BehaviorChain`1.InvokeNext(T context) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 107
   at NServiceBus.BehaviorChain`1.<>c__DisplayClass4_0.<InvokeNext>b__0() in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 95
   at NServiceBus.ProcessingStatisticsBehavior.Invoke(IncomingContext context, Action next) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Monitoring\ProcessingStatisticsBehavior.cs:line 23
   at NServiceBus.BehaviorChain`1.InvokeNext(T context) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 107
   at NServiceBus.BehaviorChain`1.<>c__DisplayClass4_0.<InvokeNext>b__0() in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 95
   at NServiceBus.AuditBehavior.Invoke(IncomingContext context, Action next) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Audit\AuditBehavior.cs:line 20
   at NServiceBus.BehaviorChain`1.InvokeNext(T context) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 107
   at NServiceBus.BehaviorChain`1.Invoke() in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 52
   at NServiceBus.Pipeline.PipelineExecutor.Execute[T](BehaviorChain`1 pipelineAction, T context) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\PipelineExecutor.cs:line 129
   at NServiceBus.Unicast.Transport.TransportReceiver.OnTransportMessageReceived(TransportMessage msg) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Unicast\Transport\TransportReceiver.cs:line 413
   at NServiceBus.Unicast.Transport.TransportReceiver.ProcessMessage(TransportMessage message) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Unicast\Transport\TransportReceiver.cs:line 344
   at NServiceBus.Unicast.Transport.TransportReceiver.TryProcess(TransportMessage message) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Unicast\Transport\TransportReceiver.cs:line 230
   at NServiceBus.Transports.SQLServer.AmbientTransactionReceiveStrategy.TryReceiveFrom(TableBasedQueue queue) in C:\BuildAgent\work\888dd942b44bad5a\src\NServiceBus.SqlServer\AmbientTransactionReceiveStrategy.cs:line 57

(Ramon Smits) #2

Can you check if you have NOCOUNT on or off on the SQL server instance? NHibernate requires this to be OFF so that it gets the row count returned.

If this is not possible then you can customize the connection factory used by NHibernate to disable this. The code for this can be found at the following issue:

Code:

public class CustomDriverConnectionProvider : NHibernate.Connection.DriverConnectionProvider
{
    public override System.Data.IDbConnection GetConnection()
    {
        var conn = base.GetConnection();
        DisableNoCount(conn);
        return conn;
    }

    private void DisableNoCount(System.Data.IDbConnection connection)
    {
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "SET NOCOUNT OFF";
            command.ExecuteNonQuery();
        }
    }
}

Configuration:

<appSettings>
  <add
      key="NServiceBus/Persistence/NHibernate/connection.provider"
      value="CustomDriverConnectionProvider, MyAssembly" />
</appSettings>

I highly recommend disabling this on the instance level to avoid any confusion between environments.


(Raj Shelar) #3

Thanks, can we make this without change in config file since we have all configurations done through code only.


(Ramon Smits) #4

You can specify a custom connection provider too in code:


(Raj Shelar) #5

Thanks I have just put below code and works fine

var hibernateConfig = new NHibernate.Cfg.Configuration();
hibernateConfig.DataBaseIntegration(x =>
{
    x.ConnectionString = GetPersistenceConnectionString();
    x.Dialect<MsSql2008Dialect>();
    x.ConnectionProvider<CustomDriverConnectionProvider>();
});
hibernateConfig.SetProperty("default_schema", GetPersistenceSchema());

//Wire NHibernate
configuration.UsePersistence<NHibernatePersistence>()
    .RegisterManagedSessionInTheContainer()
    .DisableSchemaUpdate()

(Ramon Smits) #6

Raj, thanks for also sharing how to do this via the NHibernate code API instead of the configuration file.