MySQL upgrade to 5.7 - Error thrown: Table 'performance_schema.session_variables' doesn't exist

Parsed from above:

===
Problem: Table ‘performance_schema.session_variables’ doesn’t exist

Fix:

mysql_upgrade -u root -p --force

systemctl restart mysqld

===

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



Marcello Grechi Lins

[HC] Dave Safley | Curated from Stackoverflow.com


Curated by Liquid Layer Networks

At Liquid Layer Web Hosting [LiquidLayer.net], we’ve made a shared web hosting platform that’s both feature-rich and easy to make use of. Our programmers have built up a custom Linux cloud web hosting platform plus an innovative Control Panel that perfectly takes advantage of its capabilities. After long hours of programming and bug fixing on our end, we are now capable to guarantee that all of our shared web hosting services are safe, virus-free, full of capabilities and very easy-to-work-with. In addition, they feature 99.9% server uptime as well as 99.9% network uptime warranties.

Powered by:

HostCheetah Networks
Global Web Hosting, Domain Registration, and Internet Services
https://hostcheetah.net | http://hostcheetah.uk


PC Helper | Est 1996 - Web Hosting | US, AU, UK, Finland, Bulgaria | :sunglasses:
https://pchelper.com