MySQL8.0.35 使用 show databases/show tables 时出现 ERROR 1449 (HY000) 问题的解决方法

一、问题起因:

在修改了MySQL8.0.35的用户表mysql.user中的root用户口令后(authentication_string 字段值),退出mysql再进入后,使用 show databases/show tables,就出现一下错误:

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

mysql> use mysql
Database changed

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

二、解决办法:

总体办法就是给 mysql.infoschema 用户添加权限。
MySQL8.0 之后,不支持使用 grant 时隐式地创建用户,必须先创建用户,再授权。代码如下:

mysql> create user 'mysql.infoschema'@'%' identified by '密码';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'mysql.infoschema'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

在使用show databases/ show tables,就正常了。

mysql> show databases;
Database
information_schema
mysql
performance_schema
sys
4 rows in set (0.01 sec)

mysql> show tables;
Tables_in_mysql
columns_priv
component
db
default_rolese
ngine_cost
func
general_log
global_grants
gtid_executed
help_category
help_keyword
help_relation
help_topic
innodb_index_stats
innodb_table_stats
password_history
plugin
procs_priv
proxies_priv
replication_asynchronous_connection_failover replication_asynchronous_connection_failover_managed
role_edges
server_cost
servers
slave_master_info
slave_relay_log_info
slave_worker_info
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
35 rows in set (0.00 sec)

最近更新

  1. TCP协议是安全的吗?

    2024-03-10 00:36:03       18 阅读
  2. 阿里云服务器执行yum,一直下载docker-ce-stable失败

    2024-03-10 00:36:03       19 阅读
  3. 【Python教程】压缩PDF文件大小

    2024-03-10 00:36:03       18 阅读
  4. 通过文章id递归查询所有评论(xml)

    2024-03-10 00:36:03       20 阅读

热门阅读

  1. Rust基础教程

    2024-03-10 00:36:03       21 阅读
  2. test02

    2024-03-10 00:36:03       22 阅读
  3. c 不同类型指针的转换

    2024-03-10 00:36:03       21 阅读
  4. 【数论】欧拉筛

    2024-03-10 00:36:03       22 阅读
  5. 贪心算法介绍

    2024-03-10 00:36:03       20 阅读
  6. EDA 许可证调度

    2024-03-10 00:36:03       23 阅读
  7. ArrayList和linkedList的区别精简概述

    2024-03-10 00:36:03       25 阅读
  8. 安全防范之警惕钓鱼邮件

    2024-03-10 00:36:03       21 阅读