Mysql show processlist of a specific user - filtering processlist

 SELECT * FROM information_schema.processlist WHERE User LIKE 'username';

The way I use go (golang) sql/mysql driver to create connection

I use go for web development and I have noticed I have a few problems with database connections to mysql using go-sql-driver/mysql.

Database connections created and then never closed. I reached a point of 4,096 simultaneous connections and my mysql server have reached max-connections which reached a denial of service.

I had to play a bit with some tweaking of the code and this is what I have reached:

db.SetMaxOpenConns(200) // this works occasionally 

db.SetMaxIdleConns(30) // this does not work but I keep it anyway

db.SetConnMaxLifetime(time.Second * 30) // this also does not work but I keep it

db.Exec("SET SESSION wait_timeout = 30") // this works

db.Exec("SET SESSION interactive_timeout = 30") // and also this one

Using the above directive I was able to have the number of connections under control. Please note that sometimes I still get more than 200 connections even though I set the maximum to 200, and sometimes it keeps the max 200.

SetMaxIdleConns and SetConnMaxLifetime - did not have any affect what so ever.

Again, it might be that I am not fully aware of the configuration or the right usage.

Here below is my full function to get a connection to the database with go mysql driver:

package main

import (
	"database/sql"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql" // Register the driver
)

var db *sql.DB = nil // global connection

func GetConnection(whereFrom string) (*sql.DB, error) {
	slog.Info(" * UC:", "connectIndex", connectIndex, "from", whereFrom)

	var err error

	if !isConnectionAlive(db) {
		slog.Error("20250225.01152 connection GetConnection error: connection is not alive")
		db, err = createSimpleConnection()
		if err != nil {
			slog.Error("20250225.01144b connection GetConnection error receiving database", "err", err)
			return nil, err
		}
	}

	return db, nil
}

// create a real connection to the database
func createSimpleConnection() (*sql.DB, error) {
	var err error

	db, err := sql.Open("mysql", "user:password@tcp(IP:3306)/databasename?charset=utf8mb3")
	if err != nil {
		log.Fatal("error 20241212.01314 createConnection error: " + err.Error())
		return nil, err
	}

	db.SetMaxOpenConns(200)                 // Set max open connections
	db.SetMaxIdleConns(30)                  // Set max idle connections
	db.SetConnMaxLifetime(time.Second * 30) // Set the max lifetime of a connection

	// Setting session-specific parameters
	_, err = db.Exec("SET SESSION max_execution_time = 30000") // seconds = milliseconds * 1000
	if err != nil {
		slog.Error("20241212.01641 set wait_timeout", "err", err)
		return nil, err
	}
	
	_, err = db.Exec("SET SESSION wait_timeout = 30") // seconds
	if err != nil {
		log.Fatal("error 20241212.01641 set wait_timeout error: " + err.Error())
		return nil, err
	}

	_, err = db.Exec("SET SESSION interactive_timeout = 30") // seconds
	if err != nil {
		log.Fatal("error 20241212.01642 set interactive_timeout error: " + err.Error())
		return nil, err
	}

	return db, nil
}

func isConnectionAlive(db *sql.DB) bool {
	if db == nil {
		return false
	}

	err := db.Ping()
	return err == nil
}