MySQL 各类问题及解决办法汇总

左手的ㄟ右手 2022-11-10 03:54 250阅读 0赞

> ### 推荐阅读 ###
> 
> ##### Helm3（K8S 资源对象管理工具）视频教程：[https://edu.csdn.net/course/detail/32506][https_edu.csdn.net_course_detail_32506] #####
> 
> ##### Helm3（K8S 资源对象管理工具）博客专栏：[https://blog.csdn.net/xzk9381/category\_10895812.html][https_blog.csdn.net_xzk9381_category_10895812.html] #####

> [本文原文链接：https://blog.csdn.net/xzk9381/article/details/114872726，转载请注明出处。如有发现文章中的任何问题，欢迎评论区留言。][https_blog.csdn.net_xzk9381_article_details_114872726]

### 1. MySQL 5.7 线程阻塞的解决办法 ###

--------------------

#### 1.1 问题说明 ####

在数据库中执行语句报错： ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。

#### 1.2 解决办法 ####

1.  查看当前数据库中的线程

show full processlist;

1.  如果没有看到正在执行的慢sql记录线程，再查看 innodb的事务表INNODB\_TRX，看下里面是否有正在锁定的事务线程，看看trx\_mysql\_thread\_id是否在show full processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit 或者 rollback而是卡住了，我们需要手动kill掉，例如kill 32735

### 2. MySQL 5.7远程连接数据库时速度慢的解决办法 ###

--------------------

#### 2.1 问题说明 ####

使用mysql命令远程访问数据库，输入密码后需要等待5s-10s才可完成连接，导致后台服务抛出异常。

#### 2.2 问题原因 ####

每次访问数据库，mysql就会试图去解析来访问的机器的domain name，如果这时无法解析，等一段时间会失败，数据才能被取过来。

#### 2.3 解决办法 ####

在mysql配置文件中\[mysqld\]下面添加skip-name-resolve配置项来禁止解析域名，重启数据库即可生效

### 3. MySQL 5.7 二进制安装报错 ###

--------------------

#### 3.1 问题说明 ####

在centos6.8（最小化安装）中使用二进制安装包安装MySQL 5.7报错：

> error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

#### 3.2 解决办法 ####

CentOS最小化安装时默认不安装libaio库，需要手动进行安装

yum install libaio-devel

### 4. MySQL 5.7中使用group concat函数数据被截断问题的解决方法 ###

--------------------

#### 4.1 问题说明 ####

调用GROUP\_CONCAT函数进行查询时，查询没有停止且没有返回任何数据（也有一种情况是查询出来的数据被截断了，最长长度不超过1024字节）

#### 4.2 问题原因 ####

该问题的原因是由于官方给出的默认group\_concat\_max\_len配置为1024，这个配置会限制GROUP\_CONCAT数据的长度。可以使用如下命令查询：

mysql> show variables like 'group_concat_max_len';
    +----------------------+--------+
    | Variable_name        | Value  |
    +----------------------+--------+
    | group_concat_max_len |  1024  |
    +----------------------+--------+
    1 row in set (0.05 sec)
    
    # 官方手册对该配置的定义是：The maximum permitted result length in bytes for the GROUP_CONCAT() function

#### 4.3 解决办法 ####

可以将group\_concat\_max\_len调整到最大值18446744073709551615（或根据业务实际返回的最大长度进行设置）

1.  修改MySQL的配置文件，在\[mysqld\]下面添加group\_concat\_max\_len = 18446744073709551615（永久生效）
2.  在控制台中进行设置（临时生效）

SET GLOBAL group_concat_max_len=18446744073709551615;
    SET SESSION group_concat_max_len=18446744073709551615;

### 5. MySQL 报 ERROR: Packet for query is too large (2034> 1024) ###

--------------------

#### 5.1 问题说明 ####

数据存储的时候报错：

> ERROR: Packet for query is too large (2034> 1024). You can change this value on the server by setting the max\_allowed\_packet’ variable.

#### 5.2 解决办法 ####

1.  使用如下命令查看当前数据库中的max\_allowed\_packet设置

1.  在my.cnf配置文件\[mysqld\]中添加或修改max\_allowed\_packet=128M，重启mysql即可生效。如果过一段时间查询max\_allowed\_packet的值又恢复为1024，则需要查询当前服务器的内存是否不足，这会导致MySQL自动重新设置参数为1024，预留出足够的内存即可解决该问题。

### 6. MySQL 5.7报错Waiting for table metadata lock的解决办法 ###

--------------------

#### 6.1 问题说明 ####

在MySQL5.7中执行alt table等DDL操作时，操作被阻塞无法执行，使用 show processlist查看发现对该表的alt table操作显示Waiting for table metadata lock。

#### 6.2 问题原因 ####

在MySQL5.7中执行alt table等DDL操作时，如果该表中有未提交的事务则会出现 Waiting for table metadata lock状态

#### 6.3 解决办法 ####

1.  使用如下命令从information\_schema.innodb\_trx 查看未提交事务， 一般只要kill掉这些线程，DDL操作就不会Waiting for table metadata lock

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx \G
    
    # 字段说明：
    # trx_state: 事务状态，一般为RUNNING
    # trx_started: 事务执行的起始时间，若时间较长，则要分析该事务是否合理
    # trx_mysql_thread_id: MySQL的线程ID，用于kill
    # trx_query: 事务中的sql

1.  调整锁超时阈值。lock\_wait\_timeout 表示获取metadata lock的超时（单位为秒），允许的值范围为1到31536000（1年）。 默认值为31536000，将其调整为30分钟

set session lock_wait_timeout = 1800;
    set global lock_wait_timeout = 1800;

### 7. MySQL报错ERROR 1418 (HY000)的解决办法 ###

#### 7.1 问题说明 ####

MySQL开启bin-log后，调用存储过程或者函数以及触发器时会出现错误号为1418的错误:

> ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log\_bin\_trust\_function\_creators variable)

#### 7.2 问题原因 ####

因为CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,ALTER FUNCTION,CALL, DROP PROCEDURE, DROP FUNCTION等语句都会被写进二进制日志,然后在从服务器上执行。但是，一个执行更新的不确定子程序(存储过程、函数、触发器)是不可重复的，在从服务器上执行(相对与主服务器是重复执行)可能会造成恢复的数据与原始数据不同，从服务器不同于主服务器的情况。

为了解决这个问题，MySQL强制要求在主服务器上，除非子程序被声明为确定性的或者不更改数据，否则创建或者替换子程序将被拒绝。这意味着当创建一个子程序的时候，必须要么声明它是确定性的，要么它不改变数据。

声明方式有两种：

1.  声明是否是确定性的： DETERMINISTIC和NOT DETERMINISTIC指出一个子程序是否对给定的输入总是产生同样的结果。如果没有给定任一特征，默认是NOT DETERMINISTIC，所以必须明确指定DETERMINISTIC来声明一个子程序是确定性的。这里要说明的是:使用NOW() 函数（或它的同义）或者RAND() 函数不会使一个子程序变成非确定性的。对NOW()而言，二进制日志包括时间戳并会被正确的执行。RAND()只要在一个子程序内被调用一次也可以被正确的复制。所以，可以认为时间戳和随机数种子是子程序的确定性输入，它们在主服务器和从服务器上是一样的。
2.  声明是否会改变数据：CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL用来指出子程序是读还是写数据的。  
    无论NO SQL还是READS SQL DATA都指出，子程序没有改变数据，但是必须明确地指定其中一个，因为如果任何指定，默认的指定是CONTAINS SQL。

也就是说，在开启bin-log的情况下，必须指定创建的函数是否是如下几种类型：

1.  DETERMINISTIC：不确定的
2.  NO SQL：没有SQl语句，当然也不会修改数据
3.  READS SQL DATA：只是读取数据，当然也不会修改数据
4.  MODIFIES SQL DATA：要修改数据
5.  CONTAINS SQL：包含了SQL语句

默认情况下，如果允许CREATE PROCEDURE 或CREATE FUNCTION 语句被接受，就必须明确地指定DETERMINISTIC 或 NO SQL与READS SQL DATA 中的一个，否则就会产生1418错误。

#### 7.3 解决办法 ####

解决办法有两种：

1.  创建子程序(存储过程、函数、触发器)时，声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个

CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`()
        DETERMINISTIC
    BEGIN
     #Routine body goes here...
    END;

1.  信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log\_bin\_trust\_routine\_creators=1

# 方法1，在mysql控制台中直接修改（临时生效）
    SET GLOBAL log_bin_trust_function_creators = 1;
    
    # 方法二，在MySQL启动时，加上--log-bin-trust-function-creators选项，参数设置为1
    
    # 方法三，在my.cnf配置文件[mysqld]中添加如下内容，重启后生效
    log-bin-trust-function-creators = 1

### 8. MySQL报错Communications link failure的解决办法 ###

--------------------

#### 8.1 问题说明 ####

tomcat进程连接数据库后报错：

> Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.)

#### 8.2 问题原因 ####

根据报错信息可以判断连接池中的连接Connection失效，即超过了有效时间wait\_timeout。登录mysql后查看wait\_timeout有效时间为28800，即8小时。

#### 8.3 解决办法 ####

修改wait\_timeout属性，调大数值为31536000（365天，linux最大值为一年）

# 在my.cnf配置文件中，[mysqlId]下添加wati_timeout、interactive_timeout属性，重启mysql
    wait_timeout = 31536000
    interactive_timeout = 31536000

### 9. 数据库连接时报错ERROR 1129 (00000)…is blocked because of many connection errors ###

--------------------

#### 9.1 问题说明 ####

连接数据库时报错：

> ERROR 1129 (00000): \#HY000Host ‘192.168.31.242’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

#### 9.2 问题原因 ####

连接错误次数过多（尝试连接次数过多），超出配置文件中max\_connection\_errors配置项次数。

#### 9.3 解决办法 ####

1.  在mysql服务器上输入如下命令来清除缓存

mysqladmin flush-hosts -uroot -p

1.  在my.cnf配置文件\[mysqld\]中添加如下配置并重启数据库

max_connection_errors = 1000

### 10. MySQL5.7查询时报错：\[Err\] 1055 - Expression \#1 of SELECT list is not in GROUP BY clause…this is incompatible with sql\_mode=only\_full\_group\_by ###

--------------------

#### 10.1 问题说明 ####

将数据库从5.5版本迁移至5.7版本，执行查询语句时详细报错信息如下：

> \[Err\] 1055 - Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘cdxc.a.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by

#### 10.2 问题原因 ####

MySQL5.7中，sql\_mode默认设置了ONLY\_FULL\_GROUP\_BY，使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行。

#### 10.3 解决办法 ####

1.  查询当前sql\_mode的配置

mysql> select @@sql_mode;
    +--------------------------------+
    | @@sql_mode					 |                                 
    +--------------------------------+
    |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 |
    +--------------------------------+
    1 row in set (0.04 sec)

1.  将配置中的ONLY\_FULL\_GROUP\_BY去掉，将其余配置写入配置文件中的\[mysqld\]下面，并重启数据库服务，格式如下

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1.  或者直接在数据库中输入如下内容（需要退出数据库重新进入才能生效）

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';

### 11. MySQL5.7报错Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ###

--------------------

#### 11.1 问题说明 ####

mysql5.7执行sql语句导入时报错:mysql 5.7 Error : Incorrect datetime value: ‘0000-00-00 00:00:00’ for column xxx

#### 11.2 问题原因 ####

mysql从5.7版本开始，datetime不再支持’0000-00-00 00:00:00’ 的格式。

#### 11.3 解决办法 ####

1.  将即将导入的sql语句中包含’0000-00-00 00:00:00’ 的替换为’1970-01-01 00:00:01’即可

sed -i 's/0000-00-00 00:00:00/1970-01-01 00:00:01/g' test.sql

1.  修改sql\_mode，将sql\_mode中的NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE删除即可

# 查询sql_mode
    select @@sql_mode;
    
    # 在控制台中重新设置sql_mode
    set global sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,
    NO_ENGINE_SUBSTITUTION';
    
    # 在配置文件[mysqld]中添加如下内容重新设置sql_mode
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

### 12. MySQL 5.7 报错：Multi-statement transaction required more than ‘max\_binlog\_cache\_size’ bytes of storage; increase this mysqld variable and try again ###

--------------------

#### 12.1 问题说明 ####

在对 MySQL 批量更新或删除等大事务时，会导致报如下错误：

Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage; increase this mysqld variable and try again

#### 12.2 问题原因 ####

这是由于更新和删除的大事务会写入大量binlog，可能会造成binlog cache过小而导致执行失败。也会遇到主库执行成功而从库不同步的情况。这是因为虽然主从的max\_binlog\_cache\_size参数虽然设置的一样，但是实际使用的binlog cache不一定相同，从而导致binlog cache因为过小而主从复制中断。

#### 12.3 解决办法 ####

首先查看 MySQL 中当前设置的 max\_binlog\_cache\_size 大小：

mysql> show variables like 'max_binlog_cache_size';
    +-----------------------+-----------+
    | Variable_name         | Value     |
    +-----------------------+-----------+
    | max_binlog_cache_size | 134217728 |
    +-----------------------+-----------+

查看到该参数设置的值为 134217728，也就是 128M （ 128 \* 1024 \* 1024 ），将其设置为合适的大小，例如 256M：

# 在控制台中设置
    mysql> set global max_binlog_cache_size=268435456;
    Query OK, 0 rows affected (0.05 sec)
    
    # 在配置文件 [mysqld] 中添加如下内容进行设置
    max_binlog_cache_size = 256M

[https_edu.csdn.net_course_detail_32506]: https://edu.csdn.net/course/detail/32506
[https_blog.csdn.net_xzk9381_category_10895812.html]: https://blog.csdn.net/xzk9381/category_10895812.html
[https_blog.csdn.net_xzk9381_article_details_114872726]: https://blog.csdn.net/xzk9381/article/details/114872726