CODEWITHSUNDEEP
mysqlgo
Michael
Michael

Reputation: 7123

Golang, mysql: Error 1040: Too many connections

I'm using the github.com/go-sql-driver/mysql driver for go.

I open a database:

db, err := sql.Open("mysql", str)

Then I have two functions that are called 200 times each with following mysql code:

rows, err := db.Query("select name from beehives")
if err != nil {
    panic(err)
}       
defer rows.Close()

The second:

    err = db.QueryRow("select id, secret, shortname from beehives where shortname = ?", beehive).Scan(&id, &secre
    switch {
    case err == sql.ErrNoRows:
        err = errors.New("Beehive '"+beehive+"' not found.")
    case err != nil:
        panic("loginBeehive: "+ err.Error())
    default:
        // ... do the work

The first one is panicing.

How can there be more than one connection when I open the database only once and how do I close them?

Upvotes: 36

Views: 32087

Answers (5)

Watcharapong Tassoongnern
Watcharapong Tassoongnern

Reputation: 41

hi can you try close connection after used

db, err := sql.Open("mysql", str)
defer db.Close() // close after end scope

Upvotes: 4

Thushara Buddhika
Thushara Buddhika

Reputation: 1820

My program is connecting always to database. (Realtime Face Recognition for Attendance)

Therefore opening and closing database connection is worthless.

Therefore it's keep opens the database connection only initializing the program.

func GetAllFaces() interface{} {
    OpenDatabaseConnection() ... 
}

But access database later, increased the no of connection and crashed the program. But closing the rows object kept no of active connection at minimum. (for me 1)

 func SaveAttendance(faceId int, date time.Time) error {
    sqlQuery := fmt.Sprintf("SELECT ... "))

    rows, err := DB.Query(sqlQuery) ...
    err = rows.Close()
    return err
}

Upvotes: 1

tmichel
tmichel

Reputation: 984

The *DB object that you get back from sql.Open doesn't corresponds to a single connection. It is better thought as a handle for the database: it manages a connection pool for you.

You can control the number of open connections with `(*DB).SetMaxOpenConns and its pair for idling connections.

So basically what happens here is that db.Query and db.QueryRow tries to acquire a connection for themselves and the DB handle doesn't put any restrictions on the number of simultaneous connections so your code panics when it opens more than what mysql can handle.

Upvotes: 8

nussjustin
nussjustin

Reputation: 2100

sql.Open doesn't really open a connection to your database.

A sql.DB maintains a pool of connections to your database. Each time you query your database your program will try to get a connection from this pool or create a new one otherwise. These connections are than put back into the pool once you close them.

This is what rows.Close() does. Your db.QueryRow("...") does the same thing internally when you call Scan(...).

The basic problem is that you're creating too many queries, of which each one needs a connection, but you are not closing your connections fast enough. This way your program has to create a new connection for each query.

You can limit the maximum number of connections your program uses by calling SetMaxOpenConns on your sql.DB.

See http://go-database-sql.org/surprises.html for more information.

Upvotes: 43

Uvelichitel
Uvelichitel

Reputation: 8490

Try to make prepared statements db.Prepare(query string) (*Stmt, error) and than stmt.Query or stmt.Exec and than stmt.Close to reuse connections.

Upvotes: 4

Related Questions