Vvv: Permissions error [1698 (28000)] attempting to access mysql root from shell without sudo

Created on 25 May 2020 · 15Comments · Source: Varying-Vagrant-Vagrants/VVV

Current Behavior

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Expected Behavior

MariaDB [(none)]>

Steps to Reproduce (for bugs)

vagrant destroy
vagrant box update
vagrant up --provision

then:

$ vagrant ssh
Running additional site vagrant customfile at /Users/paul/.dev/vagrant-local/www/paulschreiber/provision/Customfile

    ✧ Hello Again! ✧         ✧            ✧    
✧   ▄▀▀▀▄▄▄▄▄▄▄▀▀▀▄   ✧  __ __ __ __
   ✧█▒▒░░░░░░░░░▒▒█      \ V\ V\ V /
 ▄▄  █░░█░░░░░█░░█ ✧      \_/\_/\_/
█░░█  █░░░▀█▀░░░█     ✧              ✧          
█░░█  ▀▄░░░░░░░▄▀ ✧      Have a nice day :)


vagrant@vvv:~$ mysql
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
vagrant@vvv:~$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.4.13-MariaDB-1:10.4.13+maria~bionic mariadb.org binary 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)]>

Your Environment

macOS 10.14.15
VirtualBox 6.0.20
Vagrant 2.29
VVV develop branch (7 May)

bug

Source

paulschreiber

Most helpful comment

Out of the box It appears that to use the root user on CLI, you need to sudo mysql

https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04

So it doesn't matter if the credentials are correct, it's requiring sudo by design.

Additional steps will be needed if we want to undo this

Ask Ubuntu
can't login as mysql user root from normal user account in ubuntu 16.04
I just installed Ubuntu 16.04 LTS along with the packages php, mariadb and nginx. I ran mysql_secure_installation and changed the root password. Now when I try to login to mysql using the root acc...

tomjn on 25 May 2020

🎉1 👍1

All 15 comments

I'm surprised this ever worked for you, the root user has the password root, I did not expect mysql or sudo mysql to work for you. It says you have the wrong credentials because no credentials were provided

Does this work with the correct credentials?

mysql -uroot -proot

Also 🤔

Running additional site vagrant customfile at /Users/paul/.dev/vagrant-local/www/paulschreiber/provision/Customfile

tomjn on 25 May 2020

The Customfile contains only shared folders.

I expected this to work because of .my.cnf:

$ cat .my.cnf 
# Custom MariaDB/MySQL configuration to save the root user's password for both client
# and mysqladmin access. This allows us to use `mysql -u root` to access
# MySQL rather than having to use a password.
[client]
user     = root
password = root

[mysqladmin]
user     = root
password = root

Supplying the password also fails:

vagrant@vvv:~$ mysql -uroot -proot
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
vagrant@vvv:~$ sudo mysql -uroot -proot

paulschreiber on 25 May 2020

hmmm, when MariaDB first gets installed, the root user has no password and the intention is that you then manually set it, which is a problem with provisioners as the second time you run them it can't just set the password. Here's the code that tries to do that:

https://github.com/Varying-Vagrant-Vagrants/VVV/blob/develop/provision/provision.sh#L704-L732

Does that section of your main provisioner output look okay? Look for * Checking the root user password is root onwards in provisioner-main.log

GitHub
Varying-Vagrant-Vagrants/VVV
An open source Vagrant configuration for developing with WordPress - Varying-Vagrant-Vagrants/VVV

cc @Mte90 who worked on this for a while

tomjn on 25 May 2020

From my log:

 * Checking the root user password is root
 * The root password is the expected value
 * Restarting mysql service

 * No custom MySQL scripting found in database/init-custom.sql, skipping...
 * Initial MySQL prep...
 * Starting MariaDB Database Import

paulschreiber on 25 May 2020

Out of the box It appears that to use the root user on CLI, you need to sudo mysql

https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04

So it doesn't matter if the credentials are correct, it's requiring sudo by design.

Additional steps will be needed if we want to undo this

Ask Ubuntu
can't login as mysql user root from normal user account in ubuntu 16.04
I just installed Ubuntu 16.04 LTS along with the packages php, mariadb and nginx. I ran mysql_secure_installation and changed the root password. Now when I try to login to mysql using the root acc...

tomjn on 25 May 2020

🎉1 👍1

Can we close this ticket?

Mte90 on 8 Jun 2020

Nope! This used to work and should work again.

paulschreiber on 8 Jun 2020

Uhm I tried in my VVV instance just the mysql command and worked.
So I am wondering what is broken in that instance.
I see that you tried with a vagrant destroy and those changes was added in april.

Maybe the sql query on the previous check of the root password didn't worked.

Mte90 on 8 Jun 2020

It worked for me but not on a fresh provision. Old VMs don’t suffer this
issue only newer ones

On Mon, 8 Jun 2020 at 16:02, Daniele Scasciafratte notifications@github.com
wrote:

Uhm I tried in my VVV instance just the mysql command and worked.
So I am wondering what is broken in that instance.
I see that you tried with a vagrant destroy and those changes was added in
april.

Maybe the sql query on the previous check of the root password didn't
worked.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/Varying-Vagrant-Vagrants/VVV/issues/2169#issuecomment-640686145,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAAOLZYSYPZPJJRY2ES4NTLRVT4PHANCNFSM4NJRLU6Q
.

tomjn on 8 Jun 2020

Ok tested with a new VM and the problem it's there.
I will do some tests.

Mte90 on 8 Jun 2020

Ok the problem is MAriaDB 10.4, we did the bump of the version and https://github.com/Varying-Vagrant-Vagrants/VVV/blob/develop/provision/provision.sh#L723 it is not possible anymore.
I am looking for a new SQL query that will fix the problem.

GitHub
Varying-Vagrant-Vagrants/VVV
An open source Vagrant configuration for developing with WordPress - Varying-Vagrant-Vagrants/VVV

Mte90 on 8 Jun 2020

I'm working on it sorry for not notifying.

El El lun, 8 jun. 2020 a la(s) 14:52, Daniele Scasciafratte <
[email protected]> escribió:

Ok tested with a new VM and the problem it's there.
I will do some tests.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/Varying-Vagrant-Vagrants/VVV/issues/2169#issuecomment-640778875,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/ABPHYNCT4BBLE4TJN743QSLRVUQM5ANCNFSM4NJRLU6Q
.

msaggiorato on 8 Jun 2020

This should be enough to set the password ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("root") from https://mariadb.org/authentication-in-mariadb-10-4/

Oh did you done the pr? Got some other conclusions? @msaggiorato

MariaDB.org
Authentication in MariaDB 10.4 — Understanding the Changes - MariaDB.org
MariaDB Server 10.4 came with a whole lot of Security related changes. Some of them are merely optimizations (like MDEV-15649), some improve existing features to be more robust (MDEV-15473, MDEV-7598) or convenient (MDEV-12835, MDEV-16266). […]