CODEWITHSUNDEEP
mysqlupgrade
Taz
Taz

Reputation: 5983

Table 'performance_schema.session_variables' doesn't exist

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

Table 'performance_schema.session_variables' doesn't exist

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

Upvotes: 333

Views: 193029

Answers (9)

Marcello Grechi Lins
Marcello Grechi Lins

Reputation: 3410

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?

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

  • 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

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_56 changed its default value from being turned ON by default, to OFF. This flag controls how the engine behaves on queries for setting and reading variables (session and global) on various MySQL Versions.

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

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.

So, in summary, you may (it's impossible to tell now) have had performance_schema either 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_schema was corrupted, the connections are failing.

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.

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

Upvotes: 41

Pranay Srivastava
Pranay Srivastava

Reputation: 63

Follow these steps without -p :

  1. mysql_upgrade -u root
  2. systemctl restart mysqld

I had the same problem and it works!

Upvotes: 5

Aleksandar
Aleksandar

Reputation: 4146

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

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

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

Upvotes: 1

Alan
Alan

Reputation: 2086

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\bin and run .\mysql_upgrade.exe -u root

Upvotes: 0

Mohamed Nabli
Mohamed Nabli

Reputation: 1649

sometimes mysql_upgrade -u root -p --force is not realy enough,

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

according to it:

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

Upvotes: -2

Matteus Barbosa
Matteus Barbosa

Reputation: 2725

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

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

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

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

apt-get update

Upvotes: 1

viq
viq

Reputation: 1349

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

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

Upvotes: 133

Mihai Caracostea
Mihai Caracostea

Reputation: 8466

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

mysql_upgrade -u root -p --force

A MySQL server restart is required.

Upvotes: 497

Marcin Sz
Marcin Sz

Reputation: 2644

The mysql_upgrade worked for me as well:

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

Regards, MSz.

Upvotes: 262

Related Questions