Docker Community Forums

Share and learn in the Docker community.

Official Image for "mysql:5.7" not creating user correctly

UPDATE 3 (FINAL):

This newbie wishes to ask the forgiveness of the maintainers and the community. The Lumen “.env” file had the wrong host name (needed to be the name of the mysql container) and the wrong port number (needs to be the internal port, 3306).

Please mark this issue “Solved”.


UPDATE 2:
Issue filed with image maintainers.

UPDATE:

If you run this command:

docker run --name mysql-test -e MYSQL_ROOT_PASSWORD=my-secret-pw \
     -e MYSQL_DATABASE=mytestdb -e MYSQL_USER=testuser \
     -e MYSQL_PASSWORD=testpassword --publish 3307:3306 -d mysql:5.7

and then attach to the container, login as root to mysql, switch to the “mysql” database and run:

select * from user where User = 'testuser'\G

You get:

*************************** 1. row ***************************
                  Host: %
                  User: testuser
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *9F69E47E519D9CA02116BF5796684F7D0D45F8FA
      password_expired: N
 password_last_changed: 2019-07-08 01:48:12
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

From Docker Official Images

MYSQL_USER , MYSQL_PASSWORD

These variables are optional, used in conjunction to create a new user and to set that user’s password. This user will be granted superuser permissions (see above) for the database specified by the MYSQL_DATABASE variable. Both variables are required for a user to be created.

Unfortunately for me, this is not happening. The user gets created, but has absolutely no permissions of any kind; additionally, because --skip-name-resolve mode has been enabled in the mysql:5.7 image, I cannot change the user’s permissions.

Here is my “docker-compose.yml” file (sensitive data substituted, but the env vars are working):

version: "3.7"
services:
    mysql:
      image: mysql:5.7
      working_dir: /application
      volumes:
        - dbdata:/var/lib/mysql
      environment:
        - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
        - MYSQL_DATABASE=${MYSQL_DATABASE}
        - MYSQL_USER=${MYSQL_USER}
        - MYSQL_PASSWORD=${MYSQL_PASSWORD}
      networks:
        - web
      ports:
        - "${DB_PUBLIC_PORT}:3306"

    nginx:
      image: nginx:alpine
      restart: unless-stopped
      working_dir: /var/www
      tty: true
      volumes:
          - ./api:/var/www
          - ./phpdocker/nginx/nginx.conf:/etc/nginx/conf.d/default.conf
      networks:
        - web
      ports:
       - "${FRONT_END_PORT}:80"

    php-fpm:
      build: ./phpdocker/php-fpm
      working_dir: /var/www
      depends_on:
        - mysql
      volumes:
        - ./api:/var/www
        - ./phpdocker/php-fpm/php-ini-overrides.ini:/etc/php/7.2/fpm/conf.d/99-overrides.ini
      networks:
        - web

# Docker Networks
networks:
  web:
    driver: bridge

# Docker Volumes
volumes:
  dbdata: {}

If I attach to the mysql container, either through MySQL Workbench or by “docker exec”, and run SELECT * FROM mysql.user WHERE User=<${MYSQL_USER}>"; (I use the actual username in the query), I get back 2 rows for the named user: one for host ‘localhost’, and another for host ‘%’. In both rows, all permissions are ‘N’, i.e., the user has no permissions of any kind.

If I then attempt to change the user’s permissions, I get following message:

0 row(s) affected, 2 warning(s): 1285 MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work 1287 Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.

ALTER USER allows you to change everything except the user’s GRANT permissions.

Please assist me in either identifying what I am doing incorrectly, or how I can otherwise fix this situation, so I can move forward with my project.

Many thanks for your assistance.