Google app engine with sockets

[pjebs]

I've been using CloudSQL but it it very expensive.
I found out recently that you can connect to a external MySQL database using sockets on gae (paid app)

https://cloud.google.com/appengine/docs/go/sockets/reference#Introduction

Is there some way to get the socket interface to work with this package?

[pjebs]

I'm pretty sure the solution has something to do with https://godoc.org/github.com/go-sql-driver/mysql#DialFunc

But I can't get it to work

[pjebs]

When I try to connect to an external database the 'normal way' without appengine/socket I get this error:

error: dial tcp 173.388.532.444:3306: socket: operation not permitted

When I try connecting like this (abridged):

"google.golang.org/appengine"
"github.com/go-sql-driver/mysql"
"google.golang.org/appengine/socket"

dial := func(addr string) (net.Conn, error) {
        return socket.Dial(appengine.NewContext(r), "tcp", addr)
    }

mysql.RegisterDial("external", dial)

and change my dataSourceName = something like: "id:password@external(your-amazonaws-uri.com:3306)/dbname" I get this error:

error: API error 5 (remote_socket: PERMISSION_DENIED): connection to (2, 173.388.532.444:3306,6) denied due to policy

My app is a paid google app engine-standard environment app hence sockets are available.

[pjebs]

I couldn't get it to work so I reverted back to CloudSQL.
I know it is possible as per this article:
http://stackoverflow.com/questions/26322445/connecting-external-mysql-to-google-app-engine
(NB: Stuart Langley is from GAE-PHP but all the runtimes are pretty much the same in capability)

Even if my post is 2+ years old and you find a solution please tell me so I can change over.

[pjebs]

A significantly slower solution would be to fork this package so that it can interact with MYSQL's REST API interface which communicates using json. That would probably be slow though. GAE would definitely allow it using appengine/urlfetch.

[pjebs]

@julienschmidt any ideas?

[arnehormann]

@pjebs that doesn't look very promising... https://github.com/GoogleCloudPlatform/appengine-sockets-python-java-go/tree/master/go_socket_demo (README with "will be added soon" in a 4 year old commit)

Best of luck, but you are on your own. If you do find something, we'd love to know how it's done.

But...

• it's less of a driver and apparently more of a connection / permission problem.
• to even test this requires replicating your setup from a description only. Which is time consuming, not free, involves services out of our control which are hard to diagnose and would only help a fraction of our users.

[pjebs]

Would you be able to keep it open for a bit longer - perhaps a week?

[arnehormann]

sure

[pjebs]

Thanks

[pjebs]

https://cloud.google.com/appengine/docs/go/using-third-party-databases#other_cloud_providers

This article says it's possible for standard environment but doesn't specifically say you have to use sockets. @arnehormann Do you think there may be another way? The tone of the article really suggests they are doing their best to hide how to do it to keep you on CloudSQL.

[julienschmidt]

@pjebs I can't help with this issue. I am not an AppEngine or CloudSQL user and have no idea about it.

[pjebs]

@arnehormann and @FutureReaders

I have worked out how it is done. It is done via the code above:

"google.golang.org/appengine"
"github.com/go-sql-driver/mysql"
"google.golang.org/appengine/socket"

//r: *http.Request
dial := func(addr string) (net.Conn, error) {
        return socket.Dial(appengine.NewContext(r), "tcp", addr)
    }

mysql.RegisterDial("external", dial)

change my dataSourceName = something like: "id:password@external(your-amazonaws-uri.com:3306)/dbname"

The reason why it didn't work before was because I had a cloud sql server and to test trying to connect to an external database, I just used the cloud sql server's public ip address.

Apparently the cloud sql server's IP address is blocked by Socket API hence it didn't allow it, leading me to think my approach was wrong! A cloud sql database can only be connected via: user@cloudsql(project-id:instance-name)/dbname and not via its public ip address and socket API.

[pjebs]

I attempted to work around database/sql's connection pooling by creating a wrapper since otherwise it requires changes to the mysql driver. My attempt Q&D work-around can be found here: https://github.com/pjebs/GAE-Toolkit-Go/tree/master/sql

If anyone wants to help go ahead.

[warent]

@pjebs I'm commenting from the future to say that socket.Dial is exactly what I needed for an issue not related to SQL but rather Redis. Thank you!