MySQL错误笔记

环境：MySQL 8.0.20安装在Ubuntu20.04虚拟机上

这篇博客记录着我从初学MySQL开始遇到的所有错误和解决方案
安装命令：
sudo apt-get install mysql-server
sudo apt-get install mysql-client

1. ERROR 1698 (28000)

1.1 问题：

进入mysql时需要我们使用sudo mysql -uroot -p******才能登陆成功，否则提示ERROR 1896。发现这个的原因其实是root用户的权限问题。

1.2 解决方法：

1.2.1 先使用 你的原始密码（首次安装为空）登陆

sudo mysql -uroot -p   

1.2.2 在输入以下命令查看你的root账号是如何登陆

select User,Host,plugin from mysql.user;

1.2.3 修改root用户的登录方式

如果步骤2中的plugin是auth_socket那就说明你这个root的登陆方式不是普通登陆，需要加sudo。将其修改为mysql_native_password。

update mysql.user set plugin = 'mysql_native_password';

1.2.4 刷新修改

flush privileges;

这样如果成功了 你就可以退出去重新登陆，就不需要加sudo了。

2. ERROR 1396 (HY000)

• 修改密码时报错：“ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@’%’”
• 解决方案：

  mysql -uroot -p  # 登录MySQL
  Enter password: 
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 19
  Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)
  
  Copyright (c) 2000, 2020, 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 mysql;
  Database changed
  mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456';
  ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'
  mysql> use mysql;
  Database changed
  mysql> select user,host from user;
  +------------------+-----------+
  | user             | host      |
  +------------------+-----------+
  | debian-sys-maint | localhost |
  | mysql.infoschema | localhost |
  | mysql.session    | localhost |
  | mysql.sys        | localhost |
  | root             | localhost |
  +------------------+-----------+
  5 rows in set (0.00 sec)
  ############注释############
  我们可以看到，root用户对应的host为localhost，因此修改密码的语句应该写成：
  alter user 'root'@'localhost' identified by '123456';
  ##########注释完了##########
  mysql> alter user 'root'@'localhost' identified by '123456';
  Query OK, 0 rows affected (0.01 sec)
  
  mysql>
  

3. ERROR 1449 (HY000):

3.1 Navicat 初次连接刚配置好的MySQL报错1449

• 具体情况为：用户已经创建，支持远程登录，并且分配了权限，在Navicat中使用该用户连接时，测试连接成功，但是真正连接的时候就报错1449：
  1449 - The user specified as a definer (‘mysql.infoschema’@‘localhost’) does not exist
  
• 解决方案：
  ‘mysql.infoschema’@‘localhost’ 一看就是一个用户，检查时发现该用户虽然可以被查询到，但是其实并不存在，而且不能直接创建该用户，需要先删除然后再创建，接着进行赋予权限等操作。具体代码和执行过程如下：

  xiaoyao@Ubuntu2004:~$ mysql -uroot -p
  Enter password: 
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 24
  Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)
  
  Copyright (c) 2000, 2020, 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 mysql;
  Database changed
  mysql> show tables;
  ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
  mysql> drop user 'mysql.infoschema'@'localhost';
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> flush privileges;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> create user 'mysql.infoschema'@'localhost' identified by '12345678';
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> flush privileges;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> grant all privileges on *.* to 'mysql.infoschema'@'localhost' with grant option;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> flush privileges;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> show tables;
  +---------------------------+
  | Tables_in_mysql           |
  +---------------------------+
  | columns_priv              |
  | component                 |
  | db                        |
  *****************************
  

  至此，Navicat即可完成连接：
  

4. 10060 “Unknown error”

Navicat 远程连接虚拟机中的MySQL，测试连接时报错 10060 "Unknown error"，如下图:

问题可能处在防火墙上，可以查看防火墙的相关规则，并配置允许远程访问相关端口，本文中使用的时ubuntu20.10+ufw，执行如下命令后，Navicat即可正常连接MySQL。