Cannot cannect to a mysql:8.0.33 server with `caching_sha2_password` auth mechanism

[erfanium]

Issue description

I can't simply connect to my mysql server because caching_sha2_password not supported

Example code

func main() {
	conn, err := client.Connect(.......)
	if err != nil {
		panic(err)
	}
	defer conn.Close()

	r, _ := conn.Execute(`select 1;`)
	println(r.AffectedRows)

}

Error log

panic: ERROR 1045 (28000): Access denied for user 'base-user'@'%' (using password: YES)

Configuration

Driver version (or git SHA): v1.8.0

Go version: 1.22.2

Server version: MySQL 8.0.33

same exact code with node.js and mysql2 package works:

const { createConnection } = require("mysql2/promise");

async function main() {
  const connection = await createConnection({ ...... });

  const [rows] = await connection.query("select 1");
  console.log(rows);
}

main();

[erfanium]

I can confirm driver version 1.7.0 works, but 1.8.0 doesn't work

[methane]

Can you provide a complete step to reproduce with Docker?

[nussjustin]

I can not reproduce this error.

Code (requires Docker to be running, container will be created/removed automatically):

package main

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

	"github.com/go-sql-driver/mysql"
	"github.com/ory/dockertest/v3"
)

func main() {
	_ = mysql.SetLogger(&mysql.NopLogger{})

	log.Println("creating pool")

	pool, err := dockertest.NewPool("")
	if err != nil {
		log.Fatalf("could not construct pool: %s", err)
	}

	log.Println("checking pool connection")

	if err := pool.Client.Ping(); err != nil {
		log.Fatalf("could not connect to Docker: %s", err)
	}

	log.Println("creating mysql container")

	resource, err := pool.Run("mysql", "8.0.33", []string{"MYSQL_DATABASE=test", "MYSQL_ROOT_PASSWORD=root"})
	if err != nil {
		log.Fatalf("could not start resource: %s", err)
	}
	defer func() {
		if err := pool.Purge(resource); err != nil {
			log.Fatalf("could not purge resource: %s", err)
		}
	}()

	log.Println("waiting until mysql is ready")

	var db *sql.DB

	if err := pool.Retry(func() error {
		dsn := fmt.Sprintf("root:root@(localhost:%s)/test", resource.GetPort("3306/tcp"))

		var err error
		if db, err = sql.Open("mysql", dsn); err != nil {
			return err
		}

		return db.Ping()
	}); err != nil {
		log.Fatalf("could not connect to database: %s", err)
	}
	defer func() {
		_ = db.Close()
	}()

	log.Println("creating test user")

	if _, err := db.Exec(`CREATE USER 'test'@'%' IDENTIFIED WITH caching_sha2_password BY 'test'`); err != nil {
		log.Fatalf("failed to create user: %s", err)
	}

	if _, err := db.Exec(`GRANT ALL PRIVILEGES ON test.* TO 'test'@'%'`); err != nil {
		log.Fatalf("failed to grant privileges: %s", err)
	}

	log.Println("connecting as test user")

	userDB, err := sql.Open("mysql", fmt.Sprintf("test:test@(localhost:%s)/test", resource.GetPort("3306/tcp")))
	if err != nil {
		log.Fatalf("failed to connect to DB with test user: %s", err)
	}
	defer func() {
		_ = userDB.Close()
	}()

	log.Println("checking connection for test user")

	if err := userDB.Ping(); err != nil {
		log.Fatalf("failed to ping DB with test user: %s", err)
	}

	log.Println("running dummy query")

	var value int

	if err := userDB.QueryRow(`SELECT 1`).Scan(&value); err != nil {
		log.Fatalf("failed to execute query with test user: %s", err)
	}
}

Result:

2024/08/23 20:25:47 creating pool
2024/08/23 20:25:47 checking pool connection
2024/08/23 20:25:47 creating mysql container
2024/08/23 20:25:48 waiting until mysql is ready
2024/08/23 20:26:05 creating test user
2024/08/23 20:26:05 connecting as test user
2024/08/23 20:26:05 checking connection for test user
2024/08/23 20:26:05 running dummy query