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