How to Fix mysql: Error 1040: Too many connections in Go

The “mysql: Error 1040: Too many connections” error occurs in Golang when the “maximum number of concurrent connections to the MySQL server has been reached. By default, MySQL allows up to 151 concurrent connections.”

In a Go application, this error might occur if your application creates many connections without closing them or uses a connection pool with an improperly configured maximum limit.

Here are some of the solutions to fix the error:

Fix 1: Close connections

Ensure that you are closing each connection after it is no longer needed. Use the defer statement to ensure connections are correctly closed.

package main

import (
  "database/sql"
  "log"
)

func main() {
  conn, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
  if err != nil {
    log.Fatal(err)
  }
  defer conn.Close()
}

Fix 2: Use the connection pooling

Go’s database/SQL package provides a built-in connection pool. First, configure the connection pool’s maximum number of connections by setting the SetMaxOpenConns property:

conn.SetMaxOpenConns(50)

Adjust the value based on your application’s needs and the MySQL server’s capacity.

Fix 3: Increase MySQL’s max_connections

If your application genuinely requires a higher number of concurrent connections, consider increasing the max_connections value in the MySQL configuration file (my.cnf or my.ini) under the [mysqld] section:

[mysqld]

max_connections = 300

After making the changes, restart the MySQL server for the new configuration to take effect.

Fix 4: Monitor connections

Use the following SQL query to check the current number of connections:

SHOW STATUS WHERE Variable_name = 'Threads_connected';

Fix 5: Optimize queries and application logic

Review your application’s logic and queries to ensure they are optimized and do not cause unnecessary connections. For example, if your application has long-running queries, consider optimizing them to reduce the load on the MySQL server.

Remember that increasing concurrent connections consumes more server resources, so balancing the application’s requirements and the server’s capacity is essential.