Auto reconnect results in unknown prepared statement handler error

[Djuke]

I have a test case where my go application can't execution any prepared statement. A prepared statement will result in an 'unknown prepared statement handler' error, if the application lost its database connection because of e.a. mysql shutdown or crash and mysql is up and running again before a prepared statement is being executed.

My understanding is that the problem is caused by calling the Connect method in the function below. Because when a new connection is established the prepared statements aren't resubmitted. Calling method reconnectIfNetErr would resubmit prepared statements, but return because a connection is already established by the Connect method.

autorecon.go

...
func (c *Conn) connectIfNotConnected() (err error) {
        if c.Raw.IsConnected() {
                return
        }   
        err = c.Raw.Connect()
        nn := 0
        c.reconnectIfNetErr(&nn, &err)
        return
}
...

Changing the Query kill to connection close in the unit test, will result in the same are as described in my test case above.

autorecon_test.go

...
func TestAutoConnectReconnect(t *testing.T) {
    ...
    // Kill the connection
    //c.Query("kill %d", c.Raw.ThreadId())
    // Disconnect
    c.Raw.Close()

    // Bind insert parameters
    ins.Raw.Bind(1, "jeden")
    // Insert into table
    // Will result in an error because the prepared statements are unknown
    _, _, err = ins.Exec()
    checkErr(t, err, nil)
    ...

At the moment I don't have a suggestion to solve this problem.

[Djuke]

My suggestion to solve this problem would be to change c.Raw.Connect() to c.Raw.Reconnect().

...
func (c *Conn) connectIfNotConnected() (err error) {
        if c.Raw.IsConnected() {
                return
        }   
        err = c.Raw.Reconnect()
        nn := 0
        c.reconnectIfNetErr(&nn, &err)
        return
}
...

[ziutek]

Use c.Raw.Close() in unit test isn't right way to reproduce this problem. Can you provide some simple test code with time.Sleep in place where I should restart MySQL server to see this issue?

[Djuke]

Here is the simple test which has two time.Sleep calls. The first one gives you time to stop MySQL and the second to start it again.

package main

import (
    "fmt"
    "log"
    "github.com/ziutek/mymysql/autorc"
    _ "github.com/ziutek/mymysql/native"
    "time"
)

var (
    conn   = []string{"tcp", "", "127.0.0.1:3306"}
    user   = "testuser"
    passwd = "TestPasswd9"
    dbname = "test"
    sleep  = 15 * time.Second
)

func checkErr(err error, onlyError bool) {
    if err != nil {
        log.Printf("Error: %v\n", err)
    } else if onlyError == false {
        log.Println("No error")
    }
}

func main() {
    c := autorc.New(conn[0], conn[1], conn[2], user, passwd)
    c.Debug = true
    c.MaxRetries = 2

    // Register initialisation commands
    c.Raw.Register("set names utf8")

    // my is in unconnected state
    checkErr(c.Use(dbname), true)

    // Drop table
    _, _, err := c.Query("drop table if exists Z")
    checkErr(err, true)

    // Create table
    _, _, err = c.Query("create table Z (id int primary key, name varchar(20))")
    checkErr(err, true)

    // Prepare insert statement
    ins, err := c.Prepare("insert Z values (?,?)")
    checkErr(err, true)

    // Bind insert parameters
    fmt.Println("first insert should execute without errors")
    ins.Raw.Bind(1, "insert should work")
    // Insert into table
    _, _, err = ins.Exec()
    checkErr(err, false)

    fmt.Printf("\nstop mysql within %v\n\n", sleep)
    time.Sleep(sleep)

    // Bind insert parameters
    fmt.Println("second insert should return with (re)connect errors\n")
    ins.Raw.Bind(2, "should not be inserted")
    // Insert into table
    _, _, err = ins.Exec()
    checkErr(err, false)

    fmt.Printf("\nstart mysql within %v\n\n", sleep)
    time.Sleep(sleep)

    // Bind insert parameters
    fmt.Println("third insert should return the unknown prepared statement handler error\n")
    ins.Raw.Bind(3, "should not be inserted")
    // Insert into table
    _, _, err = ins.Exec()
    checkErr(err, false)

    // Disconnect
    c.Raw.Close()
}

[ziutek]

Ok. I found a time to check this issue.

The problem is because c.MaxRetries limit was exceeded and c.Insert returns an error. If any function in mymysql (autorc to) returns an error it means that connection is in invalid state and can't be used any more. You can't use a connection in invalid state (after error returned) because you can't assume that problem that causes an error is recoverable. If you need to wait more for MySQL server to up you need to set bigger value in c.MaxRetries.

[Djuke]

In some cases it is not possible to know how long you have to wait for the mysql service to be up and running again. So in that case I would want to set the value of c.MaxRetries to infinity. Maybe my issue is related to https://github.com/go-sql-driver/mysql/issues/98

[ziutek]

There is always some unacceptable time for wait to response. If you accept wait eg. 1 hour thats ok, if you accept 1 day still ok, but if server is down for a week this is an error and you want know that it occurs. Even if you accept when server is down for a week, you probably don't accept one month... Sou you need to set MaxRetries for value that is unacceptable for your application.