Error 1461: Can‘t create more than max_prepared_stmt_count statements (current value: 16382)

开发go项目时，其中一个方法获取计数器id时，项目运行一阵子之后出现了这个错误，Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382)。

报了该错误的原因是mysql对于超出max_prepared_stmt_count的prepare语句就会报1461的错误。

主要原因是在用prepare语句时，没有关闭语句，即 defer stmt.Close()。

如此积累，最终导致Com_stmt_prepare大于Com_stmt_close。

func DbCountID(id string) int {
	sql := "update counter set field_value=last_insert_id(field_value+1) where field_key=?"
	stmt, err := db.DB.DB().Prepare(sql)
	if err != nil {
		return 0
	}
	// defer stmt.Close() // 缺少这行代码关闭prepare语句
	res, err := stmt.Exec(id)
	if err != nil {
		return 0
	}
	lastinserid, err := res.LastInsertId()
	if err != nil {
		return 0
	}
	countID := int(lastinserid)
	return countID
}

 

---

登录数据库执行

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

Com_stmt_close prepare语句关闭的次数

Com_stmt_execute prepare语句执行的次数

Com_stmt_prepare prepare语句创建的次数

---

查询max_prepared_stmt_count数量

mysql> show global variables like 'max_prepared_stmt_count';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 16382 |
+-------------------------+-------+