Analysis and Resolution of 'Table 'performance_schema.session_variables' doesn't exist' Error After Upgrading MySQL to 5.7.8-rc

Problem Background and Error Analysis

After upgrading MySQL database from earlier versions to 5.7.8-rc, users may encounter an error message upon logging into the server: Table 'performance_schema.session_variables' doesn't exist. This error typically occurs during the upgrade process when changes to the performance_schema system database architecture are not applied correctly. performance_schema is a critical component in MySQL for monitoring server performance, and its table structures were significantly updated in version 5.7 to provide more granular session variable information.

Specifically, in MySQL 5.7, the session_variables table was introduced or restructured to replace related views from older versions. If upgrade scripts fail to update these system tables successfully, it results in the table non-existence error. This is often because the mysql_upgrade tool was not run, or the MySQL service was not restarted after running it to load the new structures. The error not only affects user login but may also disrupt monitoring tools and queries that rely on performance_schema.

Core Solution: Using the mysql_upgrade Tool

Based on best practices, the primary method to resolve this error is to run the mysql_upgrade command. This tool is an official MySQL upgrade assistant designed to check and fix inconsistencies in system tables. Below is a detailed explanation of the specific steps:

First, execute the command as the root user: mysql_upgrade -u root -p --force. Here, -u root specifies the username, -p prompts for a password, and the --force option forces the upgrade to run even if the tool might suggest skipping under certain conditions. This command scans all databases, updating the architecture of system tables like performance_schema to ensure compatibility with MySQL version 5.7.8-rc.

After running mysql_upgrade, it is essential to restart the MySQL service for the changes to take effect. For example, on Linux systems using systemd, the command can be: systemctl restart mysqld. Restarting the service reloads the updated system tables, thereby eliminating the 'session_variables' table non-existence error. This combined operation has been verified as efficient and reliable, restoring normal database functionality.

Alternative Methods and In-Depth Discussion

In addition to the main solution, other approaches can serve as temporary or supplementary measures. For instance, some users have reported resolving the issue by setting the global variable show_compatibility_56 to ON: set @@global.show_compatibility_56=ON;. This variable was introduced in MySQL 5.7 to control whether views compatible with version 5.6 are displayed, potentially bypassing some architecture checks, but it is not a long-term solution as it may hide other upgrade issues.

From a technical perspective, the advantage of mysql_upgrade lies in its comprehensiveness: it not only fixes table structures but also handles other potential issues like indexes and storage engines. In contrast, setting compatibility variables may only alleviate symptoms rather than address root causes. Therefore, after an upgrade, it is recommended to prioritize running mysql_upgrade and restarting the service to ensure system stability and performance.

Conclusion and Best Practice Recommendations

In summary, the 'session_variables' table error after upgrading MySQL to 5.7.8-rc stems from incorrect application of performance_schema architecture changes. By running mysql_upgrade -u root -p --force and restarting the MySQL service, this issue can be efficiently resolved. As preventive measures, data should be backed up before upgrading, mysql_upgrade should be run immediately after the upgrade, and error logs should be monitored to catch other potential issues. This helps ensure a smooth transition to the new version, avoiding disruptions in database operations.