Golang MySQL Database Not Selected

Question

I'm using github.com/go-sql-driver/mysql package to connect to MySQL. It works well except when I select a database (USE), I can't run queries against it.

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())
    }
}

The program produces this output:

Error 1046: No database selected

Jan Laussmann · Accepted Answer · 2013-11-12 11:45:17Z

10

Specify the database directly in the DSN (Data Source Name) part of the sql.Open function:

dsn := "user:password@/dbname"
db, err := sql.Open("mysql", dsn)

answered Nov 12, 2013 at 11:45

Jan Laussmann

1,5203 gold badges17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Over a year ago

While this does work, I have to ask why I need to do this? What if I need to change the database later? Do I just need to close and open a new connection?

Scott Daniel Over a year ago

I have the same problem. It appears that the USE statement works somewhat randomly. I read somewhere that the SQL driver caches multiple connections. Is is possible that some of them don't register the USE statement?

seven Over a year ago

although this is accepted answer, it does not answer question of why. check answer below stackoverflow.com/a/73113710/528020

kangsouth · Accepted Answer · 2019-06-25 13:58:30Z

3

That's because db maintains a connection pool that has several connections to mysql database."USE test" just let one connection use schema test. When you do database query later,the driver will select one idle connection,if the connection that use test schema is selected,it will be normal,but if another connection is chosen, it does not use test,so it will report an error:no database selected.

If you add a clause:

db.SetMaxOpenConns(1)

the db will maintain only one connection,it will not have an error.And of course it's impossible in high concurrency scene.

If you specify the database name in sql.open() function,all the connection will use this data base which can avoid this problem.

answered Jun 25, 2019 at 13:58

kangsouth

731 silver badge8 bronze badges

Comments

Zender Fufikoff · Accepted Answer · 2019-02-15 17:57:09Z

1

In your case you need to use transactions:

tx, _ := db.Begin()
tx.Query("USE test")
tx.Query("SHOW TABLES")
tx.Commit()

For SELECT/UPDATE/INSERT/etc need to specify DB name in the query.

answered Feb 15, 2019 at 17:57

Zender Fufikoff

1071 silver badge6 bronze badges

1 Comment

seven Over a year ago

Transactions are used to execute and rollback insert/update/delete, not use db / select from db. Please check my answer stackoverflow.com/a/73113710/528020. Somebody mentioned it here in this thread and from then on it is somehow accepted solution.. it shouldn't be - github.com/go-sql-driver/mysql/issues/173

seven · Accepted Answer · 2022-07-25 18:21:08Z

1

As other answers mentioned, sql.DB is not a single connection but a connection pool. When you execute use database, imagine you executed your query on just one connection in the pool. Next query will get another connection from the pool which has no databases selected.

I would strongly advise against using transactions for this (as several places suggest).

I would suggest to use context:

    ctx := context.Background()
    conn, err := db.Conn(ctx)
    conn.ExecContext(ctx, "use mydb")
    defer conn.Close()

    var found int
    err = conn.QueryRowContext(ctx, "SELECT count(*) as found FROM mytable").Scan(&found)
    if err != nil {
        panic(err)
    }

answered Jul 25, 2022 at 18:21

seven

2,65729 silver badges28 bronze badges

Collectives™ on Stack Overflow

Golang MySQL Database Not Selected

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related