I cannot reproduce this, if you could create a sample project that I can run locally I’m happy to take a look.
wojtekmach
Hex Core Team
bodhilogic
bodhilogic
I added an IO.inspect(system_opts) to the repo.ex, after the system_opts list has been set, to confirm that it is picking up what I set and this is the result (it is picking up the correct values):
PORT=4005 MIX_ENV=prod mix phx.server
[hostname: "localhost", username: "web", password: "something"]
13:57:42.968 [error] MyXQL.Connection (#PID<0.266.0>) failed to connect: ** (MyXQL.Error) (1045) (ER_ACCESS_DENIED_ERROR) Access denied for user 'root'@'localhost' (using password: YES)
0
bodhilogic
If I set the system_opts to pick up the root DB user then all is well!?
Using mysql I double-checked that the ‘web’ DB user can select, create, update on a table, and he/she can.
I guess the moral of the story is don’t try to use MyXQL with any DB user other than root.
0
cmkarlsson
That is the wrong conclusion. MyXQL should work with any user and you should definitely not use root to login into MySQL. There is some other error here.
Start from the very beginning, it is a very good place to start.
How about testing MyXQL directly:
$ iex -S mix
iex(1)> {:ok, pid} = MyXQL.start_link([username: "web", password: "something", database: "yourdb", hostname: "localhost"])
{:ok, #PID<0.186.0>}
iex(2)> MyXQL.query(pid, "SELECT count(*) FROM your_table")
{:ok,
%MyXQL.Result{
columns: ["count(*)"],
connection_id: 30,
last_insert_id: nil,
num_rows: 1,
num_warnings: 0,
rows: [[516]]
}}
This cuts out anything but MyXQL and tests the parameters directly. I just tested this with a new mix repo (elixir 1.9.1 and erlang 22.1) MyXQL 0.3.0 and MySQL 5.7.28.
Dependency tree of the test repo (because MyXQL depends on DBConnection))
xyxy
└── myxql ~> 0.3.0 (Hex package)
├── db_connection ~> 2.0 (Hex package)
│ └── connection ~> 1.0.2 (Hex package)
└── decimal ~> 1.6 (Hex package)
4
wojtekmach
Hex Core Team
I don’t think that’s a fair characterisation. A lot of work went through testing myxql against different combinations of authentication methods, server defaults, ssl, and more, and all that is done through authenticating as different users.
Yes, that’s a very good suggestion. I got caught up thinking it might be configuration issue, but the issue can be something else. In v0.3.0 there’s a bug fix for mysql_native_password authentication (under certain circumstances) and that can be a culprit too.
3
bodhilogic
I didn’t mean any ill-will towards MyXQL - sorry if you felt maligned. I certainly couldn’t have managed to do what the MyXQL team have done - thank you for making it available to the community!
I found a solution that works and allows me to use encrypted environment variables:
defmodule Pcafe.Repo do
use Ecto.Repo,
otp_app: :pcafe,
adapter: Ecto.Adapters.MyXQL
@doc """
Dynamically loads the repository url from the
DATABASE_URL environment variable.
"""
# def init(_, opts) do
# {:ok, Keyword.put(opts, :url, System.get_env("DATABASE_URL"))}
# end
def init(_, opts) do
{:ok, build_opts(opts)}
end
defp build_opts(opts) do
the_port = System.get_env("PORT") # evaluates to nil during development
prefix =
case the_port do
"4005" -> "LOCAL" #"FOREIGN"
nil -> "LOCAL" #Sensible default
_ -> "LOCAL"
end
secret = System.get_env("DB_SECRET")
the_host = Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_PTRACK_DB_HOST"), secret)
the_user = Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_PTRACK_DB_USER_NAME"), secret)
the_pass = Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_PTRACK_DB_USER_PASSWORD"), secret)
system_opts = [
hostname: the_host,
username: the_user,
password: the_pass
# hostname: System.get_env("HOST"),
# username: System.get_env("USER"),
# password: System.get_env("MYSQL_PWD")
]
Keyword.merge(opts, system_opts)
end
end
0
bodhilogic
NobbZ
bodhilogic
Where in my code would I place this code?
MyXQL.start_link([...])
Never mind - found it in the hexdocs.
0