'no database selected' when password is empty

[BSick7]

Issue description

The recommended format for DSN for root user and dbname db is root@tcp(localhost:3306)/dbname.
When specifying this and with allow empty password enabled for mysql, every query complains with no database selected.

After enabling root password for mysql, I reconfigured the DSN to root:password@tcp(localhost:3306)/dbname.
Queries worked as expected.

I confirmed that it is possible to connect to mysql without a password using https://github.com/ziutek/mymysql. As that repo is not maintained and does not align with golang db drivers, it would be better for this driver to support this scenario.

While production systems have a password, this cost me a lot of time when configuring a local mysql server via docker.

Example code

The following example requires the user to create a database dbname and a table organizations before executing.

package main

import (
	"database/sql"
	"fmt"

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

func main() {
	db, err := sql.Open("mysql", "root@/dbname")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	if err := db.Ping(); err != nil {
		panic(err)
	}

	rows, err := db.Query("SELECT * FROM organizations")
	if err != nil {
		panic(err)
	}
	fmt.Println(rows, err)
	defer rows.Close()
}

Configuration

OS: Windows
go-sql-driver/[email protected] (d523deb)

$ go version
go version go1.10.1 windows/amd64

$ docker run -it --rm mysql --version
/usr/sbin/mysqld  Ver 8.0.12 for Linux on x86_64 (MySQL Community Server - GPL)

[jonhester]

I started having this same issue as soon as I upgraded to MySQL 8.

[methane]

duplicate of #825. Use master branch for MySQL 8.0.

[philippgille]

@methane: I'm not sure if this is fixed. #825 is closed, which you mentioned as duplicate, I'm using the latest commit on the master branch (currently 6be42e0ff99645d7d9626d779001a46e39c5f280), as you suggested, but I'm seeing the same error with similar code. Note: I'm pretty new to Go and very new to this MySQL driver, so maybe I'm just doing something wrong :) .

1. Start MySQL: docker run -it --rm -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=true mysql
   • (latest tag currently equals 8.0.13)
2. Execute the following code:

package main

import (
	"database/sql"
	"fmt"
	"strconv"
	"time"

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

func main() {
	// Open connection
	db, err := sql.Open("mysql", "root@/")
	if err != nil {
		panic(err)
	}
	// Test connection
	err = db.Ping()
	if err != nil {
		panic(err)
	}
	// Create DB
	_, err = db.Exec("CREATE DATABASE IF NOT EXISTS mydb")
	if err != nil {
		panic(err)
	}
	// Use DB
	_, err = db.Exec("USE mydb")
	if err != nil {
		panic(err)
	}
	// Create table
	_, err = db.Exec("CREATE TABLE IF NOT EXISTS mytable (k VARCHAR(255) PRIMARY KEY, v BLOB NOT NULL)")
	if err != nil {
		panic(err)
	}

	// Insert data concurrently
	goroutineCount := 10
	for i := 0; i < goroutineCount; i++ {
		go func(i int) {
			_, err = db.Exec("INSERT INTO mytable (k, v) VALUES (?, ?) ON DUPLICATE KEY UPDATE v = VALUES(v)", strconv.Itoa(i), []byte("some value"))
			if err != nil {
				fmt.Println(err)
			} else {
				fmt.Println("inserted value for key " + strconv.Itoa(i))
			}
		}(i)
	}
	// Wait a bit for all goroutines to finish.
	time.Sleep(2 * time.Second)
}

I get the following output:

inserted value for key 1
Error 1046: No database selected
Error 1046: No database selected
Error 1046: No database selected
Error 1046: No database selected
Error 1046: No database selected
Error 1046: No database selected
Error 1046: No database selected
Error 1046: No database selected
Error 1046: No database selected

Sometimes one of the other goroutines is successful, while the rest isn't, so this isn't deterministic. But it's always one that works and the rest that fails.

[julienschmidt]

@philippgille you need to use a transaction or get a single connection from the pool. Otherwise it is not safe to execute this code, as there is no guarantee that these statements are executed sequentially on the same connection.

[methane]

db.Exec("USE mydb")

This just *executes" this query on one connection in the connection pool
You need to recreate connection pool (aka DB), with default db in DSL.

[philippgille]

I didn't fully understand @julienschmidt's comment at first, but your explanation helped make it clear @methane. Thanks a lot to both of you!

[gonejack]

This is fixed in 1.5.0 in my test, people meeting this problem may take a try with 1.5.0.