Column count of mysql.user is wrong. Expected 42, found 44. The table is probably corrupted
MysqlMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
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 - Mysql
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 - Mysql
This worked for me:
mysql_upgrade -uroot -p
and add your password root
Solution 4 - Mysql
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.
Solution 5 - Mysql
Had the same trouble today on debian (jessie) and another ami linux box. Removing the password expired column from mysql user table fixed the issue for me.
mysql> alter table mysql.user drop column password_expired;
Solution 6 - Mysql
Today I ran into the same problem after I did a dist-upgrade of a Debian Jessie 8 staging box. After some Investigation I found out, that the mysql table structure differs from what mysql-5.5.55 expects to find. I just compared the corrupted mysql database with a freshly installed one and created a little patch file, that should correct the error. Not sure if this works under other conditions, too. So, be careful using this patch and backup /var/lib/mysql and /etc/mysql before doing something nasty ;) I'll take no responsibility for any kind of damages possibly arising by this patch. Use it at your very own risk.
First of all MAKE BACKUPS!! and even more BACKUPS!! e.g. you could give mysqlsafebackup a try (Look at https://github.com/VerboteneZone/MySQLSafeBackup), an encrypting and compressing MySQL backup solution, written by me.
Download the following patch to your box:
# wget https://download.rent-an.expert/mysql-patch-5.5.55.sql.gz
Make sure, that no instance is currently accessing your MySQL server (stop services like apache2, postfix or whatever normally accesses the MySQL server). If you made yourself sure, that you are alone in the dark, apply the patch and force a mysql upgrade with the following commands:
# zcat mysql-patch-5.5.55.sql.gz | mysql -uroot -p mysql
# mysql_upgrade --force -uroot -p
If anything worked without any error, restart your MySQL service:
# service mysql stop
# service mysql start
After that, try to create a testuser to see, if the patch has been applied correctly:
# mysql -uroot -p
> CREATE USER 'Testuser123'@'localhost' IDENTIFIED BY 'Pass0worZ';
You should get a message like:
> Query OK, 0 rows affected (0.00 sec)
Now, you can safely delete your testuser again, with:
> DROP USER 'Testuser123'@'localhost';
Anyway, if something went wrong, restore your backup and try again ;)
Hope that helps.
Solution 7 - Mysql
I moved from mariadb to mysql because i was unable to change the myriadb data directory on centos 7 x 64.
on mysql When i tried adding new user other than root. i got
column count of mysql.user is wrong expected 45 found 48
i tried
mysql_upgrade -uroot -p
and
mysql_upgrade --force -uroot -p
but still got the same error.
so i went ahead and added new user manually in mysql.user table by copying all details from other rows having root username.
restart service mysqld
and done.
Solution 8 - Mysql
In my case, with Debian 8 and MySQL 5.5, mysql_upgrade --force -uroot -p wont fix the issue.
I needed upgrading to MySQL 5.6 first and then run the command above.
http://www.debiantutorials.com/install-mysql-server-5-6-debian-7-8/
Solution 9 - Mysql
When migrating from mysql 5.5 to 5.7, (by using a full mysqldump and then the source command) I had the error only when I tried to edit or add a user
> ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, > found 42. The table is probably corrupted
Similar to some others here I did
> sudo mysql_upgrade -u root -p #sudo so it can write a log sudo
> service mysql restart
And that fixed the issue, I could add and edit users again. I would have added this small difference as a comment to one of the similar answers, but I don't have the reputation yet
Solution 10 - Mysql
I've ran into the same issue today.. The solution for me was to manually add the missing columns to the user table.
Beware - Use at your own risk
The newly added columns with mysql.5.5.55 are:
plugin, authentication_string, Create_tablespace_priv
They need to be added in a specific oder:
use mysql;
alter Table user ADD Create_tablespace_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Trigger_priv;
alter Table user ADD plugin char(64) DEFAULT '';
alter Table user ADD authentication_string text DEFAULT NULL;
After this, I was able to again, modify the user table.
Solution 11 - Mysql
After and upgrade I had "Column count of mysql.user is wrong. Expected 45, found 46. The table is probably corrupted" I was having trouble logging in so i fired up the db:
mysqld --console --skip-grant-tables
logged in and there was an extra column compared to my default table "Is_role" so i removed it:
ALTER TABLE `user` DROP COLUMN `is_role`;
restarted mysqld and we are all good.
Solution 12 - Mysql
I finally solved my problem this way:
-
Start mysql:
mysqld –-console –-skip-grant-tables –-skip-external-locking(keep the terminal open) -
Execute:
mysqlcheck –-repair mysql user
Source: https://forums.mysql.com/read.php?10,652134,652135#msg-652135
Solution 13 - Mysql
Quiet the same error message : Column count of mysql.user is wrong. Expected 42, found 43. The table is probably corrupted.
This is not the solution but a circumvention ... I backuped all my databases from mysql 5.5.55-0+deb8u1 and restored them to mysql 5.7.18-0ubuntu0.16.04.1 until this bug is not resolved.
Hard job to update all connections but usefull.
Solution 14 - Mysql
If you are trying to add a new user or even change the permission of any existing users and getting the error, firstly I would suggest to grant full permission to the root users, and then adding new users.
sudo mysql
GRANT ALL PRIVILEGES ON * . * TO 'ccuser'@'localhost';
And then,
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';