Errors on performance_schema tables after upgrade from 5.6.16-64.1 to 5.6.17-66.0

[Jericho Rivera]

From Percona Forums:
http://www.percona.com/forums/questions-discussions/mysql-and-percona-server/percona-server-5-6/16913-5-6-17-66-0-performance-schema-issues

After upgrading Percona Server 5.6.16-64.1 to latest Percona Server 5.6.17-66.0 errors on the log file show for every P_S table.
I was able to repeat on a clean CentOS 6.5 installation.

How to repeat:
- Install PS 5.6.16-64.1 from binaries

- Enable performance schema on my.cnf
[mysqld]
performance_schema=on

- Install Percona yum repo, then run yum update

See attached error log.

mysql> select @@version;
+-------------+
| @@version |
+-------------+
| 5.6.17-66.0 |
+-------------+
1 row in set (0.00 sec)

mysql> show global variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.accounts limit 1;
ERROR 1682 (HY000): Native table 'performance_schema'.'accounts' has the wrong structure
mysql>

[Laurynas Biveinis (laurynas-biveinis)]

Upstream?

[Valerii Kravchuk (valerii-kravchuk)]

Not upstream for sure. For the reason that Percona Server binaries for Ubuntu (of the same version) do not have this problem (I verified that).

[Laurynas Biveinis (laurynas-biveinis)]

I might be missing something, but if PS binaries on CentOS 6.5 show the issue and binaries on Ubuntu don't, how does that say that Oracle binaries on either are surely not affected? I'd test just to be sure.

[Jericho Rivera (jericho-rivera)]

Tested with Oracle MySQL, with the following procedures and can't repeat with either:

1st procedure:

- Installed 5.6.16 binaries
- Enabled P_S on my.cnf
- yum remove 5.6.16, then install 5.6.17 binaries
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using EditLine wrapper

Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.17 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 min 9 sec

Threads: 1 Questions: 6 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.086
--------------

mysql> show global variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.accounts limit 1
    -> ;
+------+-----------+---------------------+-------------------+
| USER | HOST | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+-----------+---------------------+-------------------+
| root | localhost | 1 | 1 |
+------+-----------+---------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit

Works! And encountered no errors on log file.

- yum update to latest 5.6.19, and nothing 'breaks'

2nd procedure
- Installed 5.6.16 binaries
- Enabled P_S on my.cnf
- Ran yum update to install 5.6.19

P_S does not break either. No errors on error log as well.

mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.19, for Linux (x86_64) using EditLine wrapper

Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.19 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 17 sec

Threads: 1 Questions: 5 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.294
--------------

mysql> show global variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.accounts limit 1;
+------+-----------+---------------------+-------------------+
| USER | HOST | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+-----------+---------------------+-------------------+
| root | localhost | 1 | 1 |
+------+-----------+---------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
bash-4.1# grep ERROR /var/lib/mysql/5164bba0dca8.err
bash-4.1#

[Vadim Tkachenko (vadim-tk)]

I have this problem with Ubuntu 12.04.4 LTS binaries installed from repo.

[Jericho Rivera (jericho-rivera)]

Looks like if you run

# mysql --upgrade-system-tables

the errors are gone.

Tested on same CentOS 6.5 server container.

bash-4.1# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17-66.0 Percona Server (GPL), Release 66.0, Revision 608

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

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> select * from performance_schema.accounts limit 1;
+------+-----------+---------------------+-------------------+
| USER | HOST | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+-----------+---------------------+-------------------+
| root | localhost | 1 | 1 |
+------+-----------+---------------------+-------------------+
1 row in set (0.00 sec)

mysql> select @@version;
+-------------+
| @@version |
+-------------+
| 5.6.17-66.0 |
+-------------+
1 row in set (0.00 sec)

mysql> exit
Bye

[Jericho Rivera (jericho-rivera)]

Disregard post #7, was testing it on Ubuntu 14.04 and not on CentOS 6.5.

[Matt (whereswardy)]

Can confirm that I cannot get mysql_upgrade --upgrade-system-tables to fix the issue on CentOS 6.5:

[root@slave3 ~]# mysql_upgrade --upgrade-system-tables
Looking for 'mysql' as: mysql
The --upgrade-system-tables option was used, databases won't be touched.
This installation of MySQL is already upgraded to 5.6.17-66.0, use --force if you still need to run mysql_upgrade

[root@slave3 ~]# mysql_upgrade --force --upgrade-system-tables
Looking for 'mysql' as: mysql
The --upgrade-system-tables option was used, databases won't be touched.
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
OK

[root@slave3 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 707024
Server version: 5.6.17-66.0-log Percona Server (GPL), Release 66.0, Revision 608

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

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> SELECT * FROM performance_schema.accounts;
ERROR 1682 (HY000): Native table 'performance_schema'.'accounts' has the wrong structure

[Jericho Rivera (jericho-rivera)]

Looks like mysql_upgrade --upgrade-system-tables fixed it only after restarting mysql server.

mysql> select * from performance_schema.accounts limit 1;
ERROR 1033 (HY000): Incorrect information in file: './performance_schema/accounts.frm'
mysql> exit
Bye

bash-4.1# mysql_upgrade --upgrade-system-tables
Looking for 'mysql' as: mysql
The --upgrade-system-tables option was used, databases won't be touched.
Running 'mysql_fix_privilege_tables'...
OK

I can still see errors on the log file:

bash-4.1# tail -n 40 /var/lib/mysql/aafa07ca99c5.err
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_statements_history_long.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_statements_summary_by_account_by_event_name.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_statements_summary_by_digest.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_statements_summary_by_host_by_event_name.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_statements_summary_by_thread_by_event_name.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_statements_summary_by_user_by_event_name.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_statements_summary_global_by_event_name.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_waits_current.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_waits_history.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_waits_history_long.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_waits_summary_by_account_by_event_name.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_waits_summary_by_host_by_event_name.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_waits_summary_by_instance.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_waits_summary_by_thread_by_event_name.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_waits_summary_by_user_by_event_name.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/events_waits_summary_global_by_event_name.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/file_instances.frm'
2014-06-17 07:39:42 516 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './performance_schema/file_summary_by_event_name.frm'
2014-06-17 07:39:42 516 [ERROR] /usr...

[Matt (whereswardy)]

Have performed the same test and can confirm that that solves the issue.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-381