I'm trying to find a MySql driver that i can use with Go which supports issuing multiple SQL statements in one call. For example i might wish to create a database using the following SQL:
DROP SCHEMA IF EXISTS foo;
CREATE SCHEMA IF NOT EXISTS foo;
In languages such as PHP you can just place both SQL statements in one string and execute it in one go, like this:
$db = new PDO(...);
$db->query("DROP SCHEMA IF EXISTS foo; CREATE SCHEMA IF NOT EXISTS foo;");
The reason i need this is because i have SQL dumps (from mysqldump) i'd like to apply programmatically to various databases.
I'm looking for the same functionality in Go but it seems all the different drivers don't support it, which, frankly, is shocking to me.
Go-MySQL-Driver
https://github.com/go-sql-driver/mysql
This seems to be the most used driver for Go.
package main
import "database/sql"
import "log"
import _ "github.com/go-sql-driver/mysql"
func main() {
db, err := sql.Open("mysql", "user:password@(127.0.0.1:3306)/")
if err != nil {
log.Println(err)
}
sql := "DROP SCHEMA IF EXISTS foo; CREATE SCHEMA IF NOT EXISTS foo;"
_, err = db.Exec(sql)
if err != nil {
log.Println(err)
}
db.Close()
}
output:
2015/02/16 18:58:08 Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE SCHEMA IF NOT EXISTS foo' at line 1
MyMySQL
https://github.com/ziutek/mymysql
This is another popular driver.
package main
import "database/sql"
import "log"
import _ "github.com/ziutek/mymysql/godrv"
func main() {
db, err := sql.Open("mymysql", "database/user/password")
if err != nil {
log.Println(err)
}
sql := "DROP SCHEMA IF EXISTS foo; CREATE SCHEMA IF NOT EXISTS foo;"
_, err = db.Exec(sql)
if err != nil {
log.Println(err)
}
sql = "USE DATABASE foo;"
_, err = db.Exec(sql) // <-- error
if err != nil {
log.Println(err)
}
db.Close()
}
output:
2015/02/16 18:58:08 packet sequence error
Does anyone know of any MySql driver compatible with Go that can handle multiple statements in one string like these?
https://github.com/ziutek/mymysql
Can do it. Although you have to use its interface vs the go defined one. The go official interface doesn't handle it, or multiple return values.
package main
import (
"flag"
"fmt"
"github.com/ziutek/mymysql/autorc"
"github.com/ziutek/mymysql/mysql"
_ "github.com/ziutek/mymysql/thrsafe"
)
type ScanFun func(int, []mysql.Row, mysql.Result) error
func RunSQL(hostport, user, pass, db, cmd string, scan ScanFun) error {
conn := autorc.New("tcp", "", hostport, user, pass, db)
err := conn.Reconnect()
if err != nil {
return err
}
res, err := conn.Raw.Start(cmd)
if err != nil {
return err
}
rows, err := res.GetRows()
if err != nil {
return err
}
RScount := 0
scanErr := error(nil)
for {
if scanErr == nil {
func() {
defer func() {
if x := recover(); x != nil {
scanErr = fmt.Errorf("%v", x)
}
}()
scanErr = scan(RScount, rows, res)
}()
}
if res.MoreResults() {
res, err = res.NextResult()
if err != nil {
return err
}
rows, err = res.GetRows()
if err != nil {
return err
}
} else {
break
}
RScount++
}
return scanErr
}
func main() {
host := flag.String("host", "localhost:3306", "define the host where the db is")
user := flag.String("user", "root", "define the user to connect as")
pass := flag.String("pass", "", "define the pass to use")
db := flag.String("db", "information_schema", "what db to default to")
sql := flag.String("sql", "select count(*) from columns; select * from columns limit 1;", "Query to run")
flag.Parse()
scan := func(rcount int, rows []mysql.Row, res mysql.Result) error {
if res.StatusOnly() {
return nil
}
for idx, row := range rows {
fmt.Print(rcount, "-", idx, ") ")
for i, _ := range row {
fmt.Print(row.Str(i))
fmt.Print(" ")
}
fmt.Println("")
}
return nil
}
fmt.Println("Host - ", *host)
fmt.Println("Db - ", *db)
fmt.Println("User - ", *user)
if err := RunSQL(*host, *user, *pass, *db, *sql, scan); err != nil {
fmt.Println(err)
}
}
Bluntly put, do not issue multiple statements in a single call. It is a security issue. If a hacker can get in a little bit, he can take over your machine. (Read about "SQL Injection".)
Stored Routines give you a safe way to combine statements (with some restrictions).
I would recommend simply making 2 calls. Why not? It makes the code easier to grok and improves the error handling.
The other option, if you have a large SQL file from the dump is to shell out and execute the whole thing in one go.
the github.com/go-sql-driver/mysql can be configured to accept multiple statements with the multiStatements=true connection parameter.
The documentation clearly states why you should be careful doing it. See https://github.com/go-sql-driver/mysql
Adding an example for the answer from @ithkuil regarding multiStatements for the go-sql-driver package for reference. (I didn't have enough rep to add as a comment).
The parameter for multiStatements is added onto the dataSourceName string for your sql.Open call. e.g.
db, err := sql.Open("mysql", "user:pass@tcp(localhost:3306)/dbname?multiStatements=true")
It's recommended that you not use such a db handler for processing user input, but it works great for processing known sql files.