Mysql: 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.

[kidoman]

Hey, I am also seeing the message "packets.go:31: EOF" Could I help in any way?

[julienschmidt]

The EOF error can have a lot of reasons, it just means that the connection is broken.
Are you sure it is related to the autoreconnecting?

Also, in the current master branch the EOF error should appear less often.
We will release it as version 1.1 very soon.

[mk6502]

Seen this on Go1.1.2 on Linux x64 (Ubuntu 12.04).

[kwe]

I'm also seeing this on Ubuntu 12.10 go 1.1.2

[julienschmidt]

Are you using v1.0 (the current go get version) or the current git master branch?
v1.1 is just around the corner. I'm optimistic to see improvements with this issue.

[kwe]

mine is the 'go get' version. I'll check out the git master - thanks :-)

[julienschmidt]

Mailed https://codereview.appspot.com/14920046

[mcandre]

Same behavior for my Go application. In my case, the error trace is coming from line 36, but otherwise looks to be a similar issue with idle connections not correctly reestablishing after a couple hours when it comes time to process more queries.

[methane]

@mcandre This issue was not about EOF error. This issue was about database/sql didn't retry correctly. I think it is already fixed in database/sql side. If no, you should report it to golang/go.