# Fixed MySQL support for Phoenix and Ecto

**URL:** <https://community.fly.io/t/fixed-mysql-support-for-phoenix-and-ecto/19983>\
**Category:** Fresh Produce\
**Tags:** elixir\
**Created:** [May 20, 2024, 3:03pm UTC](https://community.fly.io/t/fixed-mysql-support-for-phoenix-and-ecto/19983 "2024-05-20T15:03:09Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![lubien](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/lubien/32/41919_2.png) [@lubien](https://community.fly.io/u/lubien)\
**Post date:** [May 20, 2024, 3:03pm UTC](https://community.fly.io/t/fixed-mysql-support-for-phoenix-and-ecto/19983/1 "2024-05-20T15:03:09Z")

</div>

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!

> <https://github.com/elixir-ecto/myxql/pull/183>

> <https://github.com/elixir-ecto/myxql/commit/8a98bf24b496d5c0b1018996a18ed44c016598d7>

* * *

## Finding the Bug!

The error showed nothing useful, it just exited:

```auto
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:

```diff
# 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 deploy`ed the app and then using the following we were able to open the app “in development mode” but using Fly infra:

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

```

We tried to start the repo manually:

```auto
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:

```auto
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:

```auto
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:

```diff
- 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 🙂

---

<div class="post-metadata">

**Author:** ![iagoangelimc](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/iagoangelimc/32/50120_2.png) [@iagoangelimc](https://community.fly.io/u/iagoangelimc)\
**Post date:** [May 24, 2024, 11:43am UTC](https://community.fly.io/t/fixed-mysql-support-for-phoenix-and-ecto/19983/2 "2024-05-24T11:43:01Z")

</div>

Thanks for help me debug this \<3