MySQL docker 5.7.6 and later fails to initialize database

[guss77]

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]

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]

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 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]

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]

:+1: :metal:

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

[ltangvald]

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]

@guss77 did #78 fix this for you?

[bittner]

Disclaimer

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

:information_source: 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]

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 trickles down to other providers, I hit this running a percona cluster backed by glusterfs

[ramizrshaik]

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]

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]

@ltangvald re: https://github.com/mysql/mysql-docker/blob/mysql-server/5.7/docker-entrypoint.sh -- how do I use this in a docker-compose?

[ltangvald]

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

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

[le-martre]

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]

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

[md5]

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

[alwinmark]

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: https://github.com/kubernetes/charts/pull/416

[tianon]

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. :wink:

[marlon-nc]

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]

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]

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

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

[zishanj]

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]

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]

@bittner awesome~fix the problem

[ltangvald]

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.

[pauliuuso]

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

[ordinaly]

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]

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

[wicadmin]

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

[snewer]

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

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]

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:

[yosifkit]

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

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

[ktamas77]

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

this is the solution.

[SCIF]

Hi there, I would like to build an image prefilled with some data to run tests. So, idea is to build the image once and just restart it on every test iteration to use the same snapshot every time.

  mysqltest:
    container_name: mysqltest
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: "secret"
    volumes:
      - ./mysql/init:/docker-entrypoint-initdb.d:ro
    expose:
      - "3306"

and got this:

mysqltest      | 2019-05-09T09:42:47.557600Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
mysqltest      | 2019-05-09T09:42:47.558842Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
mysqltest      | 2019-05-09T09:42:47.559241Z 0 [ERROR] Aborting

@yosifkit , I've grab an idea of mounting entry dir from your comment, but it doesn't work for me. Could you advise why my installation doesn't work? Anybody else knows? Just curious if nobody run tests in such manner?

[jonesmith518]

Got the same error, but .index file was found inside MySQL data dir which prevent initializing.

[thearyanahmed]

Still have the bug on 2020, should have been fixed a long time ago. Using mariadb as a bypass for the time being as @bittner said

[albanafmeti]

I had a similar issue with the same error message.

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

I fixed with

docker volume rm {dbVolumeNameHere}
docker-compose rm {dbServiceName}

Then restart again and it's done.

But this resolved my kind of issue, it doesn't mean it can resolve yours :)

[boay24]

I'm facing a problem, I have tried everything with no luck.

[ERROR] [Server] --initialize specified but the data directory has files in it. Aborting [System] [Server] /usr/sbin/mysqld (mysqld 8.0.19) initializing of server in progress as process 231 [ERROR] [Server] --initialize specified but the data directory has files in it. Aborting. [ERROR] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it. [ERROR] [Server] Aborting [System] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL.

---

WSL2 INSIDER MYSQL 8.0 RAM 16G

[alexdrupal]

Same here. Why does it closed? I had to downgrade to 5.7.5 in order to make my docker-compose.yml file working.

[tianon]

It's closed because the original issue was resolved, and there have since been many unrelated issues posted here, mostly requests for support (and this isn't a proper support forum).

I would recommend trying the Docker Community Forums, the Docker Community Slack, or Stack Overflow for further help diagnosing your deployment.