Fixed MySQL support for Phoenix and Ecto

Fresh Produce
1

A few weeks ago one of our customers, @iagoangelimc , noticed that something felt off connecting their Phoenix app to their deployed MySQL instance.

We brainstormed a lot about what could have happened and with help of @jstiebs we found out this was actually a MyXQL issue, that’s the library that Ecto uses behind the scenes.

Passing settings such as socket_options: [:inet6] to enable IPv6 from Phoenix to MySQL wasn’t being properly set then @iagoangelimc quickly sent a fix to the repo and it’s now merged under release v0.7.0 of MyXQL!

Finding the Bug!

The error showed nothing useful, it just exited:

root@MACHINE_ID:/app/bin# /app/bin/server
18:53:53.539 [info] Running MyAppWeb.Endpoint with Bandit 1.5.2 at :::8080 (http)
18:53:53.541 [info] Access MyAppWeb.Endpoint at https://app-name.fly.dev
18:53:53.546 [notice] Application my_app exited: shutdown
Kernel pid terminated (application_controller) ("{application_terminated,app_name,shutdown}")

Crash dump is being written to: erl_crash.dump...done

This was a very though one! We noticed we were able to:

  • Connect non-elixir apps to the database through fly proxy -a mysql-db-app 3306:3306 and WireGuard app
  • Connect local running phoenix app to the database through fly proxy -a mysql-db-app 3306:3306 and WireGuard app.
  • Connect MySQL command line and DBeaver through fly proxy -a mysql-db-app 3306:3306 and WireGuard app.

But we couldn’t:

  • Connect the deployed phoenix app using DATABASE_URL.
  • Adding :inet6 didn’t really fix, just made the app shutdown.
  • Couldn’t migrate the app for the same reasons as above.

To experiment more we changed these lines on the Dockerfile:

# start a new build stage so that the final image will only contain
# the compiled release and other runtime necessities
- FROM ${RUNNER_IMAGE}
+ FROM ${BUILDER_IMAGE}

...

- COPY --from=builder --chown=nobody:root /app/_build/${MIX_ENV}/rel/app_name ./
+ COPY --from=builder --chown=nobody:root /app/ /old-app

...

- CMD ["/app/bin/server"]
+ CMD ["sleep", "infinity"]

We also removed MyApp.Repo from the application.ex file so it didn’t start the DB immediately.

That way we fly deployed the app and then using the following we were able to open the app “in development mode” but using Fly infra:

$ fly ssh console
$ cd /old-app
$ iex -S mix

We tried to start the repo manually:

iex(1)> MyApp.Repo.start_link
{:ok, #PID<0.636.0>}
** (EXIT from #PID<0.635.0>) shell process exited with reason: shutdown

We checked envs, nothing unusual:

iex(2)> Application.get_all_env(:app_name)
[
  {:ecto_repos, [MyApp.Repo]},
  {MyAppWeb.Endpoint,
   [
     adapter: Bandit.PhoenixAdapter,
     render_errors: [
       formats: [html: MyAppWeb.ErrorHTML, json: MyAppWeb.ErrorJSON],
       layout: false
     ],
     pubsub_server: MyApp.PubSub,
     live_view: [signing_salt: "KtNb/WiY"],
     cache_static_manifest: "priv/static/cache_manifest.json",
     url: [
       host: "app-name.fly.dev",
       port: 443,
       scheme: "https"
     ],
     http: [ip: {0, 0, 0, 0, 0, 0, 0, 0}, port: 8080],
     secret_key_base: "..."
   ]},
  {MyApp.Repo,
   [
     url: "REAL URL HERE",
     pool_size: 10,
     socket_options: [:inet6]
   ]},
  {:generators, [timestamp_type: :utc_datetime]},
  {:dns_cluster_query, nil},
  {MyApp.Mailer, [adapter: Swoosh.Adapters.Local]}
]

Then @jstiebs asked us to try to start MyXQL directly using real credentials:

iex> {:ok, pid} = MyXQL.start_link(username: "username", ...)
{:ok, #PID<0.455.0>}
** (EXIT from #PID<0.454.0>) shell process exited with reason: killed

Bingo. We found the issue! Then Iago sent the fix to MyXQL:

-        socket_options:
-          Keyword.merge([mode: :binary, packet: :raw, active: false], opts[:socket_options] || []),
+        socket_options: (opts[:socket_options] || []) ++ @sock_opts,

And the rest is history :slight_smile:

5 Likes
2

Thanks for help me debug this <3

1 Like