Mysql: Cannot switch database with `USE` command.

[ancarda]

If I connect to a MySQL server without using the database in the DSN, I am forced to specify the database on every call (SELECT ... FROM database.table). The program below produces this error: Error 1046: No database selected. I clearly selected one using USE. Why do I have to specify the database in the DSN?

What should I do if I need to use multiple databases or need to switch to a different one?

I am using the latest build of both the MySQL driver and Go itself (go1.1.2 darwin/amd64).

package main

import (
    "database/sql"
    "fmt"
    "log"
)

import _ "github.com/go-sql-driver/mysql"

func main() {
    dsn := "root:@/"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        fmt.Println("Failed to prepare connection to database. DSN:", dsn)
        log.Fatal("Error:", err.Error())
    }

    err = db.Ping()
    if err != nil {
        fmt.Println("Failed to establish connection to database. DSN:", dsn)
        log.Fatal("Error:", err.Error())
    }

    _, err = db.Query("USE test")
    if err != nil {
        fmt.Println("Failed to change database.")
        log.Fatal("Error:", err.Error())
    }

    _, err = db.Query("SHOW TABLES")
    if err != nil {
        fmt.Println("Failed to execute query.")
        log.Fatal("Error:", err.Error())
    }
}

[julienschmidt]

sql.DB is not a single connection but a connection pool.
If you execute "USE dbname" on db, you are not guaranteed to get the same connection for the next query on db unless you do it in a transaction.
I'd recommend you to read https://github.com/VividCortex/go-database-sql-tutorial which explains the concepts and some not so intuitive details of the database/sql package.

[julienschmidt]

I just noticed another thing: In the example code you are using db.Query wrong. A Query returns Rows which must be either closed or completely read. Otherwise the connection is not freed again and can't be reused.
db.Exec does what you are trying to do. The linked introduction also explains this :wink:

[ancarda]

Ah my mistake! I'm quite new to go. Thank you for clearing that up.

[xaprb]

I added a note about this to go-database-sql-tutorial:
https://github.com/VividCortex/go-database-sql-tutorial/commit/5be3a979f6de45198ade222495df4228e1f0e310

[lagerstrom]

Sorry for waking up such an old thread. If I would like to use the USE command before doing a SELECT on a different database. How should I accomplish that?

Thanks

[homanchou]

It doesn't seem like using USE is the preferred way of switching databases. Can you just prefix the database name in the SELECT * from database_name.table_name statement?

[ancarda]

@lagerstrom, @homanchou:

_Can you just prefix the database name in the SELECT * from database_name.table_name statement?_

Yes, you have to do that.

_It doesn't seem like using USE is the preferred way of switching databases._

It's not that it's not preferred, it's that it's not _possible_ due to the way database connections are managed in Go. Earlier in this thread, this comment explains why quite well:

sql.DB is not a single connection but a connection pool.
If you execute "USE dbname" on db, you are not guaranteed to get the same connection for the next query on db unless you do it in a transaction.

Please avoid USE. It's not going to work properly.

[homanchou]

So it sounds like it maybe can be used as long as it's within a transaction and thus the same connection? If all queries went through a gatekeeper like this:

tx := db.MustBegin() // start transaction
tx.MustExec("use " + userDB) // switch to tenant db
tx.MustExec("insert into ....") // do some work
tx.MustExec("use `no-op-db`") // switch away from tenant db (there is no unuse, so I just use a dummy)
tx.Commit() // end transaction

Wouldn't this allow using the same connection pool and still being able to switch logical databases for each tenant query? I did some local testing with this idea, and with three go routines simultaneously inserting records to three different logical databases while re-using a single connection db.SetMaxOpenConns(1), all inserts landed in the right tenant databases.

[common-nighthawk]

If a Transaction is heavier than you need, it seems like using a Connection will also work to allow manual USE statements for database switching.

conn, err := db.Conn(ctx)
conn.Exec("use database1")
conn.Exec("select id from users where user_id=1;")
conn.Close()

It's not clear to me why the docs recommend using a transaction for "guarantees that they’ll be executed on the same connection": http://go-database-sql.org/modifying.html