DEV Community

Akira Kashihara
Akira Kashihara

Posted on

I Could Not Run a Sample Code to Use MySQL with Go on Docker (Error 1064)

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

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
Enter fullscreen mode Exit fullscreen mode

I made the source code by reference to README.md of github.com/go-sql-driver/mysql package GitHub, 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 package to use MySQL.
The references are the official README.md, a sample code, the article Masao wrote (in Japanese), and the MariaDB document.

mDB.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")
}
Enter fullscreen mode Exit fullscreen mode

I got the following response when mDB.Test() runs.

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
Enter fullscreen mode Exit fullscreen mode

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.

insert into [database name].generated(owner, repository) values( ?, ? )
Enter fullscreen mode Exit fullscreen mode

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.

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")
}
Enter fullscreen mode Exit fullscreen mode

Discussion (0)