mysql数据库Can‘t create more than max_prepared_stmt_count statements (current value: 16382)

Jelly-小丑鱼

已于 2023-10-27 10:15:59 修改

阅读量955

点赞数

文章标签：数据库 mysql golang stmt错误

于 2023-10-18 17:38:22 首次发布

本文链接：https://blog.csdn.net/u013896064/article/details/133909374

版权

最近开发的一个golang项目，在测试过程中没有出现任何问题，但是在部署到客户生产环境运行一段时间后，出现了Can‘t create more than max_prepared_stmt_count statements (current value: 16382)的错误，这个错误导致了我们的后台数据库无法正常访问了，后面经过查询资料和测试，发现是prepare这个句柄没有关闭，导致最后没关闭的超过了默认值16382。

下面记录下我的排查过程：

1、定位原因

当后台报错，前端无法正常获取请求数据的时候，发现后端报stmt错，然后查阅资料发现是Prepare没有关闭的数量达到上限

2、数据库检测

命令行或者mysql的连接工具连接到数据库，命令如下所示：

PS C:\Users\jelly\Desktop\Gopath\src\cy_zta_dmt_pap_backend> mysql.exe -h 192.168.13.208 -u root -p
Enter password: *******************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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>

输入密码进入到数据库当中去。

输入命令，查询当前stmt的最大值，下面可以看到值是默认的16382

mysql> show variables like '%max_prepared_stmt_count%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 16382 |
+-------------------------+-------+
1 row in set (0.18 sec)

mysql>

输入命令，查询当前的Prepare和close的数量，如下所示，可以看到Prepare的值是1811，close的值是1784，两个有差值，这个差值如果小于stmt的最大值16382的话，那就不会报错，如果大于了16382的话，操作数据库的时候就会报错，而且只是数据库的Prepare操作才会报错，其他的数据库操作不会报错。但是从下面的差值可以看出这这个差值明显是小于16382的，所以不会报错，也是因为我重启了数据库的原因，重启后所有值都会清零，然后有继续运行，目前差值很小，但是运行时间长了差值就会越来越大，最后完全超过16382。

mysql> show global status like 'com_stmt%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Com_stmt_execute        | 1811  |
| Com_stmt_close          | 1784  |
| Com_stmt_fetch          | 0     |
| Com_stmt_prepare        | 1811  |
| Com_stmt_reset          | 0     |
| Com_stmt_send_long_data | 0     |
| Com_stmt_reprepare      | 0     |
+-------------------------+-------+
7 rows in set (0.01 sec)

mysql>

3,、定位原因

然后再去我们的项目代码里面去查询所有使用到Prepare的地方，全部给加上close，如下所示：

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
 )

G_db, err = sql.Open("mysql", mysqlurl+"/papdb?charset=utf8")
if err != nil {
    //出现错误
	return err
}

stdout, err := G_db.Prepare(sql_cmd)
if err != nil {
	return err
}
defer stdout.Close() 
//或者使用stdout.Close()，使用stdout.Close()的时候需要注意后面代码就不能stdout句柄了,但是如果加了defer的话，后面的代码还可以继续使用，因为defer是表示在函数最后再执行的意思

把所有的Prepare代码的后面都加上close的操作，那么久不会出现stmt超过最大值的问题了，修改完成后再去看Prepare和close就会发现两个的数量是一样的，不管后台怎么操作数据库，差值永远都是0，那么stmt超额的问题就再也不会发生了，如下图所示：

mysql> show global status like 'com_stmt%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Com_stmt_execute        | 287   |
| Com_stmt_close          | 287   |
| Com_stmt_fetch          | 0     |
| Com_stmt_prepare        | 287   |
| Com_stmt_reset          | 0     |
| Com_stmt_send_long_data | 0     |
| Com_stmt_reprepare      | 0     |
+-------------------------+-------+
mysql>

可以看到Prepare和close的差值是287-287=0

4、临时解决方法

如果有时候在生产环境，服务不能停，也不能重启数据库的话，有个临时解决方法就是增大stmt的最大值，设置命令如下：可以看到下面的执行操作是先查看了当前的stmt的最大值为16382，然后执行命令将其值改为32764，最后再查看是否修改成功，修改成功后，就可以暂时调用有Prepare的数据库操作接口了，但是大家应该也清除，这个是治标不治本的操作，后面隔不了多久就又会超过上限，目前查阅到的资料显示，该值最大可设置成1048576，如果可以重启数据库是最方便也是最快捷的临时解决办法，但是一般都不能，毕竟生产环境是随时都会有数据操作的。

mysql> show variables like '%max_prepared_stmt_count%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 16382 |
+-------------------------+-------+
1 row in set (0.01 sec)

mysql>
mysql> set global max_prepared_stmt_count=32764;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%max_prepared_stmt_count%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 32764 |
+-------------------------+-------+
1 row in set (0.01 sec)

mysql>