NserviceBus.NHibernate and default_schema

1

We’re in the middle of upgrading an old application (NSB 3.3) which used nHibernate based saga and timeout persistence.

Under NSB 3.3 you setup the persistence for these in independent sections of the app.config, and we chose to put the timeout related stuff in a “timeout” schema, and the saga related stuff in a “saga” schema in the SQL Server database.

We’re now trying to migrate the old application to NSB 5/ NSB.NHibernate version 6.2.10 and it does not appear that there is a way to configure a DIFFERENT schema for timeouts vs. sagas. Using the “code based” initialization as shown below STILL causes the timeout tables to be created in the “saga” schema, it appears that the last configuration of the “default_schema” pollutes all the other ones.

When the endpoint starts it errors out because it can’t query the “saga.TimeoutEntity” table.

Here’s the initialization code:

configuration.EnableFeature<Sagas>();

var timeoutConfig = new Configuration
{
    Properties =
    {
        ["dialect"] = "NHibernate.Dialect.MsSql2012Dialect",
        ["connection.provider"] = "NHibernate.Connection.DriverConnectionProvider",
        ["default_schema"] = "timeout",
        [NHibernate.Cfg.Environment.ConnectionStringName] = "NServiceBus/Persistence"
    }
};

var timeouts = configuration.UsePersistence<NHibernatePersistence, StorageType.Timeouts>();
timeouts.UseConfiguration(timeoutConfig);
timeouts.DisableSchemaUpdate();

var sagaConfig = new Configuration
{
    Properties =
    {
        ["dialect"] = "NHibernate.Dialect.MsSql2012Dialect",
        ["connection.provider"] = "NHibernate.Connection.DriverConnectionProvider",
        ["default_schema"] = "saga",
        [NHibernate.Cfg.Environment.ConnectionStringName] = "NServiceBus/Persistence"
    }
};

var sagas = configuration.UsePersistence<NHibernatePersistence, StorageType.Sagas>();
sagas.UseConfiguration(sagaConfig);
sagas.DisableSchemaUpdate();

and here’s the error:

2018-07-02 11:50:32,566 [13] ERROR NHibernate.AdoNet.AbstractBatcher - Could not execute query: SELECT this_.Id as y0_, this_.Time as y1_ FROM saga.TimeoutEntity this_ WHERE this_.Endpoint = @p0 and (this_.Time > @p1 and this_.Time <= @p2) ORDER BY this_.Time asc
System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'saga.TimeoutEntity'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
2

Hi Bill,

You are correctly creating an NHibernate Configuration instance per persistence type but these should be passed via the methods as documented at the following location:

The configuration below is your modified version that should give the expected result:

var timeoutConfig = new Configuration
{
    Properties =
    {
        [NHibernate.Cfg.Environment.Dialect] = typeof(NHibernate.Dialect.MsSql2012Dialect).FullName,
        [NHibernate.Cfg.Environment.ConnectionProvider] = typeof(NHibernate.Connection.DriverConnectionProvider).FullName,
        [NHibernate.Cfg.Environment.DefaultSchema] = "timeout",
        [NHibernate.Cfg.Environment.ConnectionStringName] = "NServiceBus/Persistence"
    }
};

var sagaConfig = new Configuration
{
    Properties =
    {
        [NHibernate.Cfg.Environment.Dialect] = typeof(NHibernate.Dialect.MsSql2012Dialect).FullName,
        [NHibernate.Cfg.Environment.ConnectionProvider] = typeof(NHibernate.Connection.DriverConnectionProvider).FullName,
        [NHibernate.Cfg.Environment.DefaultSchema] = "saga",
        [NHibernate.Cfg.Environment.ConnectionStringName] = "NServiceBus/Persistence"
    }
};

var persistence = configuration.UsePersistence<NHibernatePersistence>();
persistence.UseConfiguration(sagaConfig);
persistence.UseTimeoutStorageConfiguration(timeoutConfig);
persistence.DisableSchemaUpdate();

Let us know if it works!

– Ramon

3

Ramon,

Changed the code to match what you sent, made no difference, it’s still trying to reference the Timeout tables out of the saga schema…Note that we are running NServicebus 5, so this is with NServiceBus.Nhibernate version 6 (the docs you called out are for version seven)…

Bill

4

Any response? Still not working and the problem is trivial to reproduce?

Bill

5

I verified the behavior and the solution I shared works. Here is it working in our sample:

6

Ramon,

No, it actually does not work, the “Subscription” table gets created in the “saga” schema which is not the behavior of the prior version of nServiceBus. When we used the following configuration we got a “timeout.subscription” table and all was well…your sample is creating the subscription table in the “saga” schema, which is a major behavioral change for us.

7

Bill, the timeout schema is used for the timeouts. If you want the subscriptions in a different schema too then you must add that override too. The subscription storage was not mentioned in your original post.

persistence.UseSubscriptionStorageConfiguration(timeoutConfig);

I updated the sample and added this and works as expected as can be seen in the following screenshot.

image

– Ramon

8

Is there similar functionality in NSB5? Seems like UsePersistence returns PersistenceExtentions and the methods above are not avialable in the return type. Do I misunderstand something?
Here’s my endpoint configuration:

 public class EndpointConfig : IConfigureThisEndpoint
    {
        public void Customize(BusConfiguration configuration)
        {    
            var rabbitConnectionString = AppSettings.GetNServiceBusTransportConnectionString();
            configuration.UseTransport<RabbitMQTransport>().DisableCallbackReceiver().ConnectionString(rabbitConnectionString);
           var persistence = configuration.UsePersistence<NHibernatePersistence>();
         
            configuration.UseSerialization<JsonSerializer>();
            configuration.Conventions()
                .DefiningEventsAs(t => typeof(IEvent).IsAssignableFrom(t) ||
                                       //include ServiceControl events
                                       t.Namespace != null && t.Namespace.StartsWith("ServiceControl.Contracts"));
            configuration.Conventions().DefiningCommandsAs(
                t => t.Namespace != null && t.Namespace.StartsWith("Halp.") && t.Namespace.EndsWith("Commands"));

        }
    }
9

The code I shared is NServiceBus 5.

Please take a look at the following file

Maybe you have missing using statements.

—Ramon

10

Yes, I found that I was missing a couple of using.
Interestingly, as soon as I add setting these configurations, it seems like I also have to add configuration for saga data objects. It was creating tables automatically before, but now it can’t.

11

All tables are automatically created in the modified sample I shared.