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

MySQL docker 5.7.6 and later fails to initialize database #69

Closed
guss77 opened this Issue May 3, 2015 · 37 comments

Comments

Projects
None yet
@guss77

guss77 commented May 3, 2015

When using the docker image to start without an existing database, the container's entrypoint.sh script tries to call the mysqld binary to create the database. This fails in versions later than 5.7.5 because the script starts by calling mysqld --verbose --help to get the configured datadir and when that runs and there is no database, it initializes it automatically.

When the script then calls --initialize, that fails with the error:

 [ERROR] --initialize specified but the data directory has files in it. Aborting.

The workaround is to run mysql:5.7.5 to create the database, then upgrade to a later image, which will work because the database already exists.

@md5

This comment has been minimized.

md5 commented May 3, 2015

The mysql:5.7.6 image was tested with this test before release: https://github.com/docker-library/official-images/blob/master/test/tests/mysql-basics/run.sh

You may need to provide some more information about your failure.

@guss77

This comment has been minimized.

guss77 commented May 4, 2015

The problem is one of permissions: the container has the data dir /var/lib/mysql owned by root, so when entrypoint.sh is calling mysqld --help to get the data dir, the directory isn't writable by mysqld and mysqld can't write the innodb log files it will normally create automatically when there is no database already set up. entrypoint.sh will later chown the directory.

The problem is that the container relies on this behavior for successful initialization, but it can break when using a volume to mount the data dir from another long-running file system (whether on the host or another container): when a the container is started on an empty volume, the mysql user may have write permissions there (because the volume is writable to anyone or because the volume was previously used to host another mysql database and the admin just rm -rf $vol/* it). If that is the case, then the first call to mysqld --help will create the innodb log files and then initialization will fail.

@ltangvald

This comment has been minimized.

Collaborator

ltangvald commented May 11, 2015

This is a known issue with MySQL 5.7.6 and later, and is being looked into:
http://bugs.mysql.com/bug.php?id=75995.
We have a temporary workaround in the mysql/mysql-server:5.7 image:
https://github.com/mysql/mysql-docker/blob/mysql-server/5.7/docker-entrypoint.sh
Note that the whole if-file-exists check can probably be replaced with a simple rm -f

@ltangvald

This comment has been minimized.

Collaborator

ltangvald commented Jun 11, 2015

There's a better workaround for this; running mysqld --verbose --help with the --innodb-read-only option prevents it from creating the files. Could maybe make a PR for this, as it's not as ugly :)

@tianon

This comment has been minimized.

Member

tianon commented Jun 11, 2015

👍 🤘

It's not necessary in our latest version though, is it? Are we still creating those files prematurely?

@ltangvald

This comment has been minimized.

Collaborator

ltangvald commented Jun 12, 2015

The reason it's not necessary is that mysqld doesn't have write access to /var/lib/mysql on the Debian image (permissions on $DATADIR are set after --verbose --help is run), so it'll simply fail to create the files. On the OL image ownership on the folder is set when the server is installed, so this workaround is needed even with the default setup.

The problem for the official image is that if the user provides a --datadir that is writable from the start, it'll create the files and initialization will fail.

@yosifkit

This comment has been minimized.

Member

yosifkit commented Jul 28, 2015

@guss77 did #78 fix this for you?

@bittner

This comment has been minimized.

bittner commented Dec 28, 2016

Disclaimer

Not sure if I will be threatened to death for saying this, but ... well, see below.

ℹ️ This is meant as a help for people looking for a solution for their project, not for this project.

Use MariaDB

Instead of using the mysql image people may use the mariadb image, which works flawlessly with Docker volumes. Example docker-compose.yml file:

version: "2"

volumes:
  db_data: {}

services:
  app:
    # ...
    depends_on:
      - db

  db:
    image: mariadb
    environment:
      MYSQL_ROOT_PASSWORD: secret
      MYSQL_DATABASE: mysql
      MYSQL_USER: mysql
      MYSQL_PASSWORD: mysql
    volumes:
      - db_data:/var/lib/mysql/data
@ltangvald

This comment has been minimized.

Collaborator

ltangvald commented Jan 2, 2017

The MySQL and MariaDB images are for the most part maintained by the same people and process, one working as well as the other (or as badly). This bug is for MySQL 5.7, which doesn't have a comparable MariaDB version.

@jonathan-kosgei

This comment has been minimized.

jonathan-kosgei commented Mar 24, 2017

This trickles down to other providers, I hit this running a percona cluster backed by glusterfs

@ramizrshaik

This comment has been minimized.

ramizrshaik commented Apr 25, 2017

mine isn't fixed yet. i am still getting 'initialized specified but the data directory has files in it' i tried to clear the directories and made clean start.

@kersten

This comment has been minimized.

kersten commented Apr 28, 2017

I have this problem too. Currently I delete the volume and let kubernetes recreate one if one pod fails to start, but that is not really a nice solution.

@jcollum

This comment has been minimized.

jcollum commented Nov 3, 2017

@ltangvald

This comment has been minimized.

Collaborator

ltangvald commented Nov 6, 2017

@jcollum: Do you have a custom config for the server (or a complete compose file that can be used to reproduce)? The original bug here has been fixed, so you shouldn't be hitting the same issue.

@jcollum

This comment has been minimized.

jcollum commented Nov 8, 2017

My issue is fixed. Had a lot of help with it so I'm not sure what the problem was.

@MartinVandersteen

This comment has been minimized.

MartinVandersteen commented Jan 9, 2018

If it can help someone, I had the same problem while creating a mysql:5.7 pod on Kubernetes with a persistentVolumeClaim. Turns out that mysql considered the volume "not empty" because of the lost+found file. I followed yosifkit & alexpls's answers on issue #186 which consists of starting mysql with the arg "--ignore-db-dir=lost+found" and it worked!

@alwinmark

This comment has been minimized.

alwinmark commented Feb 14, 2018

Seriously no fix no reaction and we already got 2018????? This absolutely should be baked into the image!

@md5

This comment has been minimized.

md5 commented Feb 14, 2018

@alwinmark What “this” do you think should be baked into the image exactly?

@alwinmark

This comment has been minimized.

alwinmark commented Feb 15, 2018

I don't know which way you prefer, but I see three ways:

  1. Add the "--ignore-db-dir=lost+found" here: https://github.com/docker-library/mysql/blob/master/5.7/docker-entrypoint.sh#L7
  2. Add the config to the mysql config: https://github.com/docker-library/mysql/blob/master/5.7/Dockerfile#L67
  3. Delete the lost+found folder when it exists: https://github.com/kubernetes/charts/blob/master/stable/percona/templates/deployment.yaml#L17-L26

I think its not very nice that everyone has to do this workaround by hand or that you have to spawn a oneway init container just to remove that folder like in the helm script.

Especially this "bug" is known since 2015 and occurs in many repos and settings. For example the official Helm chart was fixed in 2017 and also stumbled about this problem: helm/charts#416

@tianon

This comment has been minimized.

Member

tianon commented Feb 15, 2018

Honestly, I would always recommend that for any service, one should be providing a subdirectory of the formatted partition rather than the root of it (not just for MySQL deployments, but across the board) -- it adds a lot of benefits beyond just avoiding "the lost+found problem", including and especially the flexibility to move the directory around, snapshot it, etc. without having to deal with doing that at the full partition level.

If I recall correctly, the way storage is configured for Pods in Kubernetes even makes using a subdirectory of the mounted volume really trivial.

If all we do is delete the lost+found directory when it exists, then it might return (as it's a feature of the underlying filesystem, not userspace). Adjusting the default configuration of the image is something we'd defer to a decision by upstream.

Additionally, "no reaction" is a bit of an overstatement -- all three co-maintainers of this image have chimed in more than once on the exact issue you're commenting on. 😉

@marlon-nc

This comment has been minimized.

marlon-nc commented Feb 15, 2018

That's nice advice I guess, but is it the position of the image maintainers that using the root of a volume as the data directory is not supported?

@neongrau

This comment has been minimized.

neongrau commented Mar 9, 2018

Today i tried docker for the first time with trying to set up a wordpress instance.
Got stuck on this issue and wasted an hour trying to get around it. Finally followed the advice from @bittner and switched to mariadb docker image. vOv

@superjose

This comment has been minimized.

superjose commented Mar 12, 2018

@bittner JESUS! 5 HOURS! 5 HOURS! Migrating a simple Rails app to Docker, and it was HELL! Tried running mysql:5.7.21 and it was AWFUL.

THANK YOU! It's freaking working now.

@oclausen

This comment has been minimized.

oclausen commented Mar 13, 2018

I tried to set up Ghost with MySQL on an AWS Docker Swarm and encountered this issue. Switching to MariaDB fixed the problem. I'm not sure if I keep this environment because unfortunately Ghost doesn't officially support MariaDB. Thank you @bittner

nymd added a commit to oysterprotocol/brokernode that referenced this issue Mar 22, 2018

changed mysql persistent data dirs so they don't overlap (both were o…
…n the same dir)

changed mysql from 5.7 to 5.6
see:
docker-library/mysql#275
docker-library/mysql#69 (comment)

I had a workaround for it by adding the user manually so if that is needed I can put it back to 5.7 but 5.6 works out of the box

added a sleep command to the buffalo / app container so that on the first creation of the mysql data dirs, it waits for them to complete.
after first creation, the command would run fine (which is why it ran locally but not on travis-ci)
@zishanj

This comment has been minimized.

zishanj commented Apr 8, 2018

Actually this is the path and you should mention a valid path for this to work. If your data directory is in current directory then instead of my-data you should mention ./my-data, otherwise it will give you that error in mysql and mariadb also.

volumes:
./my-data:/var/lib/mysql
@theterran

This comment has been minimized.

theterran commented Apr 20, 2018

After far too much time spent researching this issue in April 2018, I finally discovered a very simple change to the container volume path in docker-compose.yml that resolves the error for the current MySQL image. (Using image: mysql:5.7 pulls mysql image with version 5.7.21 on 2018-04-19.)

Although documentation for the official image suggests using a volume like /my/own/datadir:/var/lib/mysql, I found many tutorials and examples that suggested something like /my/own/datadir:/var/lib/mysql/data. Should have looked over the official material…

The error:

--initialize specified but the data directory has files in it. Aborting.

This fails, resulting in the above error:

volumes:
  - /host/folder:/var/lib/mysql/data

This succeeds:

volumes:
  - /host/folder:/var/lib/mysql

Using /var/lib/mysql instead of /var/lib/mysql/data in the container path resolved the error.

The local volume path can be relative (e.g. my-data or ./my-data) or absolute (/path/to/my-data).

Hope that helps someone!

@copriwolf

This comment has been minimized.

copriwolf commented Apr 24, 2018

@bittner awesome~fix the problem

@ltangvald

This comment has been minimized.

Collaborator

ltangvald commented May 5, 2018

For future reference:
MySQL will tend to treat any preexisting directory in datadir (var/lib/mysql) as a database schema, which is why 5.7 introduced the restriction that it has to be empty before initalizing.

Common causes of this issue is incorrect mapping of the volume (e.g. /var/lib/mysql/data instead of /var/lib/mysql, as noted above) or mapping datadir to the root directory of a partition, which will often contain a directory named LOST&FOUND or similar.

@silent-hero

This comment has been minimized.

silent-hero commented Jul 20, 2018

I don't know if this is still relevant but I for me this worked, in docker-compose.yml change this:

    volumes: - "./docker/.data/db:/var/lib/mysql"

To this:

    volumes: - .:/application
@gaussianblurs

This comment has been minimized.

gaussianblurs commented Jul 24, 2018

I am still running into this issue, with Docker on Windows 10, here is the relevant part of my docker-compose file:

mysqldb:
        image: mysql:5.7.22
        container_name: ${MYSQL_HOST}
        restart: always
        env_file:
            - ".env"
        environment:
            - MYSQL_DATABASE=${MYSQL_DATABASE}
            - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
            - MYSQL_USER=${MYSQL_USER}
            - MYSQL_PASSWORD=${MYSQL_PASSWORD}
        ports:
            - "8989:3306"
        volumes:
            - "./data/db/mysql:/var/lib/mysql"

The directory does not exists before launching the container but I systematically get

[ERROR] --initialize specified but the data directory has files in it. Aborting.

regardless of the version of mysql I am using.

I have tried adding command: --ignore-db-dir=lost+found to my docker-compose file, but still no luck

EDIT: Solved by adding --innodb-use-native-aio=0

@serchuz

This comment has been minimized.

serchuz commented Aug 6, 2018

@gaussianblurs solution command: --innodb-use-native-aio=0 on my docker-compose file works for me (Windows 10 + Docker toolbox)

@wicadmin

This comment has been minimized.

wicadmin commented Aug 10, 2018

@gaussianblurs @serchuz - where did you add this in docker-compose.yml?

@snewer

This comment has been minimized.

snewer commented Aug 10, 2018

@wicadmin

mysqldb:
        image: mysql:5.7.22
        container_name: ${MYSQL_HOST}
        restart: always
        env_file:
            - ".env"
        environment:
            - MYSQL_DATABASE=${MYSQL_DATABASE}
            - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
            - MYSQL_USER=${MYSQL_USER}
            - MYSQL_PASSWORD=${MYSQL_PASSWORD}
        ports:
            - "8989:3306"
        volumes:
            - "./data/db/mysql:/var/lib/mysql"
        command: --innodb-use-native-aio=0
@kellyjandrews

This comment has been minimized.

kellyjandrews commented Sep 13, 2018

I resolved this issue by removing --default-time-zone=UTC from my startup. I assume this is because the tables are not setup quite yet and it puts log files for the error into my /var/lib/mysql/ folder. Maybe this will help someone out.

@maykino

This comment has been minimized.

maykino commented Oct 31, 2018

I'm trying to set up docker for the first time and I appreciate everyone's help in getting it fixed.

I get the "mysqld: Can't create/write to file '/var/mysql/data/init.sql' (Errcode: 2 "No such file or directory")" and here is my docker-compose.yaml:

`version: '3'

services:
    php:
        build:
            context: ./symfony
        container_name: php
        depends_on:
        - mysql
        env_file:
        - ./symfony/.env
        # Comment out these volumes in production
        volumes:
        - ./symfony:/srv/symfony:rw,cached
        # If you develop on Linux, comment out the following volumes to just use bind-mounted project directory from host
        environment:
            # If you develop on Windows change this to remote_host=docker.for.win.localhost
            # If you develop on Linux change this to remote_host=172.17.0.1
            XDEBUG_CONFIG: "remote_host=docker.for.mac.localhost idekey=IDE_XDEBUG"
            BLACKFIRE_CLIENT_ID: ${BLACKFIRE_CLIENT_ID}
            BLACKFIRE_CLIENT_TOKEN: ${BLACKFIRE_CLIENT_TOKEN}

    symfony:
        build:
            context: ./symfony
            dockerfile: Dockerfile.nginx
        container_name: symfony
        depends_on:
        - php
        ports:
        - "8080:80"
        volumes:
        - ./symfony/public:/srv/symfony/public:ro

# Optional mysql database - uncomment and replace "depends_on: [ postgres ] with mysql
    mysql:
        image: mysql
        container_name: mysql
        environment:
             MYSQL_ROOT_PASSWORD: root
             MYSQL_USER: symfony
             MYSQL_PASSWORD: symfony
        command: --log-bin=/var/lib/mysql/mysql-bin.log --binlog-format=ROW --server-id=1 --init-file /var/mysql/data/init.sql
        # You may use a bind-mounted host directory instead, so that it is harder to accidentally remove the volume and lose all your data!
        volumes:
        - ./symfony/docker/mysql:/var/mysql/data:ro
        - db-data:/var/lib/mysql:rw

    adminer:
        image: adminer
        container_name: adminer
        depends_on:
        - mysql
        restart: always
        ports:
        - 2000:8080

    blackfire:
        image: blackfire/blackfire
        container_name: blackfire
        depends_on:
        - php
        environment:
            BLACKFIRE_SERVER_ID: ${BLACKFIRE_SERVER_ID}
            BLACKFIRE_SERVER_TOKEN: ${BLACKFIRE_SERVER_TOKEN}
            BLACKFIRE_LOG_LEVEL: 4
        ports:
        - "8707:8707"

    h2-proxy:
        # Don't use this proxy in prod
        build:
            context: ./h2-proxy
            dockerfile: ./Dockerfile
        container_name: h2-proxy
        depends_on:
        - symfony
        ports:
        - "80:80"
        - "443:443"

volumes:
    db-data: {}

`

Here is the full output I'm getting:

screen shot 2018-10-30 at 9 41 43 pm

@yosifkit

This comment has been minimized.

Member

yosifkit commented Oct 31, 2018

@maykino, you probably want to drop --init-file /var/mysql/data/init.sql and instead just mount your folder containing the sql file to /docker-entrypoint-initdb.d/ and it will automatically be run on first initialization of the data directory. Use docker volume rm db-data to clear out the old data directory after stopping and deleting the container.

    mysql:
        image: mysql
        container_name: mysql
        environment:
             MYSQL_ROOT_PASSWORD: root
             MYSQL_USER: symfony
             MYSQL_PASSWORD: symfony
        command: --log-bin=/var/lib/mysql/mysql-bin.log --binlog-format=ROW --server-id=1
        # You may use a bind-mounted host directory instead, so that it is harder to accidentally remove the volume and lose all your data!
        volumes:
# this will run all .sh, .sql and .sql.gz files found
        - ./symfony/docker/mysql:/docker-entrypoint-initdb.d/:ro
        - db-data:/var/lib/mysql:rw
@maykino

This comment has been minimized.

maykino commented Nov 1, 2018

@yosifkit wow! It worked! Thanks a lot! ###

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment