Error 1129: Host 'xxx' blocked because of many connection errors

[iliacimpoes]

Issue description

After multiple successive deadline exceeded errors, on connection Open or Ping Error 1129 is returned and can no longer connect to the DB unless FLUSH HOSTS is called.

Looks like #941 is causing the problem. I was able to reproduce it on 17ef3dd and 89ec2a9. But not on df597a2

Example code

package main

import (
	"context"
	"database/sql"
	"log"
	"time"

	"github.com/go-sql-driver/mysql"
)

func main() {
	for {
		conn, err := mysql.MySQLDriver{}.OpenConnector("user:password@tcp(addr:port)/db")
		if err != nil {
			log.Fatal("OpenConnector", err)
		}
		db := sql.OpenDB(conn)
		err = db.Ping()
		if err != nil {
			log.Fatal("Ping", err)
		}

		for j := 0; j < 200; j++ {
			query(db)
		}
		log.Println("Close", db.Close())
	}
}

func query(d *sql.DB) {
	ctx, cancel := context.WithTimeout(context.Background(), 100*time.Millisecond)
	defer cancel()
	rows, err := d.QueryContext(ctx, "SELECT * FROM my_table")
	if err != nil {
		log.Println("QueryContext", err)
		return
	}
	defer rows.Close()
}

Logs

....
2020/01/21 11:40:55 QueryContext context deadline exceeded
2020/01/21 11:40:55 QueryContext context deadline exceeded
2020/01/21 11:40:55 QueryContext context deadline exceeded
2020/01/21 11:40:55 QueryContext context deadline exceeded
2020/01/21 11:40:55 QueryContext context deadline exceeded
2020/01/21 11:40:55 Close <nil>
2020/01/21 11:40:55 PingError 1129: Host '10.9.10.189' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Process finished with exit code 1

Configuration

Driver version (or git SHA): 1.5.0 17ef3dd

Go version: go version go1.13.4 darwin/amd64

Server version: 10.1.34-MariaDB

[iliacimpoes]

looks like the error is returned on mc.readHandshakePacket -> mc.readPacket():

mysql/connector.go

Line 81 in 17ef3dd

authData, plugin, err := mc.readHandshakePacket()

Most probably caused by passing context into watchCancel:

mysql/connector.go

Lines 68 to 71 in 17ef3dd

	if err := mc.watchCancel(ctx); err != nil {
	mc.cleanup()
	return nil, err
	}

[shogo82148]

Can I have all of your code including import section?
It seems that you use an ORM mapper or a query builder, instead of the database/sql package.

[methane]

This is not surprising to me.
In your environment, 100ms is not enough for creating new connection.
And MySQL blocks clients after several times of failed attempts of creating connection.

Before the connector interface is implemented, you can not cancel the creating connection with contexts. db.query() may take much longer than 100ms, but this problem was not happened.

Now you can cancel it, so you need to chose the timeout carefully.

[iliacimpoes]

@shogo82148 Yes, sorry I was using upper.io in my example, I updated it to use database/sql

[iliacimpoes]

@methane I set it to 100ms just to be able to reproduce the issue we had.
In reality we have a bigger timeout that does not causes context deadline exceeded errors. But we had an issue where our application was under high load which caused a lot of context deadline exceeded errors. We recovered pretty fast, but anyway our host was blocked because of many connection errors.

Maybe in such cases context deadline exceeded should be handled gracefully and not cause connection errors.

[methane]

In reality we have a bigger

I think it is not big enough. You should chose the number which works well even under high load.

But we had an issue where our application was under high load which caused a lot of context deadline exceeded errors. We recovered pretty fast, but anyway our host was blocked because of many connection errors.

If timeout long enough and MaxOpenConns is low enough, you shouldn't see a lot of errors.

• Increase timeout
• Reduce MaxOpenConns
• And increase max_connect_errors. (doc)