Fails to initialize MySQL database on Windows 10

Question

System Info:

Docker version: 17.10.0-ce, build f4ffd25
OS: Windows 10 Home

When I run docker-compose up -d mysql I'm getting error. Following is the docker logs

[Note] Basedir set to /usr/

[Warning] The syntax '--symbolic-links/-s' is deprecated and will be removed in a future release

[Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

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

[ERROR] Aborting

I have tried deleting mysql folder under ~/.laradock\data and didn't work.

Update 1

MySQL Container under laradock Dockerfile

mysql:
  build:
    context: ./mysql
    args:
      - MYSQL_VERSION=${MYSQL_VERSION}
  environment:
    - MYSQL_DATABASE=${MYSQL_DATABASE}
    - MYSQL_USER=${MYSQL_USER}
    - MYSQL_PASSWORD=${MYSQL_PASSWORD}
    - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
    - TZ=${WORKSPACE_TIMEZONE}
  volumes:
    - ${DATA_SAVE_PATH}/mysql:/var/lib/mysql
    - ${MYSQL_ENTRYPOINT_INITDB}:/docker-entrypoint-initdb.d
  ports:
    - "${MYSQL_PORT}:3306"
  networks:
    - backend

MySQL Dockerfile

ARG MYSQL_VERSION=8.0
FROM mysql:${MYSQL_VERSION}

MAINTAINER Mahmoud Zalt <mahmoud@zalt.me>

#####################################
# Set Timezone
#####################################

ARG TZ=UTC
ENV TZ ${TZ}
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

RUN chown -R mysql:root /var/lib/mysql/

ADD my.cnf /etc/mysql/conf.d/my.cnf

CMD ["mysqld"]

EXPOSE 3306

Update 2

After I delete mysql folder under ~/.laradock/data I'm getting following error. After the command it generates the files in below image. When I rerun giving back the previous error mentioned above.

[Note] Basedir set to /usr/

[Warning] The syntax '--symbolic-links/-s' is deprecated and will be removed in a future release

[Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

[Warning] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive

[Warning] You need to use --log-bin to make --log-slave-updates work.

libnuma: Warning: /sys not mounted or invalid. Assuming one node: No such file or directory mbind: Operation not permitted

[ERROR] InnoDB: Operating system error number 22 in a file operation.

[ERROR] InnoDB: Error number 22 means 'Invalid argument'

[ERROR] InnoDB: File ./ib_logfile101: 'aio write' returned OS error 122. Cannot continue operation

[ERROR] InnoDB: Cannot continue operation.

** I tried in a windows 7 machine and its working.

We need at least the `mysql:` section of the docker-compose.yml — Robert, Jan 16 '18 at 09:18
Are you sure that you have this dir empty? `~/.laradock/data/mysql`, you've put '\data` in the question, instead of `/data` — Robert, Jan 16 '18 at 10:24
@Robert Yes I deleted the directory. I updated the question with new logs. Please check. Thanks — Saumini Navaratnam, Jan 17 '18 at 10:56
On Windows 10, are you using Docker for Windows (the "new" way) or an old way using Docker Toolbox? If former, are you setting it to run Linux containers and not Windows containers? I ask because the missing /sys mount, and the combination of error codes in your Update 2 section. — bluescores, Jan 19 '18 at 12:45
@bluescores I'm using Docker toolbox. Where/ How do I set to to run for windows containers? — Saumini Navaratnam, Jan 20 '18 at 03:21

AndrewD · Accepted Answer · 2018-02-05T22:04:04.013

Disable AIO

This fixed it for me when I got the AIO error as you did when I was starting a container from a guest Debian OS from Virtualbox and creating the database files on a shared folder on Windows 10.

The issue seems to be that AIO is not supported on shared folders, or at least on some versions of Windows. It seems to have occurred for me after I moved from Windows 10 Pro to Home after my main machine crashed.

For details:

Here are some options:

Option 1 - start the container like this :

docker run -it mysql --innodb_use_native_aio=0

Option 2 - add the command to your docker-compose file:

 command: --innodb_use_native_aio=0

In context, this is the relevant portion of my working docker-compose.yml:

services:
   db:
     image: ${MYSQL_IMAGE}
     command: "--innodb_use_native_aio=0"
     volumes:
       - ${DB_DATA_PATH}:/var/lib/mysql
     ports:
        - ${MYSQL_PORT}:3306

Option 3 -- add an option to your my.cnf file in your build

innodb_use_native_aio=0

Option 4 - Don't persist your DB on the local file system.(Can destroy your db, Not Recommended)

Simply remove the volume in your docker configuration that contains your mysql db. Of course, your DB will be deleted if you do a docker-compose down or otherwise destroy your container, so there's that.

note too, that you may need to empty out your ${DATA_SAVE_PATH} after you fix the AIO issue. Otherwise, mysql may refuse to initialize a data path with files in it. — AndrewD, Feb 05 '18 at 21:55
According to documentation, command line option is expected to be `--innodb-use-native-aio=0`. Correct or both works equally? — Yves Martin, Jan 15 '19 at 16:43

score 2 · Answer 2 · answered Jan 13 '18 at 12:18

I am not sure but Try this step.This is because of data folder.

Try remove the docker image. List all images using docker images then remove the mysql & laradock mysql using docker rmi imagename. Dont forget remove the docker volume using docker volume rm volumename Also go to cd ~/.laradock/data remove the mysql folder.

Then try docker-compose up mysql for debuging. If no errors, you could try docker-compose up -d mysql

Aderemi Dayo · Answer 3 · 2018-01-31T13:08:07.783

I had the same problem with my Windows 10 Enterprise, I couldn't find perfect solution around it because it seems to be my Windows version issue - I have ran the same image on other windows (8 & 10 professional) successfully. My temporary work around was to remove the /var/lib/mysql mount entirely from the docker-compose file allowing the database data files to be created and modified in the container itself.

I use kitematics which will restart and reattach my created container anytime I want to work on the container, like that I don't loose my data when the container exit. If you don't use kitematics this what it does. After running docker-compose the image will be created and a container will be created to run it as well, I avoid using run on the created image because this will create new container I stick to running the following sequentially

docker ps -a                 # this command will get container_id of all container, those that are running and those that are not
docker start <container_id>  # start container from background
docker attach <container_id> # attach container to standard input

With these docker commands my data were preserved in the container even after exiting and restarting

BACK UP

Now whenever I want to move the data, I will commit my container into an image and save the image to local storage

docker commit <container_id> <backup_image_name>:<1_31_2017> # I use date to tag it
docker save -o <local_storage_tar_name> <backup_image_name>

RESTORE

Whenever I need to restore the mysql container either on my computer or for a new programming intern

cd <dir_containing_the_tar_file>
docker load -o <local_storage_tar_name>

This solution is for your update

 File ./ib_logfile101: 'aio write' returned OS error 122. Cannot continue operation

I hope this helps somebody

Thanks for the answer. I'll try and let you know later – Saumini Navaratnam Feb 02 '18 at 02:59 — Saumini Navaratnam, Feb 02 '18 at 02:59

score 0 · Answer 4 · answered Aug 02 '19 at 20:46

0

In addition to the response above by AndrewD, you should change the permission on the my.cnf so that it does not get ignored when booting up. You can run the below command,

sudo chmod 400 my.cnf

Do this right before running docker-compose up -d

answered Aug 02 '19 at 20:46

anabeto93

221
3
8

Fails to initialize MySQL database on Windows 10

4 Answers4

Disable AIO

BACK UP

RESTORE

Linked