Reputation: 55
I'm new to golang and MySQL and I am using a dev box to create a simple web page using golang (code from the 2 files are way down below) to take input, store input into a database and display input on a webpage. Currently using MySQL locally as my database with my username as root and my password as abc, my database is also named abc.
At the top I have imported :
import (
_"github.com/go-sql-driver/mysql"
"database/sql"
)
When I serve my web page I call my connectDB function:
func init(){
INDEX_HTML , _ = ioutil.ReadFile("./templates/index.html")
connectDb()
}
func connectDb(){
//socket : var/run/mysqld/mysqld.sock
/* connection string examples :
db, err := sql.Open("mysql", "user:password@/dbname")
user:password@tcp(localhost:5555)/dbname?tls=skip-verify&autocommit=true
TCP using default port (3306) on localhost:
user:password@tcp/dbname?charset=utf8mb4,utf8&sys_var=esc%40ped
Use the default protocol (tcp) and host (localhost:3306):
user:password@/dbname
No Database preselected:
user:password@/
*/
db, err := sql.Open("mysql", "root:abc@/abc")
log.Println("DB: ", db)
checkErr(err)
// Open doesn't open a connection. Validate DSN data:
checkErr(err)
}
My understanding and according to https://github.com/go-sql-driver/mysql SQL.open returns a * sql.DB, and when the program hits following code above:
/* At the top of my code i have var db = *sql.DB */
log.Println("DB: ", db)
I get the following in the command prompt with all types of example connection strings noted in the code comments above:
DB: &{0x99c410 root:abc@/abc 0 {0 0} [] [] 0 0xc820072180 false map[] map[] 0 0 0 < nil >}
My MySQL database is running fine locally through the command line with the given username and password and I can get a few items with the following queries inserted manually.
mysql>
mysql> select * from ListItems
-> ;
+-------+
| items |
+-------+
| item1 |
| item2 |
+-------+
I'm trying to understand what the message is and how I can go about connecting to the mysql database to run queries and insert into the database.
Maybe there is something wrong with my connection string? An example of my error while trying to insert into the database:
016/06/20 02:22:36 http: panic serving 10.1.0.5:41861:
runtime error: invalid
memory address or nil pointer dereference
goroutine 7 [running]:
net/http.(*conn).serve.func1(0xc82006c380)
/usr/local/go/src/net/http/server.go:1389 +0xc1
panic(0x74ef00, 0xc82000a0c0)
/usr/local/go/src/runtime/panic.go:443 +0x4e9
database/sql.(*DB).conn(0x0, 0x1, 0x0, 0x0, 0x0)
/usr/local/go/src/database/sql/sql.go:778 +0xac9
database/sql.(*DB).exec(0x0, 0x812b60, 0x26, 0xc82003fa70, 0x1, 0x1, 0xc82000b201, 0x0, 0x0, 0x0, ...)
/usr/local/go/src/database/sql/sql.go:1021 +0x87
database/sql.(*DB).Exec(0x0, 0x812b60, 0x26, 0xc82003fa70, 0x1, 0x1, 0x0, 0x0, 0x0, 0x0)
/usr/local/go/src/database/sql/sql.go:1009 +0xbe
main.AddListHandler(0x7f56da7ac9b0, 0xc82006f6c0, 0xc8200f21c0)
/home/cabox/workspace/src/github.com/user/hello/main.go:26 +0x36c **<---**
net/http.HandlerFunc.ServeHTTP(0x859f70, 0x7f56da7ac9b0, 0xc82006f6c0, 0xc8200f21c0)
/usr/local/go/src/net/http/server.go:1618 +0x3a
net/http.(*ServeMux).ServeHTTP(0xc820010b10, 0x7f56da7ac9b0, 0xc82006f6c0, 0xc8200f21c0)
/usr/local/go/src/net/http/server.go:1910 +0x17d
net/http.serverHandler.ServeHTTP(0xc82006c180, 0x7f56da7ac9b0, 0xc82006f6c0, 0xc8200f21c0)
/usr/local/go/src/net/http/server.go:2081 +0x19e
net/http.(*conn).serve(0xc82006c380)
/usr/local/go/src/net/http/server.go:1472 +0xf2e
created by net/http.(*Server).Serve
/usr/local/go/src/net/http/server.go:2137 +0x44e
As I look at line 26 in main.go I have:
db.Exec("INSERT INTO ListItems(items) Values(?)" , (r.Form["item"][0]))
Here is the minimal code sample:
project/templates/index.html:
<!doctype html>
<h1>Enter item to add to list:</h1><br>
<form action="/addlist" method="post">
<input type="text" name="item" placeholder="Enter Item to add to list"><br>
<input type ="submit" class="add" value="Add To List"><br>
</form>
projects/main.go .. using go build main.go
package main
import (
"net/http"
"fmt"
"io/ioutil"
"log"
_"github.com/go-sql-driver/mysql"
"database/sql"
)
var INDEX_HTML []byte // holds the raw html of index.html inside /templates
var db *sql.DB // pointer to sql.open which returns a sql.db
func main(){
fmt.Println("starting server on port 8080")
http.HandleFunc("/", IndexHandler)
http.HandleFunc("/addlist", AddListHandler)
http.ListenAndServe(":8080", nil)
}
func AddListHandler(w http.ResponseWriter, r *http.Request){
r.ParseForm()
log.Println("adding list: ", r.Form["item"][0])
db.Exec("INSERT INTO ListItems(items) Values(?)" , (r.Form["item"][0])) /*error*/
if(db == nil){log.Println("DB is nill")}
http.Redirect(w,r,"/", http.StatusTemporaryRedirect)
}
func IndexHandler(w http.ResponseWriter, r *http.Request){
log.Println("GET /")
w.Write(INDEX_HTML)
}
func init(){
INDEX_HTML , _ = ioutil.ReadFile("./templates/index.html")
connectDb()
}
func connectDb(){
//socket : var/run/mysqld/mysqld.sock
/* connection string examples :
db, err := sql.Open("mysql", "user:password@/dbname")
user:password@tcp(localhost:5555)/dbname?tls=skip-verify&autocommit=true
TCP using default port (3306) on localhost:
user:password@tcp/dbname?charset=utf8mb4,utf8&sys_var=esc%40ped
Use the default protocol (tcp) and host (localhost:3306):
user:password@/dbname
No Database preselected:
user:password@/
*/
db, err := sql.Open("mysql", "root:abc@/abc")
log.Println("DB: ", db)
checkErr(err)
// Open doesn't open a connection. Validate DSN data:
checkErr(err)
}
func checkErr(err error) {
if err != nil {
log.Println(err)
}else{
log.Println(err)
}
}
Upvotes: 1
Views: 6183
Reputation: 6531
The problem is the global db
variable you declare by var db *sql.DB
is never assigned a value. So it keeps its default value which is nil
. And trying to dereference a nil pointer gives you the error.
You probably think that in the init
function you are assigning this variable the newly created DB
instance. But :=
creates a new local variable to hold the new DB
instance. The global db
variable is left untouched. See example.
So instead do,
var err error
db, err = sql.Open("mysql", "root:abc@/abc")
Also from my experience using a global variable when you can avoid it leads to errors hard to debug. (like this one.) I think you can avoid it by making connectDb
return a DB
instance.
Upvotes: 1