Unable to connect to mysql8 database remotely with mysql_native_password plugin

[doublej74]

I'm trying to connect through a wireguard vpn tunnel on debian 11 to a debian 11 mysql8 server. The user is created with mysql_native_password plugin.
I am able to connect with heidisql via wireguard tunnel to the server with same user account.
Since I'm able to connect via heidisql, I can confirm: wireguard tunnel is working correctly, mysql8 is listening on correct socket, user is created correctly.

With MyXQL I constantly get this error:
[error] MyXQL.Connection (#PID<0.516.0>) failed to connect: ** (DBConnection.ConnectionError) (/tmp/mysql.sock) connection refused - :econnrefused

on debian 11, mysql process on the server is actually running on /var/run/mysqld/mysqld.sock. However if I specify this socket in the config, it will give me back:
[error] MyXQL.Connection (#PID<0.526.0>) failed to connect: ** (DBConnection.ConnectionError) (/var/run/mysqld/mysqld.sock) no such file or directory - :enoent

config I use:
db_config = [
name: :myxql,
hostname: "192.168.1.1", # --> endpoint IP of the VPN tunnel, MySQL8 is listening on this IP.
port: 3306,
database: "mydata",
username: "mydata_user",
pool_size: 10,
password: "blablablabla123#!"
]
additional config line for specifying socket: socket: "/var/run/mysqld/mysqld.sock"

I've spend almost 2 days on troubleshooting every single component in this setup. but after eliminating everything, I just came to the conclusion it must be something in MyXQL library or dependencies, that does not allow the remote connection.

The documentation is not really helpful in troubleshooting the above error messages. It would be handy to see where in the process something goes wrong instead of just connection refused.

]

[greg-rychlewski]

Usually it is ... /mysql.sock. Are you sure there is no mistake here:

/var/run/mysqld/mysqld.sock.

[greg-rychlewski]

Another thing is that you are specifying both the unix socket and IP/port. You only need one or the other and socket takes precedence.

If you specify a unix socket it will look for that file locally and try to use it. Since you are saying it's on a remote machine this might be where your issue is, unless you mean to say it should appear as if it's local to you. But if this is not the case you can just remove the :socket config and it will use the IP/port.

[jjanviers]

So with the following config: (= config without the socket specified)

db_config = [
name: :myxql,
hostname: "192.168.1.1", # --> endpoint IP of the VPN tunnel, MySQL8 is listening on this IP.
port: 3306,
database: "mydata",
username: "mydata_user",
pool_size: 10,
password: "blablablabla123#!"
]

I get this error:
[error] MyXQL.Connection (#PID<0.516.0>) failed to connect: ** (DBConnection.ConnectionError) (/tmp/mysql.sock) connection refused - :econnrefused

So, does this mean that although IP/Port is specified it still tries to reach the local mysql.sock?

[josevalim]

It seems your configuration is not being applied, yes. Can you provide a more complete snippet?

[wojtekmach]

You can use this as a template: https://github.com/wojtekmach/mix_install_examples/blob/main/myxql.exs :)

[greg-rychlewski]

One other thing, if you are setting protocol: :socket somewhere it will also cause the issue you are seeing. In that case the fix is to not specify the :protocol option.

[jjanviers]

Sorry, I was working on another project. Now get back to this problem again:

My application.ex:

defmodule MyApp.Application do
  @moduledoc false

  use Application
  require Logger

  @impl true
  def start(_type, _args) do

    db_config = [
      name: :myxql,
      hostname: "[IP address of database server]",
      port: 3306,
      database: "[name of MySQL8 database]",
      username: "[username with all privileges on database]",
      pool_size: 10,
      password: "[password as set in user table with above user]"
    ]

    children = [
      # Start the Ecto repository,
      MyApp.Repo,
      # Starting MySQL Connector
      {MyXQL, db_config},
      # start settings cache
      MyApp.Store.SettingsStore,  # caching contents of one MySQL8 table to in memory ets db
      # Start the Telemetry supervisor
      MyAppWeb.Telemetry,
      # Start the PubSub system
      {Phoenix.PubSub, name: MyApp.PubSub},
      # Start the Endpoint (http/https)
      MyAppWeb.Endpoint,
      # for persistent sessions
      Pow.Store.Backend.MnesiaCache
  ]

    opts = [strategy: :one_for_one, name: MyWebApp.Supervisor]

    MyApp.InfluxConnector.ping()

    Supervisor.start_link(children, opts)
  end

  # Tell Phoenix to update the endpoint configuration
  # whenever the application is updated.
  @impl true
  def config_change(changed, _new, removed) do
    MyAppWeb.Endpoint.config_change(changed, removed)
    :ok
  end
end

config.exs:

import Config

config :myapp,
  ecto_repos: [MyApp.Repo, MyApp.RepoMyData]

config :logger, :debug_log,
  path: "/var/log/myapp_debug.log",
  level: :debug

config :logger, :info_log,
  path: "/var/log/myapp_info.log",
  level: :info

config :logger, :error_log,
  path: "/var/log/myapp_error.log",
  level: :error

# Configures Elixir's Logger
config :logger,
  level: :debug,
  backends: [
    :console,
    {LoggerFileBackend, :debug_log}
  ],
  format: "$time $metadata[$level] $message\n",
  metadata: [:request_id]

# Configures the endpoint
config :myapp, MyAppWeb.Endpoint,
  url: [host: "localhost"],
  render_errors: [view: MyAppWeb.ErrorView, accepts: ~w(html json), layout: false],
  pubsub_server: MyApp.PubSub,
  live_view: [signing_salt: "[omitted]"]

config :myapp, MyApp.InfluxConnector, [
  version: :v2,
  host: "[IP address of database server]",
  port: 8086,
  scheme: "http",
  org: "[influx database organisation name]",
  bucket: "[influx database bucket name]",
  http_client: MyApp.HTTPClient.Hackney,
  loggers: [{MyApp.Log.DefaultLogger, :log, []}],
  writer: MyApp.Writer.Line,
  json_decoder: {Jason, :decode!, [[keys: :atoms]]},
  json_encoder: {Jason, :encode!, []},
  auth: [
    method: :token,
    token: "[access token for influx database]"
  ],
  pool: 5
]

# Configures the mailer
config :myapp, MyApp.Mailer, adapter: Swoosh.Adapters.Local

# Swoosh API client is needed for adapters other than SMTP.
config :swoosh, :api_client, false

# Configure esbuild (the version is required)
config :esbuild,
  version: "0.14.41",
  default: [
    args:
      ~w(js/app.js --bundle --target=es2017 --outdir=../priv/static/assets --external:/fonts/* --external:/images/*),
    cd: Path.expand("../assets", __DIR__),
    env: %{"NODE_PATH" => Path.expand("../deps", __DIR__)}
  ]

# Use Jason for JSON parsing in Phoenix
config :phoenix, :json_library, Jason

config :mnesia, :dir, '/etc/myapp/mnesia'

config :myapp, :pow,
    repo: MyApp.Repo,
    user: MyApp.Users.User,
    users_context: MyApp.Users,
    web_module: MyAppWeb,
    #controller_callbacks: MyAppWeb.Pow.ControllerCallbacks,
    current_user_assigns_key: :current_user,
    session_key: "auth",
    credentials_cache_store: {
      Pow.Store.CredentialsCache,
      ttl: :timer.minutes(30),
      namespace: "credentials"
    },
    session_ttl_renewal: :timer.minutes(15),
    cache_store_backend: Pow.Store.Backend.MnesiaCache

config :cryptobot, :pow_assent,
  providers: [
    google: [
      client_id: "[omitted].apps.googleusercontent.com",
      client_secret: "[omitted]",
      strategy: Assent.Strategy.Google,
      authorization_params: [
        access_type: "offline",
        scope: "https://www.googleapis.com/auth/userinfo.email https://www.googleapis.com/auth/userinfo.profile"
      ],
      session_params: []
    ]
  ]

#config :myapp, :pow_assent, user_identities_context: MyApp.UserIdentities

config :ecto_sql, migration_module: MyApp.Repo.DataMigrations

config :nanoid,
  size: 12,
  alphabet: "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

config :dart_sass,
  version: "1.57.1",
  default: [
    args: ~w(css/app.scss ../priv/static/assets/app.css),
    cd: Path.expand("../assets", __DIR__)
  ]

[greg-rychlewski]

It looks to me like you are probably using your Repos to perform the queries but your configuration is only going to the MyXQL connection you started directly under your application supervisor. If this is the case you need to add all that configuration to your Repo: https://hexdocs.pm/ecto/Ecto.html#module-repositories.

Also if you only want to use your Repo you don't have to bother starting MyXQL like that. Ecto will do it automatically.

[dj74]

ecto repo is configured with another database (mnesia).
I don't want to use ecto for the mysql connection. That is why I start my own process with the config in the application file. I want to connect without using ecto.

Additional info: I rolled back to mysql5.7. Get the same error. One detail: I made a mistake in creation of the user account and validation went wrong, this generated another error. After fixing I still get the above error about mysql.sock. So, it seems that validation of the user account has already been taken place on the remote server, then this error pops up in the console/log.
So, still an unsolved mystery.

[dj74]

@josevalim I have the same thoughts, but I think I did the config by the book. That is why I started this issue.

[dj74]

Now with mysql5.7 I have some useful info in the server logs: Got an error reading communication packets. I'll research this first