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

Question

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

Answer 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

Answer 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!

Answer 3

This worked for me:

mysql_upgrade -uroot -p

and add your password root