SqlException: Database 'PSUDatabase' already exists. Choose a different database name

PowerShell Universal
1 
Product: PowerShell Universal
Version: 4.2.13

Hi @adam,

I ran PSU in docker compose together with a SQL Server container
to make use of the SQL Server support for PSU as part of VSCode Dev Container. This is my docker compose file

version: "3.7"
services:
  PSU:
    container_name: PSU
    image: ironmansoftware/universal:latest
    restart: unless-stopped
    ports:
      - "5000:5000"
    links:
      - SQL
    environment:
      - TZ=Europe/Berlin
      - Plugins__0=SQL
      - Data__ConnectionString=Data Source=SQL;Initial Catalog=PSU;User Id=sa;Password=Password123;TrustServerCertificate=True;Trusted_Connection=True;integrated security=false;
      - NodeName=PSUAdvanced
    depends_on:
      - SQL
    volumes:
      - localpath:/root

  SQL:
    container_name: SQL
    image: mcr.microsoft.com/mssql/server:2022-latest
    restart: unless-stopped
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=Password123
      - MSSQL_PID=Developer
    volumes:
      - localpath:/var/opt/mssql/data

The database was created at first run. If I want to start the docker compose file again the system log file displays the following:

Microsoft.Data.SqlClient.SqlException (0x80131904): Database '<databasename>' already exists. Choose a different database name.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Create()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
   at PowerShellUniversal.SQL.DatabaseFeature.InitializeDatabase(IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\PowerShellUniversal.SQL\Plugin.cs:line 66
   at Universal.Server.Services.SystemPluginService..ctor(IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Services\SystemPluginService.cs:line 56
   at Universal.Server.Startup.ConfigureServices(IServiceCollection services) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Startup.cs:line 60
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
   at System.Reflection.MethodInvoker.Invoke(Object obj, IntPtr* args, BindingFlags invokeAttr)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.InvokeCore(Object instance, IServiceCollection services)
   at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.UseStartup(Type startupType, HostBuilderContext context, IServiceCollection services, Object instance)
   at Microsoft.Extensions.Hosting.HostBuilder.InitializeServiceProvider()
   at Microsoft.Extensions.Hosting.HostBuilder.Build()
   at Universal.Server.Program.<>c__DisplayClass3_0.<Main>b__0(Options o) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Program.cs:line 80
ClientConnectionId:a7cbaba7-aed1-4e05-a6d0-994e4c935912
Error Number:1801,State:3,Class:16

After the third time of the above error message the container stops and the whole devcontainer crashes.
I have also seen this on installs through the MSI. Here I can restart the process and everything works as expected.

I already went through the docs and the only thing i found was that the user who is referenced in the connection string should have dbcreator or db_owner rights on the instance or on the database. This is the sa user, so this account is sysadmin by default. So this is not the problem.

Have you seen such a behaviour? Why does PSU behave like this?

Greetings
Constantin

2

I haven’t. I’ve been hosting in Azure with a docker setup to an Azure SQL instance and it works fine.

You could try to use the included SQL files to create the database and then set RunMigrations to false in the compose environment section to avoid having PSU attempt to create the database.

3

ok. That seems to work. If the latest version of the docker container will change, do I have to recreate the database for having the current schema?

4

Hey @adam,

I solved it. If the database engin (SQL Server) is to slow in responding that the database is available it tries to create the db even if the db is already there.

Tried it on a faster machine. Error message did not appear.

Just fyi

1 Like