User not created in MySQL when using docker-compose

Question

This is what I see when I am in the container created by docker-compose:

mysql> SELECT user FROM mysql.user;
+------+
| user |
+------+
| root |
+------+
1 row in set (0.00 sec)

root@541e4d686184:/# echo $MYSQL_USER
dbuser

So dbuser is not present in the users table even though the $MYSQL_USER is set properly .

In docker-compose.yml I have this:

version: '2'
services:
  db:
    image: mysql:latest
    environment:
      MYSQL_DATABASE: mydb
      MYSQL_USER: dbuser
      MYSQL_PASSWORD: userpass
      MYSQL_ROOT_PASSWORD: password
    ports:
      - "3306"
    volumes:
      - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
      - my-datavolume:/var/lib/mysql
volumes:
  my-datavolume:

I expected dbuser to be created automatically, but that didn't happen.

I also have a sql file to create my database and tables if they don't already exist, but right now tomcat can't connect to my database.

Same symptoms as this question, but I am already using a dictionary for my usernames/passwords.

UPDATE:

I am getting close. When inside container I manually did:

/docker-entrypoint-initdb.d/create_users.sh 

Then the user was created inside MySQL table and I was able to deploy my application to my tomcat server and I didn't get an error about dbuser being denied access.

So, why did I have to run this command myself, it should be run by docker-compose, according to the mysql docker docs under Initializing a fresh instance.

Toadeater

Did you check the output of docker logs? The mysql logs? Did you consider user permissions issues? Did you consider using the mysql client for running your sql script? (e.g. /usr/bin/mysql < /path/to/script.sql) – Trilobate

@Trilobate - I did see that it tried to create this new user, but the root user password worked. I can add the user manually, through the Dockerfile, I just didn't want to do that if possible. – Toadeater

sql scripts are good for more than creating users; it might end up being a good thing having a startup script. Do you foresee needing some extra startup actions? It might save you dealing with this altogether. – Trilobate

@mig - No, I don't see any other use, but at the moment it works this way. I can save the docker image with the database set up and then just deploy that so everything stays correct. I don't like this option, but it appears it may be my best one. – Toadeater

I'm having the same problem here. It used to work great before, and now, this. I don't know why. – Glabella

Same issue for me right now. User is not created in the DB. – Colonic

For me this issue was caused by using a blank password. Setting the password to a non empty string fixed it. What threw me off was MYSQL_ALLOW_EMPTY_PASSWORD but this only applies to the root user not the MYSQL_USER There was a message in the logs MYSQL_USER specified, but missing MYSQL_PASSWORD; MYSQL_USER will not be created – Devious

Answer 1

How about:

docker-compose down -v

From the documentation:

-v - Remove volumes declared in the volumes section of the Compose file.

Your database has been already created inside a volume, so any changes of initial settings in docker-compose.yml won't be reflected.

In case you want to remove just a single volume, you may use docker volume ls to list all existing volumes and then docker volume rm <VOLUME NAME> to remove it.

Note: Bind mounts are not removed with the -v flag, so in case you are using them instead of volumes, you'll have to manually delete folders containing MySQL data. In docker-compose bind mounts are created whenever you provide a source path in your volumes section (eg. /my-path:/var/lib/mysql).

Valuator

Great suggestion. Saved my day! Use docker-compose down -v docker-compose up --build to rebuild image – Discipline

Note: Bind mounts aren't removed with the -v flag, so you'll have to manually delete the mount's contents. This caught me off guard for far longer than I'd like to admit.. – Lauralee

After hours of struggle, I'm glad I came across your answer! Many thanks. – Tyree

Answer 2

Worked for me : stop docker and remove manually all the folder containing MySQL data from previous builds.

Also : don't forget to add a MYSQL_DATABASE environment var or it won't create the user you specified.

Colonic

Answer 3

Github issue

Important to note that the image entrypoint script will never make changes to an existing database. If you mount an existing data directory into var/lib/mysql, options like MYSQL_ROOT_PASSWORD will have no effect

Rother

How do you mount a volume for persistent storage then?... – Photometer

Answer 4

I met the same issue, you may try to remove everything under 'my-datavolume' because the environment works only in the initial stage that means there should not any data in '/var/lib/mysql'. This approach worked for me.

Av

Answer 5

What worked for me is:

docker-compose down
docker volume ls
docker volume rm <volume-name>
docker-compose up -d

In the newly created volume, my user was there.

Metaphase

Answer 6

after my testing,

1. create init.sql and links to /docker-entrypoint-initdb.d

2. docker-compose down
   docker volume ls
   docker volume rm
   docker-compose up -d

then everythi is ok

Kinchinjunga