autoreconnection fails with prepared statements

[mattetti]

From I could see, this driver doesn't support autoreconnection. Did I miss something?
I know that mymysql supports this feature, but I'd prefer to stick to your driver if possible.

[julienschmidt]

This is part of the database/sql package connection pooling. Hence auto-reconnecting is available out of the box in this driver.

[mattetti]

I must be missing something because I get a "driver: bad connection" error when the mysql connection has been severed and a new SQL query is sent. The driver doesn't auto-reconnect.
Am I doing something wrong or would you be interested in a simple reduction?

[julienschmidt]

Yes, this seems to be a bug or a problem with the query.
I think the default behavior of the databse/sql package is to send the query 10 times before giving up. This could be checked via MySQL server's query log or network sniffing (e.g. Wireshark supports recording MySQL traffic).

Can you share the query you are sending?

[julienschmidt]

And please check the according stderr log file. There should be some error logs by the driver.

[mattetti]

Here is my test code:

package main

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

func main(){
  db, err := sql.Open("mysql", "root:@/go-driver-test?charset=utf8")
  if err != nil { log.Fatal(err) }
  defer db.Close()

  stmtOut, err := db.Prepare("SELECT id FROM users LIMIT 1")
    if err != nil { log.Fatal(err) }
  defer stmtOut.Close()
  var id int
  err = stmtOut.QueryRow().Scan(&id)
  if err != nil { log.Fatal(err) }

  for i := 0; i < 500000; i++ {
    err = stmtOut.QueryRow().Scan(&id)
    if err != nil { log.Fatal("reconnection failed ", err) }
  }

  // While the the queries are looping, kill the DB connection
}

Here is what I'm seeing in the terminal:

packets.go:31: EOF
2013/06/15 14:27:41 reconnection failed driver: bad connection
exit status 1

[julienschmidt]

It seems like the server (or the OS) kills the connection:
packets.go:31: EOF

[mattetti]

Yes, I'm killing the connection manually to test the autoreconnection.
Note that the following code doesn't have the same problem:

func main(){
  db, err := sql.Open("mysql", "root:@/go-driver-test?charset=utf8")
  if err != nil { log.Fatal(err) }
  defer db.Close()

  stmtOut, err := db.Prepare("SELECT id FROM users LIMIT 1")
    if err != nil { log.Fatal(err) }
  defer stmtOut.Close()
  var id int
  err = stmtOut.QueryRow().Scan(&id)
  if err != nil { log.Fatal(err) }

  for i := 0; i < 500000; i++ {
    err = db.QueryRow("SELECT id FROM users LIMIT 1").Scan(&id)
    if err != nil { log.Fatal("reconnection failed ", err) }
  }
}

In this case, the EOF is logged, but a retry happens right away.

[julienschmidt]

Sorry, I missed your comment.
The latter prepares a new statement on each request. This could indicate a bug in the database/sql package with re-preparing statements when necessary since prepared statements are bound to the connection.
I'll try to debug this tomorrow.

[mattetti]

thanks

[mattetti]

FYI, I filed a Golang bug too: https://code.google.com/p/go/issues/detail?id=5718

[mattetti]

The bug was accepted and put on the "later" queue, is they something that could be done at the driver level to work around this issue? Or maybe do you have a suggestion on how to work around this issue in my own code?

Thanks

[julienschmidt]

I'm still tapping in the dark where exactly in the code this bug is caused. I'll let you know as soon as I have a driver or client side workaround.