12 种 mysql 常见错误总结 + 分析示例
发布于: 2 小时前
前言
小伙伴们好，我是阿沐！最近呢，前段时间在筹备上云工作，需要考虑到很多场景；比如 mongo、mysql、redis、splinx 等等迁移工作，这就涉及到版本兼容问题；在迁移之前，阿沐迁移了 mysql 到其他容器中，发现迁移机器 mysql 版本号比较高 5.7 以上，就出现了 sql 语句兼容问题。所以趁机会整理了很久以前遇到的各种 mysql 常见问题跟掘金小伙伴们分享下。小伙伴们可以收藏起来哦，遇到常规错误可以快速查询解决~~~
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 -pEnter 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> exitBye
此时再登录时我们的密码已经更新为root
复制代码
3、无法连接 mysql 服务器
报错代码：
➜ ~ mysql -uroot -pERROR 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:portmysql: [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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 -pERROR!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 -pERROR 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)--- 修改完之后需要退出重新进入mysqlmysql> 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 岁就被淘汰的打工人 ⛽️ ⛽️ ⛽️ 。创作不易觉得「阿沐」写的有点料话：👍 关注一下，💖 分享一下，我们下期再见。
划线
评论
复制
发布于: 2 小时前阅读数: 3
版权声明: 本文为 InfoQ 作者【我是阿沐】的原创文章。
原文链接:【http://xie.infoq.cn/article/f9e965d1c5a12b3bbd143db9f】。文章转载请联系作者。
我是阿沐
关注
生活最重要的是开心 | 保持一个好心态 2021.05.29 加入
公众号：我是阿沐 | 腾讯音乐 | 思绪来得快去得也快，偶尔会在这里停留
评论