Too many connections

[pmoosman]

I'm seeing the error message "Too man connections" when calling db.sqlDB.Query(). Before I dig further into this issue, I'm wondering if there is a known issue.

Thoughts?

013/07/23 03:05:35 yy.UpdateThingy() db.go:264 [Failed to insert record into XXXX: Error 1040: Too many connections]

I'm using go version 1.1.1:
go version go1.1.1 linux/386

I'm using the mysql driver version:
v1.0.1

MySql Version:
Server version: 5.5.22-0ubuntu1 (Ubuntu)

Here is a sample of one of my queries:
rows, err =
db.sqlDB.Query("insert into XXXX (yyyy) value (?)",
connect.ZZZZ)
if err != nil {
_ = rows.Close();
return result, err
}
_ = rows.Close();

I added "rows.Close()" hoping it would fix the issue, but no dice.

FYI, some of my queries use "rows.Scan" and some don't.

[xaprb]

Please take a look here: https://github.com/VividCortex/go-database-sql-tutorial

Don't use .Query, use .Exec.

[julienschmidt]

Option 1: Too high concurrency

You have too many concurrent database accesses. In this case you must manually limit the number of concurrent connections until the database/sql package provides a blocking-mechanism in a future version for that. But in many cases caching / asynchronous updates would be the better alternative.

Option 2: Leaking connections

Most probably your program is just leaking database connections. This happens if you don't close the rows returned by db.Query or forgot to end transactions (in database/sql's tx api).

My general advice is to use

• db.Exec if you don't expect a returned row (in other words you don't Scan anything). The connection is immediately free after the execution.
• db.QueryRow if you expect one row. If you use the chained syntax, you can't leak connections
• db.Query if you expect multiple rows. It is very important that you "free" the connection by reading either all returned rows (looping rows.Next) or calling rows.Close. To be sure, deferring a rows.Close call is a good idea. Don't forget about the error-cases etc.

[pmoosman]

I changed "db.Query()" to "db.Prepare() and db.Exec()" and that fixed my issue. Thanks for the fast, great answers.

[liutaihua]

I got this too.
and, I use "db.Prepare() and db.Exec()",

var db *sql.DB

func getdb() *sql.DB {
db, err = sql.Open("mysql", connArgs)
db.SetMaxIdleConns(100)
return db
}

func foo(db *sql.DB) {
do someting.....
smint, err := db.Prepare(....)
defer smint.Close()
smint.Exec(....)
}

func main() {
go func(){
db = get_db()
defer db.Close()
foo(db)
}()
}

[pantsing]

try go 1.3
----- 原始邮件 -----
发件人：liutaihua notifications@github.com
收件人：go-sql-driver/mysql mysql@noreply.github.com
主题：Re: [mysql] Too many connections (#111)
日期：2014年07月16日 15点43分

[arnehormann]

and... why do you start a goroutine in main?
If your code really looks like this, main may (and probably will) exit before the goroutine is run and your program will do nothing at all.
Just remove the go func(){ and }(). You'll be better off.

[liutaihua]

sorry for my description, actually, code:

func socke_server () {
for {
//accept_something_from_socket as receive
go func(some_receive) {
db = get_db()
defer db.Close()
foo(db)
}
}
}

func main() {
socke_server()
}

But, now, I use Unix domain protocol('user:pwd@unix(/tmp/mysql.sock)/'), it fixed.

thanks reply

[arnehormann]

You shouldn't do that, either - it may work, but it's slow.

Put the accept-loop in main.
Convert the goroutine to a top-level function.
Put your get_db code in the top of your main and defer db.Closer there, too (and only there).
Do not open and close the db like this.

Try the following code:

func handleConnection(db *sql.DB, args...interface{}) {
    // do someting.....
    // db.Exec("STATEMENT", args...)
}

func main() {
    db, err := sql.Open("mysql", connArgs)
    if err != nil {
        panic(err)
    }
    db.SetMaxIdleConns(100)
    defer db.Close()
    for {
        // accept connections and pass relevant stuff to handleConnection
        go handleConnection(db, nil)
    }
}

[liutaihua]

Thanks, I got it.