Column count of mysql.user is wrong. Expected 42, found 44. The table is probably corrupted

Currently I'm using the newest version of ISPConfig 3. Today I wanted to add a db and user. It didn't work. Then I tried it on PHPmyadmin and it didn't work.

When I tried to add a user in PHPMyadmin Users Panel I received the following error message:

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 '* TO 'test'@'localhost'' at line 1

The output from /var/log/mysql/error.log:

[ERROR] Column count of mysql.user is wrong. Expected 42, found 44. The table is probably corrupted

Mysql Version: 5.5.55-0+deb8u1 PHPMyadmin Version: 4:4.2.12-2+deb8u2

Debian Linux 8

Solution 1:

I had the same problem when I updated the mysql server from 5.5 to 5.7 in Debian 8 (jessie). In rare cases, it probably happens if you update directly bypassing the sequences of versions. (Many people do this, but such upgrades are not officially supported). In my case, it worked fine when I executed the command below:

mysql_upgrade --force -uroot -p

I hope this will help you

Solution 2:

Migrating from mariadb 10 to mysql 5.6 saw similar issues. The error message I received, was slightly different than the others listed on this page... which, of course, means it required a different solution. Upon attempting to modify a user record I received the following error:

Column count of mysql.user is wrong. Expected 43, found 46. The table is probably corrupted

Some of the advice above helped frame the problem. After taking a look at a similar server (to the mysql 5.6 one), I compared the fields in the both the "corrupted" user table (from the mariadb 10 mysql.users table) & the "functional" user table in the other mysql 5.6 mysql.users table.

I removed the three problematic fields using the mysql cli & the following commands:

mysql -u root -p
use mysql;
alter table mysql.user drop column default_role;
alter table mysql.user drop column max_statement_time;
alter table mysql.user drop column password_expired;
quit

Problem resolved!

Solution 3:

This worked for me:

mysql_upgrade -uroot -p

and add your password root

Solution 4:

In my case, and following the recommendation of the error message, I ran:

root@mysql-190877524-gm3j4:/# mysql_upgrade -uroot -p***
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.user                                         OK
Upgrading the sys schema.
Checking databases.
[...]
Upgrade process completed successfully.
Checking if update is needed.

That solved everything.