I am facing error when trying to access MySQL DB, please let me know what needs to be changed.
Enviornment details:
$: go version
go version go1.7.1 linux/amd64 OS Details:
$: uname -a
Linux poc 3.13.0-37-generic #64-Ubuntu SMP Mon Sep 22 21:28:38 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux MySQL
Your MySQL connection id is 90
Server version: 5.5.52-0ubuntu0.14.04.1 (Ubuntu)
Error Msg:
db$ go run DBMain.go
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x20 pc=0x40118c]
dsn := "service:password@tcp(localhost:3306)/service?charset=utf8"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatal(err.Error())
panic(err.Error())
}
defer db.Close()
iss := db.Ping()
fmt.Println("Ping status %s", iss.Error())
if iss != nil {
log.Fatal("Error for db conn")
log.Fatal(iss.Error())
}
var (
name string
)
rows, err := db.Query("select name from user where id = ?", id)
if err != nil {
log.Fatal(err)
}
fmt.Println("DB rows %s", rows)
defer rows.Close()
for rows.Next() {
err := rows.Scan(&name)
if err != nil {
log.Fatal(err)
}
fmt.Println("Name of student,%s", name)
}
err = rows.Err()
if err != nil {
log.Fatal(err)
}
Thanks @radovksyb, I have tried with the approach you mentioned
$: go run DBMain.go //below is the ouptput
Welcome
DB rows %s &{0xc4200202a0 0x4622d0 0xc420010a00 false [] 0xc420014db0}
Can you let me know how to retrieve each row from the rows returned by Query result set
Iâm currently out at the moment and wonât be home for about 4 hours, so if
no one else has helped you before I get home, then I will be more than
happy to help you then
Thanks oleg578, I have given full permissions of service user in mysql database. But can you please let me know how to verify localhost reachable or recognized or not? Can you please let me know
I am able to ping the localhost from shell prompt.
I have cleaned up my environment reconfigured/reinstalled Go, MySQL
Hereâs a simple example to show you what you need to do to retrieve a single row from the database using mysql (with what you were doing, you want to use QueryRow instead of Query since you are retrieving information from 1 single user and not many):
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
dsn := "username:password@tcp(127.0.0.1:3306)/mydb"
// Open the database.
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatalln(err)
}
defer db.Close()
// Ping the database to verify that the connection is valid.
if err := db.Ping(); err != nil {
log.Fatalln(err)
}
// Use QueryRow instead of Query as you are only retrieving a
// single row from the database.
row := db.QueryRow("select name from users where id = ?", id)
// Scan in the name from the row.
var name string
if err := row.Scan(&username); err != nil {
log.Fatalln(err)
}
// Print out the name.
fmt.Println(name)
}
Lastly, here is one more example, showing you how to use Query instead of my other example that uses QueryRow, so you can see how to scan in multiple users at a time.
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
// A User describes a user from the database.
type User struct {
Id int
Username string
Password string
}
func main() {
// Repace username, password and the database name.
dsn := "username:username@tcp(127.0.0.1:3306)/mydb"
// Open the database.
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatalln(err)
}
defer db.Close()
// Ping the database to verify that the connection is valid.
if err := db.Ping(); err != nil {
log.Fatalln(err)
}
// Use Query to retrieve all users from the database;
rows, err := db.Query("select * from users")
if err != nil {
log.Fatalln(err)
}
defer rows.Close()
// Iterate over all of the rows returned from the database.
for rows.Next() {
// Create a new user object.
u := new(User)
// Scan in the user's information from the row into u.
if err := rows.Scan(&u.Id, &u.Username, &u.Password); err != nil {
log.Fatalln(err)
}
// Print out the user.
fmt.Println(u)
}
// Make sure there were no errors whilst scanning in the users
// from the database.
if err := rows.Err(); err != nil {
log.Fatalln(err)
}
}
func getUserDetails(mobileNumber string) {
dsn := "service:password@tcp(127.0.0.1:3306)/service"
db, err := sql.Open(âmysqlâ, dsn)
if err != nil {
log.Fatalln(err)
}
defer db.Close()
// Ping the database to verify that the connection is valid.
if err := db.Ping(); err != nil {
log.Fatalln(err)
}
fmt.Println("Ping status ", db.Ping())
if err := db.Ping(); err != nil {
log.Fatalln(err)
}
var (
first_name string
)
// Use QueryRow instead of Query as you are only retrieving a
// single row from the database.
row := db.QueryRow(âselect first_name from user_details where mobile_number= ?â, mobileNumber)
if err := row.Scan(&first_name); err != nil {
log.Fatalln(err)
}
// Print out the name.
fmt.Println(first_name)
}
MySQL DB:
select * from user_details where mobile_number=â9â;
±--------------±--------------±----------±-----------±----------±--------±--------±--------±--------±--------±--------±--------------±---------+
| mobile_number | email_id | user_type | first_name | last_name | street1 | street2 | street3 | city | country | pincode | login_session | password |
±--------------±--------------±----------±-----------±----------±--------±--------±--------±--------±--------±--------±--------------±---------+
| 9 | sai@gmail.com | society | Sadguru | Sai | | | | Shiridi | INDIA | 560043 | | |
±--------------±--------------±----------±-----------±----------±--------±--------±--------±--------±--------±--------±--------------±---------+
A nil ping error is what you want. Ping only returns a value other than nil if there is an actual error.
Edit: You also donât need to ping the database twice like you are doing and also donât need to be writing a ping status message. However, if you are doing this for a specific reason, then just write this instead:
// Ping the database to verify that the connection is valid.
if err := db.Ping(); err != nil {
log.Fatalln(err)
} else {
fmt.Println("Ping status: OK")
}
Also, Iâm not sure if you are using a database with a different encoding, but there isnât anything technically wrong with your sql statement, so you should be receiving a value if there is actually a value in your database that matches that sql statement.
What happens if you log in to your service database and type this? select first_name from user_details where mobile_number= 9;
If you are using a separate encoding then specify it inside of your dsn.
Thanks a lot , I am able to get the data with QueryRow, but I have hardcoded the query param in the calling method I am able to get the data.
But if I passes the data from request parameter, I am not able to get the data.
func getUserDetails(mobileNumber string) {
dsn := "ekservice:password@tcp(127.0.0.1:3306)/ekservice"
db, err := sql.Open(âmysqlâ, dsn)
if err != nil {
log.Fatalln(err)
}
defer db.Close()
// Ping the database to verify that the connection is valid.
if err := db.Ping(); err != nil {
log.Fatalln(err)
}
fmt.Println("Ping status ", db.Ping())
if err := db.Ping(); err != nil {
log.Fatalln(err)
}
var (
first_name string
)
// Use QueryRow instead of Query as you are only retrieving a
// single row from the database.
city := "9632969969"
row := db.QueryRow(âselect first_name from user_details where mobile_number = ?â, city)
if err := row.Scan(&first_name); err != nil {
log.Fatalln(err)
}
// Print out the name.
fmt.Println(first_name)
}
