root user can't create new database

[Sixeight]

Problem

• Following pull-request makes prevent creating new database by root user.
  • Error early if MYSQL_USER=root to prevent partially initialized databases #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.

  • mysql/5.7/docker-entrypoint.sh

    Lines 156 to 165 in b7e0196

    	# This will prevent the CREATE USER from failing (and thus exiting with a half-initialized database)
    	if [ "$MYSQL_USER" = 'root' ]; then
    	mysql_error <<-'EOF'
    	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
    	EOF
    	fi

• 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? #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: #750 (comment)

[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-red-panda]

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 #750 (comment)) 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.