Got busy buffer error, while do a query while another rows is open

[fenisteel]

Issue description

I open a transaction and do a SELECT statement, then start to process it, but inside this loop I do an another SELECT, the second query fails with busy buffer error.
Outside of transaction it works fine.

Expected result from both woTx() and wTx() functions:

Test1: 1 T1 a
  Test2: 1 T2 a
  Test2: 2 T2 b
  Test2: 3 T2 c
Test1: 2 T1 b
  Test2: 1 T2 a
  Test2: 2 T2 b
  Test2: 3 T2 c
Test1: 3 T1 c
  Test2: 1 T2 a
  Test2: 2 T2 b
  Test2: 3 T2 c

Without transaction (woTx) it works fine.
With transaction (wTx) we got the busy buffer error.

Example code

CREATE TABLE TEST1(
    `Id` INT AUTO_INCREMENT,
    `Name` VARCHAR(50) NOT NULL DEFAULT "",
    PRIMARY KEY(`Id`));

CREATE TABLE TEST2(
    `Id` INT AUTO_INCREMENT,
    `Name` VARCHAR(50) NOT NULL DEFAULT "",
    PRIMARY KEY(`Id`));

INSERT INTO TEST1(`Name`) VALUES("T1 a"),("T1 b"),("T1 c");

INSERT INTO TEST2(`Name`) VALUES("T2 a"),("T2 b"),("T2 c");

package main

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

	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func woTx() {
	rows, err := db.Query("SELECT `Id`, `Name` FROM TEST1")
	if err != nil {
		log.Panic(err)
	}
	var (
		id   int
		name string
	)
	for rows.Next() {
		rows.Scan(&id, &name)
		fmt.Println("Test1:", id, name)
		rows2, err := db.Query("SELECT `Id`, `Name` FROM TEST2")
		if err != nil {
			log.Fatal(err)
		}
		for rows2.Next() {
			rows2.Scan(&id, &name)
			fmt.Println("  Test2:", id, name)
		}
		rows2.Close()
	}
	rows.Close()
}

func wTx() {
	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}

	rows, err := tx.Query("SELECT `Id`, `Name` FROM TEST1")
	if err != nil {
		log.Panic(err)
	}
	var (
		id   int
		name string
	)
	for rows.Next() {
		rows.Scan(&id, &name)
		fmt.Println("Test1:", id, name)
		rows2, err := tx.Query("SELECT `Id`, `Name` FROM TEST2")
		if err != nil {
			log.Fatal(err)
		}
		for rows2.Next() {
			rows2.Scan(&id, &name)
			fmt.Println("  Test2:", id, name)
		}
		rows2.Close()
	}
	rows.Close()

	tx.Commit()
}

func main() {
	var err error

	db, err = sql.Open("mysql", "users:password@(localhost:3306)/Test")
	if err != nil {
		log.Panic(err)
	}

	err = db.Ping()
	if err != nil {
		log.Panic(err)
	}

	fmt.Println()
	fmt.Println("Test without TX")
	woTx()

	fmt.Println()
	fmt.Println("Test with TX")
	wTx()

	db.Close()
}

Error log

[mysql] 2022/10/04 15:46:09 packets.go:446: busy buffer
2022/10/04 15:46:09 driver: bad connection

Configuration

Driver version (or git SHA): 1.6.0

Go version: 1.19.1

Server version: 10.7.6-MariaDB-1:10.7.6+maria~deb10

Server OS: Developer machine: Windows 10 x64, DB server: Debian 10

[methane]

woTx() uses two connections. So you can query before previous query is not finished.

wTx() can not use two connections. You need to fetch all data and call rows.Close() before executing next query.

Close this issue because this issue is duplicate of #526.