user2901304 user2901304 - 1 year ago 80
MySQL Question

Docker application container wont communicate with my MySQL container

I am using docker compose to put together two containers to help better familiarize myself with docker and I can't seem to figure out why my two containers can't communicate with each other.

My dockerfile for my app is:

FROM golang
ADD . /go/src
WORKDIR /go/src

RUN go get github.com/go-sql-driver/mysql
RUN go get github.com/gorilla/mux
RUN go build -o bin/main main.go app.go model.go

ENTRYPOINT /go/src/bin/main

EXPOSE 8080


and my docker-compose.yml is

version: '3'

services:
db:
image: mysql:5.7
volumes:
- db_data:/var/lib/mysql
restart: always
ports:
- "3306:3306"
expose:
- "3306"
environment:
MYSQL_ROOT_PASSWORD: testrootpassword
MYSQL_DATABASE: testing
MYSQL_USER: testuser
MYSQL_PASSWORD: testpassword

api:
depends_on:
- db
build: .
ports:
- "8080:8080"
# restart: always
environment:
APP_DB_HOST: db:3306
APP_DB_NAME: testing
APP_DB_USERNAME: testuser
APP_DB_PASSWORD: testpassword
volumes:
db_data:


The main app just starts a server on port 8080 and tries to establish a connection to SQL. The full implementation is here: https://github.com/bliitzkrieg/go-sql-testing

package main

import (
"os"
)

func main() {
a := App{}
a.Initialize(
os.Getenv("APP_DB_USERNAME"),
os.Getenv("APP_DB_PASSWORD"),
os.Getenv("APP_DB_NAME"))

a.Run(":8080")
}


app.go

package main

import (
"database/sql"
"encoding/json"
"fmt"
"log"
"net/http"
"strconv"

_ "github.com/go-sql-driver/mysql"
"github.com/gorilla/mux"
)

type App struct {
Router *mux.Router
DB *sql.DB
}

func (a *App) Initialize(user, password, dbname string) {
connectionString :=
fmt.Sprintf("%s:%s@/%s", user, password, dbname)

var err error
a.DB, err = sql.Open("mysql", connectionString)
if err != nil {
log.Fatal(err)
}

a.ensureTableExists()
a.Router = mux.NewRouter()
a.initializeRoutes()
}

func (a *App) ensureTableExists() {
if _, err := a.DB.Exec(tableCreationQuery); err != nil {
log.Fatal(err)
}
}

func (a *App) Run(addr string) {
log.Fatal(http.ListenAndServe(":8080", a.Router))
}

func (a *App) initializeRoutes() {
a.Router.HandleFunc("/", a.sayHello).Methods("GET")
a.Router.HandleFunc("/products", a.getProducts).Methods("GET")
a.Router.HandleFunc("/product", a.createProduct).Methods("POST")
a.Router.HandleFunc("/product/{id:[0-9]+}", a.getProduct).Methods("GET")
a.Router.HandleFunc("/product/{id:[0-9]+}", a.updateProduct).Methods("PUT")
a.Router.HandleFunc("/product/{id:[0-9]+}", a.deleteProduct).Methods("DELETE")
}

func (a *App) sayHello(w http.ResponseWriter, r *http.Request) {
fmt.Fprintf(w, "Hello Luca!")
}

func (a *App) getProduct(w http.ResponseWriter, r *http.Request) {
vars := mux.Vars(r)
id, err := strconv.Atoi(vars["id"])
if err != nil {
respondWithError(w, http.StatusBadRequest, "Invalid product ID")
return
}

p := product{ID: id}
if err := p.getProduct(a.DB); err != nil {
switch err {
case sql.ErrNoRows:
respondWithError(w, http.StatusNotFound, "Product not found")
default:
respondWithError(w, http.StatusInternalServerError, err.Error())
}
return
}

respondWithJSON(w, http.StatusOK, p)
}

func (a *App) getProducts(w http.ResponseWriter, r *http.Request) {
products, err := getProducts(a.DB)
if err != nil {
respondWithError(w, http.StatusInternalServerError, err.Error())
return
}

respondWithJSON(w, http.StatusOK, products)
}

func (a *App) createProduct(w http.ResponseWriter, r *http.Request) {
var p product
decoder := json.NewDecoder(r.Body)
if err := decoder.Decode(&p); err != nil {
respondWithError(w, http.StatusBadRequest, "Invalid request payload")
return
}
defer r.Body.Close()

if err := p.createProduct(a.DB); err != nil {
respondWithError(w, http.StatusInternalServerError, err.Error())
return
}

respondWithJSON(w, http.StatusCreated, p)
}

func (a *App) updateProduct(w http.ResponseWriter, r *http.Request) {
vars := mux.Vars(r)
id, err := strconv.Atoi(vars["id"])
if err != nil {
respondWithError(w, http.StatusBadRequest, "Invalid product ID")
return
}

var p product
decoder := json.NewDecoder(r.Body)
if err := decoder.Decode(&p); err != nil {
respondWithError(w, http.StatusBadRequest, "Invalid resquest payload")
return
}
defer r.Body.Close()
p.ID = id

if err := p.updateProduct(a.DB); err != nil {
respondWithError(w, http.StatusInternalServerError, err.Error())
return
}

respondWithJSON(w, http.StatusOK, p)
}

func (a *App) deleteProduct(w http.ResponseWriter, r *http.Request) {
vars := mux.Vars(r)
id, err := strconv.Atoi(vars["id"])
if err != nil {
respondWithError(w, http.StatusBadRequest, "Invalid Product ID")
return
}

p := product{ID: id}
if err := p.deleteProduct(a.DB); err != nil {
respondWithError(w, http.StatusInternalServerError, err.Error())
return
}

respondWithJSON(w, http.StatusOK, map[string]string{"result": "success"})
}

func respondWithError(w http.ResponseWriter, code int, message string) {
respondWithJSON(w, code, map[string]string{"error": message})
}

func respondWithJSON(w http.ResponseWriter, code int, payload interface{}) {
response, _ := json.Marshal(payload)

w.Header().Set("Content-Type", "application/json")
w.WriteHeader(code)
w.Write(response)
}

const tableCreationQuery = `CREATE TABLE IF NOT EXISTS products
(
id SERIAL,
name TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL DEFAULT 0.00,
CONSTRAINT products_pkey PRIMARY KEY (id)
)`


To run the application I am using the docker command
docker-compose up -d --build
which builds the application fine. When I run
docker ps
it shows only my SQL server is up and when I look at the logs of my api container, it shows one line saying
2017/10/01 06:54:14 dial tcp 127.0.0.1:3306: getsockopt: connection refused
. I ran the application locally with the hard coded connection string and it worked fine. I am not really sure whats going on so hopefully somebody can help me out!

Cheers!

Answer Source

Two issues in your code. One is the

a.Initialize(
    os.Getenv("APP_DB_USERNAME"),
    os.Getenv("APP_DB_PASSWORD"),
    os.Getenv("APP_DB_NAME"))

You have not used APP_DB_HOST to initialize DB, so you are going to localhost directly.

Second you are connecting to the DB at the start of the program and it takes sometime for the DB to actually get UP. So you need to have some retry and timeout either in your code, or you should wait for the DB to get up and then run the main command.

See https://github.com/vishnubob/wait-for-it a bash script which can be used in container to wait for mysql db to get up and then run the main program

Edit-1: Updated dockerfile

Below is a updated dockerfile for your github repo https://github.com/bliitzkrieg/go-sql-testing/

FROM golang
ADD . /go/src
WORKDIR /go/src

RUN go get github.com/go-sql-driver/mysql
RUN go get github.com/gorilla/mux
RUN go build -o bin/main main.go app.go model.go
RUN git clone https://github.com/vishnubob/wait-for-it.git

CMD ./wait-for-it/wait-for-it.sh --host=db --port=3306 --timeout=60 -- /go/src/bin/main

EXPOSE 8080
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download