Mariaex mysql 8+

Questions / Help
1

Hello everyone,

Anyone having success using mariaex with mysql 8.0.x?

There is an open issue, but adding to my.cnf …

[mysqld]
default-authentication-plugin=mysql_native_password

… does not solve the problem. I still got this error when trying to create the repo.

12:27:42.618 [error] GenServer #PID<0.198.0> terminating
** (Mariaex.Error) (1251): Client does not support authentication protocol requested by server; consider upgrading MySQL client
    (db_connection) lib/db_connection/connection.ex:163: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
Last message: nil
State: Mariaex.Protocol
** (Mix) The database for Ilox.Repo couldn't be created: (1251): Client does not support authentication protocol requested by server; consider upgrading MySQL client

The versions I use are

MacOSX High Sierra
Elixir 1.7.3 / OTP21
Phoenix 1.4 rc-01
Mysql 8.0.12
Ecto 2.2.1
Mariaex 0.8.4

Thanks for taking time

2 Likes
2

Could you check what’s the authentication plugin for the user you’re trying to log in as?

E.g.:

mysql> select user,plugin from mysql.user;
+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| mariaex_user     | mysql_native_password |
...

The default auth plugin for mysql 8.0 is caching_sha2_password and so if the user was created before you changed the default auth plugin that’s probably the issue.

5 Likes
3

Oh, thanks… I got caching_sha2_password for my user…

mysql> select user,plugin from mysql.user;
+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| elixir           | caching_sha2_password |
| mysql.infoschema | caching_sha2_password |
| mysql.session    | caching_sha2_password |
| mysql.sys        | caching_sha2_password |
| root             | caching_sha2_password |
+------------------+-----------------------+

I will recreate a new user :slight_smile:

2 Likes
4

That was it…

droping the user, and recreate allows me to create the Repo :slight_smile:

Thank You @wojtekmach

4 Likes
5

Hi, I’ve read the issue on GitHub and the previous comments of this thread, but this is still not working for me.

Also I can’t really understand what the solution suggested in the comments above was. Should the plugin be changed to caching_sha2_password? Or to mysql_native_password? What worked?

At the moment, I’m trying to connect as root locally, and these are my users:

mysql> select host,user,plugin from mysql.user;
+------------------+------------------+-----------------------+
| host             | user             | plugin                |
+------------------+------------------+-----------------------+
| 127.0.0.1        | root             | caching_sha2_password |
| ::1              | root             | caching_sha2_password |
| localhost        |                  | caching_sha2_password |
| localhost        | mysql.infoschema | caching_sha2_password |
| localhost        | mysql.session    | caching_sha2_password |
| localhost        | mysql.sys        | caching_sha2_password |
| localhost        | root             | mysql_native_password |
| mylaptop.local   |                  | caching_sha2_password |
| mylaptop.local   | root             | caching_sha2_password |
+------------------+------------------+-----------------------+

So far I’ve altered, dropped and recreated root@localhost so that it had mysql_native_password or caching_sha2_password. Nothing worked.

6

You should use mysql_native_password, if You did wrong, as I did, just drop the user and recreate.

BTW I did not use root, but elixir… and configured config files with this user. How do You create your user?

7

Hi, thanks for getting back to this.

The user was created ages ago when MySQL was first installed on this dev machine. Yesterday I upgraded MySQL to 8.0 and I ran the mysql upgrade command. Mariaex t was connecting fine before that.

I have already dropped and recreated 'root'@'localhost', but it didn’t work :frowning:. Yes, I have invoked FLUSH PRIVILEGES;.

Can you please share the commands that you used to drop and recreate the user? I’m more a Postgres person myself, and I might have done this the wrong way.

8

Is the host you connect from recognized as localhost? Maybe its bare IP or mylaptop.local. I have no clue how MySQL does this host thing.

If I were you, I’d alter all users.

Or explicitely create a non root user for the application, that has known to work settings.

9

As mentionned in the OP, the issue is related to MySQL 8.x

If You have recently upgraded, then You need to change the auth settings, and recreate users.

10

I think the syntax is

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
mysql> FLUSH PRIVILEGES;

The second command gives rights… You might change

  • newuser
  • password
  • ALL
  • *.*
1 Like
11

Thanks @kokolegorille.

I’ve now tried again and I’ve run exactly those commands, creating a new user. I’ve then configured Ecto with the new username, deleted the compiled beam artifacts from _build, and then tried again.

I am still getting the same error:

** (Mariaex.Error) (1251): Client does not support authentication protocol requested by server; consider upgrading MySQL client
    (db_connection) lib/db_connection/connection.ex:84: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
12

I’ve resolved by downgrading to MySQL 5.7.
Mariaex with MySQL 8.0 was just not working.

1 Like