myxql connection issues (gcp cloud sql)

Hi, i’d like to ask some help with connecting to gcp cloud sql (mysql, myxql ecto adapter). when im using dev env, i can query and everythings fine. Checked DATABASE_URL, also tried hard coding it in prod.exs, runtime (DB_PASSWORD, DB_HOST, etc.). Added the ip from fly ips list --app app-name to authorized networks in cloud sql dashboard. the mysql instance ip from gcp is public.

But when i deploy it, here’s what i get from monitoring:

2022-06-27T20:07:37.031 app[d672bcc5] lax [info] 20:07:37.030 [error] MyXQL.Connection (#PID<0.1743.0>) failed to connect: ** (DBConnection.ConnectionError) (35.202.196.111:3306) timeout - :timeout
2022-06-27T20:07:40.560 app[d672bcc5] lax [info] 20:07:40.559 [error] MyXQL.Connection (#PID<0.1738.0>) failed to connect: ** (DBConnection.ConnectionError) (35.202.196.111:3306) timeout - :timeout
2022-06-27T20:07:46.528 app[d672bcc5] lax [info] 20:07:46.527 [error] MyXQL.Connection (#PID<0.1739.0>) failed to connect: ** (DBConnection.ConnectionError) (35.202.196.111:3306) timeout - :timeout
2022-06-27T20:07:47.658 app[d672bcc5] lax [info] 20:07:47.657 [error] MyXQL.Connection (#PID<0.1733.0>) failed to connect: ** (DBConnection.ConnectionError) (35.202.196.111:3306) timeout - :timeout
2022-06-27T20:07:48.256 app[d672bcc5] lax [info] 20:07:48.255 [error] MyXQL.Connection (#PID<0.1741.0>) failed to connect: ** (DBConnection.ConnectionError) (35.202.196.111:3306) timeout - :timeout
2022-06-27T20:07:49.032 app[d672bcc5] lax [info] 20:07:49.031 [error] MyXQL.Connection (#PID<0.1737.0>) failed to connect: ** (DBConnection.ConnectionError) (35.202.196.111:3306) timeout - :timeout
2022-06-27T20:07:54.884 app[d672bcc5] lax [info] 20:07:54.883 [error] MyXQL.Connection (#PID<0.1742.0>) failed to connect: ** (DBConnection.ConnectionError) (35.202.196.111:3306) timeout - :timeout

Are the IPs returned from fly ips list the outgoing IPs :thinking:

As far as I’m aware, Fly shows the IP it receives requests on (which you need for DNS entries, etc) however when your app makes outgoing requests, those would not come from that same IP. If that is the case it would explain this issue: Google’s firewall would block the request to connect and that would result in a timeout in your app.

You could confirm this (if this is not a production database!) by allowing access to any IP, briefly, then try and connect to it from your app. If that works, you will know the IP is the issue.

hi @greg, thanks for helping me out. I’m actually confused with how to allow any or wildcard, but ill try :sweat_smile:. Can confirm it works just by adding the ip from my local to the authorized networks (gcp cloud sql dashboard).

if i dig +short app_name.fly.dev it gives me the same thing with fly ips list --app app_name

1 Like

Ah. I suspected as much (I wondered if fly ips list had been added for showing outgoing IPs as I’ve not used that command before. But it seems not, as you confirm it returns the IP the DNS points to).

So … that’s a known issue unfortunately. You will see other people asking about it, for firewall/whitelists. You could ask someone like @kurt for an outgoing IP range from your app’s region (which you could then whitelist) else perhaps use Google’s proxy which seems designed to solve this issue? About the Cloud SQL Auth Proxy  |  Cloud SQL for MySQL  |  Google Cloud

The downside of asking Fly for their IP/range is they change:

gcp auth proxy is working fine from my local machine. I added wget in Dockerfile but it’s not installed when im connected via fly ssh (does that mean when im installing wget it’s going in builder but not in the app instance?). Deployed with --no-cache and --remote-only. Am I missing something?

Er, well it should at least be there if you run wget. Strange. I haven’t used it from Fly, but in theory you should be able to wget it, chmod it to make it executable, and then run it.

The one slight complication with this approach would be you would need to have the sql proxy running in parallel to your app process. So you would likely need something like supervisor (or equivalent) to keep both it and your app running. Take a look at Running Multiple Processes Inside A Fly.io App · Fly Docs for how

@greg my mistake, it should be under the runner image not builder. That’s why i cant see wget and tmux installed.

So following with gcp auth proxy here’s what i did:

  • service account for cloud sql
  • fly ips list --app app-name or dig +short app-name.fly.dev to be added to “authorized networks” in cloud sql. Theres also connection test tab to see if its working.
  • add key with proper roles to it (filename.json)
  • add this under FROM {RUNNER_IMAGE} in Dockerfile:
    • add wget, tmux
    • COPY filename.json /filename.json
    • RUN wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O /cloud_sql_proxy && chmod +x /cloud_sql_proxy && export GOOGLE_APPLICATION_CREDENTIALS=/path/to/filename.json
  • fly ssh console --app app_name
  • tmux then ./cloud_sql_proxy -instances=projectID:region:instanceID=tcp:3306 so the exec will still run detached.
  • projectID:region:instanceID is the format for Connection name (not the ip)
  • caveat tho: it says in their page “Don’t use these instructions to set up the Cloud SQL Auth proxy for a production environment”. Atleast we tried :man_shrugging:

Thank you so much for helping me in this problem @greg. So now its working

1 Like

Good to hear it’s working. :slight_smile:

Yeah, from my read of that approach, that will indeed make the proxy work … but what if the vm restarts, or the proxy crashes (don’t know if that’s possible). I’m thinking you would still need some form of process manager running to keep it running. Else if it’s not running, database connections aren’t going to work. So I’ll leave that with you.

1 Like