Bug #57609 performance_schema does not work with lower_case_table_names
Submitted: 20 Oct 2010 21:01 Modified: 6 Jan 2011 1:12
Reporter: Matthew Lord Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S1 (Critical)
Version:5.5.7 OS:Any
Assigned to: Marc ALFF CPU Architecture:Any

[20 Oct 2010 21:01] Matthew Lord
Description:
You cannot use performance_schema if you're also using lower_case_table_names.

If you run mysql_install_db with lower_case_table_names then you will get these
errors for each table:
Native table 'performance_schema'.'XXX' has the wrong structure

You will not be able to use any of them.

If you turn lower_case_table_names on AFTER the tables are created then of course
you get:
Table 'performance_schema.xxx' doesn't exist  

How to repeat:
Use performance_schema and lower_case_table_names.

Suggested fix:
Why not simply use lower case table names in performance_schema?
[21 Oct 2010 15:41] MySQL Verification Team
seen bug #57009 ?
[21 Oct 2010 16:36] Peter Laursen
@ Shane .. that other report was classified as 'feature request'.  In the MySQL dialect that means 'nobody will touch that for the rest of their life'.
[26 Oct 2010 20:12] Axel Schwenke
see also bug #57009
[28 Oct 2010 11:19] Valeriy Kravchuk
Bug #57798 was marked as a duplicate of this one.
[3 Nov 2010 15:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122732

3102 Marc Alff	2010-11-03
      Bug#57609 performance_schema does not work with lower_case_table_names
      
      Before this fix, the performance schema tables were defined in UPPERCASE.
      
      This was incompatible with the lowercase_table_names option, and caused
      issues with the install / upgrade process, when changing the lower case
      table names setting *after* the install or upgrade.
      
      With this fix, all performance schema tables are exposed with lowercase names.
      As a result, the name of the performance schema table is always lowercase,
      no matter how / if / when the lowercase_table_names setting if changed.
[6 Nov 2010 1:13] Christopher Powers
Reviewed code changes, built with patch, did ad hoc tests with lower-case-table-names=0, 1 and 2.

All mtr regression tests passed except perfschema.pfs_upgrade.

Patch approved pending fix to pfs_upgrade.
[9 Nov 2010 5:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123166

3336 Marc Alff	2010-11-09
      Bug#57609 performance_schema does not work with lower_case_table_names
      
      Merge to mysql-trunk-bugfixing (5.6), fixed 5.6 specific test cases.
[9 Nov 2010 5:15] Marc ALFF
Documenting a tool that helps with code reviews and to verify that a merge is correct:

bzr diff --diff-options=--ignore-case
[9 Nov 2010 17:08] Marc ALFF
Pushed into:
- mysql-5.5-bugfixing
- mysql-trunk-bugfixing
- mysql-next-mr-bugfixing

Doc team:

There is some impact on the existing documentation for the performance schema.

Performance schema tables are not named performance_schema.XXX (in uppercase) anymore, but now named performance_schema.xxx (in lowercase).

The benefit is that this naming is compatible with the server "lower_case_table_name" setting, in *all* cases.

Before this fix,

(a) - using P_S with platforms natively using lower_case_table_names=1 (windows) or 2 (mac osx) did work, with both uppercase and lowercase table names.

(b) - using P_S with platforms natively using lower_case_table_names=0 (all unix) did work, only if lower_case_table_names was not changed.
The user application had to code "select * from XXX" for performance schema tables, which is not natural given that every other system tables can be used in lowercase.

(c) - using P_S on platforms natively using lower_case_table_names=0 (all unix) was broken when the lower_case_table_names setting was later changed to 1 or 2.

With this fix,

(a) is unchanged

(b) is changed, as the only valid syntax is now "select * from xxx" (in lowercase).

(c) was the reported bug, which is fixed.
Both "select * from XXX" and "select * from xxx" now works on unix platforms, with lower_case_table_names=1

This fix renamed all tables in lowercase, for all platforms.

Since the performance schema tables are named in lowercase, the name itself is more stable and not subject to change depending on the value of lower_case_table_names, which makes the entire install and upgrade process much simpler.

Writing an application that can be deployed on:
- different H/W platforms
- different lower_case_table_names configurations
is now much easier, since performance schema tables can be accessed as "select * from xxx", in all cases.
[13 Nov 2010 16:22] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:30] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:alexander.nozdrin@oracle.com-20101113152540-gxro4g0v29l27f5x) (pib:21)
[7 Dec 2010 19:19] Paul DuBois
Noted in 5.5.8 changelog.

Previously, tables in the performance_schema database had uppercase
names. This was incompatible with the lowercase_table_names system
variable, and caused issues the variable value was changed after
installing or upgrading.

Now performance_schema table names are lowercase, so they appear in
uniform lettercase regardless of the lowercase_table_names setting.
References to these tables in SQL statements should be given in
lowercase. This is an incompatible change, but provides compatible
behavior across different values of lower_case_table_names.

If you upgrade to MySQL 5.5.8 from an earlier version of MySQL 5.5,
be sure to run mysql_upgrade to change the names of existing
performance_schema tables from uppercase to lowercase.
[16 Dec 2010 22:26] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
[23 Dec 2010 3:35] Roel Van de Paar
5.5.7rc > 5.5.8 upgrade when using lower_case_table_names=1 on Linux gives this when executing mysql_upgrade:

Running 'mysql_fix_privilege_tables'...
ERROR 1010 (HY000) at line 153: Error dropping database (can't rmdir './performance_schema', errno: 39)
ERROR 1007 (HY000) at line 160: Can't create database 'performance_schema'; database exists
FATAL ERROR: Upgrade failed
[23 Dec 2010 3:58] Roel Van de Paar
Workaround procedure which works for me:

1) Shutdown mysql instance
2) Remove the performance_schema/*.frm files from the data directory 
3) Create a stand alone dummy 5.5.8 installation
4) Copy the performance_schema/*.frm files from the dummy MySQL instance to the upgraded 5.5.8 instance
5) Execute mysql_upgade --force - check if does not produces any errors
[6 Jan 2011 1:12] Paul DuBois
Added the workaround to the changelog entry.