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.

https://github.com/DATA-DOG/go-sqlmock/pull/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. https://github.com/DATA-DOG/go-sqlmock/pull/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