I've recently upgraded a system from Ubuntu 14.04 to 16.04 (mysql Ver 15.1 Distrib 10.0.36-MariaDB) and noticed root can get in without a password eg:
mysql -u root is empty.
Tried setting a new pass via mysql with
UPDATE mysql.user SET authentication_string = PASSWORD('supersekrit') WHERE User = 'root'; however this didn't disable the empty password. Also tried running
mysql_secure_installation to no avail.
Found a post about setting the
plugin field in the user row to 'NULL'. I did that, but now root cannot login at all.
I followed the link to percona in the post, which states the correct way to do this is to run the following:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';. This results in and error message ->
ERROR 1064 (42000): You have an error in your SQL syntax...
Not quite sure what the correct way is to fix this. any ideas?
UPDATE: after setting the password for the root mysql account, Ubuntu 16.04 cron daemon is now throwing errors during logrotate. Looks like this issue is a rabbit hole. I've emptied the root password for now until I can figure out what's borked with logrotate. Yeesh.
error: 'Access denied for user 'root'@'localhost' (using password: NO)'