I Could Not Run a Sample Code to Use MySQL with Go on Docker (Error 1064)
Tags: go, mysql, docker, webdev
Published: 2022-07-02 02:16:52

I executed the insert statement using Go on Docker, and I got the following error.

```shell
panic: 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 'generated(owner, repository) VALUES( ?, ? )' at line 1
```

I made the source code by reference to [README.md of `github.com/go-sql-driver/mysql` package GitHub](https://github.com/go-sql-driver/mysql/wiki/Examples#a-word-on-sqlopen), but I got the error.

I want to share the solution to resolve this problem.
I do not have any idea the reason why the source code I wrote first did not run, so if you know about the reason, I would appreciate it if you could tell me about that.

## The Source Code I Wrote for The First Time
I use [`github.com/go-sql-driver/mysql`](https://github.com/go-sql-driver/mysql) package to use MySQL. 
The references are [the official README.md](https://github.com/go-sql-driver/mysql), [a sample code](https://github.com/go-sql-driver/mysql/wiki/Examples#a-word-on-sqlopen), [the article Masao wrote (in Japanese)](https://mebee.info/2022/06/23/post-53678/), and the [MariaDB document](https://mariadb.com/ja/resources/blog/golang-with-mariadb/).

**mDB.go**
```go
package mDB

import (
	"database/sql"
	"log"

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

type MDB struct {
	Host     string
	User     string
	Password string
	Database string
}

func (m MDB) Test() {
	db, er := sql.Open("mysql", m.User+":"+m.Password+"@"+"tcp("+m.Host+":3306)"+"/"+m.Database)
	if er != nil {
		log.Fatal(er)
	}
	defer db.Close()

	er = db.Ping()
	if er != nil {
		panic(er.Error())
	}

	stmtIns, err := db.Prepare("insert into generated(owner, repository) values( ?, ? )")
	if err != nil {
		panic(err.Error()) // proper error handling instead of panic in your app
	}
	defer stmtIns.Close() // Close the statement when we leave main() / the program terminates

	stmtIns.Exec("Hacknock", "testdayo")
}
```
I got the following response when `mDB.Test()` runs.

```shell
panic: 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 'generated(owner, repository) values( ?, ? )' at line 1 [recovered]
panic: 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 'generated(owner, repository) values( ?, ? )' at line 1
```

This source code is much the same as the sample code...

## The Source Code That Runs Fine
I got the expected result if I modify to the insert statement like the one below, as the result of various trials.

```sql
insert into [database name].generated(owner, repository) values( ?, ? )
```

I specified the database name on sql.Open... Why do I need to specify the database name on the insert statement...?
The following code runs fine.

```go
package mDB

import (
	"database/sql"
	"log"

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

type MDB struct {
	Host     string
	User     string
	Password string
	Database string
}

func (m MDB) Test() {
	db, er := sql.Open("mysql", m.User+":"+m.Password+"@"+"tcp("+m.Host+":3306)"+"/"+m.Database)
	if er != nil {
		log.Fatal(er)
	}
	defer db.Close()

	er = db.Ping()
	if er != nil {
		panic(er.Error())
	}

	stmtIns, err := db.Prepare("insert into " + m.Database + ".generated(owner, repository) values( ?, ? )")
	if err != nil {
		panic(err.Error()) // proper error handling instead of panic in your app
	}
	defer stmtIns.Close() // Close the statement when we leave main() / the program terminates

	stmtIns.Exec("Hacknock", "testdayo")
}
```