About every month, I get the following error:
MySQL Error: : 'Access denied for user 'root'@'localhost'
I navigate to this StackOverflow question on how to deal with the error, and
then I get back to what I was doing.
The solution usually involves running an SQL statement from the mysql program started from the root Unix account:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
When you ALTER USER in MySQL, it's supposed to stay that way until you run another ALTER USER command.
On Ubuntu, the problem comes back after a while. One day, I'll try to connect to MySQL and I'll notice that the password doesn't work anymore. I didn't write any scripts that I forgot about. This will happen on a fresh Ubuntu install, whether from a CD, a Vagrantfile, or an AWS instance. Sometimes, whatever script is responsible for doing this will screw things up so badly, that the ALTER USER statement above no longer recovers from the problem.
There's nothing in any of the /etc/cron.* directories that should cause this.
There's nothing in /etc/mysql/* that obviously causes this to happen.
I grepped the entire filesystem and found that there are no uncompressed, unencrypted scripts that run ALTER USER queries to effect the reversion.
The only thing in /var/log that could be relevant is unattended-upgrades, but I don't see how upgrading MySQL should trash all its authentication settings every single time.
As a workaround, I resorted to brute force to stop Canonical's secret script:
chattr +i /var/lib/mysql/mysql/user.MYD
That makes it impossible to run ALTER USER commands, so whatever job Canonical has that is doing this should fail. But it also means I have to remember I did this if I ever need to make a legitimate change to this table.
Does anyone know what piece of software in Ubuntu is responsible for this behavior?
