Calling sql.db's Ping functiion returns error

[laynax]

No description provided.

[l3pp4rd]

it cannot return an error.
can you provide your test case implementation or minimal test in order to reproduce it? Currently Ping is not mockable, but it does not return error, regarding older go versions prior 1.8. it should also simply open a connection when a ping is called without any error.

[mhemmings]

I have found it to return an error on Ping() too, but this only happens when SetMaxIdleConns(0) (which is perfectly valid way to remove pooling).

The error goes away when max idle connections is >0

[l3pp4rd]

yes that makes sense @mhemmings thanks for pointing it

[Radranic]

I've found another case where calling Ping causes errors.

I used go-sqlmock by switching out the driver name in the sql.Open call. This results in getting the error "expected a connection to be available, but it is not" as the mock open call only returns connections that were created with New.

Given that Open in most real drivers returns a new connection based off the currently provided connection string would it not make sense to also emulate this behavior?

[BonnieMilian]

Reproducible code:

Function to be tested:

type responseHealth struct {
	Healthy bool `json:"healthy"`
	SQL     bool `json:"sqlDB"`
}

func (s *Server) handleHealth() http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		log.Info("handle health")

		dbHealthy := true

		db, errConnection := sql.Open("mysql", mysqlConnection)
		if errConnection != nil {
			dbHealthy = false
		}
		defer db.Close()

		errPing := db.Ping()
		if errPing != nil {
			dbHealthy = false
		}

		log.Info("Health Checked")
		res := responseHealth{
			Healthy: dbHealthy,
			SQL:     dbHealthy,
		}
		
                w.Header().Set("Content-Type", "application/json")
                response, errorToJSON := json.Marshal(res)
                if errorToJSON != nil {
                	w.WriteHeader(http.StatusInternalServerError)
                	io.WriteString(w, `{"success": false, error: "internal error"}`)
			return
		}

		w.WriteHeader(http.StatusOK)
		w.Write(response)
	}
}

Test:

func TestHandleHealth(t *testing.T) {
	db, mock, errOpenDBTests := sqlmock.New()
	if errOpenDBTests != nil {
		t.Fatalf("An error '%s' was not expected when opening a stub database connection for tests", errOpenDBTests)
	}
	defer db.Close()

	mock.ExpectPing()

	api := &server.Server{}
	api.InitializeServer()

	request, err := http.NewRequest("GET", "/v2/health", nil)
	if err != nil {
		t.Fatalf("error request %v", err)
	}

	w := httptest.NewRecorder()
	api.Router.ServeHTTP(w, request)

	if status := w.Code; status != http.StatusOK {
		t.Errorf(fmt.Sprintf("Health wrong Status: expected '%v' received '%v'", http.StatusOK, status))
	}

	expected := `{"healthy":true,"sqlDB":true}`
	if body := w.Body.String(); body != expected {
		t.Errorf("Health wrong Body: expected '%v' received '%v'", expected, body)
	}
}

But when the Ping is made the error is "connection refused"

[l3pp4rd]

Your code does not use an sqlmock db in your http server function, instead it opens mysql. If you would not just defer close sqlmock db, but instead check if all expectations were met like all examples in API docs show. It would give you an error that ping was never called

learn the library first, start from reading api docs and examples.

[rmulley]

@l3pp4rd Do we feel comfortable closing this issue?