mariadb/scripts/sys_schema/procedures/ps_setup_show_disabled.sql

-- Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; version 2 of the License.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

DELIMITER $$

CREATE OR REPLACE DEFINER='mariadb.sys'@'localhost' PROCEDURE ps_setup_show_disabled (
        IN in_show_instruments BOOLEAN,
        IN in_show_threads BOOLEAN
    )
    COMMENT '
             Description
             -----------

             Shows all currently disable Performance Schema configuration.

             Disabled users is only available for MySQL 5.7.6 and later.
             In earlier versions it was only possible to enable users.

             Parameters
             -----------

             in_show_instruments (BOOLEAN):
               Whether to print disabled instruments (can print many items)

             in_show_threads (BOOLEAN):
               Whether to print disabled threads

             Example
             -----------

             mysql> CALL sys.ps_setup_show_disabled(TRUE, TRUE);
             +----------------------------+
             | performance_schema_enabled |
             +----------------------------+
             |                          1 |
             +----------------------------+
             1 row in set (0.00 sec)

             +--------------------+
             | disabled_users     |
             +--------------------+
             | \'mark\'@\'localhost\' |
             +--------------------+
             1 row in set (0.00 sec)

             +-------------+----------------------+---------+-------+
             | object_type | objects              | enabled | timed |
             +-------------+----------------------+---------+-------+
             | EVENT       | mysql.%              | NO      | NO    |
             | EVENT       | performance_schema.% | NO      | NO    |
             | EVENT       | information_schema.% | NO      | NO    |
             | FUNCTION    | mysql.%              | NO      | NO    |
             | FUNCTION    | performance_schema.% | NO      | NO    |
             | FUNCTION    | information_schema.% | NO      | NO    |
             | PROCEDURE   | mysql.%              | NO      | NO    |
             | PROCEDURE   | performance_schema.% | NO      | NO    |
             | PROCEDURE   | information_schema.% | NO      | NO    |
             | TABLE       | mysql.%              | NO      | NO    |
             | TABLE       | performance_schema.% | NO      | NO    |
             | TABLE       | information_schema.% | NO      | NO    |
             | TRIGGER     | mysql.%              | NO      | NO    |
             | TRIGGER     | performance_schema.% | NO      | NO    |
             | TRIGGER     | information_schema.% | NO      | NO    |
             +-------------+----------------------+---------+-------+
             15 rows in set (0.00 sec)

             +----------------------------------+
             | disabled_consumers               |
             +----------------------------------+
             | events_stages_current            |
             | events_stages_history            |
             | events_stages_history_long       |
             | events_statements_history        |
             | events_statements_history_long   |
             | events_transactions_history      |
             | events_transactions_history_long |
             | events_waits_current             |
             | events_waits_history             |
             | events_waits_history_long        |
             +----------------------------------+
             10 rows in set (0.00 sec)

             Empty set (0.00 sec)
             
             +---------------------------------------------------------------------------------------+-------+
             | disabled_instruments                                                                  | timed |
             +---------------------------------------------------------------------------------------+-------+
             | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc                                             | NO    |
             | wait/synch/mutex/sql/LOCK_des_key_file                                                | NO    |
             | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit                                       | NO    |
             ...
             | memory/sql/servers_cache                                                              | NO    |
             | memory/sql/udf_mem                                                                    | NO    |
             | wait/lock/metadata/sql/mdl                                                            | NO    |
             +---------------------------------------------------------------------------------------+-------+
             547 rows in set (0.00 sec)

             Query OK, 0 rows affected (0.01 sec)
            '
    SQL SECURITY INVOKER
    NOT DETERMINISTIC
    READS SQL DATA
BEGIN
    SELECT @@performance_schema AS performance_schema_enabled;

    -- In 5.7.6 and later the setup_actors table has an ENABLED column to
    -- specify whether the actor is enabled. Before that all actors matched
    -- in the setup_actors table were enabled.
    -- So only execute the query in 5.7.6+
    /*!50706
    SELECT CONCAT('\'', user, '\'@\'', host, '\'') AS disabled_users
      FROM performance_schema.setup_actors
     WHERE enabled = 'NO'
     ORDER BY disabled_users;
    */

    SELECT object_type,
           CONCAT(object_schema, '.', object_name) AS objects,
           enabled,
           timed
      FROM performance_schema.setup_objects
     WHERE enabled = 'NO'
     ORDER BY object_type, objects;

    SELECT name AS disabled_consumers
      FROM performance_schema.setup_consumers
     WHERE enabled = 'NO'
     ORDER BY disabled_consumers;

    IF (in_show_threads) THEN
        SELECT IF(name = 'thread/sql/one_connection', 
                  CONCAT(processlist_user, '@', processlist_host), 
                  REPLACE(name, 'thread/', '')) AS disabled_threads,
        TYPE AS thread_type
          FROM performance_schema.threads
         WHERE INSTRUMENTED = 'NO'
         ORDER BY disabled_threads;
    END IF;

    IF (in_show_instruments) THEN
        SELECT name AS disabled_instruments,
               timed
          FROM performance_schema.setup_instruments
         WHERE enabled = 'NO'
         ORDER BY disabled_instruments;
    END IF;
END$$

DELIMITER ;