bodhilogic

Can't connect using a non-root user

I want to establish a ‘web-safe’ user for my MySQL database (i.e. one that only has select, create, update and delete permissions).

If I drop my web-safe user from the database, I get this error which is totally expected because I have no such user in the database:

[error] MyXQL.Connection (#PID<0.287.0>) failed to connect: ** (MyXQL.Error) (1045) (ER_ACCESS_DENIED_ERROR) Access denied for user 'web'@'localhost' (using password: YES)

If I then go and create the ‘web-safe’ user in the database, even assigning the user with full grant privileges, I get a new error:

[error] MyXQL.Connection (#PID<0.275.0>) failed to connect: ** (MyXQL.Error) (1045) (ER_ACCESS_DENIED_ERROR) Access denied for user 'root'@'localhost' (using password: YES)

Say wha???

Why, now that it has the web@localhost user defined, is it attempting to connect using the root@localhost user?

My repo.ex file is the only place any connection information is established and I am definitely not attempting to use the root database user for any of my queries (i.e. override my connection settings).

If I have my app deliberately specify the root@localhost user for it’s connection information, then all works well but that totally defeats the purpose and reason for wanting to use a ‘web-safe’ database user.

Please help… I want to get this app deployed ASAP.
Thanks for your time and troubles.

25 comments

#ecto #myxql

0 3760 25

2019-11-26 00:53:10 UTC

First Post!

hauleth

Show us your config files.

Post #1

Most Liked

bodhilogic

OMG! You are going to shoot me!!!

I was about to place the start MyXQL under my application’s supervision tree and I found this:

  use Application

  def start(_type, _args) do
    # List all child processes to be supervised
    children = [
      {MyXQL, username: "root", name: :myxql},
      ...

I totally beg your forgiveness and thank you again for all your help!

Post #24

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)

Post #15

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.

Post #16