mysql.user 的列数错误。预期 42，找到 44。表可能已损坏

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

提问by Ser1ous1

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.

目前我使用的是最新版本的 ISPConfig 3。今天我想添加一个数据库和用户。它没有用。然后我在 PHPmyadmin 上试了一下，没有用。

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

当我尝试在 PHPMyadmin 用户面板中添加用户时，我收到以下错误消息：

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

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的 '* TO 'test'@'localhost'' 附近使用的正确语法

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

/var/log/mysql/error.log 的输出：

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

[ERROR] mysql.user 的列数错误。预期 42，找到 44。表可能已损坏

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

Mysql 版本：5.5.55-0+deb8u1 PHPMyadmin 版本：4:4.2.12-2+deb8u2

Debian Linux 8

Debian Linux 8

回答by Correcter

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:

当我在 Debian 8 (jessie) 中将 mysql 服务器从 5.5 更新到 5.7 时遇到了同样的问题。在极少数情况下，如果您直接绕过版本序列进行更新，则可能会发生这种情况。（很多人都这样做，但官方不支持此类升级）。就我而言，当我执行以下命令时它工作正常：

mysql_upgrade --force -uroot -p

I hope this will help you

我希望这能帮到您

回答by jadik

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:

从 mariadb 10 迁移到 mysql 5.6 遇到了类似的问题。我收到的错误消息与此页面上列出的其他错误消息略有不同……当然，这意味着它需要不同的解决方案。在尝试修改用户记录时，我收到以下错误：

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

mysql.user 的列数错误。预期 43，找到 46。表可能已损坏

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.

上面的一些建议有助于解决问题。在查看了一个类似的服务器（到 mysql 5.6 的服务器）之后，我比较了“损坏的”用户表（来自 mariadb 10 mysql.users 表）和另一个 mysql 中的“功能”用户表中的字段5.6 mysql.users 表。

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

我使用 mysql cli 和以下命令删除了三个有问题的字段：

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!

问题解决！

回答by Fadid

This worked for me:

这对我有用：

mysql_upgrade -uroot -p

and add your password root

并添加您的密码 root

回答by tokenizer_fsj

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.

那解决了一切。

回答by nfo

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.

今天在 debian (jessie) 和另一个 ami linux box 上遇到了同样的问题。从 mysql 用户表中删除密码过期列为我解决了这个问题。

mysql> alter table mysql.user drop column password_expired;

回答by Olli

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.

今天，我在对 Debian Jessie 8 暂存盒进行了 dist 升级后遇到了同样的问题。经过一番调查，我发现 mysql 表结构与 mysql-5.5.55 期望找到的不同。我只是将损坏的 mysql 数据库与新安装的数据库进行了比较，并创建了一个小补丁文件，应该可以纠正错误。不确定这是否也适用于其他条件。所以，在做一些讨厌的事情之前，小心使用这个补丁并备份 /var/lib/mysql 和 /etc/mysql ;) 对于这个补丁可能引起的任何类型的损害，我不承担任何责任。使用它的风险由您自己承担。

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.

首先做备份！！甚至更多的备份！！例如，您可以尝试一下 mysqlsafebackup（查看https://github.com/VerboteneZone/MySQLSafeBackup），这是我编写的一种加密和压缩 MySQL 备份解决方案。

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:

确保当前没有实例正在访问您的 MySQL 服务器（停止诸如 apache2、postfix 或任何通常访问 MySQL 服务器的服务）。如果您确定自己在黑暗中独自一人，请应用补丁并使用以下命令强制 mysql 升级：

# 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:

如果一切正常且没有任何错误，请重新启动您的 MySQL 服务：

# service mysql stop
# service mysql start

After that, try to create a testuser to see, if the patch has been applied correctly:

之后，尝试创建一个 testuser 以查看补丁是否已正确应用：

# mysql -uroot -p

CREATE USER 'Testuser123'@'localhost' IDENTIFIED BY 'Pass0worZ';

创建用户 'Testuser123'@'localhost' 由 'Pass0worZ' 识别；

You should get a message like:

您应该收到如下消息：

Query OK, 0 rows affected (0.00 sec)

查询正常，0 行受影响（0.00 秒）

Now, you can safely delete your testuser again, with:

现在，您可以再次安全地删除您的 testuser，使用：

DROP USER 'Testuser123'@'localhost';

删除用户 'Testuser123'@'localhost';

Anyway, if something went wrong, restore your backup and try again ;)

无论如何，如果出现问题，请恢复备份并重试;)

Hope that helps.

希望有帮助。

回答by AMB

I moved from mariadbto mysqlbecause i was unable to change the myriadbdata directoryon centos 7 x 64.

我从mariadb到mysql因为我无法更改myriadbdata directoryon centos 7 x 64。

on mysqlWhen i tried adding new user other than root. i got

上mysql时，我试图加入除根其他新用户。我有

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 manuallyin mysql.usertable by copying all details from other rows having rootusername.

但仍然有同样的错误。所以我继续并通过从具有用户名的其他行复制所有详细信息来添加new user manually到mysql.user表中root。

restart service mysqld

and done.

并做了。

回答by user9869932

In my case, with Debian 8 and MySQL 5.5, mysql_upgrade --force -uroot -pwont fix the issue.

就我而言，使用 Debian 8 和 MySQL 5.5，mysql_upgrade --force -uroot -p无法解决问题。

I needed upgrading to MySQL 5.6 first and then run the command above.

我需要先升级到 MySQL 5.6，然后运行上面的命令。

http://www.debiantutorials.com/install-mysql-server-5-6-debian-7-8/

http://www.debiantutorials.com/install-mysql-server-5-6-debian-7-8/

回答by texdevelopers

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

当从 mysql 5.5 迁移到 5.7 时，（通过使用完整的 mysqldump 然后使用 source 命令）我只有在尝试编辑或添加用户时才会出现错误

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

ERROR 1805 (HY000): mysql.user 的列数错误。预期 45，发现 42。表可能已损坏

Similar to some others here I did

类似于我在这里做过的其他一些人

sudo mysql_upgrade -u root -p #sudo so it can write a log sudo

service mysql restart

sudo mysql_upgrade -u root -p #sudo 所以它可以写一个日志 sudo

服务mysql重启

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

这解决了问题，我可以再次添加和编辑用户。我会添加这个小的差异作为对类似答案之一的评论，但我还没有声誉

回答by Dom

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:

mysql.5.5.55 新增的列是：

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.

在此之后，我能够再次修改用户表。