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

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