Table 'performance_schema.session_variables' doesn't exist

Question

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 ?

Another one. Looks like your upgrade didn't succeed. You might want to consider doing the upgrade process again (or) re-install `5.7.8-rc` version and a restore from DB full backup. — Rahul, Aug 12 '15 at 14:06
did you run `mysql_upgrade` to ensure that any changes to core tables/dbs was done? — Marc B, Aug 12 '15 at 14:06
yeah, I made `mysql_upgrade`, I give it last try and reinstalling it again. If it won't work I'll downgrade to 5.6 version — Taz, Aug 12 '15 at 14:08
I experienced the same issue, to solve it, I run `mysql_upgrade -u root -p --force`, then I restarted the DB server. — robregonm, Aug 19 '15 at 19:14
If the mysql_upgrade command does not work, then the mysql.performance_schema table may have become corrupted. We had this problem. To fix the problem, we removed the database server using the command: apt-get purge mariadb-client-10.1 mariadb-common mariadb-server-10.1. This removed all database binary, configuration and data file. Next we reinstalled the database server and imported back the databases. After that the database server ran without problems — Nadir Latif, Apr 16 '19 at 05:38

Mihai Caracostea · Answer 1 · 2015-11-29T16:41:27.713

495

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.

edited Nov 29 '15 at 16:41

answered Oct 21 '15 at 14:58

Mihai Caracostea

8,336
4
27
46

7

That worked well. Thanks.I want to know that what the reason is. – diguage Nov 28 '15 at 03:26
This was right and solved problems on OSX. Note that I needed to run this with sudo. Otherwise I received errno: 13 on file creation. sudo mysql_upgrade -u root -p --force – KateYoak Jan 18 '16 at 20:09
2

I'm getting `Access denied for user 'root'@'localhost' (using password: YES) while connecting to the MySQL server` even though I am using the correct root password. Any help?? :-/ – sixty4bit Mar 23 '16 at 21:52
4

@sixty4bit try removing the -p – Mike Mellor Apr 25 '16 at 12:25
I am using mysql locally via easy php. Where do I run this? is there some console in the setup? and how do i restart? if i stop and start it in the client will it work? – Neville Nazerane May 02 '16 at 00:10
1

@NevilleNazerane I'm not familiar with easy php, but you should be able to locate where mysql gets installed and then just open a cdm prompt and change the directory to that location. Now you should be able to run the command. – Mihai Caracostea May 02 '16 at 01:31
I tried some online link and got it done thanks. For anyone else facing the same issue, go inside the bin folder of your mysql setup in command prompt and run it – Neville Nazerane May 02 '16 at 02:09
4

@diguage The reason is that MySQL's version upgrade has introduced version incompatible schemas for internal metadata. For me I am upgrading MySQL 5.6 to MySQL 5.7 on a Mac using Homebrew and the MySQL data directory was unchanged so the new version MySQL was reading the old internal metadata but don't know what to do - that error we've seen here is a manifest of that issue. After `mysql_upgrade` and a restart, everything worked. See: http://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html – Devy May 13 '16 at 20:10
Just as a head's up, this took about 7 minutes on my system, which for reference has a 6.0 GB backup file of all databases. I was worried it got stuck, but I just had to wait. – Tyler Collier Sep 01 '16 at 16:12
That did it. Thanks. Note: This worked after I followed the wamp directions from here: http://forum.wampserver.com/read.php?2,111797,124054 – Webmaster G Nov 10 '16 at 17:39
on ubuntu,I do following: sudo mysql_upgrade -u root -p --force sudo service mysql restart – baocheng liao Mar 14 '17 at 05:18
@sixty4bit how did you get around the error `Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) while connecting to the MySQL server` – Lazarus Thurston Nov 19 '17 at 09:47

score 262 · Accepted Answer · answered Nov 02 '15 at 16:11

262

The mysql_upgrade worked for me as well:

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

Regards, MSz.

answered Nov 02 '15 at 16:11

Marcin Sz

2,644
1
10
2

25

I did need to restart mysqld (`mysql.server restart`, since I'm using a homebrew install on os x), so this was helpful. Otherwise I got an error about session_variables having the wrong structure. – Geoffrey Wiseman Nov 25 '15 at 19:59
Identical behaviour with Homebrew on OS X 10.10.5 (Yosemite). Doing the upgrade also fixes a crash in Sequel Pro 1.1 (build 4499) when attempting to load the database. – William Turrell Dec 27 '15 at 14:01
4

`Native table 'performance_schema'.'session_variables' has the wrong structure` – tread Jan 31 '16 at 11:55
8

If you're using `brew services` you can restart your server with `brew services restart mysql`. – Frederik Kammer Feb 22 '16 at 23:30
1

This doesn't works for me, the correct answer is given by viq. only is needed to enable the show compatibility. – kato2 Jan 11 '17 at 19:40
or 'sudo service mysqld restart' – miguelfg Mar 07 '17 at 10:53
by accident I restored performance_schema from different mysqld version and this answer helped me to fix it – Jaro Dec 11 '17 at 12:39
If you are using MAMP, make sure to restart mysql service from there. – Lothre1 Oct 08 '19 at 11:31
Note : dont put 'root' only if your db user is 'root' if you have anything else, put that. this is the name you put in phpmyadmin – Reejesh PK May 16 '21 at 16:59

score 129 · Answer 3 · answered Mar 16 '16 at 11:56

129

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

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

answered Mar 16 '16 at 11:56

viq

1,309
1
8
4

1

This worked perfectly for me! And I did not have to restart the mysql server which would have been so cumbersome – anu.agg Jun 02 '16 at 08:30
4

I'm sorry, this is a somewhat over-size solution: like using a bazooka to shoot a fly. This compatibility switch has many more effects, you might not want all of them. – Tuncay Göncüoğlu Jul 13 '16 at 15:10
@Tuncay Göncüoğlu what are some of these side effects? – katzmopolitan Nov 05 '16 at 00:24
@katzmopolitan Read up here: https://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html . The changes are mostly related to INFORMATION_SCHEMA handling (security etc), but there are more. – Tuncay Göncüoğlu Nov 07 '16 at 12:58
This worked for me too. The error message I got was from mysqldump. Once I made the suggested change mysqldump worked. Once I had the dump I simply changed the show_compatibility_56 back to OFF. – Bryan Nov 26 '16 at 22:27
The "DB Navigator" plugin (Version: 3.0.7021.1, current as of May 2017) for IDEA (and other JetBrains IDEs) won't connect to MySQL 5.7 without this setting enabled. – Pavel May 31 '17 at 05:24
This works perfectly and no need to upgrade your mysql. Also no need to restart mysql – Kiran Apr 18 '19 at 10:13
This worked perfectly for me. I am using mysql 5.7 – sinsuren Feb 04 '21 at 16:53

score 40 · Answer 4 · edited Jun 10 '19 at 09:34

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

Thanks. I was wondering what caused this problem before jumping on and making changes. — Ken Ingram, Jan 08 '20 at 18:40
@MarcelloGrechiLins thank you so much for the explanation. It is really a good thing to actually have answers explained as to what the problem was and why the answer works. — Gharbad The Weak, Nov 16 '20 at 20:21
I'm happy to have helped. This issue really is shady and I'm aware it affects thousands of databases. Most people simply don't have the time or means to dive this deep. — Marcello Grechi Lins, Nov 17 '20 at 21:40

score 5 · Answer 5 · edited Jun 22 '18 at 12:07

5

Follow these steps without -p :

mysql_upgrade -u root
systemctl restart mysqld

I had the same problem and it works!

edited Jun 22 '18 at 12:07

Akansha Sharma

11
3

answered Nov 23 '17 at 09:21

Pranay Srivastava

65
1
6

This works! Only `systemctl restart mysqld` did not work. – Ninja Dec 11 '17 at 08:14
then use ```systemctl restart mysql``` – BitDEVil2K16 Feb 02 '19 at 17:54

score 1 · Answer 6 · answered Jul 31 '16 at 18:46

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

score 1 · Answer 7 · answered Apr 19 '18 at 12:19

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

score 0 · Answer 8 · answered Jan 10 '18 at 19:00

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

score -2 · Answer 9 · edited May 23 '17 at 10:31

-2

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:

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

edited May 23 '17 at 10:31

Community

1
1

answered Nov 22 '16 at 16:37

Mohamed Nabli

1,629
3
17
24

Table 'performance_schema.session_variables' doesn't exist

9 Answers9

Linked

Related