How does SET FOREIGN_KEY_CHECKS=0 work with parallel query execution?

[prasad83]

Issue description

I'm running create-table commands in parallel threads using db.MustExec before spawning the threads I do db.MustExec ("SET FOREIGN_KEY_CHECKS=0") but looks like this fails when db.SetMaxOpenConns is > 1

Example code

package main

import (
    "strconv"
    "sync"

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

func execSqls(wg *sync.WaitGroup, db *sqlx.DB, sqls []string) {
    defer wg.Done()
    for _, sql := range sqls {
        db.MustExec(sql)
    }
}

func main() {
    DSN := "user:pass@tcp(127.0.0.1:3306)/dbname"

    db := sqlx.MustConnect("mysql", DSN)
    defer db.Close()

    db.MustExec("SET FOREIGN_KEY_CHECKS=0")

    wg := new(sync.WaitGroup)
    wg.Add(2)

    for i := 0; i < 10; i++ {
        depTable := "A_DEP_" + strconv.Itoa(i)
        go execSqls(wg, db,
            []string{
                "DROP TABLE IF EXISTS " + depTable,
                "CREATE TABLE " + depTable + "  (a_id integer not null, foreign key (a_id) references A(id))",
            },
        )
    }

        // Intentional - this statement is triggered at end to test how FOREIGN_KEY_CHECKS=0 holds up
    go execSqls(wg, db,
        []string{
            "DROP TABLE IF EXISTS A",
            "CREATE TABLE A (id integer not null, primary key(id))",
        },
    )

    wg.Wait()
}

How can I ensure (FOREIGN_KEY_CHECKS) is holding until all waitGroup function completes?

[arp242]

This sets the parameter on the open connection; if you have more than one connection then you need to set it for all connections.

The go-sqlite driver has a ConnectHook to run commands whenever a new connection is opened; I don't know if the MySQL driver has something similar but it probably does.