Connecting to CloudSQL from App Engine (Second Generation CloudSQL) GO

Question

Love the Stack, My first post out of complete frustration. Thanks for you comments!

• Created App Engine Project
• Created Second Generation MySQL Instance in my App Engine Project
• Created Database in the MySQL Instance
• In App Engine, I activate the --> Google Cloud Shell <--. ( I am working at a command prompt in my console.cloud.google.com)

I have copied this basic GO program to connect to my MySQL instance.

I build it and run it. go build mysqlexample.go ./mysqlexample

I have not been able to achieve a successful connection. You can see all the various connection strings that I have tried and to the right of them is the response I get.

I can connect from my local windows machine using mysql admin.

Help?

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "log"
    )

func main() {

    const dbIP = "104.xxx.xx.x"
    const dbInstanceName =  "esp-1-dev:us-central1:espdev"
    const dbName = "servpro"
    const dbUserName = "root"
    const dbPassword = "xxxxxxx"

    const dbOpenString = dbUserName + ":" + dbPassword + "@/" + dbInstanceName + "/" + dbName  //GETS RESPONSE default addr for network 'AppEngine:Zone:Project' unknown
    //const dbOpenString = dbUserName + "@cloudsql(" + dbInstanceName + ")/" + dbName   //GETS RESPONSE  dial cloudsql: unknown network cloudsql
    //const dbOpenString = dbUserName + "@/"  //+ "?parseTime=true&loc=UTC"                 //GETS RESPONSE  getsockopt: connection refused
    //const dbOpenString = dbUserName + ":" + dbPassword + "@tcp(" + dbIP + ":3306)/" + dbName  //GETS RESPONSE  dial tcp 104.xxx.xxx.x:3306: getsockopt: connection timed out

    //  Got this from stack overflow.  GoDocs are not updated to reflect 2nd Gen databases.
    //  http://stackoverflow.com/questions/38890022/tls-requested-but-server-does-not-support-tls-error-with-google-cloud-sql-2nd
    //user:password@cloudsql(copiedPastedInstanceConnectionName)/d‌​atabaseName?charset=‌​charset&collation=co‌​llation&tls=tlsConfi‌​gName&parseTime=true
    //First Generation Connection String    
        //username:password@cloudsql(appID:CloudSQLInstance)/databasename?parseTime=true&loc=UTC

    db, err := sql.Open("mysql", dbOpenString);
    defer db.Close()

    log.Println("Attempting Ping of database....")

    err = db.Ping()

    if err != nil {
        log.Println("db.Ping() failed:  " + dbOpenString)
        log.Println(err)
    } else {
        log.Println("Success!")
    }

}

Answer 1

If you are migrating to the second generation Go App Engine (Golang 1.11) change your connection string from:

user:password@cloudsql(instanceID)/db

to

user:password@unix(/cloudsql/instanceID)/db

Answer 2

Have run in to the same problem of connecting to a cloud SQL instance. One variant that I got working on my windows localhost environment is below. The IP of my localhost needs to be added to Authorized networks for the db instance.

Windows localhost connect over tcp:

user:password@tcp(104.xxx.xxx.xxx:3306)/dbname

Answer 3

https://cloud.google.com/appengine/docs/flexible/go/using-cloud-sql

For new people to GO, App Engine and CloudSQL just writing the simplest GO program to connect and communicate with your CloudSQL 2nd Gen database is frustrating!

You have choices to make, App Eng or App Eng Flex, SQL 1st or 2nd Gen.... Depending on the combination connection strings are different. All of google's documentation when you search drives you to first gen SQL and App Engine without flex because this is what is predominantly in production. Make sure you are reading Flex documentation if you are doing that. Make sure you are reading 2nd Gen docs if doing that. Sometimes they are entirely different docs, sometimes the documentation is stacked on a page and you have to goto the bottom to see about the newer stuff 2nd gen sql and app eng flex.

CloudShell is tricky, I still cannot compile GO and talk to SQL 2nd here. I am successfully talking to cloud sql 2nd gen from a deployed app engine flex WITH A SQL PROXY RUNNING, you have to use SQL PROXY. You have to go thru the setup for this create users on appengine and SQL.

This is my working program.

package main

import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"log"
"fmt"
"net/http"
)

func healthCheckHandler(w http.ResponseWriter, r *http.Request) {
fmt.Fprint(w, "ok")
}

func main() {
http.HandleFunc("/", handle)
http.HandleFunc("/_cloudshellProxy/_ah/health", healthCheckHandler)
log.Print("Listening on port 8080")
log.Fatal(http.ListenAndServe(":8080", nil))
}


func handle(w http.ResponseWriter, r *http.Request) {

const dbIP = "104.xxx.xxx.x"
const dbInstanceName =  "projectname:us-central1:sqlinstance"
const dbName = "servxxx"
const dbUserName = "sqlproxysuser"
const dbPassword = "xxxRockxxx"


if r.URL.Path != "/" {
    http.NotFound(w, r)
    return
}
fmt.Fprint(w, "Hello SQL!  Hello?")
fmt.Fprint(w, "\n")

const dbOpenString = dbUserName + ":" + dbPassword + "@unix(/cloudsql/" + dbInstanceName + ")/" + dbName 
//const dbOpenString = dbUserName + ":" + dbPassword + "@cloudsql(" + dbInstanceName + ")/" + dbName 

//-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
//-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
//-=- SQL OPEN Statement,  per docs, DOES NOT return an error ever
//-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
//-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
db, err := sql.Open("mysql", dbOpenString);
defer db.Close()

err = db.Ping()
if err != nil {
    fmt.Fprint(w, "Failed Connection" + "  " + dbOpenString)
    fmt.Fprint(w, "\n")
    fmt.Fprint(w, err)
    return
} else {
    fmt.Fprint(w, "SUCCESSFUL CONNECTION" + "  " + dbOpenString)
    fmt.Fprint(w, "\n")
}

_, err = db.Exec("CREATE TABLE IF NOT EXISTS exercisecloudsql101 (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, description TEXT, PRIMARY KEY (id))")
if err != nil {
    fmt.Fprint(w, "CREATE TABLE failed:")
    fmt.Fprint(w, "\n")
    fmt.Fprint(w, err) 
    fmt.Fprint(w, "\n")
} else {
    fmt.Fprint(w, "SUCCESSFUL CreateTable" + "  " + dbOpenString)
    fmt.Fprint(w, "\n")
}

}

Answer 4

The following are the correct connection strings, but they differ depending on which version of App Engine you are connecting from.

App Engine Standard:

user:password@cloudsql(INSTANCE_CONNECTION_NAME)/dbname

App Engine Flexible:

user:password@unix(/cloudsql/INSTANCE_CONNECTION_NAME)/dbname