Bug #78159 Document behavior of SHOW command with show_compatibilty OFF &PFS compiled out
Submitted: 21 Aug 2015 0:52 Modified: 1 Oct 2015 18:08
Reporter: Anitha Gopi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7.9 OS:Any
Assigned to: CPU Architecture:Any

[21 Aug 2015 0:52] Anitha Gopi
Description:
Show commands fail with error if SHOW_COMPATIBILTY_56 is set to OFF on a build compiled without PFS. This can be confusing to the user. SHOW_COMPATIBILTY_56 is OFF by default from 5.7.9. The error message does not give any indication that the command can be made to work by setting this variable to ON

How to repeat:
1. Set cmake flag -DWITH_PERFSCHEMA_STORAGE_ENGINE=0 and compile
2. Start server
3. mysql> set @@global.show_compatibility_56=OFF;
Query OK, 0 rows affected (0.00 sec)

 
mysql>  select @@show_compatibility_56;
+-------------------------+
| @@show_compatibility_56 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

4. mysql> show status;
ERROR 1146 (42S02): Table 'performance_schema.session_status' doesn't exist
mysql> show variables;
ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist
mysql> show global status;
ERROR 1146 (42S02): Table 'performance_schema.global_status' doesn't exist
mysql> show global variables;
ERROR 1146 (42S02): Table 'performance_schema.global_variables' doesn't exist
mysql> show session status;
ERROR 1146 (42S02): Table 'performance_schema.session_status' doesn't exist
mysql> show session variables;
ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist

Suggested fix:
1. Fix error message to indicate that show commands can be made to work by setting show_compatibility_56 to ON
2. If error message cannot be fixed document the situation clearly

Filing against product for now. Please reassign if the decision is to document.
[1 Oct 2015 18:08] Paul Dubois
Noted in 5.7.9 changelog.

With the show_compatibility_56 system variable disabled, SHOW
VARIABLES and SHOW STATUS statements failed if MySQL was compiled 
without Performance Schema support. Consequently, it is no longer to
compile without the Performance Schema. If it is desired to compile
without particular types of instrumentation, that can be done with
the following CMake options:
      
DISABLE_PSI_COND
DISABLE_PSI_FILE
DISABLE_PSI_IDLE
DISABLE_PSI_MEMORY
DISABLE_PSI_METADATA
DISABLE_PSI_MUTEX
DISABLE_PSI_PS
DISABLE_PSI_RWLOCK
DISABLE_PSI_SOCKET
DISABLE_PSI_SP
DISABLE_PSI_STAGE
DISABLE_PSI_STATEMENT
DISABLE_PSI_STATEMENT_DIGEST
DISABLE_PSI_TABLE
DISABLE_PSI_THREAD
DISABLE_PSI_TRANSACTION

For example, to compile without mutex instrumentation, configure
MySQL using the -DDISABLE_PSI_MUTEX=1 option.