CODEWITHSUNDEEP
mysqlmariadb
Carol.Kar
Carol.Kar

Reputation: 5345

Change MySQL authentication plugin to `caching_sha2_password`

I am running xampp 7.4.1-1 on an Ubuntu 18.4 LTS machine.

When trying to connect to a remote database I get the following warning:

Warning: mysqli::__construct(): Unexpected server response while doing caching_sha2 auth: 109 in /home/admin/Desktop/Code/project/src/testMySQLConnection.php on line 2

My file looks like the following:

<?php
$mysqli = new mysqli("127.0.0.1", "root", "myPassword", "test_db", 3306);

/* check connection */
if ($mysqli->connect_errno) {
    mysqli_debug("/home/admin/Desktop/client.trace");
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

I tried the following to fix the above warning:

admin@admin-VirtualBox:~$ /opt/lampp/bin/mysql -u root
Warning: World-writable config file '/opt/lampp/etc/my.cnf' is ignored
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 72
Server version: 10.4.11-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> 
MariaDB [(none)]> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------+-----------------------+--------+-----------+
| user | authentication_string | plugin | host      |
+------+-----------------------+--------+-----------+
| root |                       |        | localhost |
| root |                       |        | 127.0.0.1 |
| root |                       |        | ::1       |
|      |                       |        | localhost |
| pma  |                       |        | localhost |
+------+-----------------------+--------+-----------+
5 rows in set (0.002 sec)

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY ''' at line 1
MariaDB [(none)]>

However, I get the above error.

Any suggestions how to change the change MySQL authentication plugin to caching_sha2_password?

I appreciate your replies!

Upvotes: 2

Views: 2863

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

Review https://mariadb.com/kb/en/alter-user/

The syntax is one of the following forms:

ALTER USER 'root'@'localhost' IDENTIFIED BY '';

ALTER USER 'root'@'localhost' IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD';

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password USING '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD';

And a few other forms. But you're mixing BY with USING in a way that MariaDB doesn't recognize.

Upvotes: 2

Related Questions