| Bug #91432 | ERROR1449(HY000):The user specified as a definer ('mysql.infoschema'@'localhost' | ||
|---|---|---|---|
| Submitted: | 27 Jun 2018 7:01 | Modified: | 27 Jun 2018 12:14 |
| Reporter: | Mohana Sundaram A | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Package Repos | Severity: | S2 (Serious) |
| Version: | 8.0.11 | OS: | Linux |
| Assigned to: | CPU Architecture: | x86 (x86_64) | |
[27 Jun 2018 7:02]
Mohana Sundaram A
Updating OS version and CPU architecture
[27 Jun 2018 7:04]
Mohana Sundaram A
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
[27 Jun 2018 8:00]
MySQL Verification Team
Hello Mohana, Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at https://dev.mysql.com/doc/refman/8.0/en/docker-mysql-getting-started.html#docker-upgrading Imho you haven't ran the mysql_upgrade utility in the MySQL 8.0 Server container after upgrading from 5.7 to 8.0 -- When prompted, enter the root password for your old MySQL 5.7 Server docker exec -it test-mysqldb mysql_upgrade -uroot -p ## Reported error can be seen even in standalone if 'mysql.infoschema'@'localhost' user doesn't exists. mysql> show grants for 'mysql.infoschema'@localhost; +-------------------------------------------------------+ | Grants for mysql.infoschema@localhost | +-------------------------------------------------------+ | GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost` | +-------------------------------------------------------+ 1 row in set (0.00 sec) mysql> delete from mysql.user where user='mysql.infoschema'; Query OK, 1 row affected (0.09 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> \q Bye [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.11 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 databases; ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist Thanks, Umesh
[27 Jun 2018 9:33]
Mohana Sundaram A
For the above issue, we need to upgrade MySQL container with mysql_upgrade tool with the specific version to upgrade. In additional to the above specified steps, we need to perform below steps as well. Shut down the old MySQL server:: mysqladmin -u root -p shutdown Start the MySQL8.0 server mysqld_safe --user=mysql --datadir=/path/to/existing-datadir If your MySQL server is running inside docker container, use the below command to upgrade. docker exec -it test-mysqldb mysql_upgrade -uroot -p Thanks to Praveen H, MySQL Team for helping us.
[27 Jun 2018 9:38]
Mohana Sundaram A
Hi Team, Its Working Fine now. We can close this bug.
[27 Jun 2018 12:14]
Mohana Sundaram A
Thanks PraveenKumar Hulakund for helping us.
Description: Hi Team, I am trying to upgrade MySQL 5.7.16 to 8.0.11 version while is already installed inside docker container. After i am pulling latest docker image for v8.0.11, I have upgraded the MySQL container from 5.7.16 to 8.0.11 by giving referencing old data directory itself. So that after MySQL upgrade 8.0.11, i can access the same data/information. After MySQL upgrade, i am getting below issue. $ mysql -uroot --protocol=tcp -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.11 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> use import; Database changed mysql> show tables; ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist mysql> show databases; ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist Please help me to fix this issue. How to repeat: Step 1: Install MySQL in the container. sudo docker run --name test-mysqldb -e MYSQL_ROOT_PASSWORD=ASK_FOR_PASSWORD -v /data1/test/mysql_data:/var/lib/mysql -p 3306:3306 -d mysql:5.7.16 Step 2: Create some databases and tables with data, so that it will store inside data directory. (i.e: /data1/test/mysql_data) Step 3: For upgrading MySQL, i dont want to loose the data from this data directory. Step 4: sudo docker stop test-mysqldb Step 5: sudo docker rm test-mysqldb Step 6: sudo docker run --name=test-mysqldb --restart=always -e MYSQL_ROOT_PASSWORD=testp -v /data1/test/mysql_data:/var/lib/mysql -p 3306:3306 -d mysql:8.0 In the above command, testp is the MYSQL_ROOT_PASSWORD /data1/test/mysql_data is the data directory. Step 7: mysql -uroot --protocol=tcp -p Enter password: testp Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.11 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> use crawler_import; Database changed mysql> show tables; ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist mysql> show databases; ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist