Probable update error to mysql 5.7.21

Question

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

The 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.

UPDATE

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.

Answer 1

You must be operating in some restricted mode

The MySQL Documentation for ALTER USER says the following:

Password expiration for an account affects the corresponding row of the mysql.user system table: The server sets the password_expired column to 'Y'.

A client session operates in restricted mode if the account password has been expired. In restricted mode, operations performed within the session result in an error until the user establishes a new account password:

mysql> SELECT 1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> SET PASSWORD = PASSWORD('new_password');
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

SUGGESTION : Try the aforementioned code

mysql> SET PASSWORD = PASSWORD('new_password');

If this does not works due to old_passwords, do the hash algorithm manually

mysql> SET PASSWORD = CONCAT('*',UPPER(SHA1(UNHEX(SHA1('new_password')))));

I learned about this hash algorithm from PalominoDB (last bought by Pythian).