ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, found 48. The table is probably corrupted

Question

I have a problem with mysql as following:

mysql> SELECT User FROM mysql.user;
+------+
| User |
+------+
| root |
| root |
|      |
| root |
|      |
| root |
+------+
6 rows in set (0.00 sec)

mysql> CREATE USER 'dummy'@'localhost';
ERROR 1805 (HY000): Column count of mysql.user is wrong. 
Expected 45, found 48. The table is probably corrupted

Have anyone meet this problems?Thanks

Answer 1

Use the following script:

alter table user drop column is_role;
alter table user drop column default_role;
alter table user drop column max_statement_time;
alter table user modify max_user_connections int(11) unsigned NOT NULL DEFAULT '0';
flush privileges;

credit: https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/48583-column-count-of-mysql-user-is-wrong-expected-45-found-48

Answer 2

mysql_upgrade -u root -p

Then restart database.

see: https://nixcp.com/error-1805-hy000-column-count-of-mysql-user-is-wrong/

The blog write:

ERROR 1805 (HY000): Column count of mysql.user is wrong. Table is probably corrupted error can be fixed by running a simple mysql_upgrade command. mysql_upgrade tool is used often to check for mysql table incompatibilities (crashed or corrupted tables) running on the current MySQL version. It can also be used to upgrade mysql tables if needed. In this case, we will use it to fix any corrupted tables.