Mysql: root user can't create new database

[Sixeight]

Problem

• Following pull-request makes prevent creating new database by root user.
  
  
  
  • https://github.com/docker-library/mysql/pull/749
  
  
• Use this container on CI, I want to create database at the launching time.
  
  
  
  • example: use circleci/mysql:5.7-ram , I can't create database before luanch.
  
  
• I think following code are wrong condition or wrong error message.
  
  
  
  • https://github.com/docker-library/mysql/blob/b7e01968f8f78ad8e4786be59eeec231541e9c58/5.7/docker-entrypoint.sh#L156-L165
  
  
• Message order to set environment variable, but all environment are ignored by this condition.

Expected result

• I want to use root user for CI testing.

[davidstosik]

We started facing the same issue when CircleCI switched our mysql image to 5.6.51 (previously was 5.6.50).

2021-03-18 01:21:53+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.6.51-1debian9 started.
2021-03-18 01:21:54+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2021-03-18 01:21:54+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.6.51-1debian9 started.
2021-03-18 01:21:54+00:00 [ERROR] [Entrypoint]: MYSQL_USER="root", MYSQL_PASSWORD cannot be used for the root user
    Use one of the following to control the root user password:
    - MYSQL_ROOT_PASSWORD
    - MYSQL_ALLOW_EMPTY_PASSWORD
    - MYSQL_RANDOM_ROOT_PASSWORD
Exited with code 1
CircleCI received exit code 1

We've always been setting MYSQL_ALLOW_EMPTY_PASSWORD to true and it was working so far.

@yosifkit Could this be related? https://github.com/docker-library/mysql/pull/749

[Sixeight]

@davidstosik You can avoid the error, use mysql:5.7.32 (previous patch version) to use old docker-entrypoint.sh .

[davidstosik]

@Sixeight Yeah, I'm about to freeze the mysql image in our project to 5.6.50. 👍🏻

For other CircleCI users coming here, this is what it looks like:

-  - image: circleci/mysql:5.6
+  - image: circleci/mysql:5.6.50-ram

[polamjag]

We encountered probably same problem (in image labeled mysql:5.6) and this mitigation worked: https://github.com/docker-library/mysql/issues/750#issuecomment-801565537

[ogii]

On CircleCI, It is also possible to specify the image using a previous working SHA

- image: circleci/mysql@sha256:eb6d35250a42f16994dea0726d62453c11c956e8a7f5f4fc946c76041bb2ab86

where the SHA can be found by checking the logs under the container step log

[Sixeight]

Also avoid this issue, Use another user name If you can .

MYSQL_USER=user
MYSQL_PASSWORD=pass

[davidstosik]

Also avoid this issue, Use another user name If you can .

MYSQL_USER=user
MYSQL_PASSWORD=pass

A note on this: that work-around indeed works, but does not prevent the root user to be created with no password, and then used by our app. See the docs.

Feels like if #749 did not really prevent me from using root, then it should not introduce this limitation nor require this confusing workaround.

[lokst]

Based on the displayed error message (see below for sample), it looks like MYSQL_USER="root" should be allowed when MYSQL_ALLOW_EMPTY_PASSWORD is set to true, but this error does occur in such a case. I would appreciate clarifications on whether this is expected behavior.

2021-03-18 08:33:09+00:00 [ERROR] [Entrypoint]: MYSQL_USER="root", MYSQL_PASSWORD cannot be used for the root user
    Use one of the following to control the root user password:
    - MYSQL_ROOT_PASSWORD
    - MYSQL_ALLOW_EMPTY_PASSWORD
    - MYSQL_RANDOM_ROOT_PASSWORD

[coding-bunny]

This just broke our entire test suit for every singe project...

[ltangvald]

This just broke our entire test suit for every singe project...

How is your test set up? Part of the point of this is that MYSQL_USER=root is redundant. There is always a 'root'@'localhost', and by default a 'root'@'%', which has a password set by MYSQL_ROOT_PASSWORD (or MYSQL_ALLOW_EMPTY_PASSWORD)

[willdurand]

I don't quite understand what is happening here. The error we now get in our CI (which is the same as pasted in https://github.com/docker-library/mysql/issues/750#issuecomment-801736293) says that we should be using MYSQL_ALLOW_EMPTY_PASSWORD, which we do! We don't have any other env var configured for the mysql container (except MYSQL_DATABASE)...

[ltangvald]

I'll try to summarize how this works:
By default, mysqld --initialize creates a 'root'@'localhost' user. This happens automatically, always. This docker image also creates a 'root'@'%' (accessible remotely, while root@localhost can only be logged in to from within the container) by default (you can change the '%' part by setting MYSQL_ROOT_HOST).
To set a password for the root user, you should either set the MYSQL_ROOT_PASSWORD environment value to the desired password, or MYSQL_ALLOW_EMPTY_PASSWORD=1 to leave the root password empty.

The variables MYSQL_USER and MYSQL_PASSWORD are meant to create an extra, nonadministrator account. If MYSQL_DATABASE is also specified, this used will have all accesses for that database alone.

Edit: So using MYSQL_USER for a root account is almost certainly incorrect, because you're telling the entrypoint script to create a user that already exists.

[willdurand]

To set a password for the root user, you should either set the MYSQL_ROOT_PASSWORD environment value to the desired password, or MYSQL_ALLOW_EMPTY_PASSWORD=1 to leave the root password empty.

So, that's what we are doing for years and the MySQL container started to fail last night with the following error:

```2021-03-18 10:06:11+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.23-1debian10 started.
2021-03-18 10:06:11+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2021-03-18 10:06:11+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.23-1debian10 started.
2021-03-18 10:06:11+00:00 [ERROR] [Entrypoint]: MYSQL_USER="root", MYSQL_PASSWORD cannot be used for the root user
Use one of the following to control the root user password:
- MYSQL_ROOT_PASSWORD
- MYSQL_ALLOW_EMPTY_PASSWORD
- MYSQL_RANDOM_ROOT_PASSWORD

Exited with code 1

CircleCI received exit code 1

The config for this container:

environment:
MYSQL_ALLOW_EMPTY_PASSWORD: yes
MYSQL_DATABASE: olympia

What's missing then?

Edit: wait, it's possible that this is specific to the Circle CI image, which seems to override a few env vars...

Edit (2): according to https://hub.docker.com/r/circleci/mysql, this is the customization applied to the base mysql image:

FROM mysql:latest
ENV MYSQL_ALLOW_EMPTY_PASSWORD=true \
MYSQL_DATABASE=circle_test \
MYSQL_HOST=127.0.0.1 \
MYSQL_ROOT_HOST=% \
MYSQL_USER=root
```

So I guess they should update this customization to remove MYSQL_USER=root?

Edit (3): looks like the circle folks have updated their image: https://github.com/circleci/circleci-images/pull/540

[coding-bunny]

Setting the DATABASE_URL as follows: "mysql2://[email protected]:3306/my_app_test" so our Rails application knows where to connect.
The following Image configuration is used for our MySQL Image:

- image: circleci/mysql:5.7
  environment:
    MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
    MYSQL_ROOT_PASSWORD: ""
    MYSQL_ROOT_HOST: "%"

So we should have the root user, accessible remote, without a password
But it doesn't work:

2021-03-18 11:13:04+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.33-1debian10 started.
2021-03-18 11:13:04+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2021-03-18 11:13:04+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.33-1debian10 started.
2021-03-18 11:13:05+00:00 [ERROR] [Entrypoint]: MYSQL_USER="root", MYSQL_PASSWORD cannot be used for the root user
    Use one of the following to control the root user password:
    - MYSQL_ROOT_PASSWORD
    - MYSQL_ALLOW_EMPTY_PASSWORD
    - MYSQL_RANDOM_ROOT_PASSWORD

Exited with code 1

CircleCI received exit code 1

Attempt 2

- image: circleci/mysql:5.7
  environment:
     MYSQL_ALLOW_EMPTY_PASSWORD: "yes"

2021-03-18 11:14:52+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.33-1debian10 started.
2021-03-18 11:14:52+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2021-03-18 11:14:52+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.33-1debian10 started.
2021-03-18 11:14:52+00:00 [ERROR] [Entrypoint]: MYSQL_USER="root", MYSQL_PASSWORD cannot be used for the root user
    Use one of the following to control the root user password:
    - MYSQL_ROOT_PASSWORD
    - MYSQL_ALLOW_EMPTY_PASSWORD
    - MYSQL_RANDOM_ROOT_PASSWORD

Exited with code 1

CircleCI received exit code 1

Attempt 3

- image: circleci/mysql:5.7
        environment:
          MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
          MYSQL_ROOT_PASSWORD: ""

2021-03-18 11:16:22+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.33-1debian10 started.
2021-03-18 11:16:22+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2021-03-18 11:16:22+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.33-1debian10 started.
2021-03-18 11:16:22+00:00 [ERROR] [Entrypoint]: MYSQL_USER="root", MYSQL_PASSWORD cannot be used for the root user
    Use one of the following to control the root user password:
    - MYSQL_ROOT_PASSWORD
    - MYSQL_ALLOW_EMPTY_PASSWORD
    - MYSQL_RANDOM_ROOT_PASSWORD

Exited with code 1

CircleCI received exit code 1

So what is it?

[lokst]

Hi, it looks like a number of commenters on this thread are users of circleci/mysql images which use the upstream mysql image described here. With https://github.com/circleci/circleci-images/pull/541, we have now removed the redundant MYSQL_USER=root setting in circleci/mysql images, so that should resolve this error for users of the circleci/mysql images. Thank you for your patience.

[coding-bunny]

Hi, it looks like a number of commenters on this thread are users of circleci/mysql images which use the upstream mysql image described here. With circleci/circleci-images#541, we have now removed the redundant MYSQL_USER=root setting in circleci/mysql images, so that should resolve this error for users of the circleci/mysql images. Thank you for your patience.

It really doesn't. Half the containers simply don't work

[lokst]

@coding-bunny Sorry to hear that, could you share an example of an image that still suffers from the issue?

[coding-bunny]

just look at the previous comments...

[davidstosik]

Removing MYSQL_USER=root did not work for me either.
@lokst latest build on my CircleCI account (tied to my GitHub account) should show you.

Update: seems to work now, but didn't 80 minutes ago. Did that CircleCI change (merged 3 hours ago) take time to propagate?

[ltangvald]

I'm guessing that's the case. The above error message only triggers on MYSQL_USER='root'

[lokst]

@davidstosik Thank you for sharing your experience! The PR https://github.com/circleci/circleci-images/pull/540#event-4474693086 was for the staging branch, https://github.com/circleci/circleci-images/pull/541 was the one that actually republished the images

@coding-bunny Could you try again? I tested with your examples and the image is working for me. My guess is that at the time of your tests, the images had not yet been published

[davidstosik]

merged at 8:18

Likely explains why it didn't work for @coding-bunny between 8:16 and 8:28. 😉
It's worth trying again.

[coding-bunny]

Keeping an eye on our builds to see how it goes, but not all coworkers are awake yet :D

Still advocating internally that our projects should step away regardless from relying on the root user to do their thing.
They don't get root either in production :D :D

[Sixeight]

the problem is over. thank you for their comments and sharing knowledge.