create-db.sh for docker incorrectly reports "Creating database completed" when sqlcmd has failed

[ChristopherHackett]

When setting the Alloy demo up

$ dotnet new epi-alloy-mvc --name alloy-docker  --output ./alloy-docker --enable-docker
The template "Optimizely Alloy MVC" was created successfully.

$ cd alloy-docker
$ docker-compose up

I have an issue with the database creation. This may be a local issue with docker however on reviewing the logs I noticed CREATE DATABASE failed. followed by Creating database completed.

2023-02-22 11:38:02.26 spid51      CREATE FILE encountered operating system error 87(The parameter is incorrect.) while attempting to open or create the physical file '/var/opt/mssql/host_data/cms.mdf'.
Msg 5123, Level 16, State 1, Server 9b4d2b960188, Line 1
CREATE FILE encountered operating system error 87(The parameter is incorrect.) while attempting to open or create the physical file '/var/opt/mssql/host_data/cms.mdf'.
Msg 1802, Level 16, State 4, Server 9b4d2b960188, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Creating database completed

On reviewing the source code the status code is checked

content-templates/templates/Alloy.Mvc/Docker/create-db.sh

Lines 1 to 12 in 0c72f0e

	#!/bin/bash
	for i in {1..100}; do
	/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -Q "IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '${DB_NAME}') CREATE DATABASE ${DB_NAME} ON (NAME=${DB_NAME}_data, FILENAME='/var/opt/mssql/host_data/${DB_NAME}.mdf') LOG ON (NAME=${DB_NAME}_log, FILENAME='/var/opt/mssql/host_data/${DB_NAME}.ldf')"
	if [ $? -eq 0 ]; then
	echo "Creating database completed"
	break
	else
	echo "Creating database. Not ready yet..."
	sleep 1
	fi
	done

However sqlcmd requires -b to return an error value https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility

Error Reporting Options
-b
Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0. If the -V option has been set in addition to -b, sqlcmd will not report an error if the severity level is lower than the values set using -V. Command prompt batch files can test the value of ERRORLEVEL and handle the error appropriately. sqlcmd does not report errors for severity level 10 (informational messages).

If the sqlcmd script contains an incorrect comment, syntax error, or is missing a scripting variable, ERRORLEVEL returned is 1.

The following files appear to be affected by this behaviour:

• templates/Alloy.Mvc/Docker/create-db.sh
• templates/Cms.Empty/Docker/create-db.sh
• templates/Commerce.Empty/Docker/create-db.sh

[ChristopherHackett]

The underlying issue that caused the command to fail was related to local docker setup however the reported problem re error checking is still a relevant issue to resolve.

[JohanPetersson]

Thanks for reporting this. We will add -b to improve the error-handling.