Trouble connecting to Managed Digital Ocean MySQL Database

Hi!

I’m trying to connect a Phoenix (1.5) app to a Managed Digital Ocean MySQL Database but I’m receiving the following error when I try to boot up my application

[error] GenServer #PID<0.625.0> terminating
** (RuntimeError) Connect raised a FunctionClauseError error. The exception details are hidden, as
they may contain sensitive data such as database credentials.

    (mariaex 0.8.4) lib/mariaex/protocol.ex:1006: Mariaex.Protocol.password/3
    (mariaex 0.8.4) lib/mariaex/protocol.ex:207: Mariaex.Protocol.handle_handshake/3
    (mariaex 0.8.4) lib/mariaex/protocol.ex:166: Mariaex.Protocol.handshake_recv/2
    (db_connection 1.1.3) lib/db_connection/connection.ex:135: DBConnection.Connection.connect/2
    (connection 1.0.4) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 3.13) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
Last message: nil
State: Mariaex.Protocol
[error] GenServer #PID<0.611.0> terminating

Things of note:

  • The version of Mariaex I’m using is 0.8.4
  • I am able to connect to the database from the server
  • I have a Laravel application on the same server which connects to the database ok
  • I’m using a MySQL user with Legacy – MySQL 5.x password encryption

I have tried setting DATABASE_URL="mysql://<USER>:<PASSWORD>@<PRIVATE_HOSTNAME>:25060/<DB_NAME> as well as using this in my prod.secret.exs

config :appname, Appname.Repo,
  adapter: Ecto.Adapters.MySQL,
  username: "<USERNAME>",
  password: "<PASSWORD>",
  database: "<DB_NAME>",
  hostname: "<PRIVATE_HOSTNAME>",
  port: 25060,
  pool_size: 15,
  ssl: true,
  show_sensitive_data_on_connection_error: true

I have the same set up working on a staging server with the one difference being that the MySQL database is on the same server.

I get this error both from firing up mix phx.server and from using mix release. Even when I try with the environment set to dev I’m unable to see more detailed logs.

I feel like I’ve hit a brick wall with this. Has anyone ran into anything like this before? Any advice for if I’ve missed something?

Seems like the DB doesn’t allow external connections?

Did you try connecting with the normal CLI mysql client from your machine to the server?

Thanks for the suggestion :slight_smile:

The DB only allows connections from the private network it’s located in.

I can connect to it via SSHing to the server and additionally with the separate Laravel app that’s on the same server so I know it’s not a firewall issue.

Well, not sure Elixir’s libraries will allow you to initiate a tunnelled SSH connection to your server.

You should make an SSH tunnel to your server and then you will have a localhost:<your_port> listener on your machine which will transparently re-transmit data to the remote machine (in this case the MySQL server). And then point your Elixir app to that local tunnel address.

Although looking at your error I’d guess you shouldn’t use mariaex anymore. MyXQL is much more recent and is officially supported by Ecto’s maintainers.

So maybe you should try that first.

1 Like

When you changed your secrets config, did you rebuilt the release? Do you perhaps have something in your release.exs that would override what you have in your Secrets?

When you use mix phx.server instead of a release, do you set your MIX_ENV correctly?

I don’t believe it’s possible to SSH to the MySQL database server - this is a managed service where you add the external database credentials provided by Digital Ocean so I don’t think I’ll be able to test that.

I was not aware of MyXQL or the state of Mariaex to be honest! I will check this library out, thanks!

Thanks for the suggestions.

I did rebuild the release and I ran MIX_ENV=prod mix phx.server. I will double check my release.exs when I’m back at my computer :slight_smile:

Thank you both - I managed to get it working with @dimitarvp suggestion of changing to the myxql adapter.