CODEWITHSUNDEEP
mysqlgo
Sai Srinivasan
Sai Srinivasan

Reputation: 55

Error getting mysql connection using golang

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: 6192

Answers (1)

Aruna Herath
Aruna Herath

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

Related Questions