MySQL 错误剖析

每个 MySQL 错误都由标识错误的以下部分组成：

ERROR NUMBER 是标识每个错误的唯一编号。
SQLSTATE 是标识 SQL 错误情况的代码。
ERROR MESSAGE 以人类可读的格式描述错误。
下面是一个 MySQL 错误示例：

1
ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist

在上面的示例中：

  • 1146 是错误号
  • 42S02 是 SQLSTATE
  • 表 ‘test.no_such_table’ 不存在是错误消息

You have an error in your SQL syntax

1
ERROR 1064 (42000): You have an error in your SQL syntax

这意味着 MySQL 由于语法问题而无法理解您的查询。通常，问题的原因是忘记将某些文本或值括在反引号或引号中。例如，而不是在 MySQL 查询中使用数据库名称，因为它应该是 。my-databasemy-database

错误消息甚至会更进一步，并指出语法开始无效的位置，您可以将其用作寻找问题的起点。

💡 仔细检查您的查询，确保所有 SQL 语句的格式正确并遵循 MySQL 的语法规则。

连接问题

用户 ‘root‘@’localhost’ 的访问被拒绝（使用密码：YES）

1
ERROR 1045: Access denied for user 'root'@'localhost'

任何使用 MySQL 的人都可能至少遇到过一次。
此错误可能有多种原因，例如用户名和/或密码错误，或缺少对数据库的权限。

此错误表示 MySQL 服务器在尝试使用提供的密码从 ‘localhost’ 服务器进行连接时拒绝了对 ‘root’ 用户的访问。

💡要解决此问题，请仔细检查密码并确保用户具有必要的权限。
您可以重置密码或授予解决此问题所需的权限。

Lost connection to MySQL server during query

1
ERROR 2013: Lost connection to MySQL server during query

在连接被断开之后，客户端再次发送请求，导致的。需要重新来连接一下。

Too many connections

1
ERROR 1040: Too many connections

当 MySQL 服务器达到其允许的最大连接数限制时，会出现此错误。

💡 如果实际连接线程数过大，可以考虑增加服务器节点来分流；如果实际线程数并不算过大，那么可以配置 max_connections 来增加允许的最大连接数。需要注意的是，连接数不宜过大，一般来说，单库每秒有 2000 个并发连接时，就可以考虑扩容了，健康的状态应该维持在每秒 1000 个并发连接左右。

（1）查看最大连接数

1
2
3
4
5
6
7
mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+

（2）查看服务器响应的最大连接数

1
2
3
4
5
6
7
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 142   |
+----------------------+-------+
1 row in set (0.00 sec)

（3）临时设置最大连接数

1
set GLOBAL max_connections=256;

注意：当服务器重启时，最大连接数会被重置。

（4）永久设置最大连接数

修改 /etc/my.cnf 配置文件，在 [mysqld] 添加以下配置：

1
max_connections=256

重启 mysql 以生效

（5）修改 Linux 最大文件数限制

设置了最大连接数，如果还是没有生效，考虑检查一下 Linux 最大文件数

Mysql 最大连接数会受到最大文件数限制，vim /etc/security/limits.conf，添加 mysql 用户配置

1
2
mysql hard nofile 65535
mysql soft nofile 65535

（6）检查 LimitNOFILE

如果是使用 rpm 方式安装 mysql，检查 mysqld.service 文件中的 LimitNOFILE 是否配置的太小。

MySQL server has gone away

1
ERROR 2006 (HY000): MySQL server has gone away

此错误表示 MySQL 服务器意外终止了连接。
这可能是由于各种原因造成的，包括长时间运行的查询或服务器超时。

💡 要防止这种情况，您可以在 MySQL 配置中调整 wait_timeout 和 interactive_timeout 设置。

MySQL client ran out of memory

1
ERROR 2008: MySQL client ran out of memory

当 MySQL 客户端消耗的内存超过可用内存时，会发生此错误。

💡 要解决此问题，您可能需要优化查询、限制结果集或为 MySQL 客户端分配更多内存。

查询问题

索引失效问题

eg：假设age是整数类型，但是却使用字符串类型

1
SELECT * FROM user WHERE age = '20';

MySQL 需要在查询时转换 ‘20’ 为整数类型，可能会导致索引无法使用。 某个电商平台就有这么一个类似的bug，导致下单超时崩盘30分钟。

MySql 5.7 解决GROUP BY出现的问题

报错如下：

1
2
1055 - Expression #1 of SElECT list is not in GRoUp BY clause and contains nonaggregated column 'projhigh.project node.i whicn is not functionally dependent on columns in GROup BY clause; this is incompatible with sql mode=only_full_group_by

引起的原因是：这个错误一般发生在mysql 5.7以及 5.7以上的版本中，其原因是mysql的默认配置中,sql_mode=”ONLY_FULL_GROUP_BY” 这个配置严格执行了 ‘SQL92标准’,所以很高网站维护人员在升级mysql版本时，都会修改 sql_mode 的配置，使其能兼容。

解决方法1，重启mysql后会失效：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查看ONLY_FULL_GROUP_BY 校验规则是否开启
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
 
 
-- 第一个sql语句的结果，使用自己查询的结果
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 
-- 第二个sql语句的结果
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


-- 关闭only_full_group_by的规则校验
set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set @@SESSION.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

解决方法2：
修改配置文件 my.ini 

1
2
在 [mysqld] 下面添加代码：
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

重启mysql服务。

注意：

1、不同的系统，mysql 的配置文件名以及路径不同

2、Mac或Linux文件 /etc/my.cnf

3、windows 在数据库安装目录下的 my.ini

空间问题

The table is full

1
ERROR 1114 (HY000): The table is full

如果发生 table-full 错误，则可能是磁盘已满或表已达到其最大大小。
MySQL 数据库的有效最大表大小通常由操作系统对文件大小的限制决定，而不是由 MySQL 内部限制决定。

💡 要解决此问题，您可以：

  • 增加 MEMORY 表允许的最大大小
1
2
3
[mysqld]
max_heap_table_size = 2G
tmp_table_size = 2G
  • 切换到 InnoDB 存储引擎
  • 检查磁盘空间并增加可用空间：

Packet too large

1
ERROR: Packet too large

当 MySQL 客户端或 mysqld 服务器获取的数据包大于max_allowed_packet字节，它会发出 Packet too large 错误并关闭连接。

1 GB 数据包大小是可以传输到 MySQL 服务器或客户端或从 MySQL 服务器或客户端传输的最大数据包大小。如果 MySQL 服务器或客户端收到大于 max_allowed_packet 字节的数据包，则发出 ER_NET_PACKET_TOO_LARGE 错误并关闭连接。

💡 您可以通过调整 MySQL 配置文件中的 max_allowed_packet 设置来解决此问题。

Can’t create/write to file

1
ERROR: Can’t create/write to file

当 MySQL 无法创建或写入文件时，通常会发生此错误，通常是由于权限不足或磁盘空间不足。
💡 确保 MySQL 用户具有必要的文件权限，并检查可用磁盘空间以解决此问题。

Packet for query is too large错误

1
2
在mysql安装目录中找到my.ini配置文件
在最后添加 max_allowed_packet=10485760

数值可以 MB为单位，16M

数据库事务问题

Lock wait timeout exceeded; try restarting transaction

这是 MySQL 数据库中的一个常见错误，通常发生在使用 InnoDB 存储引擎的事务中。它的含义是：某个事务在尝试获取锁资源时，等待时间超过了数据库配置的阈值（innodb_lock_wait_timeout，默认 50 秒），因此被强制终止

时区问题

时区（time_zone）偏差

现象

数据库中存储的 Timestamp 字段值比真实值少了 13 个小时。

原因

  • 当 JDBC 与 MySQL 开始建立连接时，会获取服务器参数。
  • 当 MySQL 的 time_zone 值为 SYSTEM 时，会取 system_time_zone 值作为协调时区，若得到的是 CST 那么 Java 会误以为这是 CST -0500 ，因此会给出错误的时区信息（国内一般是CST +0800，即东八区）。

查看时区方法：

通过 show variables like '%time_zone%'; 命令查看 Mysql 时区配置：

1
2
3
4
5
6
7
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

解决方案

方案一

1
2
3
4
5
mysql> set global time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> set time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)

方案二

修改 my.cnf 文件，在 [mysqld] 节下增加 default-time-zone='+08:00' ，然后重启。

参考

https://simplebackups.com/blog/extensive-mysql-common-errors-list/#the-anatomy-of-mysql-errors