1-- Copyright (c) 2008, 2021, Oracle and/or its affiliates.
2--
3-- This program is free software; you can redistribute it and/or modify
4-- it under the terms of the GNU General Public License, version 2.0,
5-- as published by the Free Software Foundation.
6--
7-- This program is also distributed with certain software (including
8-- but not limited to OpenSSL) that is licensed under separate terms,
9-- as designated in a particular file or component or in included license
10-- documentation.  The authors of MySQL hereby grant you an additional
11-- permission to link the program and your derivative works with the
12-- separately licensed software that they have included with MySQL.
13--
14-- This program is distributed in the hope that it will be useful,
15-- but WITHOUT ANY WARRANTY; without even the implied warranty of
16-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17-- GNU General Public License, version 2.0, for more details.
18--
19-- You should have received a copy of the GNU General Public License
20-- along with this program; if not, write to the Free Software Foundation,
21-- 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301  USA
22
23delimiter ||;
24
25use mtr||
26
27CREATE DEFINER=root@localhost PROCEDURE check_testcase_perfschema()
28BEGIN
29  IF ((SELECT count(*) from information_schema.engines
30       where engine='PERFORMANCE_SCHEMA' and support='YES') = 1) THEN
31  BEGIN
32
33    BEGIN
34      -- For tests tampering with performance_schema table structure
35      DECLARE CONTINUE HANDLER for SQLEXCEPTION
36      BEGIN
37      END;
38
39      -- Leave the instruments in the same state
40      SELECT * from performance_schema.setup_instruments
41        where enabled='NO' order by NAME;
42    END;
43
44    -- Leave the consumers in the same state
45    SELECT * from performance_schema.setup_consumers
46      order by NAME;
47
48    -- Leave the actors setup in the same state
49    SELECT * from performance_schema.setup_actors
50      order by USER, HOST;
51
52    -- Leave the objects setup in the same state
53    SELECT * from performance_schema.setup_objects
54      order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
55  END;
56  END IF;
57END||
58
59--
60-- Procedure used to check if server has been properly
61-- restored after testcase has been run
62--
63CREATE DEFINER=root@localhost PROCEDURE check_testcase()
64BEGIN
65
66  CALL check_testcase_perfschema();
67
68  -- Dump all global variables except those that may change.
69  -- timestamp changes if time passes. server_uuid changes if server restarts.
70  SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
71    WHERE variable_name NOT IN ('timestamp', 'server_uuid',
72                                'innodb_file_format_max',
73                                'gtid_executed', 'gtid_purged',
74                                'group_replication_group_name')
75    ORDER BY VARIABLE_NAME;
76
77  SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES
78    WHERE variable_name = 'debug_sync';
79
80  -- Dump all databases, there should be none
81  -- except those that was created during bootstrap
82  SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
83
84  -- The test database should not contain any tables
85  SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES
86    WHERE table_schema='test';
87
88  -- Show "mysql" database, tables and columns
89  SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql
90    FROM INFORMATION_SCHEMA.TABLES
91      WHERE table_schema='mysql' AND table_name != 'ndb_apply_status'
92        ORDER BY tables_in_mysql;
93  SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql,
94         column_name, ordinal_position, column_default, is_nullable,
95         data_type, character_maximum_length, character_octet_length,
96         numeric_precision, numeric_scale, character_set_name,
97         collation_name, column_type, column_key, extra, column_comment
98    FROM INFORMATION_SCHEMA.COLUMNS
99      WHERE table_schema='mysql' AND table_name != 'ndb_apply_status'
100        ORDER BY columns_in_mysql;
101
102  -- Dump all events, there should be none
103  SELECT * FROM INFORMATION_SCHEMA.EVENTS;
104  -- Dump all triggers except mtr internals, only those in the sys schema should exist
105  -- do not select the CREATED column however, as tests like mysqldump.test / mysql_ugprade.test update this
106  SELECT TRIGGER_CATALOG, TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION,
107         EVENT_OBJECT_CATALOG, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE, ACTION_ORDER, ACTION_CONDITION,
108         ACTION_STATEMENT, ACTION_ORIENTATION, ACTION_TIMING ACTION_REFERENCE_OLD_TABLE, ACTION_REFERENCE_NEW_TABLE,
109         ACTION_REFERENCE_OLD_ROW, ACTION_REFERENCE_NEW_ROW, SQL_MODE, DEFINER CHARACTER_SET_CLIENT,
110         COLLATION_CONNECTION, DATABASE_COLLATION
111    FROM INFORMATION_SCHEMA.TRIGGERS
112   WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert');
113  -- Dump all created procedures, only those in the sys schema should exist
114  -- do not select the CREATED or LAST_ALTERED columns however, as tests like mysqldump.test / mysql_ugprade.test update this
115  SELECT SPECIFIC_NAME,ROUTINE_CATALOG,ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,
116         CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_NAME,COLLATION_NAME,
117         DTD_IDENTIFIER,ROUTINE_BODY,ROUTINE_DEFINITION,EXTERNAL_NAME,EXTERNAL_LANGUAGE,PARAMETER_STYLE,
118         IS_DETERMINISTIC,SQL_DATA_ACCESS,SQL_PATH,SECURITY_TYPE,SQL_MODE,ROUTINE_COMMENT,DEFINER,
119         CHARACTER_SET_CLIENT,COLLATION_CONNECTION,DATABASE_COLLATION
120    FROM INFORMATION_SCHEMA.ROUTINES;
121  -- Dump all views, only those in the sys schema should exist
122  SELECT * FROM INFORMATION_SCHEMA.VIEWS;
123
124  -- Dump all created compression dictionaries if InnoDB is enabled
125  IF ((SELECT COUNT(*) FROM information_schema.engines
126       WHERE engine = 'InnoDB' AND support IN ('YES', 'DEFAULT')) = 1) THEN
127    SELECT * FROM information_schema.xtradb_zip_dict ORDER BY name;
128  END IF;
129
130  SHOW GLOBAL STATUS LIKE 'slave_open_temp_tables';
131
132  -- Checksum system tables to make sure they have been properly
133  -- restored after test
134  -- skip mysql.proc however, as created timestamps may have been updated by mysqldump.test / mysql_ugprade.test
135  -- the above SELECT on I_S.ROUTINES ensures consistency across runs instead
136  checksum table
137    mysql.columns_priv,
138    mysql.db,
139    mysql.func,
140    mysql.help_category,
141    mysql.help_keyword,
142    mysql.help_relation,
143    mysql.host,
144    mysql.procs_priv,
145    mysql.tables_priv,
146    mysql.time_zone,
147    mysql.time_zone_leap_second,
148    mysql.time_zone_name,
149    mysql.time_zone_transition,
150    mysql.time_zone_transition_type,
151    mysql.user;
152
153END||
154
155--
156-- Procedure used by test case used to force all
157-- servers to restart after testcase and thus skipping
158-- check test case after test
159--
160CREATE DEFINER=root@localhost PROCEDURE force_restart()
161BEGIN
162  SELECT 1 INTO OUTFILE 'force_restart';
163END||
164