78

I am getting the following:

Native table 'performance_schema'.'file_instances' has the wrong structure
Native table 'performance_schema'.'cond_instances' has the wrong structure
Native table 'performance_schema'.'rwlock_instances' has the wrong structure
Native table 'performance_schema'.'mutex_instances' has the wrong structure
...

And on it goes These errors come up when I restart MySql. It seems to cause MySql Administrator to become unstable, I get a lot of:

"MySQL server has gone away"
CarbonMan
  • 4,350
  • 12
  • 54
  • 75

8 Answers8

248

Try following command in shell (the root user here is the mysql root user, not the system root)

sudo mysql_upgrade -u root -p

sudo service mysql restart

Make sure to restart mysql after running this (All credit to @Mikepote in the comments.)

Nico Haase
  • 11,420
  • 35
  • 43
  • 69
Musa
  • 3,944
  • 4
  • 21
  • 27
  • Yeah, upgrade to the newer version without mysql_upgrade may cause the wrong structure error – Truong Hua Mar 09 '15 at 16:04
  • 87
    If anyone still has this error *after running mysql_upgrade* try restarting the mysql service. This fixed it for me. – Mikepote Oct 29 '15 at 13:08
  • 2
    Thanks, Mikepote, that's exactly the problem I had. I ran mysql_upgrade, then got this error, and realized after reading your comment that I hadn't restarted mysql. – Geoffrey Wiseman Nov 25 '15 at 20:00
  • I was upgrade to 5.7.10 on Ubuntu 15.04 and it required a restart of the machine for that error to disappear from MySQL Workbench. – Hatem Jaber Jan 16 '16 at 13:10
  • 2
    Worked for me as well on two debian based machines, though I had to use the `--force` switch as it was no mysql fault in my case. – Nicolas Mar 09 '16 at 22:22
  • I copy data from 5.5 to 5.6 (portable version on Windows 64bit) and get same errors but still can use db correctly for a while. However, this answer also works for me to eliminate those errors in errlog. Thanks! – Scott Chu Apr 15 '16 at 06:15
  • I was getting this error in my docker-compose based container. Just did `docker exec -it mysql-container-id bash` and `mysql_upgrade -u root -p` and it fixed the issue. Thanks. – Andy Jun 16 '17 at 16:30
  • Using homebrew: `brew services restart mysql@5.7` did the trick for me. – ryantuck Sep 08 '20 at 23:27
24

Im my case it appeared when specific query was run on a table.

And log also contained:

Missing system table mysql.proxies_priv; please run mysql_upgrade to create it

I've run mysql_upgrade and after that problem has gone.

Oleksandr Yanovets
  • 4,661
  • 4
  • 31
  • 26
  • 3
    As above, you have to restart the server after running mysql_upgrade. Until you do, you will continue to see these errors. – mostlydev Jan 06 '16 at 14:24
19

I had this problem, the answer was here by @Berend de Boer

Restart mysql after the upgrade.

[ERROR]Native table performance schema has the wrong structure

Community
  • 1
  • 1
Matt
  • 5,315
  • 1
  • 30
  • 57
8

This error is encountered when you installed MySQL over a previous installation that was configured without the Performance Schema or an older version of Performance schema that may not have all the current tables.

I also encountered this issue on . To resolve it, I have executed the following:

cd /Applications/MAMP/bin/
sudo ./upgradeMysql.sh

Remember to restart the mysql server.

You can read the Performance Schema Build Configuration for more details.

vvns
  • 3,548
  • 3
  • 41
  • 57
2

If the database is a Akonadi (KDE) database the above won't be enough. You need to mirror the options given to your mysqld, check with

ps aux | grep mysql

Copy the options to the mysql_upgrade commands (I did not need '-u root -p' but you might)

mysql_upgrade --defaults-file=/home/USER/.local/share/akonadi/mysql.conf --datadir=/home/USER/.local/share/akonadi/db_data/ --socket=/tmp/akonadi-USER.x0Bvxr/mysql.socket

I really think the --socket option is the key.

user1960812
  • 21
  • 1
0

Try mysql_upgrade and then restart mysql and its working back

Roshana Pitigala
  • 8,437
  • 8
  • 49
  • 80
Alailson Ribeiro
  • 3
  • 1
0

It seems this happens after you have done upgrade. Simply restart mysql:

Like run below command in CMD

sudo mysql_upgrade -u root -p

service mysql restart

and the error should now have disappeared.

Baharuni Asif
  • 325
  • 1
  • 17
-3

Apparently MySQL schema storage is broken due to a reason. These reasons may be:

  • You have broken the database information_schema
  • File system corrupted or some bugs in the file system damaged the database.
  • MySQL internals broke the schema database due to a bug in MySQL (maybe nobody encountered it before).

If you don't have backups however you are still able to access your data, first backup your data then do the following:

If you have backups, then reinstall MySQL (before that completely clear all data of mysql) and then import your data.

ahmet alp balkan
  • 42,679
  • 38
  • 138
  • 214
  • It was innodb tried a number of times to reinstall, didn't work. In the end I had to reinstall as a non-transactional database (disabling innodb I believe) and that fixed it. Thanks – CarbonMan Jun 09 '11 at 11:36
  • 8
    Instead of reinstallation first check this answer: http://stackoverflow.com/a/20262826/2166188 . @Musa's answer worked for me. – Michal_Szulc Apr 18 '16 at 00:33
  • 19
    This answer needs to be deleted. – Handsome Nerd Sep 01 '16 at 18:30
  • 1
    right solution is what @Musa updated in his post....this may be fine with small data size but what about if db size is more than 500 GB. – Zafar Malik Jun 02 '17 at 05:40
  • 1
    This answer is the extreme path, while Musa's answer proved effective for many (including me) and, being faster and easier to apply, should be tried before proceeding with a database replacement. – dubrox Dec 19 '17 at 17:53