Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ZFS Support #13

Open
au-phiware opened this issue Jan 11, 2017 · 78 comments
Open

ZFS Support #13

au-phiware opened this issue Jan 11, 2017 · 78 comments

Comments

@au-phiware
Copy link

@au-phiware au-phiware commented Jan 11, 2017

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.

@au-phiware
Copy link
Author

@au-phiware au-phiware commented Jan 11, 2017

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

Loading

@twright-msft
Copy link
Collaborator

@twright-msft twright-msft commented Jan 11, 2017

@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?

Loading

@au-phiware
Copy link
Author

@au-phiware au-phiware commented Jan 11, 2017

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...?

Loading

@au-phiware
Copy link
Author

@au-phiware au-phiware commented Jan 11, 2017

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?

Loading

@au-phiware
Copy link
Author

@au-phiware au-phiware commented Jan 11, 2017

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

Loading

@twright-msft
Copy link
Collaborator

@twright-msft twright-msft commented Jan 12, 2017

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

Loading

@au-phiware
Copy link
Author

@au-phiware au-phiware commented Jan 12, 2017

Loading

@rouke-broersma
Copy link

@rouke-broersma rouke-broersma commented Jan 25, 2017

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

Loading

@twright-msft
Copy link
Collaborator

@twright-msft twright-msft commented Jan 26, 2017

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

Loading

@rouke-broersma
Copy link

@rouke-broersma rouke-broersma commented Jan 26, 2017

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.

Loading

@laurentleseigneur
Copy link

@laurentleseigneur laurentleseigneur commented Feb 2, 2017

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

Loading

@twright-msft
Copy link
Collaborator

@twright-msft twright-msft commented Feb 2, 2017

@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

Loading

@laurentleseigneur
Copy link

@laurentleseigneur laurentleseigneur commented Feb 2, 2017

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

Loading

@sirio3mil
Copy link

@sirio3mil sirio3mil commented Feb 13, 2017

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

Loading

@PatrickDePuydt
Copy link

@PatrickDePuydt PatrickDePuydt commented Mar 8, 2017

@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?

Loading

@twright-msft
Copy link
Collaborator

@twright-msft twright-msft commented Mar 8, 2017

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

Loading

@PatrickDePuydt
Copy link

@PatrickDePuydt PatrickDePuydt commented Mar 8, 2017

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

Loading

@twright-msft
Copy link
Collaborator

@twright-msft twright-msft commented Mar 8, 2017

@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

Loading

@PatrickDePuydt
Copy link

@PatrickDePuydt PatrickDePuydt commented Mar 8, 2017

@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 ?

Loading

@twright-msft
Copy link
Collaborator

@twright-msft twright-msft commented Mar 8, 2017

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.

Loading

@somashekarg
Copy link

@somashekarg somashekarg commented Mar 11, 2017

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

Loading

@au-phiware
Copy link
Author

@au-phiware au-phiware commented May 15, 2017

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

I decided to take ZFS out of the mix:

  1. First I confirmed I was still receiving the error with the latest image.
  2. I create a zvol and formatted it with ext4 and mounted it as my /var/lib/docker directory.
  3. Started the docker daemon and ensured it wasn't using zfs as the storage device.
  4. 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

Loading

@gigatexal
Copy link

@gigatexal gigatexal commented May 15, 2017

Loading

@Mic92
Copy link

@Mic92 Mic92 commented May 15, 2017

Here is a full syscall trace from machine with zfsonlinux:

Steps to reproduce

  1. docker run -ti -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 microsoft/mssql-server-linux bash
  2. root@f5f756e8d68c:/# apt update && apt install strace
  3. 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.

Loading

@twright-msft
Copy link
Collaborator

@twright-msft twright-msft commented May 15, 2017

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!

Loading

@au-phiware
Copy link
Author

@au-phiware au-phiware commented May 15, 2017

The issue can be viewed at moby/moby#33191

Loading

@Mic92
Copy link

@Mic92 Mic92 commented May 16, 2017

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

Loading

@au-phiware
Copy link
Author

@au-phiware au-phiware commented May 16, 2017

@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?

Loading

@silverspringhusker
Copy link

@silverspringhusker silverspringhusker commented Feb 6, 2019

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?

Loading

@dropsonic
Copy link

@dropsonic dropsonic commented Mar 11, 2019

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

Loading

@eggeggss
Copy link

@eggeggss eggeggss commented Mar 21, 2019

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

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

Loading

@t-oster
Copy link

@t-oster t-oster commented May 28, 2019

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

Loading

@Y-YJ
Copy link

@Y-YJ Y-YJ commented Jul 15, 2019

原来的

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

改成

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

Loading

@dennisdaotvlk
Copy link

@dennisdaotvlk dennisdaotvlk commented May 24, 2020

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.

Loading

@aaronamm
Copy link

@aaronamm aaronamm commented May 27, 2020

@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

Loading

@darkguy2008
Copy link

@darkguy2008 darkguy2008 commented Jun 2, 2020

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

Loading

@darkguy2008
Copy link

@darkguy2008 darkguy2008 commented Jun 10, 2020

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! :)

Loading

@t-oster
Copy link

@t-oster t-oster commented Jun 10, 2020

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

Loading

@darkguy2008
Copy link

@darkguy2008 darkguy2008 commented Jun 10, 2020

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!

Loading

@Window-Hero
Copy link

@Window-Hero Window-Hero commented Jul 4, 2020

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.

Loading

@lolli42
Copy link

@lolli42 lolli42 commented Jan 28, 2021

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

Loading

potiuk added a commit to potiuk/airflow that referenced this issue May 29, 2021
Seems that MSSQL is not able to use data volume when it is mounted
from tmpfs filesystem. See microsoft/mssql-docker#13

In such case, instead of mounting docker-created volume we mount
a volume mounted from home directory of the user which is unlikely
to be a tmpfs volume.
potiuk added a commit to potiuk/airflow that referenced this issue May 29, 2021
Seems that MSSQL is not able to use data volume when it is mounted
from tmpfs filesystem. See microsoft/mssql-docker#13

In such case, instead of mounting docker-created volume we mount
a volume mounted from home directory of the user which is unlikely
to be a tmpfs volume.
potiuk added a commit to potiuk/airflow that referenced this issue May 30, 2021
Seems that MSSQL is not able to use data volume when it is mounted
from tmpfs filesystem. See microsoft/mssql-docker#13

In such case, instead of mounting docker-created volume we mount
a volume mounted from home directory of the user which is unlikely
to be a tmpfs volume.
potiuk added a commit to potiuk/airflow that referenced this issue May 30, 2021
Seems that MSSQL is not able to use data volume when it is mounted
from tmpfs filesystem. See microsoft/mssql-docker#13

In such case, instead of mounting docker-created volume we mount
a volume mounted from home directory of the user which is unlikely
to be a tmpfs volume.
potiuk added a commit to potiuk/airflow that referenced this issue May 30, 2021
Seems that MSSQL is not able to use data volume when it is mounted
from tmpfs filesystem. See microsoft/mssql-docker#13

In such case, instead of mounting docker-created volume we mount
a volume mounted from home directory of the user which is unlikely
to be a tmpfs volume.
potiuk added a commit to potiuk/airflow that referenced this issue May 30, 2021
Seems that MSSQL is not able to use data volume when it is mounted
from tmpfs filesystem. See microsoft/mssql-docker#13

In such case, instead of mounting docker-created volume we mount
a volume mounted from home directory of the user which is unlikely
to be a tmpfs volume.
potiuk added a commit to potiuk/airflow that referenced this issue May 30, 2021
Seems that MSSQL is not able to use data volume when it is mounted
from tmpfs filesystem. See microsoft/mssql-docker#13

In such case, instead of mounting docker-created volume we mount
a volume mounted from home directory of the user which is unlikely
to be a tmpfs volume.
potiuk added a commit to potiuk/airflow that referenced this issue May 31, 2021
Seems that MSSQL is not able to use data volume when it is mounted
from tmpfs filesystem. See microsoft/mssql-docker#13

In such case, instead of mounting docker-created volume we mount
a volume mounted from home directory of the user which is unlikely
to be a tmpfs volume.
potiuk added a commit to potiuk/airflow that referenced this issue May 31, 2021
Seems that MSSQL is not able to use data volume when it is mounted
from tmpfs filesystem. See microsoft/mssql-docker#13

In such case, instead of mounting docker-created volume we mount
a volume mounted from home directory of the user which is unlikely
to be a tmpfs volume.
potiuk added a commit to apache/airflow that referenced this issue May 31, 2021
…16159)

Seems that MSSQL is not able to use data volume when it is mounted
from tmpfs filesystem. See microsoft/mssql-docker#13

In such case, instead of mounting docker-created volume we mount
a volume mounted from home directory of the user which is unlikely
to be a tmpfs volume.
andormarkus pushed a commit to andormarkus/airflow that referenced this issue Jun 5, 2021
…pache#16159)

Seems that MSSQL is not able to use data volume when it is mounted
from tmpfs filesystem. See microsoft/mssql-docker#13

In such case, instead of mounting docker-created volume we mount
a volume mounted from home directory of the user which is unlikely
to be a tmpfs volume.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet