Using with goroutines

[ceyhunkerti]

Hi, having trouble with using go routines.

sqlmock version 1.5.0

below is the code when functions run serial no error occurs but panics when used with goroutines.

🖊️ update:
second script works, which does not use prepared statements

package main

import (
	"database/sql"
	"sync"

	"github.com/DATA-DOG/go-sqlmock"
)

func main() {
	db, mock, _ := sqlmock.New()

	mock.MatchExpectationsInOrder(false)
	data := []interface{}{
		1,
		"John Doe",
		2,
		"Jane Doe",
	}
	rows := sqlmock.NewRows([]string{"id", "name"})
	rows.AddRow(data[0], data[1])
	rows.AddRow(data[2], data[3])

	mock.ExpectExec("DROP TABLE IF EXISTS test_out").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("TRUNCATE TABLE").WillReturnResult(sqlmock.NewResult(0, 0))

	mock.ExpectExec("CREATE TABLE IF NOT EXISTS test_out").WillReturnResult(sqlmock.NewResult(0, 0))

	mock.ExpectQuery("select").WillReturnRows(rows).WithArgs()

	mock.ExpectPrepare("INSERT INTO").
		ExpectExec().
		WithArgs(
			data[0],
			data[1],
			data[2],
			data[3],
		).
		WillReturnResult(sqlmock.NewResult(0, 2))

	funcs := []func(db *sql.DB) error{
		runSelect,
		runCreate,
		runInsert,
		runDrop,
		runTruncate,
	} 
	// // this works
	// for _, f := range funcs {
	// 	if err := f(db); err != nil {
	// 		panic(err)
	// 	}
	// }

	//! this does not work
	wg := &sync.WaitGroup{}
	for _, f := range funcs {
		wg.Add(1)
		go func(f func(db *sql.DB) error, wg *sync.WaitGroup) {
			defer wg.Done()
			if err := f(db); err != nil {
				panic(err)
			}
		}(f, wg)
	}
	wg.Wait()
}

func runSelect(db *sql.DB) error {
	_, err := db.Query("select * from test_out")
	return err
}

func runCreate(db *sql.DB) error {
	_, err := db.Exec("CREATE TABLE IF NOT EXISTS test_out (a varchar(255)")
	return err
}

func runInsert(db *sql.DB) error {
	stmt, err := db.Prepare("INSERT INTO test_out (id,full_name) VALUES (?,?),(?,?)")
	if err != nil {
		return err
	}
	_, err = stmt.Exec(1, "John Doe", 2, "Jane Doe")
	return err
}

func runDrop(db *sql.DB) error {
	_, err := db.Exec("DROP TABLE IF EXISTS test_out")
	return err
}

func runTruncate(db *sql.DB) error {
	_, err := db.Exec("TRUNCATE TABLE test_out")
	return err
}

package main

import (
	"database/sql"
	"sync"

	"github.com/DATA-DOG/go-sqlmock"
)

func main() {
	db, mock, _ := sqlmock.New()

	mock.MatchExpectationsInOrder(false)
	data := []interface{}{
		1,
		"John Doe",
		2,
		"Jane Doe",
	}
	rows := sqlmock.NewRows([]string{"id", "name"})
	rows.AddRow(data[0], data[1])
	rows.AddRow(data[2], data[3])

	mock.ExpectExec("DROP TABLE IF EXISTS test_out").WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec("TRUNCATE TABLE").WillReturnResult(sqlmock.NewResult(0, 0))

	mock.ExpectExec("CREATE TABLE IF NOT EXISTS test_out").WillReturnResult(sqlmock.NewResult(0, 0))

	mock.ExpectQuery("select").WillReturnRows(rows).WithArgs()

	// mock.ExpectPrepare("INSERT INTO").
	// 	ExpectExec().
	// 	WithArgs(
	// 		data[0],
	// 		data[1],
	// 		data[2],
	// 		data[3],
	// 	).
	// 	WillReturnResult(sqlmock.NewResult(0, 2))

	mock.
		ExpectExec("INSERT INTO").
		WithArgs(
			data[0],
			data[1],
			data[2],
			data[3],
		).
		WillReturnResult(sqlmock.NewResult(0, 2))

	funcs := []func(db *sql.DB) error{
		runSelect,
		runCreate,
		runInsert,
		runDrop,
		runTruncate,
	}

	// println("Testing")
	// for _, f := range funcs {
	// 	if err := f(db); err != nil {
	// 		panic(err)
	// 	}
	// }
	println("Testing with go routines")
	wg := &sync.WaitGroup{}
	for _, f := range funcs {
		wg.Add(1)
		go func(f func(db *sql.DB) error, wg *sync.WaitGroup) {
			defer wg.Done()
			if err := f(db); err != nil {
				panic(err)
			}
		}(f, wg)
	}
	wg.Wait()
}

func runSelect(db *sql.DB) error {
	_, err := db.Query("select * from test_out")
	return err
}

func runCreate(db *sql.DB) error {
	_, err := db.Exec("CREATE TABLE IF NOT EXISTS test_out (a varchar(255)")
	return err
}

func runInsert(db *sql.DB) error {
	_, err := db.Exec("INSERT INTO test_out (id,full_name) VALUES (?,?),(?,?)",
		1, "John Doe", 2, "Jane Doe",
	)

	// stmt, err := db.Prepare("INSERT INTO test_out (id,full_name) VALUES (?,?),(?,?)")
	// if err != nil {
	// 	return err
	// }
	// _, err = stmt.Exec(1, "John Doe", 2, "Jane Doe")
	return err
}

func runDrop(db *sql.DB) error {
	_, err := db.Exec("DROP TABLE IF EXISTS test_out")
	return err
}

func runTruncate(db *sql.DB) error {
	_, err := db.Exec("TRUNCATE TABLE test_out")
	return err
}

[JessieAMorris]

I've been able to reproduce the issue (it is sometimes inconsistent but happens often enough). I've added a test case on a branch and am going to dig into the issue further over the next few days.

#288

[JessieAMorris]

The issue seems to be related to how Golang handles prepared statements. From the docs:

When the Stmt needs to execute on a new underlying connection, it will prepare itself on the new connection automatically.

At this point I'm thinking about adding a flag similar to mock.MatchExpectationsInOrder(bool) but that would allow prepared statements to be prepared multiple times.

[siddharth178]

Tried to fix it, but with my limited knowledge of codebase. Please give it a look.
#309

[ninadingole]

Shouldn't Prepare be called once when the code is initialising and then reused multiple times?

[ninadingole]

There are three changes pending on this issue could someone take a look at which one to go with, seems both the PRs are doing same thing:
#309
#321

and @JessieAMorris has written really good tests on #288