mssql tests are failing in CI because a recent mcr.microsoft.com/mssql/server docker image release breaks our healthcheck

[trentm]

TAV=mssql tests are failing on main. E.g.:

https://github.com/elastic/apm-agent-nodejs/actions/runs/10066834059/job/27830878023#step:3:167

 .ci/scripts/test.sh:253: docker --log-level error compose --ansi never -f .ci/docker/docker-compose-mssql.yml up --exit-code-from node_tests --remove-orphans --abort-on-container-exit node_tests
 mssql Pulling 
...
dependency failed to start: container docker-mssql-1 is unhealthy

This is because the healthcheck is failing:

apm-agent-nodejs/.ci/docker/docker-compose.yml

Line 45 in 921dbb9

test: ["CMD", "/opt/mssql-tools/bin/sqlcmd", "-S", "mssql", "-U", "sa", "-P", "Very(!)Secure", "-Q", "select 1"]

That is because the command used for healthchecking, /opt/mssql-tools/bin/sqlcmd is now in a different dir:

% docker pull mcr.microsoft.com/mssql/server
...

% docker run -ti --rm mcr.microsoft.com/mssql/server /bin/sh
WARNING: The requested image's platform (linux/amd64) does not match the detected host platform (linux/arm64/v8) and no specific platform was requested
SQL Server 2022 will run as non-root by default.
This container is running as user mssql.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
$ ls /opt
microsoft  mssql  mssql-extensibility  mssql-tools18

issue 2

However, if we update the path:

@@ -42,7 +42,7 @@ services:
     volumes:
       - nodemssqldata:/var/opt/mssql
     healthcheck:
-      test: ["CMD", "/opt/mssql-tools/bin/sqlcmd", "-S", "mssql", "-U", "sa", "-P", "Very(!)Secure", "-Q", "select 1"]
+      test: ["CMD", "/opt/mssql-tools18/bin/sqlcmd", "-S", "mssql", "-U", "sa", "-P", "Very(!)Secure", "-Q", "select 1"]

We get a failure (I'm guessing a different one now):

% docker inspect docker-mssql-1
[
    {
        "Id": "019ec4163ba48044959f84c03ca2abe09bfa8514ebb4d811fc0af15b095df0cb",
        "Created": "2024-07-23T23:36:39.794694925Z",
        "Path": "/opt/mssql/bin/permissions_check.sh",
        "Args": [
            "/opt/mssql/bin/sqlservr"
        ],
        "State": {
            "Status": "running",
            "Running": true,
            "Paused": false,
            "Restarting": false,
            "OOMKilled": false,
            "Dead": false,
            "Pid": 3698849,
            "ExitCode": 0,
            "Error": "",
            "StartedAt": "2024-07-23T23:36:39.866359634Z",
            "FinishedAt": "0001-01-01T00:00:00Z",
            "Health": {
                "Status": "starting",
                "FailingStreak": 1,
                "Log": [
                    {
                        "Start": "2024-07-23T23:37:09.95525505Z",
                        "End": "2024-07-23T23:37:10.118584801Z",
                        "ExitCode": 1,
                        "Output": "Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate].\nSqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722.\n"
                    }
                ]
            }
...

The go.microsoft.com link there is no use.

I stupidly don't know how to play with the older mssql/server iamge on my machine, even though I have its sha:

% docker run --rm -ti mcr.microsoft.com/mssql/server@sha256:a702c27e4969788bf9205b64ba8b28247eec01382d14ca81f151ed9899dfa3d5 /bin/sh
Unable to find image 'mcr.microsoft.com/mssql/server@sha256:a702c27e4969788bf9205b64ba8b28247eec01382d14ca81f151ed9899dfa3d5' locally
docker: Error response from daemon: manifest for mcr.microsoft.com/mssql/server@sha256:a702c27e4969788bf9205b64ba8b28247eec01382d14ca81f151ed9899dfa3d5 not found: manifest unknown: manifest sha256:a702c27e4969788bf9205b64ba8b28247eec01382d14ca81f151ed9899dfa3d5 is not found.
See 'docker run --help'.

The docker hub page for this image (https://hub.docker.com/r/microsoft/mssql-server) confirms there was a recent release (earlier today):

<h3 class="MuiTypography-root MuiTypography-h2 css-17g3sxl">Full Tag Listing</h3><h5 class="MuiTypography-root MuiTypography-subtitle1 css-dp32wz">Linux Images</h5><div class="MuiTableContainer-root css-1xo87ss">
Tags | Architecture | Dockerfile | OsVersion | CreatedTime | LastUpdatedTime
-- | -- | -- | -- | -- | --
2022-latest | amd64 | No Dockerfile | Ubuntu 22.04 | 05/31/2022 | 07/23/2024
latest | amd64 | No Dockerfile | Ubuntu 22.04 | 09/10/2018 | 07/23/2024
2022-CU14-ubuntu-22.04 | amd64 | No Dockerfile | Ubuntu 22.04 | 07/23/2024 | 07/23/2024
2022-CU13-ubuntu-22.04 | amd64 | No Dockerfile | Ubuntu 22.04 | 05/16/2024 | 05/16/2024
...

Links in that Docker Hub page suggest the relevant repo for this image is https://github.com/microsoft/mssql-docker
However, there hasn't been a commit there in 4mo, no tags, no useful-looking branches. So either that repo is no longer used for releases or it hasn't been updated yet (i.e. is actually developed in private with periodic public syncs to this repo?).

There is no useful changelog that I can find.

There is this ancient issue asking for a working/workable Docker healthcheck for the image: microsoft/mssql-docker#133

I'm stuck here.

Perhaps there is an older tag we could pin to as a workaround for now.

[trentm]

Yup... using CU13 (Cumulative Update) seems to get us working again.

[MaxBeauchemin]

Having same issue here, thanks for reporting this

[alejandropadillav1]

+1, Having same issue as well

[Anonynym3845937]

Have the same issue here! It is blocking us bigly (!), and we have an upcoming release.
Please address this.

[trentm]

@MaxBeauchemin @alejandropadillav1 @Anonynym3845937 You say you are having the same issue. Do you mean you are having an issue with the mssql/server Docker image, but not with the elastic-apm-node Node.js/npm package that lives in this repo?

I have worked around this issue for now by just not using the latest mssql/server image.

[trentm]

It looks like microsoft/mssql-docker#892 (comment) shows a working healthcheck: command for the latest (CU14) image.

[MaxBeauchemin]

@MaxBeauchemin @alejandropadillav1 @Anonynym3845937 You say you are having the same issue. Do you mean you are having an issue with the mssql/server Docker image, but not with the elastic-apm-node Node.js/npm package that lives in this repo?

I have worked around this issue for now by just not using the latest mssql/server image.

Yes sorry, I am having an issue with the latest Docker image itself, but not in reference to this particular github repo using that image

[Anonynym3845937]

Hi, @trentm

So, we had our pipeline configured with latest tag -> the error in Point 1 appeared. We patched it up and the error from Point 2 appeared. Since there was no answer at the time, we downgraded to the previous CU13 and all worked well.

[Anonynym3845937]

Is there a ticket on the sql's github open anywhere for this sql issue?

[trentm]

Is there a ticket on the sql's github open anywhere for this sql issue?

@Anonynym3845937 See my comment above referring to microsoft/mssql-docker#892 (comment)