1

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.

1
0

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

10
  • Thanks for your respond @RolandoMySQLDBA...I tried both commands but the result is the same. You are right that SELECT 1; will return an error, in my case ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
    – dcalvom
    Aug 19 '18 at 3:30
  • I tried SET PASSWORD = PASSWORD('new_password'); and the error is ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, found 46. The table is probably corrupted and in the case of mysql> SET PASSWORD = CONCAT('*',UPPER(SHA1(UNHEX(SHA1('new_password'))))); throws ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT('*',UPPER(SHA1(UNHEX(SHA1('new_password')))))' at line 1. My assumption is that I broke something. Do you have any additional recommendation?
    – dcalvom
    Aug 19 '18 at 3:30
  • What version of MySQL did you upgrade to ??? (My guess is 8.0 because the column count for mysql.user table is 45, 43, 42, 39, 37 in MySQL 5.7, 5.6, 5.5, 5.1, 5.0 (See my old post dba.stackexchange.com/questions/203038/…)) Aug 19 '18 at 3:34
  • MySQL 8.0 probably has 46 because of the presence of the password_expired column. Please make sure you are using MySQL 8,0 client because it would expect 46 columns. Aug 19 '18 at 3:36
  • I am sorry I have not worked with MySQL 8.0 as of yet, so I can only help but so much in this case. Aug 19 '18 at 3:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.