Is it possible to execute set variable sequentially？

[Fanduzi]

Issue description

I encountered a special scenario when using Aurora mysql. To enable the RC isolation level for the slave, two variable need to be modified in order

SET aurora_read_replica_read_committed=1,transaction_isolation='READ-COMMITTED';

That is, first modify aurora_read_replica_read_committed and then modify transaction_isolation

Only by modifying variables in this order will the isolation level become RC

But according to my test, variable settings are random out of order

The following figure is a screenshot of the audit log, you can see that the set is out of order

Does it support setting variables in order?

Example code

package main

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

	"github.com/doug-martin/goqu/v9"
	_ "github.com/doug-martin/goqu/v9/dialect/mysql"
	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB
var dialect = goqu.Dialect("mysql")

func initDB() (err error) {
	db, err = sql.Open("mysql", "user:pass@tcp(ip:3306)/db?aurora_read_replica_read_committed=1&transaction_isolation=%27READ-COMMITTED%27")
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetConnMaxLifetime(60 * time.Second)
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return
}

func QuerySessionVariable(varName string) (varValue string, err error) {
	table := "performance_schema.session_variables"
	sqlStmt, _, _ := dialect.From(table).
		Select("VARIABLE_VALUE").
		Where(
			goqu.Ex{
				"VARIABLE_NAME": varName,
			},
		).ToSQL()
	row := db.QueryRow(sqlStmt)
	err = row.Scan(&varValue)
	return
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Println(err)
		return
	}
	var wg sync.WaitGroup

	for i := 0; i < 5; i++ {
		wg.Add(1)
		go func(d int) {
			defer wg.Done()
			val, err := QuerySessionVariable("aurora_read_replica_read_committed")
			if err != nil {
				fmt.Println(err)
				return
			}
			fmt.Printf("%d aurora_read_replica_read_committed: %s\n", d, val)
			val, err = QuerySessionVariable("transaction_isolation")
			if err != nil {
				fmt.Println(err)
				return
			}
			fmt.Printf("%d transaction_isolation: %s\n", d, val)
		}(i)
	}

	wg.Wait()
	fmt.Println("ALL DONE")
}

Error log

If you have an error log, please paste it here.

Configuration

Driver version (or git SHA): github.com/go-sql-driver/mysql v1.6.0

Go version: go version go1.20.3 darwin/arm64

Server version: E.g. Aurora MySQL 3.03.1

Server OS: Centos7

[methane]

Confirmed. I will consider about is it possible to fix it without backward incompatibility.