mysql: packets.go:36: unexpected EOF (Invalid Connection)

Issue description

Got a long time running query, and go routine exit, but executed show processlist; in mysql presented that the query was still running.

I have seted db.SetMaxIdleConns(0) and db.SetConnMaxLifetime(1000000000000), but that not works.

Example code

func CreateUserIdsMap(beginning int64, end int64) {
	trx, err := db.Begin()
	if err != nil {
		panic(err.Error())
	}
	defer trx.Commit()

	_, err = trx.Exec("DROP TABLE IF EXISTS user_ids_map;")
	if err != nil {
		panic(err.Error())
	}

	_, err = trx.Exec("CREATE TABLE user_ids_map (row_id INT NOT NULL PRIMARY KEY, user_id INT NOT NULL);")
	if err != nil {
		panic(err.Error())
	}

	_, err = trx.Exec("SET @row_id = 0;")
	if err != nil {
		panic(err.Error())
	}

	_, err = trx.Exec("INSERT INTO user_ids_map SELECT @row_id := @row_id + 1, user_id FROM users WHERE create_time BETWEEN ? AND ?;", beginning, end)
	if err != nil {
		fmt.Printf("db: %+v\n", db.Stats())
		panic(err.Error())
	}
}

func main() {
        ...
	CreateUserIdsMap(0, 1539747119) // line:147
        ...
}

Error log

[mysql] 2018/10/17 10:35:19 packets.go:36: unexpected EOF
panic: invalid connection

goroutine 1 [running]:
git.***/***/internal/pkg/dbutil.CreateUserIdsMap(0x0, 0x5b4a8e80)
        /Users/gentcys/Codes/gocode/src/git.***/***/internal/pkg/dbutil/dbutil.go:96 +0x38b
main.main()
        /Users/gentcys/Codes/gocode/src/git.***/***/cmd/***/main.go:147 +0xc7e
exit status 2

Configuration

*Driver version (or git SHA): 1.4

*Go version: go1.11.1 darwin/amd64

*Server version: MySQL 5.7

*Server OS: host is macOS and MySQL is on Docker container

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 24 (9 by maintainers)

Most upvoted comments

Having this same issue. Tried all of the above, no luck 😦

mattbonnell on Mar 12, 2021

I just wanted to add to this convo since I spent a few days debugging this same thing and in case someone else runs into my issue. My app was throwing an error at the same line of code as this issue title, to be exact the error was:

[mysql] 2018/12/27 21:04:13 packets.go:36: unexpected EOF
driver: bad connection

The circumstances were that my golang app started a docker container from image, mysql:latest and then attempted to connect to it through database/sql and go-sql-driver/mysql package or gorm a popular golang orm. A snippet of the code I used to start the container was:

args := []string{
  "run", "--name", "foobar",
  "--env", "MYSQL_ALLOW_EMPTY_PASSWORD=yes",
  "--env", "MYSQL_DATABASE=foobar_db",
  "--env", "MYSQL_USER=foobar_user",
  "--env", "MYSQL_PASSWORD=foobar_pass",
  "-p", "54016:3306", "mysql:latest",
  "mysqld", "--default-authentication-plugin=mysql_native_password",
}
cmd := exec.Command("docker", args...)
err := cmd.Start()
// more code here to loop 2 actions
// (1) establish connection to container with `sql` package or `gorm`
// (2) pinging the db

Then, the app would ping the db until it could connect to mysql. No matter how long I pinged the DB or how much I adjusted the mysql timeouts or other settings, this error persisted. I could not for the life of me get this to function. The odd things were that when starting the container with exec.Command() as mentioned above, I could successfully login to mysql from my terminal in bash. Also, if I started a container with the same exact above commands from bash, my app using the same code to ping the db was able to connect to ping successfully and execute mysql queries.

My solution to resolve this was to use docker API to work with docker containers in my app instead of exec.Command(). Instead of rebuilding this, I just used the ory implementation which is working great for me now: https://github.com/ory/dockertest . I used their function pool.RunWithOptions() instead of Run() so I could control the docker command arguments a bit more. Ory uses docker api from here: https://github.com/docker/go-connections and it does have some big package dependencies, and I have not researched why yet but I think it’s related to docker (https://github.com/fsouza/go-dockerclient/issues/599 ), but I was not worried about that considering how good the solution is for me.

p.s. I just discovered ory in this process, and have no affiliation, so this is not an ory promotion.

johncoleman83 on Dec 29, 2018

Ok, i receive this error but why library don’t reconnecting broken connection in background ? All my requests failed with error, i think it’s wrong behavior !?

That’s the question I answered hundreds times…

SQL is not idempotant, and we can not know sent query is executed or not.

https://golang.org/pkg/database/sql/driver/ says:

ErrBadConn should be returned by a driver to signal to the sql package that a driver.Conn is in a bad state (such as the server having earlier closed the connection) and the sql package should retry on a new connection.

To prevent duplicate operations, ErrBadConn should NOT be returned if there’s a possibility that the database server might have performed the operation. Even if the server sends back an error, you shouldn’t return ErrBadConn.

So, if the connection is close by 5 min, you must use db.SetConnMaxLifetime() with duration shorter than 5 min. (e.g. time.Minute * 3).

methane on Nov 30, 2018