Table 'performance_schema.session_variables

问题:

I m new at using MySql data base, i have downloaded EasyPHP-Devserver-16.1, when I run my server to update my data base schema this error message shows up.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'performance_schema.session_variables' doesn't exist

I know that the problem is not in my spring configuration file but in mysql server.

public class Configurations {

    protected static final String PROPERTY_NAME_DATABASE_DRIVER = "com.mysql.jdbc.Driver";
    protected static final String PROPERTY_NAME_DATABASE_PASSWORD = "";
    protected static final String PROPERTY_NAME_DATABASE_URL = "jdbc:mysql://127.0.0.1:3306/quraa";
    protected static final String PROPERTY_NAME_DATABASE_USERNAME = "root";

    private static final String PROPERTY_PACKAGES_TO_SCAN = "com.med.quraa.models";
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(DataSource dataSource, JpaVendorAdapter jpaVendorAdapter){
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setDataSource(dataSource);
        entityManagerFactoryBean.setJpaVendorAdapter(jpaVendorAdapter);
        entityManagerFactoryBean.setPackagesToScan(PROPERTY_PACKAGES_TO_SCAN);
        return entityManagerFactoryBean;
    }

    @Bean
    public DriverManagerDataSource dataSource(){
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName(PROPERTY_NAME_DATABASE_DRIVER);
        ds.setUrl(PROPERTY_NAME_DATABASE_URL);
        ds.setUsername(PROPERTY_NAME_DATABASE_USERNAME);
        ds.setPassword(PROPERTY_NAME_DATABASE_PASSWORD);
        return ds;
    }

    @Bean
    public JdbcTemplate jdbcTemplate(){
        JdbcTemplate jdbcTemplate=new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource());
        return jdbcTemplate;
    }

    @Bean
    public JpaVendorAdapter jpaVendorAdapter(){
        HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setDatabase(Database.MYSQL);
        adapter.setShowSql(true);
        adapter.setGenerateDdl(true);
        adapter.setDatabasePlatform("org.hibernate.dialect.MySQL5InnoDBDialect");
        return adapter;
    }

    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

}

Note that I have tested org.apache.tomcat.dbcp.dbcp.BasicDataSource also but I had the same error, that means that org.springframework.jdbc.datasource.DriverManagerDataSource have no problem with that

回答1:

1. First upgrade your MySql Server to solve the issue by running this command:

   mysql_upgrade -u root -p --force

2. Then restart the server:

回答2:

Solved... I opened cmd then :

cd [installation_path]\eds-binaries\dbserver\mysql5711x86x160420141510\bin

then executed this command line :

mysql_upgrade -u root -p --force

A server Restart is needed!

回答3:

Performance schema is not installed by default.
For checking, you can run the command

SHOW VARIABLES LIKE 'performance_schema';

Suppose, now you will see OFF

To enable it, start the server with the performance_schema variable enabled. For example, use these lines in your my.cnf file:

[mysqld]
performance_schema=ON

More details you can found in official documentation:

https://dev.mysql.com/doc/refman/en/performance-schema-quick-start.html

回答4:

Here's how to fix it (from the command line):

mysql -u {user} -p
mysql> set @@global.show_compatibility_56=ON;

In /etc/my.cnf:

show_compatibility_56 = 1

this one really works for me without upgrading mysql. After this you need to restart mysql.

回答5:

As some people asked about the part of the server restart, i will post my answer considering that part:

1 .Go to the directory of the MySQL installation folder in my case it was: C:\Program Files\MySQL\MySQL Server 5.7\bin.

2. Open CMD in this directory, then Run "mysql_upgrade -u root -p" command.

3. If your schema was protected by a password, you should enter the password when it is requested.

4. This will make checking of all tables of all schema, so you should wait until the checking is finished.

5. At the end the following message should appear:

Upgrade process completed successfully.

Checking if update is needed.

6. Restart the MySQL server and router services:

open task manager-->services-->right click on the services MySQL 
server and MySQL router-->restart them.

7. Connect to your server using your workbench, it should connect.