mysql error on scanning binary() field into []byte

[jaanreinok]

mysql error on scanning binary() field into []byte. Happened when updating older project from 3.8 to 4.6. Worked on 3.8. I was unable to find anything on this in issues. Didn't find anything on supported types except mysql.JSON

Code to reproduce:

package main

import (
	"fmt"
	"log"
	"os"

	"github.com/upper/db/v4"
	"github.com/upper/db/v4/adapter/mysql"
)

type User struct {
	ID       int    `json:"id" db:"id"`
	Email    string `json:"email" db:"email"`
	Password []byte `json:"password" db:"password"` //binary(60)
}

func main() {
	if len(os.Args) < 2 {
		fmt.Println("missing arguments - dbURL as as argument")
		os.Exit(1)
	}

	dbConnectionUrl := os.Args[1]

	session, err := OpenMySQL(dbConnectionUrl, 10, 1, true)
	if err != nil {
		panic(err)
	}
	defer session.Close()

	userRepo, err := NewUserRepo(session)
	if err != nil {
		log.Fatalf("failed to create user repo: %q", err)
	}

	user1 := &User{
		Password: []byte("123456"),
		Email:    "[email protected]",
	}

	err = userRepo.Insert(user1)
	if err != nil {
		log.Fatalf("failed to insert user: %v with error %v", user1, err)
	}

	users, err := userRepo.GetUsers()
	if err != nil {
		log.Fatalf("res.All(): %q\n", err)
	}

	for i, u := range users {
		fmt.Printf("user%d name %d, email %s, psw %s \n", i+1, u.ID, u.Email, u.Password)
	}
}

type userRepo struct {
	db db.Session
}

func NewUserRepo(db db.Session) (*userRepo, error) {
	_, err := db.SQL().Exec(`DROP TABLE IF EXISTS user;`)
	if err != nil {
		return nil, err
	}

	sqlStmt := `CREATE TABLE user (
		id int NOT NULL AUTO_INCREMENT,
		password binary(60) DEFAULT NULL,
		email varchar(128) NOT NULL,
		PRIMARY KEY (id))`
	_, err = db.SQL().Exec(sqlStmt)

	return &userRepo{db: db}, err
}

func (r *userRepo) Insert(user *User) error {
	_, err := r.db.Collection("user").Insert(user)

	return err
}

func (r *userRepo) GetUsers() ([]User, error) {
	res := r.db.Collection("user").Find()
	defer res.Close()

	var users []User

	err := res.All(&users)
	if err != nil {
		return nil, err
	}

	return users, nil
}

func OpenMySQL(url string, maxConnections, maxIdleConnections int, logging bool) (db.Session, error) {
	var err error

	connectionURL, err := mysql.ParseURL(url)
	if err != nil {
		return nil, fmt.Errorf("mysql url: %w", err)
	}

	session, err := mysql.Open(connectionURL)
	if err != nil {
		return nil, fmt.Errorf("mysql open: %v", err)
	}

	session.SetMaxOpenConns(maxConnections)
	session.SetMaxIdleConns(maxIdleConnections)

	return session, nil
}

[chrisbishop-looka]

I am getting a similar issue. The error I am getting is that the []byte is getting converted to mysql.JSON.

Query:          INSERT INTO `table` (`token`) VALUES (?)
Arguments:      []interface {}{(*mysql.JSON)(0xc00063ce90)}

The calling code is InsertReturning. When I check the type before passing it in it is []unit8.

[chrisbishop-looka]

I had also switch from v3 to v4. I had to setup a Value Converter.

func (u DBBytes) Value() (driver.Value, error) {
	return u[:], nil
}
func (u *DBBytes) Scan(src any) error {
	b, ok := src.([]byte)
	if !ok || len(b) != 16 {
		return fmt.Errorf("DBBytes.Scan: expected 16‑byte slice, got %T(%d)", src, len(b))
	}
	copy(u[:], b)
	return nil
}