Mysql: MySQL 8.0.4 and users from env: user rightless

[bscheshirwork]

I can't connect to fresh mysql 8.0.4 from php or J-connector

I change new default (this is terrible) authentication_plugin to old default (from instruction)

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin

I try to use old plugin

  mysql:
    image: mysql:8.0.4
    ports:
      - "3308:3306" #for external connection
    entrypoint: ['/entrypoint.sh', '--default-authentication-plugin=mysql_native_password'] # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
    # docker exec -ti projects_mysql_1 bash
    environment:
      TZ: Europe/Moscow
      MYSQL_ROOT_PASSWORD: secret
      MYSQL_DATABASE: yiitest
      MYSQL_USER: travis
      MYSQL_PASSWORD: travis

but users has no rights (authentication plugin is old)

docker-compose up -d
docker exec -ti projects_mysql_1 bash

root@c8c97496e726:/# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
root@c8c97496e726:/# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@c8c97496e726:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create user travis
    -> ;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for travis@%                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'travis'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*7A0EBF5CB2B2C3DDE9D8F186A8F502941FE1A2BA' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql> show tables from yiitest;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql> show create user root;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for root@%                                                                                                                                                                                                     |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
root@c8c97496e726:/# mysql -utravis -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables from yiitest;
ERROR 1045 (28000): Access denied for user 'travis'@'%' (using password: YES)
mysql> 

[bscheshirwork]

upd: I try to check it with new default settings

docker-compose down

  mysql:
    image: mysql:8.0.4
    ports:
      - "3308:3306" #for external connection
    environment:
      TZ: Europe/Moscow
      MYSQL_ROOT_PASSWORD: secret
      MYSQL_DATABASE: yiitest
      MYSQL_USER: travis
      MYSQL_PASSWORD: travis



md5-5f67cf239b380dee2f1d7e5cd0421bf0



```sh
root@37058839e30d:/# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@37058839e30d:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create user root;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for root@%                                                                                                                                                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'root'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$(f<~b\ZjR\nM0(4aoswmmZ5gZmzyCQIp3QyYljkCcA62pt6.Cf4ILgXzqH8' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql> show tables from yiitest;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql> 

[yosifkit]

Ah, looks like we need to keep mysql.infoschema user now (like the oraclelinux based ones). PR incoming!

[moble]

I just want to point out that the key error that was being seen, which was

ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

made it essentially impossible to do anything with the database — even the most basic operations, including adding a user, listing databases or tables, or changing the definer. This is the most informative hit when googling for that error, so I think it's worth pointing out that the solution, of course, is just to run mysql_upgrade.

[greenec]

@moble Was running into this error after upgrading to 8.0.11. Didn't know that mysql_upgrade existed, so the fix was much appreciated!

[ericmulder]

@moble Same here, saved the day with mysql_upgrade 🎉