Mysql: [wiki] examples: Check database connection

[vadimtk]

In your examples you have

db, err := sql.Open("mysql", "user:password@/database")
if err != nil {
panic(err.Error())
}

but this check is not enough.

err is nil even connection is not established.
If we check sql.Open source, it
returns err !=nil only if there is issue with driver.
It does not check if connection is established.

For proper check one should use:

db, err := sql.Open("mysql", ConnectionString)

err = db.Ping()

if err != nil {
    panic(err.Error())
}

[DisposaBoy]

Don't ping nor check the db connection before executing queries, it's racey.

[xaprb]

The key is to understand that a DB isn't a connection. When you sql.Open()
you get a DB. It manages a pool of connections in the background, and
doesn't open any until you need them. Doing a Ping() is a fine way to do an
initial check if you want, but it's not all that Go-ish. I wish they hadn't
added Ping() in go1.1.

[fiorix]

but ping is common practice and it's there in go1.1 because a lot of people wanted it

On 2013-05-22, at 10:43 AM, Baron Schwartz [email protected] wrote:

The key is to understand that a DB isn't a connection. When you sql.Open()
you get a DB. It manages a pool of connections in the background, and
doesn't open any until you need them. Doing a Ping() is a fine way to do an
initial check if you want, but it's not all that Go-ish. I wish they hadn't
added Ping() in go1.1.
—
Reply to this email directly or view it on GitHub.

[vadimtk]

So what is the proper way to re-establish connection if it was disconnected from MySQL, say because MySQL was down ?

From example:

db, err := sql.Open("mysql", "user:password@/dbname?charset=utf8")
if err != nil {
    panic(err.Error())
}
defer db.Close()

// Execute the query
rows, err := db.Query("SELECT * FROM table")
if err != nil {
    panic(err.Error())
}

If SELECT * FROM table fails because connection is not established,
I do not want to panic, but rather keep trying until MySQL is ready.

What is the proper logic to do that?

[xaprb]

I don't think the examples are very good -- using panic() and so on :-) I
think this is a general question about database/sql and Go in general. If
you are the vadimtk that I think you are, maybe we can have a hangout and I
can share what I think I know (but not today because I'm in a car right
now)

[fiorix]

from what you guys are saying we're not supposed to use ping before the connection is established; however, think of this scenario:

mysql is running
our server is running and mysqlclient is connected, executes 1 query and call 1 ping
mysql shuts down (or becomes unreachable)
next query fails, ping fails, etc
mysql is back
next ping shouldn't fail

which is essentially the same as connecting and sending a ping
in other words, why wouldn't ping itself initialize a new connection?

On 2013-05-22, at 11:07 AM, Baron Schwartz [email protected] wrote:

I don't think the examples are very good -- using panic() and so on :-) I
think this is a general question about database/sql and Go in general. If
you are the vadimtk that I think you are, maybe we can have a hangout and I
can share what I think I know (but not today because I'm in a car right
now)
—
Reply to this email directly or view it on GitHub.

[vadimtk]

@xaprb Baron, Yes, I am that vadimtk from https://github.com/percona/ . Let's connect.

To make this comment not fully personal, there is excerpt from "database/sql" doc
"Open may just validate its arguments without creating a connection to the database. To verify that the data source name is valid, call Ping."

If this is a not Go-ish way to do it, I would like to understand what is.

[julienschmidt]

First of all, you shouldn't use panic(err.Error()) in your real-world applications like in the examples. It's just there to show that you are supposed to handle errs, I just didn't want to introduce more extensive error handling like you should have in your applications. Maybe I should add a comment on that.

db.Ping() was introduced to give you an option to check the DSN since sql.Open(..) doesn't really open a connection: https://code.google.com/p/go/issues/detail?id=4804
Moreover it can be used to check if the server is still alive. So essentially @vadimtk is right. In many cases you "open" the DB right after you application starts and want to make sure you connection data is right. That's where db.Ping() is useful for. But you should be aware, that you can get connection errors at any time. It is really just to validate your DSN data.

But @vadimtk example is not correct, you overwrite the first err. It should look like this:

db, err := sql.Open("mysql", "user:password@/dbname")
if err != nil {
    panic(err.Error()) // proper error handling instead of panic
}
defer db.Close()

// Open doesn't open a connection. Validate DSN data:
err = db.Ping()
if err != nil {
    panic(err.Error()) // proper error handling instead of panic
}

// Execute the query or w/e
[...]

I'll update the Wiki now

[joshuaellul]

I am new to golang and the google app sdk. I have tried the code above however I receive the following error: "db.Ping undefined (type *sql.DB has no field or method Ping)"

I only downloaded the sdk a few days ago, however perhaps the database/sql library may not be up to date. Can someone tell me from where I can download the update, and how to update my sdk accordingly?

Thanks and best regards,

Josh

[julienschmidt]

db.Ping is only available in Go 1.1. App Engine is currently still running Go 1.0.3, but a beta is available: https://groups.google.com/forum/?fromgroups#!topic/google-appengine-go/V1HMwZHbjZQ
It will be auto-updated to Go 1.1 in a few days.

You can use a simple Query like DO 1 in Go 1.0.3 instead:

err, _ = db.Exec("DO 1")
if err != nil {
...

[joshuaellul]

Thanks!

Josh

[julienschmidt]

Wiki updated.
I added a new section instead of bloating every single example.