~/.my.cnf values should not override phoenix config!

[seeekr]

Hi there,

first off, thanks for building this. Here's an issue I've found when creating and trying to run my first phoenix/elixir/maria app:

Created standard app with mix phoenix.new --database mysql app, didn't change anything, latest versions of Elixir, Erlang, Phoenix (just installed). Fetched deps and compiled.

Created a mysql server with empty root password. Tried to run mix ecto.create, but got error from the mysql server that I'm using a password to connect to the instance, which was clearly (and by default) configured to be empty.

** (Mix) The database for App.Repo couldn't be created: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

After consulting with folks in #elixir slack we couldn't find any solution. In the end, after more investigation, it turned out that something (can't tell whether mariaex or phoenix_ecto or ecto, since I'm a newbie) was reading my ~/.my.cnf which had a default password configured and using that to override my locally-configured dev.exs values!

This is super unexpected and, I believe, the wrong thing to do. The ~/.my.cnf is a global defaults file that should be used for values which are otherwise not configured, and should never override any options more specifically configured within a project.

Having said that I assume that this is simply a bug that occurs because a specifically configured empty password is assumed to be a non-specified option and is then overwritten by the value from the global .my.cnf.

If desired I can, with a little guidance, work on a PR for solving this.

Thanks,
Denis

[surik]

Provide more info, please. Do you run db via docker? What about MYSQL_ALLOW_EMPTY_PASSWORD? Can you show your config.exs and dev.exs?

[seeekr]

DB running in Docker for Mac. Elixir stuff on the host directly.

docker-compose.yml:

version: '3'
services:
  db:
    image: "mariadb:10.2"
    ports:
      - "3309:3306"
    environment:
      - MYSQL_ALLOW_EMPTY_PASSWORD=true

config.exs:

# This file is responsible for configuring your application
# and its dependencies with the aid of the Mix.Config module.
#
# This configuration file is loaded before any dependency and
# is restricted to this project.
use Mix.Config

# General application configuration
config :backend_exs,
  ecto_repos: [BackendExs.Repo]

# Configures the endpoint
config :backend_exs, BackendExs.Endpoint,
  url: [host: "localhost"],
  secret_key_base: "Zz7P1GlGuW/HJKG/3Wuf6TBoFnO70OB9qDx1FykOBrT0y/M5bqPQnWGjPIlxTvTP",
  render_errors: [view: BackendExs.ErrorView, accepts: ~w(html json)],
  pubsub: [name: BackendExs.PubSub,
           adapter: Phoenix.PubSub.PG2]

# Configures Elixir's Logger
config :logger, :console,
  format: "$time $metadata[$level] $message\n",
  metadata: [:request_id]

# Import environment specific config. This must remain at the bottom
# of this file so it overrides the configuration defined above.
import_config "#{Mix.env}.exs"

dev.exs:

use Mix.Config

# For development, we disable any cache and enable
# debugging and code reloading.
#
# The watchers configuration can be used to run external
# watchers to your application. For example, we use it
# with brunch.io to recompile .js and .css sources.
config :backend_exs, BackendExs.Endpoint,
  http: [port: 4000],
  debug_errors: true,
  code_reloader: true,
  check_origin: false,
  watchers: [node: ["node_modules/brunch/bin/brunch", "watch", "--stdin",
                    cd: Path.expand("../", __DIR__)]]


# Watch static and templates for browser reloading.
config :backend_exs, BackendExs.Endpoint,
  live_reload: [
    patterns: [
      ~r{priv/static/.*(js|css|png|jpeg|jpg|gif|svg)$},
      ~r{priv/gettext/.*(po)$},
      ~r{web/views/.*(ex)$},
      ~r{web/templates/.*(eex)$}
    ]
  ]

# Do not include metadata nor timestamps in development logs
config :logger, :console, format: "[$level] $message\n"

# Set a higher stacktrace during development. Avoid configuring such
# in production as building large stacktraces may be expensive.
config :phoenix, :stacktrace_depth, 20

# Configure your database
config :backend_exs, BackendExs.Repo,
  adapter: Ecto.Adapters.MySQL,
  username: "root",
  password: "",
  database: "backend",
  hostname: "127.0.0.1",
  port: 3309,
  pool_size: 10

In my ~/.my.cnf if I comment out

[client]
password=somepassword

the password line, then things work as expected. With a password in there set I get the error I described.

[surik]

Are you sure that with setting default password in ~/.my.cnf you can start DB? Can you check the logs for this line MySQL init process failed.?

[seeekr]

Why would I not be able to start the MySQL server if I only have [client] values in my ~/.my.cnf? (As well as the server being started basically on a different host (Docker for Mac is running on a hypervisor, i.e. basically a VM, not on the host).) They do not affect each other.

[surik]

I tried to run the same setup but stuck here: https://github.com/docker-library/mariadb/blob/master/10.2/docker-entrypoint.sh#L100. This is the reason why I ask.
For me, it looks that it is not possible to use this docker image with default password for the client in my.cnf.

[seeekr]

Oh, you're trying to reproduce the issue? You need nothing else but Docker and Docker Compose installed, then use the docker-compose.yml, maybe modify the port in there if you don't like the 3309, and execute docker-compose up -d and the server will come up.

OK, and just to clarify:
What my local ~/.my.cnf does it set a default password for the client tools I'm using, like the mysql command. So let's say on my local host for development I use lots of MySQL instances where I just set some simple local/dev password, and I don't want to repeat myself by typing it over and over whenever I quickly want to connect to MySQL from the command-line, without having to use any other more specialized or complex tools (like docker exec or some GUI tool).

I'm not using this file for configuring the server at all, and it would not work for that. Yes, if I install a MySQL server on a host (or in a Docker file) and in that environment I put a ~/.my.cnf file in the MySQL user's home, then I think a MySQL server would indeed read any [server] or [mysqld] section and apply configuration from there. But, again, this is not the case here.

I'm not sure if this explanation was necessary, I just wanted to make sure the issue is absolutely clear. So there's really almost nothing to do to reproduce it. Docker Compose file as above, then create new standard phoenix project with mysql database option and then have a .my.cnf in your home folder where you set a password value in the client section. That's it.

[surik]

Now I see. Check this https://github.com/elixir-ecto/ecto/blob/v2.1/lib/ecto/adapters/mysql.ex#L139. Ecto 2.1 uses mysql command to setup database. So the command looks like MYSQL_PWD="" mysql --user root --host localhost --port 3306 --protocol=tcp --silent --execute ... with new phoenix application.

[seeekr]

Ok, that's unexpected. I'm wondering why they do it that way. It would seem logical to me to just use mariaex and consistently employ that to talk to MySQL, no matter when or where. MySQL cli tools should also not be required... Though I see that they provide structure_dump/2 and in there implicitly rely on how mysqldump works... It wouldn't be very hard though to just implement that as a bunch of queries of the form SHOW CREATE TABLE ..., it seems.

To me it looks like an an open task that noone has taken care of yet -- move away from MySQL commandline tools and just use a library (i.e. mariaex) instead.

Your thoughts?

[seeekr]

And about the actual password issue here: Why do they have this weird password handling there? Why don't they pass the password as they pass all other options as well? Why pass it as an env var? So that MySQL won't complain about passing password on the command line? (https://github.com/elixir-ecto/ecto/blob/v2.1/lib/ecto/adapters/mysql.ex#L301)

[surik]

To me it looks like an an open task that noone has taken care of yet -- move away from MySQL commandline tools and just use a library (i.e. mariaex) instead.

Already fixed and will be available in Ecto 2.2, see elixir-ecto/ecto#1939

[seeekr]

Alright, you guys are way ahead of me :D

[surik]

So close this issue because it is not related to mariaex.