MySQL returning "Too many connections" error

Question

I'm doing something like this:

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

var db *sql.DB

func main() {
    var err error
    db, err = sql.Open(...)

    if err != nil {
        panic(err)
    }

    for j := 0; j < 8000; j++ {
        _, err := db.Query("QUERY...")
        
        if err != nil {
            logger.Println("Error " + err.Error())
            return
        }
    }
 }

It works for the first 150 queries (for that I'm using another function to make) but after that, I get the error :

mysqli_real_connect(): (HY000/1040): Too many connections

So clearly I'm doing something wrong but I can't find what is it. I don't know what to open and close a new connection for each query.

Error in the log file : 
"reg: 2020/06/28 03:35:34 Errores  Error 1040: Too many connections"
(it is printed only once)

Error in mysql php my admin: 
 "mysqli_real_connect(): (HY000/1040): Too many connections"
 "La conexión para controluser, como está definida en su configuración, fracasó."
(translated: "the connection for controluser, as it is defined in ti's configuration , failed.")
 "mysqli_real_connect(): (08004/1040): Too many connections"

Jonathan Hall · Accepted Answer · 2020-06-29 09:18:58Z

2

Every time you call Query(), you're creating a new database handle. Each active handle needs a unique database connection. Since you're not calling Close, that handle, and thus the connection, remains open until the program exits.

Solve your problem by calling rows.Close() after you're done with each query:

    for j := 0; j < 8000; j++ {
        rows, err := db.Query("QUERY...")
        if err != nil {
            logger.Println("Error " + err.Error())
            return
        }
        // Your main logic here
        rows.Close()
    }

This Close() call is often called in a defer statement, but this precludes the use of a for loop (since a defer only executes when then function returns), so you may want to move your main logic to a new function:

    for j := 0; j < 8000; j++ {
        doStuff()
    }

// later

func doStuff() {
    rows, err := db.Query("QUERY...")
    if err != nil {
        logger.Println("Error " + err.Error())
        return
    }
    defer rows.Close()
    // Your main logic here
}

edited Jun 29, 2020 at 9:18

answered Jun 28, 2020 at 19:23

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Camisamus Over a year ago

I Used the first recomendation (withdout defer) but now the script takes arround 2 minutes to execute, is it normal? can I make it faster?

Jonathan Hall Over a year ago

That's probably normal. Doing 8000 queries in sequence isn't immediate.

Collectives™ on Stack Overflow

MySQL returning "Too many connections" error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related