解决方法Table 'performance_schema.session_variables' doesn't exist

ゝ一世哀愁。 2024-02-17 20:47 31阅读 0赞

Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.  
  
Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.  
  
mysql> show variables like 'performance\_schema';

ERROR 1146 (42S02): Table 'performance\_schema.session\_variables' doesn't exist

或

##### 运行mysql时，提示Table ‘performance\_schema.session\_variables’ doesn’t exist #####

日志文件报错如下：

2017-04-27T08:58:04.195252Z 0 \[Warning\] System table 'time\_zone\_leap\_second' is expected to be transactional.  
2017-04-27T08:58:04.195281Z 0 \[Warning\] System table 'time\_zone\_name' is expected to be transactional.  
2017-04-27T08:58:04.195289Z 0 \[Warning\] System table 'time\_zone' is expected to be transactional.  
2017-04-27T08:58:04.195296Z 0 \[Warning\] System table 'time\_zone\_transition\_type' is expected to be transactional.  
2017-04-27T08:58:04.195303Z 0 \[Warning\] System table 'time\_zone\_transition' is expected to be transactional.  
2017-04-27T08:58:04.196269Z 0 \[Warning\] System table 'servers' is expected to be transactional.  
2017-04-27T08:58:04.196770Z 0 \[ERROR\] Incorrect definition of table performance\_schema.events\_waits\_current: expected column 'NESTING\_EVENT\_TYPE' at position 15 to have type enum('TRANSACTION','STATEMENT','STAGE','WAIT', found type enum('STATEMENT','STAGE','WAIT').  
2017-04-27T08:58:04.196873Z 0 \[ERROR\] Incorrect definition of table performance\_schema.events\_waits\_history: expected column 'NESTING\_EVENT\_TYPE' at position 15 to have type enum('TRANSACTION','STATEMENT','STAGE','WAIT', found type enum('STATEMENT','STAGE','WAIT').  
2017-04-27T08:58:04.196968Z 0 \[ERROR\] Incorrect definition of table performance\_schema.events\_waits\_history\_long: expected column 'NESTING\_EVENT\_TYPE' at position 15 to have type enum('TRANSACTION','STATEMENT','STAGE','WAIT', found type enum('STATEMENT','STAGE','WAIT').  
2017-04-27T08:58:04.197278Z 0 \[ERROR\] Incorrect definition of table performance\_schema.events\_waits\_summary\_by\_user\_by\_event\_name: expected column 'USER' at position 0 to have type char(32), found type char(16).  
2017-04-27T08:58:04.197369Z 0 \[ERROR\] Incorrect definition of table performance\_schema.events\_waits\_summary\_by\_account\_by\_event\_name: expected column 'USER' at position 0 to have type char(32), found type char(16).  
2017-04-27T08:58:04.198123Z 0 \[ERROR\] Column count of performance\_schema.setup\_actors is wrong. Expected 5, found 3. Created with MySQL 50616, now running 50717. Please use mysql\_upgrade to fix this error.  
2017-04-27T08:58:04.198356Z 0 \[ERROR\] Incorrect definition of table performance\_schema.setup\_objects: expected column 'OBJECT\_TYPE' at position 0 to have type enum('EVENT','FUNCTION','PROCEDURE','TABLE','TRIGGER', found type enum('TABLE').  
2017-04-27T08:58:04.198868Z 0 \[ERROR\] Column count of performance\_schema.table\_lock\_waits\_summary\_by\_table is wrong. Expected 68, found 73. Created with MySQL 50616, now running 50717. Please use mysql\_upgrade to fix this error.  
2017-04-27T08:58:04.198960Z 0 \[ERROR\] Column count of performance\_schema.threads is wrong. Expected 17, found 14. Created with MySQL 50616, now running 50717. Please use mysql\_upgrade to fix this error.  
2017-04-27T08:58:04.199045Z 0 \[ERROR\] Column count of performance\_schema.events\_stages\_current is wrong. Expected 12, found 10. Created with MySQL 50616, now running 50717. Please use mysql\_upgrade to fix this error.  
2017-04-27T08:58:04.199128Z 0 \[ERROR\] Column count of performance\_schema.events\_stages\_history is wrong. Expected 12, found 10. Created with MySQL 50616, now running 50717. Please use mysql\_upgrade to fix this error.  
2017-04-27T08:58:04.199211Z 0 \[ERROR\] Column count of performance\_schema.events\_stages\_history\_long is wrong. Expected 12, found 10. Created with MySQL 50616, now running 50717. Please use mysql\_upgrade to fix this error.  
2017-04-27T08:58:04.199372Z 0 \[ERROR\] Incorrect definition of table performance\_schema.events\_stages\_summary\_by\_account\_by\_event\_name: expected column 'USER' at position 0 to have type char(32), found type char(16).  
2017-04-27T08:58:04.199461Z 0 \[ERROR\] Incorrect definition of table performance\_schema.events\_stages\_summary\_by\_user\_by\_event\_name: expected column 'USER' at position 0 to have type char(32), found type char(16).  
2017-04-27T08:58:04.199750Z 0 \[ERROR\] Column count of performance\_schema.events\_statements\_current is wrong. Expected 41, found 40. Created with MySQL 50616, now running 50717. Please use mysql\_upgrade to fix this error.  
2017-04-27T08:58:04.199885Z 0 \[ERROR\] Column count of performance\_schema.events\_statements\_history is wrong. Expected 41, found 40. Created with MySQL 50616, now running 50717. Please use mysql\_upgrade to fix this error.  
2017-04-27T08:58:04.200013Z 0 \[ERROR\] Column count of performance\_schema.events\_statements\_history\_long is wrong. Expected 41, found 40. Created with MySQL 50616, now running 50717. Please use mysql\_upgrade to fix this error.  
2017-04-27T08:58:04.200196Z 0 \[ERROR\] Incorrect definition of table performance\_schema.events\_statements\_summary\_by\_account\_by\_event\_name: expected column 'USER' at position 0 to have type char(32), found type char(16).  
2017-04-27T08:58:04.200296Z 0 \[ERROR\] Incorrect definition of table performance\_schema.events\_statements\_summary\_by\_user\_by\_event\_name: expected column 'USER' at position 0 to have type char(32), found type char(16).  
2017-04-27T08:58:04.200609Z 0 \[ERROR\] Native table 'performance\_schema'.'events\_statements\_summary\_by\_program' has the wrong structure  
2017-04-27T08:58:04.200651Z 0 \[ERROR\] Native table 'performance\_schema'.'events\_transactions\_current' has the wrong structure  
2017-04-27T08:58:04.200687Z 0 \[ERROR\] Native table 'performance\_schema'.'events\_transactions\_history' has the wrong structure  
2017-04-27T08:58:04.200723Z 0 \[ERROR\] Native table 'performance\_schema'.'events\_transactions\_history\_long' has the wrong structure  
2017-04-27T08:58:04.200762Z 0 \[ERROR\] Native table 'performance\_schema'.'events\_transactions\_summary\_by\_thread\_by\_event\_name' has the wrong structure  
2017-04-27T08:58:04.200800Z 0 \[ERROR\] Native table 'performance\_schema'.'events\_transactions\_summary\_by\_account\_by\_event\_name' has the wrong structure  
2017-04-27T08:58:04.200838Z 0 \[ERROR\] Native table 'performance\_schema'.'events\_transactions\_summary\_by\_user\_by\_event\_name' has the wrong structure  
2017-04-27T08:58:04.200883Z 0 \[ERROR\] Native table 'performance\_schema'.'events\_transactions\_summary\_by\_host\_by\_event\_name' has the wrong structure  
2017-04-27T08:58:04.200921Z 0 \[ERROR\] Native table 'performance\_schema'.'events\_transactions\_summary\_global\_by\_event\_name' has the wrong structure  
2017-04-27T08:58:04.201002Z 0 \[ERROR\] Incorrect definition of table performance\_schema.users: expected column 'USER' at position 0 to have type char(32), found type char(16).  
2017-04-27T08:58:04.201083Z 0 \[ERROR\] Incorrect definition of table performance\_schema.accounts: expected column 'USER' at position 0 to have type char(32), found type char(16).  
2017-04-27T08:58:04.201576Z 0 \[ERROR\] Native table 'performance\_schema'.'memory\_summary\_global\_by\_event\_name' has the wrong structure  
2017-04-27T08:58:04.201621Z 0 \[ERROR\] Native table 'performance\_schema'.'memory\_summary\_by\_account\_by\_event\_name' has the wrong structure  
2017-04-27T08:58:04.201676Z 0 \[ERROR\] Native table 'performance\_schema'.'memory\_summary\_by\_host\_by\_event\_name' has the wrong structure  
2017-04-27T08:58:04.201715Z 0 \[ERROR\] Native table 'performance\_schema'.'memory\_summary\_by\_thread\_by\_event\_name' has the wrong structure  
2017-04-27T08:58:04.201755Z 0 \[ERROR\] Native table 'performance\_schema'.'memory\_summary\_by\_user\_by\_event\_name' has the wrong structure  
2017-04-27T08:58:04.201791Z 0 \[ERROR\] Native table 'performance\_schema'.'table\_handles' has the wrong structure  
2017-04-27T08:58:04.201827Z 0 \[ERROR\] Native table 'performance\_schema'.'metadata\_locks' has the wrong structure  
2017-04-27T08:58:04.201864Z 0 \[ERROR\] Native table 'performance\_schema'.'replication\_connection\_configuration' has the wrong structure  
2017-04-27T08:58:04.201898Z 0 \[ERROR\] Native table 'performance\_schema'.'replication\_group\_members' has the wrong structure  
2017-04-27T08:58:04.201934Z 0 \[ERROR\] Native table 'performance\_schema'.'replication\_connection\_status' has the wrong structure  
2017-04-27T08:58:04.201969Z 0 \[ERROR\] Native table 'performance\_schema'.'replication\_applier\_configuration' has the wrong structure  
2017-04-27T08:58:04.202003Z 0 \[ERROR\] Native table 'performance\_schema'.'replication\_applier\_status' has the wrong structure  
2017-04-27T08:58:04.202041Z 0 \[ERROR\] Native table 'performance\_schema'.'replication\_applier\_status\_by\_coordinator' has the wrong structure  
2017-04-27T08:58:04.202077Z 0 \[ERROR\] Native table 'performance\_schema'.'replication\_applier\_status\_by\_worker' has the wrong structure  
2017-04-27T08:58:04.202118Z 0 \[ERROR\] Native table 'performance\_schema'.'replication\_group\_member\_stats' has the wrong structure  
2017-04-27T08:58:04.202153Z 0 \[ERROR\] Native table 'performance\_schema'.'prepared\_statements\_instances' has the wrong structure  
2017-04-27T08:58:04.202192Z 0 \[ERROR\] Native table 'performance\_schema'.'user\_variables\_by\_thread' has the wrong structure  
2017-04-27T08:58:04.202227Z 0 \[ERROR\] Native table 'performance\_schema'.'status\_by\_account' has the wrong structure  
2017-04-27T08:58:04.202260Z 0 \[ERROR\] Native table 'performance\_schema'.'status\_by\_host' has the wrong structure  
2017-04-27T08:58:04.202294Z 0 \[ERROR\] Native table 'performance\_schema'.'status\_by\_thread' has the wrong structure  
2017-04-27T08:58:04.202332Z 0 \[ERROR\] Native table 'performance\_schema'.'status\_by\_user' has the wrong structure  
2017-04-27T08:58:04.202366Z 0 \[ERROR\] Native table 'performance\_schema'.'global\_status' has the wrong structure  
2017-04-27T08:58:04.202400Z 0 \[ERROR\] Native table 'performance\_schema'.'session\_status' has the wrong structure  
2017-04-27T08:58:04.202434Z 0 \[ERROR\] Native table 'performance\_schema'.'variables\_by\_thread' has the wrong structure  
2017-04-27T08:58:04.202472Z 0 \[ERROR\] Native table 'performance\_schema'.'global\_variables' has the wrong structure  
2017-04-27T08:58:04.202520Z 0 \[ERROR\] Native table 'performance\_schema'.'session\_variables' has the wrong structure  
2017-04-27T08:58:04.202654Z 0 \[ERROR\] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(16).  
2017-04-27T08:58:04.202686Z 0 \[ERROR\] mysql.user has no \`Event\_priv\` column at position 28  
2017-04-27T08:58:04.202896Z 0 \[ERROR\] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.  
2017-04-27T08:58:04.202987Z 0 \[Note\] Executing 'SELECT \* FROM INFORMATION\_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.  
2017-04-27T08:58:04.202999Z 0 \[Note\] Beginning of list of non-natively partitioned tables  
2017-04-27T08:58:04.219168Z 0 \[Note\] End of list of non-natively partitioned tables  
2017-04-27T08:58:04.219316Z 0 \[Note\] mysqld: ready for connections.

解决方法如下：

1、查看数据状态

root@storm-master-01:/root\#ps -ef|grep mysql  
root 68391 67024 0 16:01 pts/4 00:00:00 mysql -h192.168.22.22 -uloan -px xxxx -P3308  
mysql 72949 68459 16 17:55 pts/6 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/backup/backup-my.cnf  
root 72980 68459 0 17:55 pts/6 00:00:00 grep --color=auto mysql

2、登录数据库如下

root@storm-master-01:/data/backup\#mysql\_upgrade -h192.168.22.22 -u root -p --force -P3308 sock=/data/backup/mysql3308.sock  
mysql\_upgrade: \[Warning\] Using a password on the command line interface can be insecure.  
Checking server version.  
Running queries to upgrade MySQL server.  
Checking system database.  
mysql.columns\_priv OK  
mysql.db OK  
mysql.engine\_cost OK  
mysql.event OK  
mysql.func OK  
mysql.general\_log OK  
mysql.gtid\_executed OK  
mysql.ha\_health\_check OK  
mysql.help\_category OK  
mysql.help\_keyword OK  
mysql.help\_relation  
sys.sys\_config OK  
Upgrade process completed successfully.  
Checking if update is needed.

3、关闭数据库

4、启动数据库  
root@storm-master-01:/data/backup\#/usr/local/mysql/bin/mysqld --defaults-file=/data/backup/backup-my.cnf &