I have a VM with Ubuntu 16.04.4 LTS. I use it for wordpress sites, so it haves mysql, nginx and php. I migrated from Azure to Vultr. For doing so I backed up all the databases in Azure and restored them into my VM at Vultr.
That was a couple of months ago. Everything was working fine until today, when I wanted to add a new wordpress site. As usual I started by configuring the database for mysql, so I tried to loging to mysql and my issues started. A problem with the socket appeared, and then I ran
mysqld_safe --skip-grant-tables & to be able to access mysql.
I was able to access it and to fix my issue I ran
update user set plugin="mysql_native_password"; and exited mysql. After struggling a while some other sockets errors appeared.
Reading on StackOverflow an upgrade was recommended, therefore I updated my database and now this is my current issue. I can login with
mysql -u root -p. But, when I enter
use mysql; I get:
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
Then, when I run
ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxxx'; i get
ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, found 46. The table is probably corrupted
password column doesn't exists in the
user table, so instead of the previous statement I used
update mysql.user set authentication_string=password('xxxxxxx') where user='root'; and I got
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. so it seams to me that I'm in a dead end.
This is my current error when I run
mysql_upgrade --force -uroot -p...
mysql_upgrade: Got error: 1862: Your password has expired. To log in you must change it using a client that supports expired passwords. while connecting to the MySQL server
Upgrade process encountered error and will not continue.
I appreciate any help you can give me.
Looking at the user.frm files I noticed that the column that the
is_role column is the extra column in my user table. I tried to edit the user.frm file but I wasn't able to make it work.