Docker中自动创建数据库及EF Core迁移失败排查求助

问题描述 #

执行docker-compose up后，API与SQL Server容器成功启动，可通过SQL Server Management Studio连接SQL Server容器，但Demo数据库未自动创建，日志显示entrypoint.sh未执行，同时API容器抛出SQL Server连接错误。

相关配置文件 #

Dockerfile #

FROM mcr.microsoft.com/dotnet/nightly/aspnet:6.0 AS base
WORKDIR /app
EXPOSE 80
EXPOSE 443

FROM mcr.microsoft.com/dotnet/sdk:6.0 AS build
WORKDIR /src
COPY ["MyApi.csproj", "."]
RUN dotnet restore "./MyApi.csproj"
COPY . .
WORKDIR "/src/."
RUN dotnet build "MyApi.csproj" -c Release -o /app/build

FROM build AS publish
RUN dotnet publish "MyApi.csproj" -c Release -o /app/publish

FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .
COPY entrypoint.sh /app/entrypoint.sh
ENTRYPOINT ["dotnet", "MyApi.dll"]

RUN chmod +x ./entrypoint.sh
CMD /bin/bash ./entrypoint.sh

docker-compose.yml #

version: "3"
services:
    api:
        build: 
          context: .
          dockerfile: Dockerfile
        ports:
            - "8888:80"
        depends_on:
            - db
    db:
        image: "mcr.microsoft.com/mssql/server:2022-latest"
        environment:
            SA_PASSWORD: "P@55w0rd"
            ACCEPT_EULA: "Y"
        ports:
            - "1433:1433"

entrypoint.sh #

#!/bin/bash

set -e
run_cmd="dotnet run --server.urls http://*:80"

until dotnet ef database update; do
>&2 echo "SQL Server is starting up"
sleep 1
done

>&2 echo "SQL Server is up - executing command"
exec $run_cmd
sleep 10

>&2 echo "Creating Database"
/opt/mssql-tools/bin/sqlcmd -S db -U sa -P P@55w0rd -i create-database.sql

create-database.sql #

USE master
GO

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'Demo')
BEGIN
  CREATE DATABASE Demo;
END;
GO

appsettings.json连接字符串 #

"ConnectionStrings": {
    "MyServicesConnection": "Server=localhost,1433;Database=Abc;User Id=sa; Password=P@55w0rd#513;Trusted_Connection=True;MultipleActiveResultSets=true"
  },

报错日志 #

SQL Server容器日志 #

db_1   | /opt/mssql/bin/permissions_check.sh: line 4: [: : integer expression expected
db_1   | SQL Server 2019 will run as non-root by default.
db_1   | This container is running as user mssql.
db_1   | /opt/mssql/bin/permissions_check.sh: line 59: [: : integer expression expected
db_1   | 2022-09-21 07:32:01.44 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2022-09-21 07:32:01.49 Server      Did not find an existing master data file /var/opt/mssql/data/master.mdf, copying the missing default master and other system database files. If you have moved the database location, but not moved the database files, startup may fail. To repair: shutdown SQL Server, move the master database to configured location, and restart.

API容器日志 #

api_1  | Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server: Could not open a connection to SQL Server)
api_1  |    at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
api_1  |    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
api_1  |    at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
api_1  |    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
api_1  |    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
api_1  |    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
api_1  |    at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
api_1  |    at Microsoft.Data.SqlClient.SqlConnectionFactory.Create

问题原因与修复方案 #

1. ENTRYPOINT与CMD冲突导致entrypoint.sh未执行 #

Docker规则：同时指定ENTRYPOINT和CMD时，CMD内容会作为ENTRYPOINT的参数传递，而非单独执行。当前Dockerfile中实际执行的是dotnet MyApi.dll /bin/bash ./entrypoint.sh，完全未触发脚本。

修复Dockerfile：
调整ENTRYPOINT为执行脚本，确保权限设置在拷贝脚本之后：

FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .
COPY entrypoint.sh /app/entrypoint.sh
# 先给脚本加执行权限
RUN chmod +x ./entrypoint.sh
# 设置ENTRYPOINT为执行脚本
ENTRYPOINT ["/bin/bash", "./entrypoint.sh"]

2. entrypoint.sh脚本逻辑错误 #

• dotnet run是开发环境命令，发布后应直接运行dll
• exec $run_cmd会替换当前进程，后续代码永远无法执行
• aspnet镜像默认无EF工具和mssql-tools，导致dotnet ef和sqlcmd命令失败

修复entrypoint.sh：
修改脚本逻辑，先等待SQL就绪，再创建数据库，最后启动API。同时需在Dockerfile中安装mssql-tools：

#!/bin/bash
set -e

# 等待SQL Server服务就绪
until /opt/mssql-tools/bin/sqlcmd -S db -U sa -P P@55w0rd -Q "SELECT 1" > /dev/null 2>&1; do
    >&2 echo "SQL Server 正在启动中，等待连接..."
    sleep 2
done

# 创建Demo数据库
>&2 echo "SQL Server 已就绪，创建Demo数据库..."
/opt/mssql-tools/bin/sqlcmd -S db -U sa -P P@55w0rd -i create-database.sql

# 启动API服务
>&2 echo "启动API服务..."
exec dotnet MyApi.dll --server.urls http://*:80

在Dockerfile的final阶段添加mssql-tools安装命令：

RUN apt-get update && apt-get install -y curl apt-transport-https && \
    curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
    apt-get update && ACCEPT_EULA=Y apt-get install -y msodbcsql17 mssql-tools && \
    apt-get clean && rm -rf /var/lib/apt/lists/*

3. API连接字符串错误 #

• 容器内连接db服务需用服务名db而非localhost
• 密码与docker-compose中设置的SA_PASSWORD不匹配
• Trusted_Connection=True在SQL认证模式下无需设置
• 数据库名称应改为目标Demo

修复appsettings.json连接字符串：

"ConnectionStrings": {
  "MyServicesConnection": "Server=db,1433;Database=Demo;User Id=sa;Password=P@55w0rd;MultipleActiveResultSets=true"
}

4. 替代方案：让SQL Server容器自动初始化数据库 #

更简便的方式是将create-database.sql挂载到SQL Server容器的/docker-entrypoint-initdb.d目录，容器启动时会自动执行该目录下的SQL脚本，无需在API容器中处理：

修改docker-compose.yml的db服务：

db:
    image: "mcr.microsoft.com/mssql/server:2022-latest"
    environment:
        SA_PASSWORD: "P@55w0rd"
        ACCEPT_EULA: "Y"
    ports:
        - "1433:1433"
    volumes:
        - ./create-database.sql:/docker-entrypoint-initdb.d/create-database.sql

此方案可省去API容器中安装mssql-tools和编写entrypoint脚本的步骤，只需确保API连接字符串正确，且entrypoint脚本仅负责等待SQL就绪后启动API。

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