CODEWITHSUNDEEP
mysql
Ser1ous1
Ser1ous1

Reputation: 581

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

Upvotes: 58

Views: 113220

Answers (16)

Md Ariful Islam Roni
Md Ariful Islam Roni

Reputation: 72

start your MySQL/MariaDB database server on local host then if you have a root password?

sudo mysql_upgrade --force -uroot -p

else (if root password is null or No)

sudo mysql_upgrade --force -uroot

Upvotes: 0

Abhilash Singh Chauhan
Abhilash Singh Chauhan

Reputation: 289

In case you do not want to Upgrade the MySQL using mysql_upgrade --force -uroot -p or mysql -u root -p as it may interfere with the database, as if the database may be important i.e. it may either be running on a live server, or too big to take a backup and restore it, and you may not want to take that risk of getting it deleted from a live server, then you may simply rectify the error after understanding it line by line.

It may be different in your case but, In my case

I had to give privileges to a remote host as: GRANT ALL PRIVILEGES ON some_db_name_abc.* to 'root'@'xxx.xxx.x.xxx' IDENTIFIED BY 'test123';

and the error was #1558 - Column count of mysql.user is wrong. Expected 42, found 39. Created with MySQL 50173, now running 50562. Please use mysql_upgrade to fix this error.

enter image description here

So Basically 3 columns named Create_tablespace_priv, plugin and authentication_string were absent from the table named user of database mysql. The column names, their position in the table and their datatypes, I came to know from another testing server, which I added as follows:

ALTER TABLE mysql.user
  ADD Create_tablespace_priv ENUM('N','Y') NOT NULL
    AFTER Trigger_priv;
    
ALTER TABLE mysql.user
  ADD plugin CHAR(64) NOT NULL
    AFTER max_user_connections; 
    
    
ALTER TABLE mysql.user
  ADD authentication_string TEXT NOT NULL
    AFTER plugin;

And the query to give privileges to a remote host ran successfully after it

In case you made mistake, then you can delete the added column from the table as:

ALTER TABLE mysql.user DROP COLUMN plugin;

Upvotes: 0

Dibya Darshan Khanal
Dibya Darshan Khanal

Reputation: 65

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';

Upvotes: 0

Fadid
Fadid

Reputation: 1298

This worked for me:

mysql_upgrade -uroot -p

and add your password root

Upvotes: 24

Correcter
Correcter

Reputation: 3686

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

Upvotes: 129

nikoskip
nikoskip

Reputation: 1920

I finally solved my problem this way:

1) Start mysql: mysqld –-console –-skip-grant-tables –-skip-external-locking (keep the terminal open)

2) Execute: mysqlcheck –-repair mysql user

Source: https://forums.mysql.com/read.php?10,652134,652135#msg-652135

Upvotes: 1

Soth
Soth

Reputation: 3045

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.

Upvotes: 1

jadik
jadik

Reputation: 1946

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!

Upvotes: 30

user2420206
user2420206

Reputation:

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

Upvotes: 2

user9869932
user9869932

Reputation: 7337

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/

Upvotes: 2

user1642018
user1642018

Reputation:

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.

Upvotes: 3

tokenizer_fsj
tokenizer_fsj

Reputation: 1240

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.

Upvotes: 6

nfo
nfo

Reputation: 647

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;

Upvotes: 4

Dom
Dom

Reputation: 21

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.

Upvotes: 1

Olli
Olli

Reputation: 21

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.

Upvotes: 2

fdav
fdav

Reputation: 1

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.

Upvotes: 0

Related Questions