How to connect to mysql with go?

Question

I have been trying to connect mysql with go, but i can seem to succeed. I'm just trying to get a value from table and set it to variable and print it. What i'm missing maybe just something obvious

this is from the database called bankdata

mysql> select * from accounts ;
+----+----------+-----------------+----------+---------+
| id | username | email           | facebook | twitter |
+----+----------+-----------------+----------+---------+
|  1 | user1    | [email protected] | userfb1  | NULL    |
|  2 | user2    | [email protected] | NULL     | NULL    |
|  3 | user3    | [email protected] | NULL     | NULL    |
+----+----------+-----------------+----------+---------+

there's two drivers that i used, and i can seem to understand what wrong with both of them

using go-sql-driver

package main

import (
    "database/sql"
    _ "github.com/Go-SQL-Driver/MySQL"
    "log"
)

const (
    DB_HOST = "tcp(127.0.0.1:3306)"
    DB_NAME = "bankdata"
    DB_USER = /*"root"*/ "bankadmin"
    DB_PASS = /*""*/ "1234"
)

func main() {
    dsn := DB_USER + ":" + DB_PASS + "@" + DB_HOST + "/" + DB_NAME + "?charset=utf8"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    var str string
    q := "select username from bankadmin.accounts where id = 1"
    err = db.QueryRow(q).Scan(&str)
    if err != nil {
        log.Fatal(err)
    }
    log.Println(str)
}

this returns

D:\_>go run dbtest.go
2013/03/29 13:14:11 Error 1045: Access denied for user 'bankadmin'@'localhost'
(using password: YES)
exit status 1

the password is actually right, for the both user root and bankadmin, and the mysql in running in port 3306. But then again, if i change the DB_HOST to "tcp(127.0.0.1:3000)" or change the 127.0.0.1 to localhost, it gave me the same error.

using mymysql driver

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/ziutek/mymysql/godrv"
    "log"
    "strconv"
)

const (
    DB_HOST = "tcp(127.0.0.1:3306)"
    DB_NAME = "bankdata"
    DB_USER = "bankadmin"
    DB_PASS = "1234"
)

type User struct {
    Id       int    
    Username string 
    Email    string 
    Facebook string
}

func OpenDB() *sql.DB {
    db, err := sql.Open("mymysql", fmt.Sprintf("%s/%s/%s", DB_NAME, DB_USER, DB_PASS))
    if err != nil {
        panic(err)
        log.Fatal(err)
    }
    return db
}

func UserById(id int) *User {
    db := OpenDB()
    defer db.Close()
    row := db.QueryRow("SELECT id, username, email FROM accounts WHERE id=?", id)
    user := new(User)
    row.Scan(&user.Id, &user.Username, &user.Email, &user.Facebook)
    return user
}

func main() {
    db := OpenDB()
    defer db.Close()
    row := db.QueryRow("SELECT id, username, email, facebook FROM accounts WHERE id=2")
    user := new(User)
    row.Scan(&user.Id, &user.Username, &user.Email, &user.Facebook)
    fmt.Println("id    : " + strconv.Itoa(user.Id) + "\nname  : " + user.Username + "\nemail : \n" + user.Email)

}

this returns:

D:\_>go run dbtest.go
id    : 0
name  :
email :

it looks like both the id and the strings is empty, like it's never received any values from row.Scan function. And i don't know how to make sure that they are connected even though they didn't give me the same error as the previous driver

Oh, and i'm using Windows 7, MySQL 5.5.8 that comes with WAMP.

If i made any mistake in this post, pardon me. This is my first post.

Answer 1

The problem isn't your go code the problem is you mysql configuration. You may have the right username and password but you probably haven't authorized that user to connect to the database using tcp.