MySQL on ubuntu + MyXQL connection refused issues nightmare

I am trying to build my phoenix app on my server and facing a nightmarish issue because of the major platform differences with my dev machine.


mysqld  Ver 5.7.33-0ubuntu0.18.04.1 for Linux on x86_64 ((Ubuntu))


23:01:12.690 [error] MyXQL.Connection (#PID<0.382.0>) failed to connect: ** (DBConnection.ConnectionError) connection refused

very helpful! I know.

tried with ecto:// or mysql:// no luck. I have hardcoded the values into the config files still refused!

I know for a fact that MySQL is up and running, the user credentials are correct and verified several times. Scratching my head found this gem in the source code.

defmodule Ecto.Adapters.MyXQL do
  @moduledoc """
  Adapter module for MySQL.

  It uses `MyXQL` for communicating to the database.

  ## Options

  MySQL options split in different categories described
  below. All options can be given via the repository

  ### Connection options

    * `:protocol` - Set to `:socket` for using UNIX domain socket, or `:tcp` for TCP
      (default: `:socket`)
    * `:socket` - Connect to MySQL via UNIX sockets in the given path.

Would be useful if this was published in the hex docs but I can raise an issue for it later.

Although I am on unix for my dev machine and setup is completely different, it works with no problems.

so now I have hope! I pass in actual socket path with ecto://.....?socket=PATH still refused or mysql:// still refused

I have set the protocol still no luck

protocol: :tcp

I can’t really use iex -S because of the constant spamming of connection refused.

I see in the documentation about starting with the supervisor…

It's recommended to start MyXQL under supervision tree:

defmodule MyApp.Application do
  use Application

  def start(_type, _args) do
    children = [
      {MyXQL, username: "root", name: :myxql}

    Supervisor.start_link(children, opts)

which made me go WTF! Because everything worked till now I didn’t realise this required a ‘boot’ config till now.

This is beyond F’ed level up of configuration at this point but let me try.

Oh connection refused for my user, it did work and have an affect.

change to protocol to tcp… nope

it expects a goddamn socket and then I realized I have two different errors from different processes.

23:29:45.463 [error] MyXQL.Connection (#PID<0.404.0>) failed to connect: ** (MyXQL.Error) (1045) (ER_ACCESS_DENIED_ERROR) Access denied for user 'test'@'localhost' (using password: NO)
23:29:45.520 [error] MyXQL.Connection (#PID<0.379.0>) failed to connect: ** (DBConnection.ConnectionError) connection refused

I hardcode the password and still, shoddy shit won’t connect.

      {MyXQL, username: "user", name: :myxql, socket: "/var/run/mysqld/mysqld.sock", password: "test", database: "test"}

I put all the values into the shoddy MyXQL supervisor tree and drop from config still refused.

Does anyone have any advice? Which bit am I missing here?

The only reason I am not using Postgres is the bigger footprint on the xxx-small server I have.

That is an old version of mysqld… are You sure You are using the same authentication plugin between mysqld and myxql?

It is the default that comes with the ubuntu stable branch, not that old.

I have partially solved the issue.

  1. Created a brand new phx barebones app with mysql on the server to use iex -S
  2. used root credentials… yup
  3. directly working on the dev config and specifying the socket path in Repo config
  4. now I have connection
  5. switch to user credentials
  6. connection stable.
  7. create database succeeds

now next step is to get it to use protocol tcp because socket path changes from server to server!

to see if connection url is achievable

I thought the latest stable was Ubuntu Server 20.04 LTS…

oh you mean the ubuntu not mysql. well I have production stuff on it and has breaking changes, not upgrading anytime soon. I have got 10 years to go.

it is not going to work with connection string for this driver.

No luck of :tcp it is just ignored.

# Configure your database
config :test, Test.Repo,
  database: "test",
  socket: "/var/run/mysqld/mysqld.sock",
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

Isn’t this the content you’re referring to?

I came across this MyXQL — MyXQL v0.4.5 and dived into it’s code not the Ecto.Adapter one, you are right, it is documented on that one!