MySQL 表“performance_schema.session_variables”不存在

Table 'performance_schema.session_variables' doesn't exist

提问by Taz

After upgrading MySQL to 5.7.8-rc and loging to server I got error:

将 MySQL 升级到 5.7.8-rc 并登录到服务器后，出现错误：

Table 'performance_schema.session_variables' doesn't exist

I can't find any solution for this. Can you help ?

我找不到任何解决方案。你能帮我吗 ？

采纳答案by Marcin Sz

The mysql_upgrade worked for me as well:

mysql_upgrade 也对我有用：

# mysql_upgrade -u root -p --force
# systemctl restart mysqld

Regards, MSz.

问候， MSz。

回答by Mihai Caracostea

I was able to log on to the mysql server after running the command @robregonm suggested:

运行@robregonm 建议的命令后，我能够登录到 mysql 服务器：

mysql_upgrade -u root -p --force

A MySQL server restart is required.

需要重启 MySQL 服务器。

回答by viq

mysql -u app -p
mysql> set @@global.show_compatibility_56=ON;

as per http://bugs.mysql.com/bug.php?id=78159worked for me.

根据http://bugs.mysql.com/bug.php?id=78159为我工作。

回答by Marcello Grechi Lins

Since none of the answers above actually explain what happened, I decided to chime in and bring some more details to this issue.

由于上面的答案都没有真正解释发生了什么，我决定插话并为这个问题提供更多细节。

Yes, the solution is to run the MySQL Upgrade command, as follows: mysql_upgrade -u root -p --force, but what happened?

是的，解决办法是运行 MySQL Upgrade 命令，如下：mysql_upgrade -u root -p --force，但是发生了什么？

The root cause for this issue is the corruption of performance_schema, which can be caused by:

此问题的根本原因是 的损坏performance_schema，这可能是由以下原因引起的：

• Organic corruption (volumes going kaboom, engine bug, kernel driver issue etc)
• Corruption during mysql Patch (it is not unheard to have this happen during a mysql patch, specially for major version upgrades)
• A simple "drop database performance_schema" will obviously cause this issue, and it will present the same symptoms as if it was corrupted

• 有机损坏（卷变得卡布，引擎错误，内核驱动程序问题等）
• mysql 补丁期间的损坏（在 mysql 补丁期间发生这种情况并非闻所未闻，特别是针对主要版本升级）
• 一个简单的“drop database performance_schema”显然会导致这个问题，它会表现出与损坏相同的症状

This issue might have been present on your database even before the patch, but what happened on MySQL 5.7.8 specifically is that the flag show_compatibility_56changed its default value from being turned ONby default, to OFF. This flag controls how the engine behaves on queries for setting and reading variables (session and global) on various MySQL Versions.

这个问题可能在补丁之前就已经存在于您的数据库中，但在 MySQL 5.7.8 上发生的具体情况是该标志show_compatibility_56将其默认值从默认值更改ON为OFF. 此标志控制引擎如何处理在各种 MySQL 版本上设置和读取变量（会话和全局）的查询。

Because MySQL 5.7+ started to read and store these variables on performance_schemainstead of on information_schema, this flag was introduced as ONfor the first releases to reduce the blast radius of this change and to let users know about the change and get used to it.

由于 MySQL 5.7+ 开始读取和存储这些变量 onperformance_schema而不是 on information_schema，因此ON在第一个版本中引入了此标志以减少此更改的影响范围并让用户了解更改并习惯它。

OK, but why does the connection fail? Because depending on the driver you are using (and its configuration), it may end up running commands for every new connection initiated to the database (like show variables, for instance). Because one of these commands can try to access a corrupted performance_schema, the whole connection aborts before being fully initiated.

好的，但是为什么连接失败？因为根据您使用的驱动程序（及其配置），它可能最终为每个启动到数据库的新连接运行命令（例如show variables，）。因为这些命令之一可以尝试访问损坏的performance_schema，整个连接在完全启动之前中止。

So, in summary, you may(it's impossible to tell now) have had performance_schemaeither missing or corrupted before patching. The patch to 5.7.8 then forced the engine to read your variables out of performance_schema(instead of information_schema, where it was reading it from because of the flag being turned ON). Since performance_schemawas corrupted, the connections are failing.

因此，总而言之，您可能（现在无法确定）performance_schema在修补之前丢失或损坏了。然后，5.7.8 的补丁强制引擎从 中读取您的变量performance_schema（而不是information_schema因为标志被转动而从哪里读取ON）。由于performance_schema已损坏，连接失败。

Running MySQL upgrade is the best approach, despite the downtime. Turning the flag on is one option, but it comes with its own set of implications as it was pointed out on this thread already.

尽管有停机时间，但运行 MySQL 升级是最好的方法。打开标志是一种选择，但它有自己的一套含义，正如在这个线程中已经指出的那样。

Both should work, but weight the consequences and know your choices :)

两者都应该有效，但要权衡后果并了解您的选择:)

回答by Pranay Srivastava

Follow these steps without -p:

请按照以下步骤操作-p：

1. mysql_upgrade -u root
2. systemctl restart mysqld

1. mysql_upgrade -u root
2. systemctl restart mysqld

I had the same problem and it works!

我有同样的问题，它的工作原理！

回答by Matteus Barbosa

As sixty4bit question, if your mysql root user looks to be misconfigured, try to install the configurator extension from mysql official source:

作为604位问题，如果您的mysql root用户看起来配置错误，请尝试从mysql官方源安装配置器扩展：

https://dev.mysql.com/downloads/repo/apt/

https://dev.mysql.com/downloads/repo/apt/

It will help you to set up a new root user password.

它将帮助您设置新的 root 用户密码。

Make sure to update your repository (debian/ubuntu) :

确保更新您的存储库（debian/ubuntu）：

apt-get update

回答by Alan

For my system the problem ended up being that I still had Mysql 5.6 installed and so the mysql_upgrade.exe from that installation was being called instead of the one for 5.7. Navigate to C:\Program Files\MySQL\MySQL Server 5.7\binand run .\mysql_upgrade.exe -u root

对于我的系统，问题最终是我仍然安装了 Mysql 5.6，因此调用了该安装中的 mysql_upgrade.exe 而不是 5.7 的那个。导航到C:\Program Files\MySQL\MySQL Server 5.7\bin并运行.\mysql_upgrade.exe -u root

回答by Aleksandar

If, while using the mysql_upgrade -u root -p --forcecommand You get this error:

如果在使用mysql_upgrade -u root -p --force命令时出现此错误：

Could not create the upgrade info file '/var/lib/mysql/mysql_upgrade_info' in the MySQL Servers datadir, errno: 13

just add the sudobefore the command. That worked for me, and I solved my problem. So, it's: sudo mysql_upgrade -u root -p --force:)

只需sudo在命令之前添加。这对我有用，我解决了我的问题。所以，它是：sudo mysql_upgrade -u root -p --force:)

回答by Mohamed Nabli

sometimes mysql_upgrade -u root -p --forceis not realy enough,

有时mysql_upgrade -u root -p --force还不够真实，

please refer to this question : Table 'performance_schema.session_variables' doesn't exist

请参考这个问题：表'performance_schema.session_variables'不存在

according to it:

根据它：

1. open cmd
2. cd [installation_path]\eds-binaries\dbserver\mysql5711x86x160420141510\bin
3. mysql_upgrade -u root -p --force

1. 打开cmd
2. cd [installation_path]\eds-binaries\dbserver\mysql5711x86x160420141510\bin
3. mysql_upgrade -u root -p --force