ZFS Support

Question

ZFS Support

au-phiware opened this issue 8 years ago · 81 comments

Upon executing docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 microsoft/mssql-server-linux, I encounter the following message:

Configuring Microsoft(R) SQL Server(R)...
Microsoft(R) SQL Server(R) setup failed with error code 1. Please check the setup log in /var/opt/mssql/log for more information.

The setup log contains:

This is an evaluation version.  There are [154] days left in the evaluation period.
2017-01-10 23:55:24.82 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to 'C:\var\opt\mssql\data\master.mdf'.
2017-01-10 23:55:24.90 Server      Setup step is copying system data file 'C:\templatedata\mastlog.ldf' to 'C:\var\opt\mssql\data\mastlog.ldf'.
2017-01-10 23:55:24.91 Server      Setup step is copying system data file 'C:\templatedata\model.mdf' to 'C:\var\opt\mssql\data\model.mdf'.
2017-01-10 23:55:24.92 Server      Setup step is copying system data file 'C:\templatedata\modellog.ldf' to 'C:\var\opt\mssql\data\modellog.ldf'.
2017-01-10 23:55:24.94 Server      Setup step is copying system data file 'C:\templatedata\msdbdata.mdf' to 'C:\var\opt\mssql\data\msdbdata.mdf'.
2017-01-10 23:55:24.95 Server      Setup step is copying system data file 'C:\templatedata\msdblog.ldf' to 'C:\var\opt\mssql\data\msdblog.ldf'.
2017-01-10 23:55:25.02 Server      Microsoft SQL Server vNext (CTP1.1) - 14.0.100.187 (X64) 
	Dec 10 2016 02:51:11 
	Copyright (C) 2016 Microsoft Corporation. All rights reserved.
	on Linux (Ubuntu 16.04.1 LTS)
2017-01-10 23:55:25.02 Server      UTC adjustment: 0:00
2017-01-10 23:55:25.02 Server      (c) Microsoft Corporation.
2017-01-10 23:55:25.03 Server      All rights reserved.
2017-01-10 23:55:25.03 Server      Server process ID is 4116.
2017-01-10 23:55:25.03 Server      Logging SQL Server messages in file 'C:\var\opt\mssql\log\errorlog'.
2017-01-10 23:55:25.03 Server      Registry startup parameters: 
	 -d C:\var\opt\mssql\data\master.mdf
	 -l C:\var\opt\mssql\data\mastlog.ldf
	 -e C:\var\opt\mssql\log\errorlog
2017-01-10 23:55:25.03 Server      Command Line Startup Parameters:
	 --setup
	 --sa-password
2017-01-10 23:55:25.04 Server      Error: 17113, Severity: 16, State: 1.
2017-01-10 23:55:25.04 Server      Error 87(The parameter is incorrect.) occurred while opening file 'C:\var\opt\mssql\data\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
2017-01-10 23:55:25.05 Server      SQL Server shutdown has been initiated

The /var/opt/mssql/data directory contains:

-rw-r----- 1 root root  4194304 Jan 11 10:55 master.mdf
-rw-r----- 1 root root   524288 Jan 11 10:55 mastlog.ldf
-rw-r----- 1 root root  8388608 Jan 11 10:55 modellog.ldf
-rw-r----- 1 root root  8388608 Jan 11 10:55 model.mdf
-rw-r----- 1 root root 13959168 Jan 11 10:55 msdbdata.mdf
-rw-r----- 1 root root   524288 Jan 11 10:55 msdblog.ldf

Please advise.

Answer 1 · 2017-01-11T00:40:53.000Z

uname -a
Linux tecknack-corin 4.6.5-stable #54 SMP Tue Sep 13 11:33:24 AEST 2016 x86_64 Intel(R) Core(TM) i7-5500U CPU @ 2.40GHz GenuineIntel GNU/Linux

docker version
Client:
 Version:      1.12.1
 API version:  1.24
 Go version:   go1.6.3
 Git commit:   23cf638
 Built:        
 OS/Arch:      linux/amd64

Server:
 Version:      1.12.1
 API version:  1.24
 Go version:   go1.6.3
 Git commit:   23cf638
 Built:        
 OS/Arch:      linux/amd64

Answer 2 · 2017-01-11T08:04:01.000Z

@au-phiware I tried running the exact command that you provided and it worked ok for me. In my case I was using Docker for Windows, but I've done essentially the same thing recently on RHEL and Docker for Mac. I'm wondering - is that the exact command you used or did you possibly try mounting a volume using docker run -v?

Answer 3 · 2017-01-11T20:20:47.000Z

Hi @twright-msft, you are right I did run it with -v initially (that's how I got the log contents) but before posting I made sure to run that exact command. I tried a number of variations, all had exactly the same output, and I'm assuming the setup log is the same too. So, I could be wrong, is it likely that the log would be different? I could copy the file out if you suspect there's something in that...?

Answer 4 · 2017-01-11T20:37:20.000Z

I have no doubt that there must be something particular about my kernel or filesystem. I use Gentoo and build my own kernel from git (the stable branch) but I'm not doing anything special there. I use zfs and so the docker storage driver is zfs too, could it be that there's an issue with accessing metadata or something else to do with the filesystem?

Answer 5 · 2017-01-11T21:06:16.000Z

Just to be sure I tried on my home machine (also Gentoo and ZFS) and used docker cp to retrieve the errorlog file. Here's what happened:

$ docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 microsoft/mssql-server-linux
Unable to find image 'microsoft/mssql-server-linux:latest' locally
latest: Pulling from microsoft/mssql-server-linux
aed15891ba52: Pull complete 
773ae8583d14: Pull complete 
d1d48771f782: Pull complete 
cd3d6cd6c0cf: Pull complete 
8ff6f8a9120c: Pull complete 
1fd7e8b10447: Pull complete 
bd485157db89: Pull complete 
273a1970ce9c: Pull complete 
d2d0b4dc209f: Pull complete 
Digest: sha256:f17b5a6d31f2e863581ba91169641a21f05584d9e5f82e43ee066c307e5550af
Status: Downloaded newer image for microsoft/mssql-server-linux:latest
Configuring Microsoft(R) SQL Server(R)...
Microsoft(R) SQL Server(R) setup failed with error code 1. Please check the setup log in /var/opt/mssql/log for more information.
$ docker ps -a | head -n 2
CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS                      PORTS               NAMES
d66b9878ea41        microsoft/mssql-server-linux        "/bin/sh -c /opt/m..."   14 minutes ago      Exited (1) 14 minutes ago                       nervous_engelbart
$ docker cp d66b9878ea41:/var/opt/mssql/log/errorlog .
$ cat errorlog
ÿþ2017-01-11 20:59:36.77 Server      Microsoft SQL Server vNext (CTP1.1) - 14.0.100.187 (X64) 
	Dec 10 2016 02:51:11 
	Copyright (C) 2016 Microsoft Corporation. All rights reserved.
	on Linux (Ubuntu 16.04.1 LTS)
2017-01-11 20:59:36.78 Server      UTC adjustment: 0:00
2017-01-11 20:59:36.78 Server      (c) Microsoft Corporation.
2017-01-11 20:59:36.78 Server      All rights reserved.
2017-01-11 20:59:36.78 Server      Server process ID is 4116.
2017-01-11 20:59:36.78 Server      Logging SQL Server messages in file 'C:\var\opt\mssql\log\errorlog'.
2017-01-11 20:59:36.78 Server      Registry startup parameters: 
	 -d C:\var\opt\mssql\data\master.mdf
	 -l C:\var\opt\mssql\data\mastlog.ldf
	 -e C:\var\opt\mssql\log\errorlog
2017-01-11 20:59:36.78 Server      Command Line Startup Parameters:
	 --setup
	 --sa-password
2017-01-11 20:59:36.79 Server      Error: 17113, Severity: 16, State: 1.
2017-01-11 20:59:36.79 Server      Error 87(The parameter is incorrect.) occurred while opening file 'C:\var\opt\mssql\data\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
2017-01-11 20:59:36.80 Server      SQL Server shutdown has been initiated
$ docker version
Client:
 Version:      1.13.0-rc3
 API version:  1.25
 Go version:   go1.7.1
 Git commit:   4d92237
 Built:        
 OS/Arch:      linux/amd64

Server:
 Version:      1.13.0-rc3
 API version:  1.25 (minimum version 1.12)
 Go version:   go1.7.1
 Git commit:   4d92237
 Built:        
 OS/Arch:      linux/amd64
 Experimental: false
$ uname -a
Linux fibonacci 4.3.6-stable #61 SMP Sun Dec 18 14:11:45 AEDT 2016 x86_64 Intel(R) Core(TM) i7-4710HQ CPU @ 2.50GHz GenuineIntel GNU/Linux

Answer 6 · 2017-01-12T13:30:18.000Z

We havent done any testing on Gentoo/zfs yet. It's definitely possible there is an issue there with the kernel/file system.

Answer 7 · 2017-01-12T19:22:02.000Z

Is there any further information that I can provide? Or turn on verbose logging?

…

On Fri, 13 Jan 2017, 12:30 AM Travis Wright ***@***.***> wrote: We havent done any testing on Gentoo/zfs yet. It's definitely possible there is an issue there with the kernel/file system. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#13 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAx2LutMzCdMoMYWB_5SqhkOvDcjEVLOks5rRirqgaJpZM4LgDrC> .

Answer 8 · 2017-01-25T09:11:49.000Z

I am having this exact same issue on Docker for windows.

Answer 9 · 2017-01-26T02:20:56.000Z

One thing to check, especially if you are using Docker for Windows or Docker for Mac is that you adjust the amount of RAM that Docker Engine has available to it.
https://docs.docker.com/docker-for-mac/#/advanced
https://docs.docker.com/docker-for-windows/#/advanced

Answer 10 · 2017-01-26T06:48:23.000Z

The mssql image shows a clear and specific error message when docker engine has less than 4gb ram available. This is not the error I was getting. After a full docker reset the issue seems to have gone away now though.

Answer 11 · 2017-02-02T08:25:11.000Z

having same issue and error message using mac and docker v1.13 with 4gb memory

when mounting volume with -v option to bind /var/opt/mssql, we can see logs but .mdf id failing with error 87.

workaround is to start without this -v option, it works, but .mdf file are inside container

Answer 12 · 2017-02-02T08:48:34.000Z

@laurentleseigneur This is a known issue with macOS and -v. We have a separate issue tracking that. #12
For now, you can use an attached Docker container volume for database file storage.
https://docs.docker.com/engine/tutorials/dockervolumes/#/creating-and-mounting-a-data-volume-container

Answer 13 · 2017-02-02T14:41:13.000Z

thanks @twright-msft for pointing this known issue, i will give a try to a docker compose using a container volume to store .mdf files

Answer 14 · 2017-02-13T14:55:08.000Z

I had the same error and I solved it by erasing the images and containers and re-creating them.

Docker version 1.13.1, build 092cba3
Windows 10 build 15031.rs2_release.170204-1546

Answer 15 · 2017-03-08T21:01:33.000Z

@twright-msft Thanks for this thread; came here because I'm experiencing the same issue. I wanted to see if my case is a part of the known issue because I'm using mounted data volume containers.

In my docker-compose.yml I have a service that is configured:

  mssql:
    build: ./mssql
    env_file:
    - .env
    ports:
    - "1433:1433"
    volumes:
    - ./mssql/volumes/data:/var/opt/mssql

But when I pull up the logs from docker logs <container iD> It spits out:

2017-03-08 20:01:34.80 Server      Error: 17113, Severity: 16, State: 1.
2017-03-08 20:01:34.80 Server      Error 87(The parameter is incorrect.) occurred while opening file 'C:\var\opt\mssql\data\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
2017-03-08 20:01:34.80 Server      SQL Server shutdown has been initiated

Is this part of the the known issue?

Answer 16 · 2017-03-08T21:05:24.000Z

@PatrickDePuydt Are you on MacOS or Linux? If Linux, what distribution/version are you running on?

Answer 17 · 2017-03-08T21:21:57.000Z

@twright-msft I'm running OS X Sierra but the base Docker image is a Centos7

Answer 18 · 2017-03-08T22:08:09.000Z

@PatrickDePuydt OK, then yes, you are running into the issue I described above with using Docker for Mac and -v (volumes: in the way you are using it in docker-compose.yml is effectively docker run -v). Please use a 'data volume container' instead.
More info:
https://docs.docker.com/engine/tutorials/dockervolumes/#creating-and-mounting-a-data-volume-container

http://stackoverflow.com/questions/32908621/how-can-i-create-a-data-container-only-using-docker-compose-yml

Answer 19 · 2017-03-08T22:57:52.000Z

@twright-msft Ok, thanks! Could you clear up one last thing? You mentioned using an attached Docker container volume for database file storage as a workaround, but isn't that what I'm doing in the volume declaration of the service: - ./mssql/volumes/data:/var/opt/mssql ?

Answer 20 · 2017-03-08T23:21:55.000Z

As I understand it what you are doing there is mounting a directory on the host to a directory in the container. That's what doesnt work on macOS. What you want to do is mount a volume container to a directory in the mssql container.

Answer 21 · 2017-03-11T10:01:33.000Z

This is an evaluation version. There are [156] days left in the evaluation period.
RegQueryValueEx HADR for key "Software\Microsoft\Microsoft SQL Server\MSSQL\MSSQLServer\HADR" failed.
2017-03-11 09:58:56.66 Server Microsoft SQL Server vNext (CTP1.3) - 14.0.304.138 (X64)
Feb 13 2017 16:49:12
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
on Linux (Ubuntu 16.04.1 LTS)
2017-03-11 09:58:56.66 Server UTC adjustment: 0:00
2017-03-11 09:58:56.66 Server (c) Microsoft Corporation.
2017-03-11 09:58:56.66 Server All rights reserved.
2017-03-11 09:58:56.67 Server Server process ID is 4116.
2017-03-11 09:58:56.67 Server Logging SQL Server messages in file 'C:\var\opt\mssql\log\errorlog'.
2017-03-11 09:58:56.67 Server Registry startup parameters:
-d C:\var\opt\mssql\data\master.mdf
-l C:\var\opt\mssql\data\mastlog.ldf
-e C:\var\opt\mssql\log\errorlog
2017-03-11 09:58:56.69 Server Error: 17113, Severity: 16, State: 1.
2017-03-11 09:58:56.69 Server Error 3(The system cannot find the path specified.) occurred while opening file 'C:\var\opt\mssql\data\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
2017-03-11 09:58:56.70 Server SQL Server shutdown has been initiated

I have the same problem but not in docker. it running and working in docker after pushing the docker image to heroku app it is giving the above message message. anybody has any idea about this issue.
Please help if anybody knows

Thanks

Answer 22 · 2017-05-15T02:23:37.000Z

(I feels as if this thread has been hijacked by Mac users)

I decided to take ZFS out of the mix:

First I confirmed I was still receiving the error with the latest image.
I create a zvol and formatted it with ext4 and mounted it as my /var/lib/docker directory.
Started the docker daemon and ensured it wasn't using zfs as the storage device.
Tried again.

This time I didn't receive the error, the container stayed up and I have an operational mssql server on linux!

I guess the next question is: where does the fault lie? This project (seems unlikely)? Docker (more specifically the zfs storage driver)? Or ZFS?

I think I'll close this issue and go ask Docker.

For the record here's my working configuration:

$ docker info
Containers: 1
 Running: 1
 Paused: 0
 Stopped: 0
Images: 2
Server Version: 1.12.1
Storage Driver: devicemapper
 Pool Name: docker-230:1-131073-pool
 Pool Blocksize: 65.54 kB
 Base Device Size: 10.74 GB
 Backing Filesystem: ext4
 Data file: /dev/loop0
 Metadata file: /dev/loop1
 Data Space Used: 2.485 GB
 Data Space Total: 107.4 GB
 Data Space Available: 7.923 GB
 Metadata Space Used: 2.257 MB
 Metadata Space Total: 2.147 GB
 Metadata Space Available: 2.145 GB
 Thin Pool Minimum Free Space: 10.74 GB
 Udev Sync Supported: true
 Deferred Removal Enabled: false
 Deferred Deletion Enabled: false
 Deferred Deleted Device Count: 0
 Data loop file: /var/lib/docker/devicemapper/devicemapper/data
 WARNING: Usage of loopback devices is strongly discouraged for production use. Use `--storage-opt dm.thinpooldev` to specify a custom block storage device.
 Metadata loop file: /var/lib/docker/devicemapper/devicemapper/metadata
 Library Version: 1.02.93 (2015-01-30)
Logging Driver: json-file
Cgroup Driver: cgroupfs
Plugins:
 Volume: local
 Network: bridge host null overlay
Swarm: inactive
Runtimes: runc
Default Runtime: runc
Security Options: seccomp
Kernel Version: 4.6.5-stable
Operating System: Gentoo/Linux
OSType: linux
Architecture: x86_64
CPUs: 4
Total Memory: 15.59 GiB
Name: tecknack-corin
ID: JXJV:BP5E:T547:PZTF:4JSO:6SV5:B7EO:XA3U:EPV7:7Z5H:BS2Y:SAV7
Docker Root Dir: /var/lib/docker
Debug Mode (client): false
Debug Mode (server): false
Registry: https://index.docker.io/v1/
Insecure Registries:
 127.0.0.0/8

Answer 23 · 2017-05-15T03:15:57.000Z

Have you run any traces while it was running and then capturing the crash?

…

On May 14, 2017 at 7:23 PM, <Corin Lawson ***@***.***)> wrote: (I feels as if this thread has been hijacked by Mac users) I decided to take ZFS out of the mix: First I confirmed I was still receiving the error with the latest image. I create a zvol and formatted it with ext4 and mounted it as my /var/lib/docker directory. Started the docker daemon and ensured it wasn't using zfs as the storage device. Tried again. This time I didn't receive the error, the container stayed up and I have an operational mssql server on linux! I guess the next question is: where does the fault lie? This project (seems unlikely)? Docker (more specifically the zfs storage driver)? Or ZFS? I think I'll close this issue and go ask Docker. For the record here's my working configuration: $ docker info Containers: 1 Running: 1 Paused: 0 Stopped: 0 Images: 2 Server Version: 1.12.1 Storage Driver: devicemapper Pool Name: docker-230:1-131073-pool Pool Blocksize: 65.54 kB Base Device Size: 10.74 GB Backing Filesystem: ext4 Data file: /dev/loop0 Metadata file: /dev/loop1 Data Space Used: 2.485 GB Data Space Total: 107.4 GB Data Space Available: 7.923 GB Metadata Space Used: 2.257 MB Metadata Space Total: 2.147 GB Metadata Space Available: 2.145 GB Thin Pool Minimum Free Space: 10.74 GB Udev Sync Supported: true Deferred Removal Enabled: false Deferred Deletion Enabled: false Deferred Deleted Device Count: 0 Data loop file: /var/lib/docker/devicemapper/devicemapper/data WARNING: Usage of loopback devices is strongly discouraged for production use. Use `--storage-opt dm.thinpooldev` to specify a custom block storage device. Metadata loop file: /var/lib/docker/devicemapper/devicemapper/metadata Library Version: 1.02.93 (2015-01-30) Logging Driver: json-file Cgroup Driver: cgroupfs Plugins: Volume: local Network: bridge host null overlay Swarm: inactive Runtimes: runc Default Runtime: runc Security Options: seccomp Kernel Version: 4.6.5-stable Operating System: Gentoo/Linux OSType: linux Architecture: x86_64 CPUs: 4 Total Memory: 15.59 GiB Name: tecknack-corin ID: JXJV:BP5E:T547:PZTF:4JSO:6SV5:B7EO:XA3U:EPV7:7Z5H:BS2Y:SAV7 Docker Root Dir: /var/lib/docker Debug Mode (client): false Debug Mode (server): false Registry: https://index.docker.io/v1/ Insecure Registries: 127.0.0.0/8 — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub (#13 (comment)), or mute the thread (https://github.com/notifications/unsubscribe-auth/AAk8rXrpPZxLV7L8WbCyd5L3NKHVINDvks5r57cqgaJpZM4LgDrC).

Answer 24 · 2017-05-15T21:58:17.000Z

Here is a full syscall trace from machine with zfsonlinux:

Steps to reproduce

docker run -ti -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 microsoft/mssql-server-linux bash
root@f5f756e8d68c:/# apt update && apt install strace
strace -f -o /tmp/sqlservr.log /opt/mssql/bin/sqlservr

https://dl.thalheim.io/Z03MnoPGwbCXhJOXpBvgwg/sqlservr.log?dl=1

I tried to reason about the repeated sequence of syscalls (some strange enterprise retry handler), but found not failing syscall. Without access to the source code and the uninformative error message, I have no clue what's going on here.

Answer 25 · 2017-05-15T23:06:19.000Z

FWIW we have done zero testing on zfs. @au-phiware Please let us know the issue ID if you do raise this with Docker in a way that is publicly trackable. Thanks!

Answer 26 · 2017-05-15T23:21:56.000Z

The issue can be viewed at moby/moby#33191

Answer 27 · 2017-05-16T06:20:35.000Z

@au-phiware I do not think docker is the best issue tracker for this, as we miss domain knowledge about mssql internals.

Answer 28 · 2017-05-16T09:41:03.000Z

@Mic92 yes, well I'm really not sure where to go... I'll try recreate the issue with docker removed from the mix.

Is there a better issue tracker someplace or should I reopen this one?

Answer 29 · 2017-05-16T10:27:26.000Z

this one is probably better for the moment.

Answer 30 · 2017-05-16T13:46:25.000Z

Okay, I have installed mssql-server onto my system and encounter the same error. Mounting an ext4 filesystem at /var/opt/mssql resolves the error.

I realise that only xfs and ext4 is supported. So, I guess this is a feature request?

At least I have a work around now; I look forward to giving it a spin and adding it to my workflow.

Answer 31 · 2018-02-03T02:19:46.000Z

Same here. Ubuntu + ZFS + LXC.

Answer 32 · 2018-02-05T03:31:52.000Z

@crazybert - Please use Ubuntu 16.04 + ext4 + Docker for now. Keeping this issue open only as an 'enhancement' tracking issue for us to consider in a future release.

Answer 33 · 2018-02-05T04:19:25.000Z

@twright-msft Thanks! Also appeared to work with ext4 on zvol. Not ideal, but good enough.

Answer 34 · 2018-02-14T12:39:37.000Z

Does anyone know what exactly is the missing feature of ZFS, what stops mssql from working? Maybe we just have to enable something like ACLs or similar. ZVOLs are an option but not ideal. I would prefer a normal ZFS filesystem much more.

Answer 35 · 2018-02-14T18:14:03.000Z

looks like the O_DIRECT support is missing in ZFS openzfs/zfs#224...

Answer 36 · 2018-02-15T10:09:48.000Z

a quick hack would be to have a seccomp profile for the container, that masks O_DIRECT in open calls. This would only make things slightly less performant as data would be cached both by ZFS and mssql.

Answer 37 · 2018-02-15T10:11:30.000Z

But to be honest, I would be better if mssql would use POSIX_FADV_DONTNEED instead of O_DIRECT.

Answer 38 · 2018-02-15T10:11:42.000Z

Yes, I found something similar https://code.uplex.de/liblongpath/liblongpath/commit/2e46a921ce2b6b1caa56d39cbd58be85c5988bd0 but I did not get it to work yet

Answer 39 · 2018-02-15T15:11:35.000Z

@Mic92 could you post how exactly one would set up such a seccomp profile?

Answer 40 · 2018-02-16T00:18:13.000Z

Turns out seccomp alone is not enough without using ptrace. However this is quite complicated.
LD_PRELOAD is much easier to use:

// nodirect_open.c 
#define _GNU_SOURCE
#include <dlfcn.h>
#include <fcntl.h>
typedef int (*orig_open_f_type)(const char *pathname, int flags);
int open(const char *pathname, int flags, ...) {
    static orig_open_f_type orig_open;
    if (!orig_open) {
                        orig_open = (orig_open_f_type)dlsym(RTLD_NEXT, "open");
    }
    return orig_open(pathname, flags & ~O_DIRECT);
}

$ apt update gcc && apt install gcc
$ gcc -shared -fpic -o /nodirect_open.so nodirect_open.c -ldl
$ echo /nodirect_open.so > /etc/ld.so.preload
$ /opt/mssql/bin/sqlservr --accept-eula

I had to delete the old database in /var/opt/mssql before

Answer 41 · 2018-02-16T06:45:40.000Z

Thank you! I am not into the filesystem stuff, so can you tell me what effect this has, or more specifically does using this hack (over ZFS) affect stability or performance of the SQL Server in any way?

Answer 42 · 2018-02-16T07:12:23.000Z

@t-oster O_DIRECT is this dirty hack that database guys introduced to bypass any caches for read/writes in the operating system. Reason for that is that it would result in double caching: both the operating system and the database cache data. However databases have in general a better idea what data needs to be cached. In the worst case your caches would be only half as efficient. In practice however it is not that bad:
If the system is under memory pressure it will drop the page cache, while the database continues to keep its own. That means that your operating system will waste some CPU cycles on maintaining unnecessary page and write back caches.

Answer 43 · 2018-02-16T07:13:08.000Z

Also read this thread on the linux developer kernel mailing list: https://lkml.org/lkml/2007/1/10/231

Answer 44 · 2018-02-16T07:49:48.000Z

so I would use zfs set primaraycache=metadata and be happy without concerns about other drawbacks than maybe performance?

Answer 45 · 2018-02-16T08:22:42.000Z

In regards to O_DIRECT I don't see other drawbacks. I have not tested mssql intensively to see what it does elsewhere.

Answer 46 · 2018-02-21T07:41:42.000Z

Thank you @Mic92 it seems to work very good, maybe I will switch our production system from ext4/zvol directly to zfs (after some tests). If anyone else needs this, here you are: https://github.com/t-oster/mssql-docker-zfs

Answer 47 · 2018-02-22T01:53:53.000Z

i just ran into this today. IMO this is more than an enhancement - zfs is the default storage driver for docker on systems that support it.

Answer 48 · 2018-03-25T19:35:30.000Z

Is mssql going to implement something similar to the innodb_flush_method?

It allows for the following types of methods to flush writes to disk:
fsync
O_DSYNC
littlesync
nosync
O_DIRECT
O_DIRECT_NO_FSYNC

Is there any news on this?

Answer 49 · 2018-03-26T19:19:17.000Z

@Smithx10 - We don’t currently have any plans to support additional/configurable flush methods. Do you think we need to?

Answer 50 · 2018-03-26T20:25:38.000Z

@twright-msft , Is there a plan to support ZFS and other operating systems without forcing them to hack around the real problem using LD_PRELOAD?

To be honest, it would be awesome if there was native FreeBSD and Illumos support. My experience would tell me that... this probably will never happen, but hey... It's a New Microsoft right?

:(

DirectIO via the O_DIRECT flag was originally introduced in XFS by IRIX
for database workloads. Its purpose was to allow the database to bypass
the page and buffer caches to prevent unnecessary IO operations (e.g.
readahead) while preventing contention for system memory between the
database and kernel caches.

Unfortunately, the semantics were never defined in any standard. The
semantics of O_DIRECT in XFS in Linux are as follows:

O_DIRECT requires IOs be aligned to backing device's sector size.

O_DIRECT performs unbuffered IO operations between user memory and block
device (DMA when the block device is physical hardware).

O_DIRECT implies O_DSYNC.

O_DIRECT disables any locking that would serialize IO operations.

The first is not possible in ZFS beause there is no backing device in
the general case.

The second is not possible in ZFS in the presence of compression because
that prevents us from doing DMA from user pages. If we relax the
requirement in the case of compression, we encunter another hurdle. In
specific, avoiding the userland to kernel copy risks other userland
threads modifying buffers during compression and checksum computations.
For compressed data, this would cause undefined behavior while for
checksums, this would imply we write incorrect checksums to disk. It
would be possible to avoid those issues if we modify the page tables to
make any changes by userland to memory trigger page faults and perform
CoW operations. However, it is unclear if it is wise for a filesystem
driver to do this.

The third is doable, but we would need to make ZIL perform indirect
logging to avoid writing the data twice.

The fourth is already done for all IO in ZFS.

Other Linux filesystems such as ext4 do not follow #3. Mac OS X does not
implement O_DIRECT, but it does implement F_NOCACHE, which is similiar
to #2 in that it prevents new data from being cached. AIX relaxes #3 by
only committing the file data to disk. Metadata updates required should
the operations make the file larger are asynchronous unless O_DSYNC is
specified.

On Solaris and Illumos, there is a library function called directio(3C)
that allows userspace to provide a hint to the filesystem that DirectIO
is useful, but the filesystem is free to ignore it. The semantics are
also entirely a filesystem decision. Those that do not implement it
return ENOTTY.

Given the lack of standardization and ZFS' heritage, one solution to
provide compatibility with userland processes that expect DirectIO is to
treat DirectIO as a hint that we ignore. This can be done trivially by
implementing a shim that maps aops->direct_IO to AIO. There is also
already code in ZoL for bypassing the page cache when O_DIRECT is
specified, but it has been inert until now.

If it turns out that it is acceptable for a filesystem driver to
interact with the page tables, the scatter-gather list work will need be
finished and we would need to utilize the page tables to make operations
on the userland pages safe.

References:
http://xfs.org/docs/xfsdocs-xml-dev/XFS_User_Guide/tmp/en-US/html/ch02s09.html
https://blogs.oracle.com/roch/entry/zfs_and_directio
https://ext4.wiki.kernel.org/index.php/Clarifying_Direct_IO's_Semantics
https://illumos.org/man/3c/directio
https://developer.apple.com/library/mac/#documentation/Darwin/Reference/ManPages/man2/fcntl.2.html
https://lists.apple.com/archives/filesystem-dev/2007/Sep/msg00010.html

:(

Using O_DIRECT well requires taking on a surprising amount of additional resource management responsibility. Also, the implementation is not portable and, combined with the additional responsibility previously mentioned, adds a ton of conditional compilation. The alignment requirements are not an issue at all; anyone using O_DIRECT is also going to be used properly aligned I/O buffers in any case.O_DIRECT can have a lot of benefits. However, while O_DIRECT may be simple to use by itself, the other design and implementation requirements that are indirectly dragged along are not trivial by any means. Consequently, you should not be using O_DIRECT unless you can manage the substantial indirect overhead in implementation. It turns off a lot of OS features most developers take for granted. | Using O_DIRECT well requires taking on a surprising amount of additional resource management responsibility. Also, the implementation is not portable and, combined with the additional responsibility previously mentioned, adds a ton of conditional compilation. The alignment requirements are not an issue at all; anyone using O_DIRECT is also going to be used properly aligned I/O buffers in any case.O_DIRECT can have a lot of benefits. However, while O_DIRECT may be simple to use by itself, the other design and implementation requirements that are indirectly dragged along are not trivial by any means. Consequently, you should not be using O_DIRECT unless you can manage the substantial indirect overhead in implementation. It turns off a lot of OS features most developers take for granted.
Using O_DIRECT well requires taking on a surprising amount of additional resource management responsibility. Also, the implementation is not portable and, combined with the additional responsibility previously mentioned, adds a ton of conditional compilation. The alignment requirements are not an issue at all; anyone using O_DIRECT is also going to be used properly aligned I/O buffers in any case.O_DIRECT can have a lot of benefits. However, while O_DIRECT may be simple to use by itself, the other design and implementation requirements that are indirectly dragged along are not trivial by any means. Consequently, you should not be using O_DIRECT unless you can manage the substantial indirect overhead in implementation. It turns off a lot of OS features most developers take for granted.

:(

Not that it's particularly bad for PostgreSQL, but as a point of possible future interest, I found out that the Linux kernel does not (and most likely will not in the future) recommend mixing O_DIRECT and non-O_DIRECT I/O on a single file. Doing this is expected to result in very poor performance, because the use of O_DIRECT causes page cache invalidations. I haven't been able to think of a scenario in which this will actually hurt PostgreSQL users today, because we only use O_DIRECT for WAL, and then only if wal_sync_method = open_sync or open_datasync, and then only neither archiving nor streaming replication is in use. However, it's certainly worth keeping in mind if we ever consider expanding the use of O_DIRECT.

:(

Richrd Yao says:
July 7, 2013 at 8:56 am
Vadim, O_DIRECT was designed for in-place filesystems to allow IO to bypass the filesystem layer and caching. A literal implementation of O_DIRECT in a copy-on-write filesystem like ZFS is not possible (because checksum and parity calculations must be done). It is possible to implement it by effectively ignoring the O_DIRECT flag, but I imagine that would defeat the purpose. I imagine is the main reason the solution where O_DIRECT is ignored has not been implemented is that Linux uses a different code path for O_DIRECT and time spent implementing the separate code path is time that could be spent on other bugs.

Most of the development of ZFSOnLinux over the past two years has focused on making it ready for the first stable release. Adding O_DIRECT support did not help contribute to that, so it was a low priority. I imagine that adding O_DIRECT support would occur rather quickly if someone were to write a patch to add it that works. However, adding it would be misleading unless O_DIRECT is implemented in a way that provided some kind of tangible benefit over not using it.

Lastly, ZFSOnLinux development is done by a few professional developers at LLNL and volunteers, such as myself, that happen to use it. LLNL uses ZFSOnLinux as an OSD for the Lustre filesystem on the Sequoia suprecomputer while volunteers tend to use it on either servers or desktops. O_DIRECT is currently scheduled for a release rather far in the future because none of us have any need for O_DIRECT. It should be possible to configure your software to not use O_DIRECT, so doing it sooner does not seem like it should be a priority.

Answer 51 · 2018-03-28T01:36:40.000Z

But hey there is still postgresql/mariadb which will perform faster anyway on *bsd/linux.

Answer 52 · 2018-03-28T02:50:59.000Z

@Mic92 Sometimes as an operator, I get in situations where .net developers want to use mssql, or the Enterprise needs that Windows Stamp.

Answer 53 · 2018-03-28T08:18:28.000Z

Well for professional usage you don't want to use the linux port anyway... It does so many strange things I have seen in strace.

Answer 54 · 2018-03-30T20:46:39.000Z

@twright-msft These are the reasons why you should support more than just Linux. https://www.postgresql.org/message-id/flat/CAMsr%2BYE5Gs9iPqw2mQ6OHt1aC5Qk5EuBFCyG%2BvzHun1EqMxyQg%40mail.gmail.com#CAMsr+YE5Gs9iPqw2mQ6OHt1aC5Qk5EuBFCyG+vzHun1EqMxyQg@mail.gmail.com

Answer 55 · 2018-03-30T20:58:06.000Z

Hmmm.. that URL isnt resolving for me. Can you please check?

Answer 56 · 2018-03-30T21:17:34.000Z

@twright-msft https://twitter.com/justincormack/status/979540227109744640

Answer 57 · 2018-03-31T18:46:15.000Z

Thanks for the heads up @Smithx10 - we are taking a look at it.

Answer 58 · 2018-09-10T13:05:49.000Z

Starting with https://github.com/zfsonlinux/zfs/releases/tag/zfs-0.8.0-rc1 ZFS on Linux supports Direct IO. A quick test with the mssql image succeeded for me. Can't say anything about performance/stability though.

Answer 59 · 2018-09-10T13:11:31.000Z

That's good news! I am successfully running some mssql in docker on ZFS with the LD_PRELOAD hack and did not have any problems so far. But it's better if that is not necessary anymore. I will report as soon as this version is on archzfs, so I can test.

Answer 60 · 2018-10-06T04:15:49.000Z

看了一遍都是英文的,反正英文也不好.就直接上解决思路吧,出现这样的问题呢是因为启动的时候使用了-V挂载数据卷,因为也只是为了有个mssql服务器来调试环境,也就懒得去折腾数据是在本地还是容器中,直接去掉-V就开工干活了,问题也解决了

Answer 61 · 2018-11-24T07:19:03.000Z

I am using macOS Mojave 10.14.1

➜  ~ docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=123456a@' -p 1433:1433 -d -v /Users/donhuvy/Documents/mssql:/var/opt/mssql -d microsoft/mssql-server-linux:2017-CU8
9d4bd2a1da7a3bece6ce1052bc221538fea44b0469c19543e670b2cf5f2a6205
➜  ~ docker logs -f 9d4bd2a1da7a3bece6ce1052bc221538fea44b0469c19543e670b2cf5f2a6205
2018-11-24 07:16:46.99 Server      Microsoft SQL Server 2017 (RTM-CU8) (KB4338363) - 14.0.3029.16 (X64) 
	Jun 13 2018 13:35:56 
	Copyright (C) 2017 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 16.04.4 LTS)
2018-11-24 07:16:47.01 Server      UTC adjustment: 0:00
2018-11-24 07:16:47.01 Server      (c) Microsoft Corporation.
2018-11-24 07:16:47.01 Server      All rights reserved.
2018-11-24 07:16:47.01 Server      Server process ID is 4120.
2018-11-24 07:16:47.01 Server      Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2018-11-24 07:16:47.01 Server      Registry startup parameters: 
	 -d /var/opt/mssql/data/master.mdf
	 -l /var/opt/mssql/data/mastlog.ldf
	 -e /var/opt/mssql/log/errorlog
2018-11-24 07:16:47.04 Server      Error: 17113, Severity: 16, State: 1.
2018-11-24 07:16:47.04 Server      Error 87(The parameter is incorrect.) occurred while opening file '/var/opt/mssql/data/master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
➜  ~

Answer 62 · 2018-11-25T09:00:06.000Z

@donhuvy - macos volume mounting is not currently working/supported. Please see the following issue tracking adding support for this in the future:
#12

Answer 63 · 2018-11-30T15:47:55.000Z

@twright-msft Thank you for your explaination! I have a question (Outside the issue). If I run command

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=123456a@' -p 1433:1433 -d microsoft/mssql-server-linux:2017-CU8

When I quite Docker, my database schema is removed. Then I must run creating new schema SQL script again. When I turn off my computer, then I turn on. I must create database schema(s) again. I mean that, I don't know how to save state of existing database schema(s) after quitting Docker.

(Because I don't know much about Docker) Please help me, show me command, how to saving database schema(s) (even when I turn off computer then turn on to continuing my old work)?

Answer 64 · 2018-11-30T22:46:54.000Z

@donhuvy check this basically you have two options, a docker volumes or bind mount a local folder, by default docker doesn't create this.

Answer 65 · 2018-12-01T02:01:29.000Z

Yep, what @sirio3mil linked to is the general concept. Here's some details and examples for SQL Server specifically. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-docker?view=sql-server-2017#persist

Just a reminder that -v mounting to a host file system ("bind mount") doesnt work on macos (#12 ). If you are using macos you'll need to use the mounted docker volume approach (docker volume create and then mount the docker volume to your container when you run it.)

Answer 66 · 2019-02-06T23:10:17.000Z

Starting with https://github.com/zfsonlinux/zfs/releases/tag/zfs-0.8.0-rc1 ZFS on Linux supports Direct IO. A quick test with the mssql image succeeded for me. Can't say anything about performance/stability though.

That's good. Every few months, I set up a new server and forget that I have to put my SQL Server on non-ZFS volumes. On first start up, I scratch my head for an hour trying to figure it out. Hopefully this fixes the problem.
But until ZFS 0.8 is in production and widely available, if MS fixes it on their end, would that resolve the issue for MacOS too?

Answer 67 · 2019-03-11T13:19:34.000Z

I have exactly the same issue when using docker-compose up with Docker for Windows.

Answer 68 · 2019-03-21T07:59:56.000Z

看了一遍都是英文的,反正英文也不好.就直接上解决思路吧,出现这样的问题呢是因为启动的时候使用了-V挂载数据卷,因为也只是为了有个mssql服务器来调试环境,也就懒得去折腾数据是在本地还是容器中,直接去掉-V就开工干活了,问题也解决了

Do not hang volume? Then your docker restart data still?

Answer 69 · 2019-05-28T04:54:21.000Z

ZFS 0.8 is released and included at least in ArchLinux. I tested it and it works, so this can be closed IMHO. If you are stuck with an old version, use https://github.com/t-oster/mssql-docker-zfs

Answer 70 · 2019-07-15T03:46:47.000Z

原来的

volumes:
- ../data/mssql:/var/opt/mssql

改成

volumes:
- ../data/mssql:/var/opt/mssql
- /var/opt/mssql/data

Answer 71 · 2020-05-24T12:12:17.000Z

I have same issue with both versions 2017-CU8-ubuntu and 2019-CU3-ubuntu-18.04

Run Command

docker run --name mssql-2019-express-docker -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -e 'MSSQL_PID=Express' -v ~/.mssql/data:/var/opt/mssql/data -v ~/.mssql/log:/var/opt/mssql/log -v ~/.mssql/secrets:/var/opt/mssql/secrets -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04

Error

2020-05-24 12:09:10.56 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.mdf' to '/var/opt/mssql/data/model_replicatedmaster.mdf'.
2020-05-24 12:09:10.71 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.ldf' to '/var/opt/mssql/data/model_replicatedmaster.ldf'.
2020-05-24 12:09:10.76 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_msdbdata.mdf' to '/var/opt/mssql/data/model_msdbdata.mdf'.
2020-05-24 12:09:10.93 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_msdblog.ldf' to '/var/opt/mssql/data/model_msdblog.ldf'.
2020-05-24 12:09:11.27 Server      Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) 
Mar  4 2020 00:59:26 
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Linux (Ubuntu 18.04.4 LTS) <X64>
2020-05-24 12:09:11.27 Server      UTC adjustment: 0:00
2020-05-24 12:09:11.28 Server      (c) Microsoft Corporation.
2020-05-24 12:09:11.28 Server      All rights reserved.
2020-05-24 12:09:11.28 Server      Server process ID is 40.
2020-05-24 12:09:11.28 Server      Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2020-05-24 12:09:11.28 Server      Registry startup parameters: 
 -d /var/opt/mssql/data/master.mdf
 -l /var/opt/mssql/data/mastlog.ldf
 -e /var/opt/mssql/log/errorlog
2020-05-24 12:09:11.30 Server      Error: 17113, Severity: 16, State: 1.
2020-05-24 12:09:11.30 Server      Error 87(The parameter is incorrect.) occurred while opening file '/var/opt/mssql/data/master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

Answer 72 · 2020-05-27T10:40:28.000Z

@dennisdaotvlk, you have exact the same issue I have, here is my docker compose file.

version: "3.8"
services:
  db:
    build:
      context: .
      dockerfile: db.Dockerfile
    image: my-db
    # https://docs.docker.com/compose/compose-file/#container_name
    container_name: my-db-server
    ports:
      - "1444:1433"
    # https://docs.docker.com/compose/compose-file/#environment
    # https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-environment-variables?view=sql-server-ver15#environment-variables
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "${SA_PASSWORD:?err}"
      MSSQL_PID: Developer
      MSSQL_DATA_DIR: /var/opt/mssql/data
      MSSQL_LOG_DIR: /var/opt/mssql/logs
      MSSQL_BACKUP_DIR: /var/opt/mssql/backups
      MSSQL_MEMORY_LIMIT_MB: 2048
      MSSQL_AGENT_ENABLED: "true" #requires string, not bool
      MSSQL_COLLATION: Thai_CI_AS
    volumes:
      - mssql_data:/var/opt/mssql/data
      - mssql_logs:/var/opt/mssql/logs
      - mssql_backups:/var/opt/mssql/backups
  # alternative syntax to just have it in local path
  # - ./sqlsystem:/var/opt/mssql/data
  # - ./sqluser:/var/opt/mssql/sqlserver
  # - ./sqlbackups:/var/opt/mssql/backups

# Top level volumes to define name volumes
# https://docs.docker.com/compose/compose-file/#volumes
volumes:
  mssql_data:
  mssql_logs:
  mssql_backups:

My Dockerfile

FROM mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

EXPOSE 1433
COPY ./init-db.sh /
COPY ./query.sql /

RUN whoami
USER root
RUN chmod +x ./init-db.sh
USER mssql

CMD  ./init-db.sh & /opt/mssql/bin/sqlservr

Answer 73 · 2020-06-02T02:26:35.000Z

I have the exact same issue but I'm not using ZFS. Plain Debian 10 server. See details here -> #542 (comment)

Answer 74 · 2020-06-10T10:15:11.000Z

Nevermind, fixed it setting the container to run as root. In non-root mode, it can't create the .mdf files on a bind-mount volume (and no, I don't want to use docker volumes).

Previously:

version: '3.6'
services:

  mssql:
    restart: always
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=BLAH
    volumes:
      - ./data:/var/opt/mssql/data

Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2019-latest
Creating mssql_mssql_1 ... done
Attaching to mssql_mssql_1
mssql_1  | SQL Server 2019 will run as non-root by default.
mssql_1  | This container is running as user mssql.
mssql_1  | To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
mssql_1  | 2020-06-10 09:54:56.74 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2020-06-10 09:54:56.94 Server      ERROR: Setup FAILED copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf':  2(The system cannot find the file specified.)
ERROR: BootstrapSystemDataDirectories() failure (HRESULT 0x80070002)
mssql_mssql_1 exited with code 1

Now:

version: '3.6'
services:

  mssql:
    restart: always
    image: mcr.microsoft.com/mssql/server:2019-latest
    user: root
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=BLAH
    volumes:
      - ./data:/var/opt/mssql/data

Recreating mssql_mssql_1 ... done
Attaching to mssql_mssql_1
mssql_1  | SQL Server 2019 will run as non-root by default.
mssql_1  | This container is running as user root.
mssql_1  | To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
mssql_1  | 2020-06-10 10:11:55.69 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2020-06-10 10:11:55.91 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.
2020-06-10 10:11:55.94 Server      Setup step is copying system data file 'C:\templatedata\mastlog.ldf' to '/var/opt/mssql/data/mastlog.ldf'.
2020-06-10 10:11:55.95 Server      Setup step is copying system data file 'C:\templatedata\model.mdf' to '/var/opt/mssql/data/model.mdf'.
2020-06-10 10:11:55.99 Server      Setup step is copying system data file 'C:\templatedata\modellog.ldf' to '/var/opt/mssql/data/modellog.ldf'.
2020-06-10 10:11:56.02 Server      Setup step is copying system data file 'C:\templatedata\msdbdata.mdf' to '/var/opt/mssql/data/msdbdata.mdf'.
2020-06-10 10:11:56.07 Server      Setup step is copying system data file 'C:\templatedata\msdblog.ldf' to '/var/opt/mssql/data/msdblog.ldf'.
2020-06-10 10:11:56.08 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.mdf' to '/var/opt/mssql/data/model_replicatedmaster.mdf'.
2020-06-10 10:11:56.12 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.ldf' to '/var/opt/mssql/data/model_replicatedmaster.ldf'.
2020-06-10 10:11:56.14 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_msdbdata.mdf' to '/var/opt/mssql/data/model_msdbdata.mdf'.
2020-06-10 10:11:56.19 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_msdblog.ldf' to '/var/opt/mssql/data/model_msdblog.ldf'.
2020-06-10 10:11:56.43 Server      Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64)
mssql_1 Mar 14 2020 16:10:35
mssql_1 Copyright (C) 2019 Microsoft Corporation
mssql_1 Developer Edition (64-bit) on Linux (Ubuntu 18.04.4 LTS) <X64>
2020-06-10 10:11:56.45 Server      UTC adjustment: 0:00
2020-06-10 10:11:56.45 Server      (c) Microsoft Corporation.
2020-06-10 10:11:56.45 Server      All rights reserved.
2020-06-10 10:11:56.46 Server      Server process ID is 36.
2020-06-10 10:11:56.46 Server      Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2020-06-10 10:11:56.47 Server      Registry startup parameters:
mssql_1  -d /var/opt/mssql/data/master.mdf
mssql_1  -l /var/opt/mssql/data/mastlog.ldf
mssql_1  -e /var/opt/mssql/log/errorlog
blah...

Success! :)

Answer 75 · 2020-06-10T10:41:44.000Z

Wouldn't it be better to just chown the volume folders to whatever uid the container is using by default?

Answer 76 · 2020-06-10T10:47:28.000Z

Wouldn't it be better to just chown the volume folders to whatever uid the container is using by default?

Maybe, but I don't know how to do that with docker-compose. My use-case was creating a brand new container, no existing files (i.e. ./data didn't exist). It works out-of-the-box if I use an existing data folder though, but I needed to create an empty container and found this issue.

If you know how to do that with docker-compose, and it's safer to do this than running it as root, awesome!

Answer 77 · 2020-07-04T05:13:02.000Z

Hey, so I ran into this issue, and updated my ZFS just to solve it, but it didn't get rid of the issue, mssql still claims it can't access the files that are stored on the ZFS pool.

Answer 78 · 2021-01-28T08:54:29.000Z

This is still an issue on other file systems that do not support O_DIRECT like ramfs.

There are a couple of toggles like -T3979 and control.writethrough and control.alternatewritethrough (more details at https://bobsql.com/sql-server-on-linux-forced-unit-access-fua-internals/), but from my understanding they do not suppress open(O_DIRECT).

The situation that sql server crashes on ramfs is especially an issue for CI - This cares much more about speed than about consistency in case of power outages or similar.

In our case, we're running the entire /var/lib/docker in ramfs, to speed up testing by an order of magnitude even with 'quick' disks. sql server container are then nested in docker-dind. The option to volume mount 'real disk' as /var/opt/mssql is quite a bit of hassle and also leads to headaches when multiple such containers run in parallel. Not an option in our case.

But the hack from https://github.com/t-oster/mssql-docker-zfs works: It overrides open() to drop O_DIRECT flag and forces this into the system via LD_PRELOAD. Brutal and for sure not for anything production like, but it's a simple solution for CI.

Put the nodirect_open.c next to a Dockerfile with this content:

FROM mcr.microsoft.com/mssql/server:2019-latest
ADD nodirect_open.c /
USER root
RUN apt-get update \
	&& apt-get install -y gcc \
	&& gcc -shared -fpic -o /nodirect_open.so nodirect_open.c -ldl \
	&& apt-get purge -y gcc binutils binutils-common binutils-x86-64-linux-gnu cpp cpp-7 gcc gcc-7 \
  		gcc-7-base libasan4 libatomic1 libbinutils libc-dev-bin libc6-dev \
  		libcilkrts5 libgcc-7-dev libgomp1 libisl19 libitm1 liblsan0 libmpc3 libmpx2 \
  		libquadmath0 libtsan0 libubsan0 linux-libc-dev manpages manpages-dev \
	&& apt-get clean \
	&& rm -rf /var/lib/apt/lists/* \
	&& echo "/nodirect_open.so" >> /etc/ld.so.preload

Answer 79 · 2023-04-20T09:12:19.000Z

IMHO this can be closed. ZFS now supports O_DIRECT out of the box, so no hack is necessary anymore.
However, it would be good to have a few infos about the best ZFS configuration (recordsize, primarycache etc) for optimizing sql-server on docker/linux.

Answer 80 · 2023-04-20T13:00:08.000Z

The issue has been abused (by me as well) for other file systems, too.
afaiks, mssql still has no solution to suppress O_DIRECT itself, the hack is still required for different file systems.
We could close the issue (judging by issue title), but if so, a new issue regarding O_DIRECT should be created, I suppose.

Answer 81 · 2024-03-02T14:58:30.000Z

Nevermind, fixed it setting the container to run as root. In non-root mode, it can't create the .mdf files on a bind-mount volume (and no, I don't want to use docker volumes).

Previously:

version: '3.6'
services:

  mssql:
    restart: always
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=BLAH
    volumes:
      - ./data:/var/opt/mssql/data

Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2019-latest
Creating mssql_mssql_1 ... done
Attaching to mssql_mssql_1
mssql_1  | SQL Server 2019 will run as non-root by default.
mssql_1  | This container is running as user mssql.
mssql_1  | To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
mssql_1  | 2020-06-10 09:54:56.74 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2020-06-10 09:54:56.94 Server      ERROR: Setup FAILED copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf':  2(The system cannot find the file specified.)
ERROR: BootstrapSystemDataDirectories() failure (HRESULT 0x80070002)
mssql_mssql_1 exited with code 1

Now:

version: '3.6'
services:

  mssql:
    restart: always
    image: mcr.microsoft.com/mssql/server:2019-latest
    user: root
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=BLAH
    volumes:
      - ./data:/var/opt/mssql/data

Recreating mssql_mssql_1 ... done
Attaching to mssql_mssql_1
mssql_1  | SQL Server 2019 will run as non-root by default.
mssql_1  | This container is running as user root.
mssql_1  | To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
mssql_1  | 2020-06-10 10:11:55.69 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2020-06-10 10:11:55.91 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.
2020-06-10 10:11:55.94 Server      Setup step is copying system data file 'C:\templatedata\mastlog.ldf' to '/var/opt/mssql/data/mastlog.ldf'.
2020-06-10 10:11:55.95 Server      Setup step is copying system data file 'C:\templatedata\model.mdf' to '/var/opt/mssql/data/model.mdf'.
2020-06-10 10:11:55.99 Server      Setup step is copying system data file 'C:\templatedata\modellog.ldf' to '/var/opt/mssql/data/modellog.ldf'.
2020-06-10 10:11:56.02 Server      Setup step is copying system data file 'C:\templatedata\msdbdata.mdf' to '/var/opt/mssql/data/msdbdata.mdf'.
2020-06-10 10:11:56.07 Server      Setup step is copying system data file 'C:\templatedata\msdblog.ldf' to '/var/opt/mssql/data/msdblog.ldf'.
2020-06-10 10:11:56.08 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.mdf' to '/var/opt/mssql/data/model_replicatedmaster.mdf'.
2020-06-10 10:11:56.12 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.ldf' to '/var/opt/mssql/data/model_replicatedmaster.ldf'.
2020-06-10 10:11:56.14 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_msdbdata.mdf' to '/var/opt/mssql/data/model_msdbdata.mdf'.
2020-06-10 10:11:56.19 Server      Setup step is FORCE copying system data file 'C:\templatedata\model_msdblog.ldf' to '/var/opt/mssql/data/model_msdblog.ldf'.
2020-06-10 10:11:56.43 Server      Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64)
mssql_1 Mar 14 2020 16:10:35
mssql_1 Copyright (C) 2019 Microsoft Corporation
mssql_1 Developer Edition (64-bit) on Linux (Ubuntu 18.04.4 LTS) <X64>
2020-06-10 10:11:56.45 Server      UTC adjustment: 0:00
2020-06-10 10:11:56.45 Server      (c) Microsoft Corporation.
2020-06-10 10:11:56.45 Server      All rights reserved.
2020-06-10 10:11:56.46 Server      Server process ID is 36.
2020-06-10 10:11:56.46 Server      Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2020-06-10 10:11:56.47 Server      Registry startup parameters:
mssql_1  -d /var/opt/mssql/data/master.mdf
mssql_1  -l /var/opt/mssql/data/mastlog.ldf
mssql_1  -e /var/opt/mssql/log/errorlog
blah...

Success! :)

Steps to reproduce

this helped me solve my mistake, thank you