mysql: Resource leakage: Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382)

Issue description

For some reason, this suddenly started happening. It seems like this is happening because queries built as strings are being “Prepared” behind the scenes, which is not something you want when every query is unique. The queries are executed with tx.Exec(str) and therefore there is no obvious indication to the user that statements are being prepared, causing this error to be extremely confusing.

Please note, that I’m still not entirely sure what the issue is, but this ticket represents my best guess.

Example code

// Lazy error handling, because example
func (sqlConf DBConfig) ExampleOverPrepare(items map[uint64]SomeStruct) (affected []uint64) {
    db, err := sql.Open("mysql", sqlConf.String()+"?parseTime=true")
    if err != nil { return }
    defer db.Close()
    tx, err := db.Begin()
    if err != nil { return }
    for uid, item := range items {
        queryStr := buildQuery(item) /* func(item SomeStruct) string */
        res, err := tx.Exec(queryStr)
        /* rest of block stubbed out */
    }
    if err := tx.Commit(); err != nil {
         log.Println("[ERROR]", err)
        return
    }
    return affected /* would have been set in stubbed out block */
}

Error log

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

(this is the only error message relating to DB at all, and they appear every time DB is accessed)

Configuration

Driver version (or git SHA): 0b58b37b664c21f3010e836f1b931e1d0b0b0685

Go version: run go version in your console go version go1.9.2 linux/amd64

Server version: E.g. MySQL 5.6, MariaDB 10.0.20 MySQL 5.6.32-78.0-log

Server OS: E.g. Debian 8.1 (Jessie), Windows 10 Unknown

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 1
Comments: 17 (7 by maintainers)

Most upvoted comments

You can use interpolateParams to prevent queries being prepared

percona-csalguero on Nov 6, 2017

This is consistently reproducing the problem. Processes continue to increase until execution completes, then they all disappear. After the process runs but before the program terminates, you can run mysql -uroot -e 'SHOW PROCESSLIST' | grep 'MYSQL_USER_FROM_GO_CLIENT' | wc -l to see all the processes still alive by the go client.

package main

import (
	"fmt"
	"time"
	"database/sql"
)

var connection, err = sql.Open("mysql", "connection-uril")

func query() {
	// TODO: what about uuid for the item?
	var sql = `select *
		from table
		where id = ? order by update_date desc
		`

	//var sc = utils.GetSQLConnector()
	stmt, err := connection.Prepare(sql)
	if err != nil {
		return
	}
	rows, err := stmt.Query(123)
	rows = rows
	stmt.Close()
	return

}

func main() {
	for i := 1; i <= 10000; i++ {
		query()
	}

	fmt.Println("queries complete")
	time.Sleep(30 * time.Second)

	return
}

What’s interesting is the number of processes. If the look is set to 100, processes at the end are 100. 1000 is 1000. but 10,000 is 1017. There is some sort of cap that’s not getting me to hit the max_prepared_stmt_count - however this is still the same problem because the processes are left open. It’s possible that this problem is exacerbated by using gin on top of the problem itself.

macdabby on Jan 8, 2018

To configure max_prepared_stmt_count properly, you should know this:

When you use DB.Exec(query, arg...), it may consume MaxConnections statements on server side.
When you have N statements created by DB.Prepare(), it may consume N * MaxConnections statements.
When you have N Tx.Prepare() in transaction, it may consume N * MaxConnections statements.

So max_prepared_stmt_count should be larger than sum of all above. And you can reduce statement count easily by reducing connection. When you use some ORM or other toolkit which automatically manages prepared statement, you should know how many statements are used in it.

I close this issue for now. If you sure about there is leak, please reopen with reproducible complete example code.

methane on Jan 6, 2018

@macdabby You need DB.SetConnMaxLifetime(time.Second * 10). Default setting will keep connections eternally and it is very bad for MySQL.

methane on Jan 6, 2018