PostgreSQL - System.TypeInitializationException for Npgsql.TypeHandlerRegistry

[Roemer]

I now tried for an hour getting roundhouse to work with postgres. I've setup a new 11.1 instance and try to run roundhouse. I always get the following exception:

RoundhousE encountered an error.
System.TypeInitializationException: Der Typeninitialisierer für "Npgsql.TypeHandlerRegistry" hat eine Ausnahme verursacht. ---> System.Reflection.ReflectionTypeLoadException: Mindestens ein Typ in der Assembly kann nicht geladen werden. Rufen Sie die LoaderExceptions-Eigenschaft ab, wenn Sie weitere Informationen benötigen.
bei System.Reflection.RuntimeModule.GetTypes(RuntimeModule module)
bei System.Reflection.Assembly.GetTypes()
bei Npgsql.TypeHandlerRegistry..cctor()
--- Ende der internen Ausnahmestapelüberwachung ---
bei Npgsql.TypeHandlerRegistry.Setup(NpgsqlConnector connector, NpgsqlTimeout timeout, Boolean async)
bei Npgsql.NpgsqlConnector.d__145.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
bei Npgsql.ConnectorPool.d__24.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
bei System.Runtime.CompilerServices.ValueTaskAwaiter1.GetResult() bei Npgsql.NpgsqlConnection.<Open>d__28.MoveNext() --- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde --- bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) bei Npgsql.NpgsqlConnection.Open() bei roundhouse.databases.AdoNetDatabase.open_connection(Boolean with_transaction) bei roundhouse.runners.RoundhouseMigrationRunner.run() Der Typeninitialisierer für "Npgsql.TypeHandlerRegistry" hat eine Ausnahme verursacht. System.TypeInitializationException: Der Typeninitialisierer für "Npgsql.TypeHandlerRegistry" hat eine Ausnahme verursacht. ---> System.Reflection.ReflectionTypeLoadException: Mindestens ein Typ in der Assembly kann nicht geladen werden. Rufen Sie die LoaderExceptions-Eigenschaft ab, wenn Sie weitere Informationen benötigen. bei System.Reflection.RuntimeModule.GetTypes(RuntimeModule module) bei System.Reflection.Assembly.GetTypes() bei Npgsql.TypeHandlerRegistry..cctor() --- Ende der internen Ausnahmestapelüberwachung --- bei Npgsql.TypeHandlerRegistry.Setup(NpgsqlConnector connector, NpgsqlTimeout timeout, Boolean async) bei Npgsql.NpgsqlConnector.<Open>d__145.MoveNext() --- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde --- bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) bei Npgsql.ConnectorPool.<AllocateLong>d__24.MoveNext() --- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde --- bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) bei System.Runtime.CompilerServices.ValueTaskAwaiter1.GetResult()
bei Npgsql.NpgsqlConnection.d__28.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
bei Npgsql.NpgsqlConnection.Open()
bei roundhouse.databases.AdoNetDatabase.open_connection(Boolean with_transaction)
bei roundhouse.runners.RoundhouseMigrationRunner.run()
bei roundhouse.console.Program.run_migrator(ConfigurationPropertyHolder configuration)
bei roundhouse.console.Program.Main(String[] args)

Sorry for German language but I hope you get the point:
I'm simply using rh.exe with the following command line:

rh.exe --dt postgres --cs "host=host; port=5432; database=db; username=postgres; password=pw;"

I'm using Windows 10/64 bit. How can I solve this problem?

[erikbra]

Hi, @Roemer , thanks for reporting. Could you please specify which version of RoundhousE you are using? I know there have been some problems with the ILMerge that we run and some dynamic loading of types before, but that was with StructureMap (which we have removed the dependency on).

I see we are using a quite old version of the NpgSql nuget package, it might be worth upgrading that one too.

If you are in an environment with .net core sdk installed, could you please try installing roundhouse with dotnet tool install -g dotnet-roundhouse, and see if that works better, alternatively run from docker (if that suits your environment). The reason I'm suggesting these, are that we are not ILMerge-ing the dotnet core version of RoundhousE, so if the issues are related to the merging, that may solve the issues.

[Roemer]

I‘m using 1.0.3 from Nuget. Also tried using it in cake build (which should use the same) with the same result. In the end, I want to use it from cake build.

[erikbra]

OK, thanks. Could you please try to use the dotnet tool version, to see if the problem is there too?

[Roemer]

I now tested with another computer, same problem. Using the dotnet tool version works.

[erikbra]

OK, thanks a lot. So, there's probably an issue with the ILMerge and the npgsql library. I will try to get a PostgreSQL test environment up and running to verify. I am not experienced in Postgres, but using this container should do the trick, don't you think? https://hub.docker.com/_/postgres

[Roemer]

Yes, I'm also using this image (and pgadmin4 to administer it, but that's not needed for this test). All you need is the environment variable POSTGRES_PASSWORD in the container and you're ready to go for the test. Edit: And port 5432 :)

[Roemer]

I could make it work in cake by using the new module feature from cake (#module nuget:?package=Cake.DotNetTool.Module), installing the dotnet version of roundhouse (#tool dotnet:?package=dotnet-roundhouse) and overwriting the ToolsPath like:

RoundhouseMigrate(new RoundhouseSettings{
    ConnectionString = "host=localhost; port=5432; database=postgres; username=postgres; password=pwd;",
    DoNotCreateDatabase = true,
    DatabaseType = "postgres",
    ToolPath = Context.Tools.Resolve("rh.exe")
});

Not the best solution but at least it works and does not need further changes when the non-dotnetcore version is fixed, except for re-adding it as a tool in cake and remove the dotnetcore one.

[erikbra]

@Roemer , I made some changes to the merging, trying out Costura.Fody, which uses a different technique for merging the assemblies.

Are you able to give your test a shot with the binaries from this pull request build, and see if it solves the problem? It it does, I am going to release a new version of RoundhousE with the fixes applied.

https://ci.appveyor.com/project/chucknorris/roundhouse/builds/22069025/artifacts

[Roemer]

Works like a charm with the new executable 👍. With command line and also with cake. I also noticed that all identifiers (like database name) are converted to lower. Whereas Postgresql actually supports case sensitivity by default. The rule is, that if an identifier is inside quotes, it is case sensitive, if not, it is not case sensitive. Maybe that's something that should be considered in Roundhouse?

[erikbra]

Great, thanks for the testing. I'll merge it, then, and make a new release one of the next few days.

Good point on case sensitivity, though. Could you please file a separate issue on it, so it's easier to solve it separately?

[Roemer]

I opened issue #366 for the case sensitivity.