Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mariadb connection client: Access denied for user (using password: NO) on mysql 8.0

mariadb-java-client throws access denied on mysql 8.0, but works on mysql 5.6 So I wonder if mariadb client is compatible with mysql 8.0

  1. in mysql, test users are set limit to hosts machine = %

  2. even tested on mysql 8.0's machine, same access denied error.


Caused by: java.sql.SQLException: Access denied for user 'user1'@'192.168.238.1' (using password: NO)
Current charset is UTF-8. If password has been set using other charset, consider using option 'passwordCharacterEncoding'

    Connection connection = DriverManager.getConnection("jdbc:mariadb://192.168.0.2:3306/test", "user1", "admin@123");
    Statement stmt = connection.createStatement();
    stmt.executeUpdate("CREATE TABLE a (id int not null primary key, value varchar(20))");
    stmt.executeUpdate("DROP TABLE a");
    stmt.close();
    connection.close();

Same user name and password on mysql 5.6 and 8.0

  1. Succeeded: 'mariadb-java-client', version: '2.3.0', mysql 5.6

  2. Failed: 'mariadb-java-client', version: '2.3.0', mysql 8.0

  3. Succeeded: 'mysql-connector-java', version: '8.0.13' mysql 8.0 (jdbc:mysql://....)

like image 291
Tonghua Avatar asked Jan 14 '19 19:01

Tonghua


People also ask

How do I fix access denied in MySQL?

To resolve the error, you must create a user with the following command: mysql> GRANT ALL ON *. * to user_name@localhost IDENTIFIED BY 'password'; Replace user_name with the user's username and password with the user's password.

Can't connect to MySQL server on MariaDB?

the server is either not running, or not running on the specified port, socket or pipe. Make sure you are using the correct host, port, pipe, socket and protocol options, or alternatively, see Getting, Installing and Upgrading MariaDB, Starting and Stopping MariaDB or Troubleshooting Installation Issues.

How do I fix MySQL error Access denied for user root localhost?

Use the ALTER USER command and change the authentication method to log into MySQL as root: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'insert_password'; This command changes the password for the user root and sets the authentication method to mysql_native_password.


1 Answers

MySQL 8 uses caching_sha2_password rather than mysql_native_password as of MySQL 5.7 (and MariaDB).

"caching_sha2_password, it is as of MySQL 8.0 the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password. This change affects both the server and the libmysqlclient client library:"

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

MariaDB's Java Connector does not yet implement this, but has a task assigned:

https://jira.mariadb.org/browse/CONJ-663

In order to connect to MySQL 8, you will have to use the Oracle connector, another connector that supports the change, or wait for MariaDB to implement.

like image 197
Ian Gilfillan Avatar answered Sep 18 '22 22:09

Ian Gilfillan