12种mysql常见错误总结 +分析示例

前言

小伙伴们好，我是阿沐！最近呢，正筹备上云工作，需要考虑到很多场景；比如mongo、mysql、redis、splinx等等迁移工作，这就涉及到版本兼容问题；在迁移之前，阿沐迁移了mysql到其他容器中，发现迁移机器mysql版本号比较高5.7以上，就出现了sql语句兼容问题。所以趁机会整理了很久以前遇到的各种mysql常见问题跟csdn小伙伴们分享下。小伙伴们可以收藏起来哦，遇到常规错误可以快速查询解决~~~

1、localhost上的mysql无法连接

报错代码：

ERROR 2003 (HY000)：Can’t connect to MySQL server on 'localhost' (10061)

报错原因：

① 很明显，localhost本机是存在的；但是它却没有提供mysql的服务供给使用

② 检查磁盘空间是否还有剩余可用空间，尽量保持有足够的磁盘空间可用

③ 查看mysql的负载能力，可能存在mysql的负载过高我们连接不上；一般是看processlist来看下具体线程和连接数运行情况：
       
       1、show processlist只能列出当前100条，我们可以看到所有用户的连接情况
       
       mysql> show processlist;
        +----+-----------------+-----------+------+---------+-------+------------------------+------------------+
        | Id | User            | Host      | db   | Command | Time  | State                  | Info             |
        +----+-----------------+-----------+------+---------+-------+------------------------+------------------+
        |  4 | event_scheduler | localhost | NULL | Daemon  | 30404 | Waiting on empty queue | NULL             |
        | 14 | root            | localhost | NULL | Query   |     0 | starting               | show processlist |
        +----+-----------------+-----------+------+---------+-------+------------------------+------------------+
        2 rows in set (0.00 sec)
       
       2、查看全部的链接情况
       
       mysql> show full processlist;
        +----+-----------------+-----------+------+---------+-------+------------------------+-----------------------+
        | Id | User            | Host      | db   | Command | Time  | State                  | Info                  |
        +----+-----------------+-----------+------+---------+-------+------------------------+-----------------------+
        |  4 | event_scheduler | localhost | NULL | Daemon  | 30527 | Waiting on empty queue | NULL                  |
        | 14 | root            | localhost | NULL | Query   |     0 | starting               | show full processlist |
        +----+-----------------+-----------+------+---------+-------+------------------------+-----------------------+
        2 rows in set (0.00 sec)
        
# 注意，针对以上查看结果进行详细字段说明：

① Id 当用户登录mysql时，系统会为用户分配一个"connection_id"，可以使用函数connection_id()来查看：
    mysql> select connection_id();
    +-----------------+
    | connection_id() |
    +-----------------+
    |              14 |  -- 系统分配的id为14
    +-----------------+
    1 row in set (0.01 sec)

② User 展示当前链接用户

③ Host 连接mysql的ip地址；可查到来源端口，同时可以跟踪出现问题语句的用户

④ db   连接数据库的名称

⑤ Command 当前链接执行的命令；query(查询)、sleep(休眠)、connect(连接)、daemon(守护进程)

⑥ Time  当前连接持续时长，单位时间是秒

⑦ State 展示当前连接的sql语句状态

⑧ Info 展示sql语句，对用来判断sql语句是否有问题很重要

问题解决方案：

① mysql未启动，则启动即可：

    mac端：brew services start mysql   mysql.server start 
    centos端： systemctl start mysqld.service    service mysql start
    其他：找到执行文件根目录执行 启动也ok

2、localhost/IP地址连接不上

报错代码：

➜  ~ mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

报错原因：

① 概述：用户root访问localhost/IP被拒绝访问

② 一般是数据库的用户名或者密码跟服务器上mysql设置的不一致，导致匹配失败

问题解决方案：

# 注意解决步骤：

① 查看mysql配置文件

② 查看 mysql 配置文件加载顺序

③ 修改配置文件跳过权限验证(skip-grant-tables)

④ 登录mysql客户端修改密码

注意mac版本方案：

① 查看mysql运行情况，看看在哪里

➜  ~ ps aux | grep mysql   -- 查看mysql的运行进程 可以看到启动地址
amu       15262   0.0  4.9  4899000 407680   ??  S     1:14下午   0:01.18 /usr/local/opt/mysql/bin/mysqld 
--basedir=/usr/local/opt/mysql 
--datadir=/usr/local/var/mysql 
--plugin-dir=/usr/local/opt/mysql/lib/plugin 
--log-error=liyangyang.local.err 
--pid-file=liyangyang.local.pid 
--socket=/tmp/mysql.sock

② 查看mysql配置文件加载情况：

➜  ~ /usr/local/opt/mysql/bin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

# 概述：
① 服务器首先读取的是 /etc/my.cnf文件

② 如果前一个文件不存在则继续读/etc/mysql/my.cnf文件，依此类推往下寻找，一直到最后一个~/.my.cnf文件

③ 如果以上所有文件都不存在；则说明安装mysql之后未进行配置文件；可以自己编辑一份名为my.cnf；修改文件拥有者和所属组且赋予可执行权限即可
       
       ① mkdir /usr/local/mysql/etc  

       ② vim /usr/local/mysql/etc/my.cnf  -- 内容可以复制一份本地的配置文件即可

       ③ chown -R root:root /usr/local/mysql/etc/

       ④ chmod 755 /usr/local/mysql/etc/my.cnf

③ 查看mysql读取配置文件方法：

## 查看是否使用了指定目录的my.cnf
➜  ~ ps aux | grep mysql | grep 'my.cnf'

## 查看mysql默认读取my.cnf的目录
➜  ~ mysql --help | grep 'my.cnf'
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

这些就是mysql默认会搜寻my.cnf的目录，顺序排前的优先；等同于上面搜索语句

④ 免密码登录并修改密码

### mysql 5.8版本之前的修改方法：

① 在mysql配置文件中，[mysqld]下添加一行，使其登录时跳过权限检查
[mysqld]
skip_grant_tables

② 重启mysql服务；登录mysql -uroot -p

### mysql5.8版本之后修改方法：

    ① 执行目录下执行，例如我本地运行地址：
    /usr/local/opt/mysql/bin/mysqld  -console --skip-grant-tables --shared-memory
    
    ② 新开启一个窗口：mysqld -uroot -p 直接回车(大家可以升级版本测试下)

### 以上操作完毕后 进度mysql终端，修改密码：

set password for root@localhost = '新密码';
或者
update user set authentication_string='新密码' where user='root'; 
或者
alter user 'root'@'localhost' identified with mysql_native_password by '新密码';

### 注意：刷新mysql相关权限
flush privileges;

### 最终流程：
mysql> update user set authentication_string='root' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> set password for root@localhost = 'root';
Query OK, 0 rows affected (0.02 sec)

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'root';
Query OK, 0 rows affected (0.01 sec)

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

mysql> exit
Bye

此时再登录时我们的密码已经更新为root

3、无法连接mysql服务器

报错代码：

➜  ~ mysql -uroot -p
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

报错原因：

① mysql服务器没有开启

② mysql服务器开启了，但不能找到 socket 文件

概述mysql的连接方式：

mysql的登陆方式有两种，分别是socket和tcp/ip方式登陆

### socket(套接字)连接方式：

只能在mysql客户端和数据库实例在同一台服务器上的情况下使用(本地连接)；

通常连接localhost是通过一个Unix域套接字文件进行，一般是/tmp/mysql.sock；

若套接字文件被删除了，本地客户就不能再连接了

### 登录实例后查询
mysql> show variables like 'socket';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| socket        | /tmp/mysql.sock |
+---------------+-----------------+
1 row in set (0.04 sec)

### 当然我们也可以通过socket文件登录数据库
➜  ~ mysql -uroot -proot -S /tmp/mysql.sock -- 用户名 + 密码 + Socket文件路径地址(可不带默认)
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
............. 省略部分
#### 注意事项：
mysql.sock必须是mysql中配置的文件且必须在/tmp下存在；若不存在则启动不了mysql

### TCP/IP连接方式：
➜  ~ mysql -uroot -proot -h 127.0.0.1 -- 用户名 + 密码 + ip:port
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
............. 省略部分

#### 说明概述
若通过tcp/ip地址连接mysql；它将先检查权限视图表，检测请求方的ip是否允许被连接
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)
    ① host 表示该用户只能通过localhost的ip访问此数据库
    ② host:% 表示任何ip都可以连接mysql实例

问题解决方案：

① 修改配置文件增加socket路径
       ➜  ~ vim /usr/local/etc/my.cnf
       [mysqld] 
       socket=/tmp/mysql.sock
② 使用软连接将已经存在的mysql.sock软链到/tmp/mysql.sock
       ln -s /usr/local/xxx/mysql.sock  /tmp/mysql.sock

③ 最最最暴力解决方案；卸载mysql，mysql相关的全部删除；为了实践演示我本机mac删除卸载重装😭 😭 😭
       ➜  ~ brew remove mysql
       ➜  ~ brew cleanup
       ➜  ~ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
       ➜  ~ rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
       ➜  ~ sudo rm -rf /usr/local/var/mysql
       ➜  ~ brew install mysql
       ➜  ~ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist --设置开机启动
        /Users//Library/LaunchAgents/homebrew.mxcl.mysql.plist: service already loaded

4、无法连接mysql服务器

报错代码：

➜  ~ mysql -uroot -p
ERROR!The server quit without updating PID file (/usr/local/var/mysql/***MacBook-Pro.local.pid)

报错原因：

① mysql服务器没有开启

② mysql服务器开启了，但不能找到 socket 文件

问题解决方案：

sudo chown -R mysql:mysql /usr/local/mysql/

5、连接数过多，导致连接不上数据库

报错代码：

➜  ~ mysql -uroot -p
ERROR 1040 (0000)：Too many connections

报错原因：

① mysql链接数太多，已经超出我们设置的默认链接数；导致链接不上数据库，业务自然也拉跨了

② 可能存在mysql设置wait_timeout的值偏大，导致连接的空闲等待太长，则造成当前连接数变大

解决问题的思路：

### 查询mysql数据库当前设置的最大连接数

mysql> show variables like "%connections";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |  -- mysql支持的最大连接
| max_user_connections   | 0     |  -- 用户能最大连接进来的数量
| mysqlx_max_connections | 100   |  -- 可以接受的最大并发客户端连接数 跟max_connections相同；8.0版本增加
+------------------------+-------+
3 rows in set (0.01 sec)

### 再看看最大连接数
mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 1     | -- 是指mysql管理的线程池中还有多少可以被复用的资源
| Threads_connected | 5     | -- 是指打开的连接数；跟show processlist结果相同
| Threads_created   | 10    | -- 是指新创建的thread
| Threads_running   | 2     | -- 是指真正运行中的连接数；当前并发数量
+-------------------+-------+
4 rows in set (0.04 sec)

### 查看链接超时配置

mysql> show variables like '%timeout%';
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| connect_timeout                   | 10       |
| delayed_insert_timeout            | 300      |
| have_statement_timeout            | YES      |
| innodb_flush_log_at_timeout       | 1        |
| innodb_lock_wait_timeout          | 50       |
| innodb_rollback_on_timeout        | OFF      |
| interactive_timeout               | 28800    | -- 服务器关闭交互式连接前等待活动的秒数
| lock_wait_timeout                 | 31536000 |
| mysqlx_connect_timeout            | 30       |
| mysqlx_idle_worker_thread_timeout | 60       |
| mysqlx_interactive_timeout        | 28800    | -- 服务器关闭交互式连接前等待活动的秒数
| mysqlx_port_open_timeout          | 0        |
| mysqlx_read_timeout               | 30       |
| mysqlx_wait_timeout               | 28800    | -- 等待交互式客户端超时的秒数
| mysqlx_write_timeout              | 60       |
| net_read_timeout                  | 30       |
| net_write_timeout                 | 60       |
| rpl_stop_slave_timeout            | 31536000 |
| slave_net_timeout                 | 60       |
| wait_timeout                      | 28800    |  -- 服务器关闭非交互连接之前等待活动的秒数
+-----------------------------------+----------+
20 rows in set (0.00 sec)

问题解决方案：

### 设置最大连接数(必须要根据服务器的实际负载情况来设置，不是很推荐)
① mysql> set global max_connections =200;
  Query OK, 0 rows affected (0.00 sec)

② mysql> set global max_user_connections =100;
  Query OK, 0 rows affected (0.00 sec)
   
③ mysql> set global mysqlx_max_connections =100;
  Query OK, 0 rows affected (0.00 sec)

### 重启mysql服务，释放部分链接
systemctl start mysqld.service

### 如果是因为等待超时时间太长引起可以修改时间 (不推荐)
① mysql> set interactive_timeout =31536000;
  Query OK, 0 rows affected (0.00 sec)

② mysql> set wait_timeout =31536000;
  Query OK, 0 rows affected (0.00 sec)
  
以上都可以通过修改mysql的配置文件重启生效

调整最大连接数：存在安全隐患，我们无法确认数据库能承受最大连接压力；就好比你一天能做3个需求，突然组长给你10需求，让你什么时候提测；那肯定接受不了啊，随时陷入奔溃边缘，脾气瞬间暴涨，离职了不干了。

涉及业务：① 写业务操作数据库结束之后一定要释放当前的链接；② 系统初始化创建一块连接池；用户访问数据库时，从连接池中取出一个已建立的空闲连接对象；使用完毕之后，并不是关闭连接，而是放回连接池给下一个请求使用。

6、mysql5.7版本only_full_group_by导致原sql语句报错

报错代码：

1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'table.starttime' which is not functionally dependent on columns 
in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

报错原因：

mysql5.7版本之后only_full_group_by字段就为真；字面意思就是select后面查询的字段没有出现在group by 中，导致报错。

核心原则：① select、having或order by后面存在的非聚合列必须全部在group by中存在； ② order by后面的列必须是在select后面存在的

问题解决方案：

### 第一种方案：
去掉only_full_group_by规则配置

### 第二种方案：
修改业务sql语句使其遵守only_full_group_by规则

### 第三种
mysql版本降到5.7以下版本

第一种方案：推荐使用；原因相对来说风险较小简单操作，如下：

## 查看下SQL的模式
mysql> show variables like '%sql_mode%';  -- 第一个指令
mysql> SELECT @@sql_mode;  -- 第二个指令
mysql> SELECT @@GLOBAL.sql_mode; --第三个指令
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

说明本地的mysql是开启了规则校验

### 关闭only_full_group_by的规则校
    ① set sql_mode = '' 
    或者 
    set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
    或者
    set @@GLOBAL.sql_mode= 'xxx'  set @@SESSION.sql_mode= 'xxx'
    #### 注意事项
        此设置只针对你当前修改生效，一旦mysql重启以后则会被立即还原的；需要到mysql的配置文件中增加次选项，然后重启生效。

第二种方案：如果是新项目推荐使用，则改动较小；如是就项目升mysql版本，基本上项目sql没有遵循only_full_group_by规则，修改成本较高，且需要测试介入全面测试，人力成本在这里；且存在潜在测不到的问题

第三种方案：不建议使用；降级版本不知道会出现其他的问题，风险较大，安全度不够高

7、编码错误

报错代码：

SQL Error: 1366: Incorrect string value: "xBB\x86…" for column "user_name" at row 1 

报错原因：

因为insert into插入的数据时，某一个字段带有表情(微信登录)；正常utf-8编码可能有2，3，4字节，那么emoji表情或者某些特殊字符是4个字节，而mysql的utf8编码最多3个字节，所以导致插入数据失败；应该很多初学者会遇到这样的问题

问题解决方案：

### 查看mysql配置的字符集
mysql> show variables like 'character_set_%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8mb4                                              |
| character_set_connection | utf8mb4                                              |
| character_set_database   | utf8mb4                                              |
| character_set_filesystem | binary                                               |
| character_set_results    | utf8mb4                                              |
| character_set_server     | utf8mb4                                              |
| character_set_system     | utf8                                                 |
| character_sets_dir       | /usr/local/Cellar/mysql/8.0.19/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.00 sec)
## 参数说明：
① character_set_client      -- 用来设置客户端使用的字符集
② character_set_connection  -- 用来设置连接数据库时的字符集，如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置
③ character_set_database    -- 用来设置默认创建数据库的编码格式，如果在创建数据库时没有设置编码格式，就按照这个格式设置
④ character_set_filesystem  -- 文件系统的编码格式，把操作系统上的文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的
⑤ character_set_results     -- 数据库给客户端返回时使用的编码格式，如果没有指明，使用服务器默认的编码格式
⑥ character_set_server      -- 服务器安装时指定的默认编码格式，这个变量建议由系统自己管理，不要人为定义
⑦ character_set_system     -- 数据库系统使用的编码格式，这个值一直是utf8，不需要设置，它是为存储系统元数据的编码格式
⑧ character_sets_dir       -- 字符集安装的目录

### 修改配置文件
[mysql]
default-character-set=utf8mb4
[mysqld]
character_set_server=utf8mb4 #设置字符集

### 修改数据库字符集
ALTER database 数据库名 CHARACTER SET utf8mb4;
修改完数据库字符集，需要重启mysql数据库；且只对新表有作用
### 修改表的字符集
ALTER TABLE  表名 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
只对该表的新行有用，之前的表无作用

8、mysql数据库导入sql文件报1153

报错代码：

Error Code: 1153 - Got a packet bigger than 'max_allowed_packet' bytes

报错原因：

小伙伴们可能经常性使用navicat客户端软件或者其他mysql客户端软件导入sql数据，文件小点可能没有太大问题，但是一旦文件几十兆或者几百兆就会报上面的错误：具体是受限于mysql默认读取sql的文件大小

问题解决方案：

### 先查看下默认读取sql文件大小
mysql> show VARIABLES like '%max_allowed_packet%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| max_allowed_packet        | 67108864   | -- 指mysql服务器端和客户端在一次传送数据包的过程当中最大允许的数据包大小 64M
| mysqlx_max_allowed_packet | 67108864   | 
| slave_max_allowed_packet  | 1073741824 |
+---------------------------+------------+
3 rows in set (0.00 sec)

### ① 临时修改默认大小
mysql> set global max_allowed_packet = 16*1024*1024;
Query OK, 0 rows affected (0.01 sec)
--- 修改完之后需要退出重新进入mysql
mysql> show VARIABLES like '%max_allowed_packet%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| max_allowed_packet        | 16777216   | -- 16M
+---------------------------+------------+
3 rows in set (0.00 sec)

### ② 修改配置文件my.cnf(找不到配置文件：mysql --help | grep my.cnf)
[mysqld]
max_allowed_packet = 20M

9、创建mysql数据库或者表显示1044无权限创建

报错代码：

mysql> use test;
ERROR 1044 (42000): Access denied for user 'root1'@'localhost' to database 'test'

报错原因：

当前root1账号没有授予创建数据库或者数据表的权限。

问题解决方案：

### 查看账号权限
mysql> show grants for root1@localhost;
+-------------------------------------------+
| Grants for root1@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `root1`@`localhost` |
+-------------------------------------------+
1 row in set (0.01 sec)
### 直观查看root1权限
mysql> select * from mysql.user where user='root1' and host='localhost' \G;
*************************** 1. row ***************************
                    Host: localhost
                    User: root1
             Select_priv: N
             Insert_priv: N
             Update_priv: N
             Delete_priv: N
             Create_priv: N
               Drop_priv: N
             Reload_priv: N
           Shutdown_priv: N
         ....................
1 row in set (0.00 sec)

### 赋予root1账号所有权限
mysql> grant all privileges on *.* to 'root1'@'localhost' with grant option;
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for root1@localhost;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root1@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root1`@`localhost` WITH GRANT OPTION                                                                                                           |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root1`@`localhost` WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
此时再去创建数据库或表操作都是ok的了

10、撤销用户权限报错

报错代码：

ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

报错原因：

因本地mysql的版本是8.0以上，所以在root下创建新用户时会给新用户赋予SYSTEM_USER权限，但是自己本身是没有该权限的，所以导致root账号下对新用户进行撤销账号权限报错。

问题解决方案：

### 第一种方案：新用户root1下操作
mysql> grant system_user on *.* to root;
Query OK, 0 rows affected (0.04 sec)
// 切换到root用户下对root1用户取消权限
mysql> revoke all privileges on *.* from oot1; 
Query OK, 0 rows affected (0.01 sec)

### 第二种方案：新用户账号下直接取消自己全部权限
mysql> revoke all privileges on *.* from root1;
Query OK, 0 rows affected (0.01 sec)

11、插入重复数据因唯一建失败

报错代码：

ERROR 1062 (23000): Duplicate entry 'amu-1' for key 'test.uniq_flag'

报错原因：

因数据表设置了唯一键，导致插入重复数据失败；流程如下：

mysql> CREATE TABLE `test` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    ->   `uniq_flag` varchar(64) NOT NULL DEFAULT ''COMMENT '唯一建',
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `uniq_flag` (`uniq_flag`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试表';
    Query OK, 0 rows affected, 1 warning (0.04 sec)
    
mysql> insert into test(`uniq_flag`) values('amu-1');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+-----------+
| id | uniq_flag |
+----+-----------+
|  1 | amu-1     |
+----+-----------+
1 row in set (0.00 sec)

mysql> insert into test(`uniq_flag`) values('amu-1');
ERROR 1062 (23000): Duplicate entry 'amu-1' for key 'test.uniq_flag'

问题解决方案：

①  方案一：做好业务程序上的判断，若返回状态码是1062则可以记录日志，不报致命错误（推荐使用）

② 方案二：忽略致命报错 insert ignore  into
        mysql> insert ignore  into  test(`uniq_flag`) values('amu-1');
        Query OK, 0 rows affected, 1 warning (0.01 sec)
    注意：出现错误时，只会以警告形式返回；要保证自身的sql语句ok，否则ignore会忽略掉所有报错 （不建议使用）
    
③ 方案三：插入数据时先查询后插入 insert … select … where not exist  
        insert into test(`uniq_flag`) 
        SELECT 'amu-1' FROM dual WHERE NOT EXISTS 
        (SELECT uniq_flag FROM test WHERE uniq_flag = 'amu-1');
        通过select来检测判断是否插入；存在则不插入否则插入数据。 （不推荐使用）

④ 方案四：检测存在相同数据先删除，再重新插入 replace into
        mysql> replace into test(`uniq_flag`) values('amu-2');
        Query OK, 2 rows affected (0.00 sec)
    注意：对业务需求没有影响可以使用，假如存在对数据的统计等等有负面影响则推荐使用第一种方案，程序做兼容判断处理 （可以推荐使用）

12、未知的字段名 name(小伙伴提交代码review发现的)

报错代码：

mysql> select ids  from test;
ERROR 1054 (42S22): Unknown column 'name' in 'field list'

报错原因：

写sql语句时出现了指定表中没有的字段名称，就会出现这个错误。

• ① 可能开发对表字段在测试环境进行增加或者修改，未同步到线上；导致代码上线出现这种情况
• ② 若存在多主多从集群，存在修改表结构未及时同步到从库；导致程序读从库报错
• ③ 极大可能是开发编码不够细心，这种低级错误不能犯；所有的sql语句都要经过expline和测试环境的验证；未做到代码review工作

总结

以上只是总结了部分mysql的常见报错问题的案例分析解决方案；也会继续总结mysql的错误案例分享出来。不足之处，希望小伙伴们指正。

好了，我是阿沐，一个不想30岁就被淘汰的打工人 ⛽️ ⛽️ ⛽️ 。创作不易觉得「阿沐」写的有点料话：👍 关注一下，💖 分享一下，我们下期再见。