mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
90 lines
2.8 KiB
SQL
90 lines
2.8 KiB
SQL
-- Copyright (c) 2014, 2015, 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
|
|
|
|
DROP PROCEDURE IF EXISTS ps_truncate_all_tables;
|
|
|
|
DELIMITER $$
|
|
|
|
CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE ps_truncate_all_tables (
|
|
IN in_verbose BOOLEAN
|
|
)
|
|
COMMENT '
|
|
Description
|
|
-----------
|
|
|
|
Truncates all summary tables within Performance Schema,
|
|
resetting all aggregated instrumentation as a snapshot.
|
|
|
|
Parameters
|
|
-----------
|
|
|
|
in_verbose (BOOLEAN):
|
|
Whether to print each TRUNCATE statement before running
|
|
|
|
Example
|
|
-----------
|
|
|
|
mysql> CALL sys.ps_truncate_all_tables(false);
|
|
+---------------------+
|
|
| summary |
|
|
+---------------------+
|
|
| Truncated 44 tables |
|
|
+---------------------+
|
|
1 row in set (0.10 sec)
|
|
|
|
Query OK, 0 rows affected (0.10 sec)
|
|
'
|
|
SQL SECURITY INVOKER
|
|
DETERMINISTIC
|
|
MODIFIES SQL DATA
|
|
BEGIN
|
|
DECLARE v_done INT DEFAULT FALSE;
|
|
DECLARE v_total_tables INT DEFAULT 0;
|
|
DECLARE v_ps_table VARCHAR(64);
|
|
DECLARE ps_tables CURSOR FOR
|
|
SELECT table_name
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE table_schema = 'performance_schema'
|
|
AND (table_name LIKE '%summary%'
|
|
OR table_name LIKE '%history%');
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
|
|
|
|
OPEN ps_tables;
|
|
|
|
ps_tables_loop: LOOP
|
|
FETCH ps_tables INTO v_ps_table;
|
|
IF v_done THEN
|
|
LEAVE ps_tables_loop;
|
|
END IF;
|
|
|
|
SET @truncate_stmt := CONCAT('TRUNCATE TABLE performance_schema.', v_ps_table);
|
|
IF in_verbose THEN
|
|
SELECT CONCAT('Running: ', @truncate_stmt) AS status;
|
|
END IF;
|
|
|
|
PREPARE truncate_stmt FROM @truncate_stmt;
|
|
EXECUTE truncate_stmt;
|
|
DEALLOCATE PREPARE truncate_stmt;
|
|
|
|
SET v_total_tables = v_total_tables + 1;
|
|
END LOOP;
|
|
|
|
CLOSE ps_tables;
|
|
|
|
SELECT CONCAT('Truncated ', v_total_tables, ' tables') AS summary;
|
|
|
|
END$$
|
|
|
|
DELIMITER ;
|