## Test the Performance Schema System service
##
## Verify that the SOURCE column in all wait, stage, statement,
## transaction and metadata lock tables is set to NULL after a plugin
## or component is unloaded.
##
## 0. Setup
## 1. Install an example plugin to generate wait events
## 2. Generate stage, statement and transaction events
## 3. Unload the component / plugin
## 4. Verify that the SOURCE column is null in relevant tables
## 5. Re-install the test plugin
## 6. Verify that the SOURCE column is re-enabled
## 7. Cleanup

#
# 0) Setup
#
Connection default
USE performance_schema;

# Disable all events from the control thread.
UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID();

# Disable all events.
UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'NO';

# Clear the tables that have a SOURCE column.
TRUNCATE events_waits_current;
TRUNCATE events_waits_history;
TRUNCATE events_waits_history_long;
TRUNCATE events_stages_current;
TRUNCATE events_stages_history;
TRUNCATE events_stages_history_long;
TRUNCATE events_statements_current;
TRUNCATE events_statements_history;
TRUNCATE events_statements_history_long;
TRUNCATE events_transactions_current;
TRUNCATE events_transactions_history;
TRUNCATE events_transactions_history_long;
# metadata_locks is not truncatable

#
# 1) Install a plugin that generates its own wait events.
#
Connection con0
USE test;
INSTALL PLUGIN pfs_example_plugin_employee SONAME 'pfs_example_plugin_employee.so';

Connection default

# Enable events that record the source file and line.
UPDATE performance_schema.setup_instruments SET enabled = 'yes', timed = 'yes'
  WHERE name LIKE 'stage/%' OR name LIKE 'statement/%' OR name = 'transaction' OR
name LIKE '%metadata/sql%' OR name LIKE 'wait/synch/mutex/sql/THD::%';

Connection con0

#
# 2) Generate wait, stage, statement, transaction and metadata lock events. Keep the transaction open for now.
#
INSERT INTO performance_schema.pfs_example_employee_name VALUES (99, "First", "Last");
UPDATE performance_schema.pfs_example_employee_name SET employee_number = 100 where employee_number = 99;
DELETE FROM performance_schema.pfs_example_employee_name;

CREATE TABLE t1 (s1 INT);
START TRANSACTION;
INSERT INTO t1 VALUES (1), (2), (3);

Connection default

# Verify that the plugin registered and generated the wait events.
## TODO Resolve timing issues with events_waits_history. ##
# SELECT (COUNT(*) > 0) AS "Expect 1" FROM performance_schema.events_waits_history WHERE event_name LIKE "%pfs_example%";
# SELECT (COUNT(*) > 0) AS "Expect 1" FROM performance_schema.events_waits_history_long WHERE event_name LIKE "%pfs_example%";

# Verify that each table is non-empty and that the SOURCE columns are populated.

SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_current AS t1 WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_history WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_history_long WHERE source <> "";
Expect 1
1
# May or may not contain rows, but shares same code path as _history and _history_long
# SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_current WHERE source <> "";

SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history_long WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_current WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_history WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_history_long WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_current WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_history WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_history_long WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM metadata_locks WHERE object_schema = "test" AND source <> "";
Expect 1
1

#
# 3) Uninstall the test plugin to force a reset of the SOURCE columns.
#
UNINSTALL PLUGIN pfs_example_plugin_employee;

#
# 4) Verify that the SOURCE column was reset for all events.
#
SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_waits_current WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_waits_history WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_waits_history_long WHERE source <> "";
Expect 1
1
# May or may not contain rows, but shares same code path as _history and _history_long
# SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_current WHERE source = "";

# Some stage events occur after the plugin is unloaded, so confirm that at least some of the SOURCE columns were reset.
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history WHERE source = "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history_long WHERE source = "";
Expect 1
1

SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_statements_current WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_statements_history WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_statements_history_long WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_transactions_current WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_transactions_history WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) = 0) AS "Expect 1" FROM events_transactions_history_long WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) = 0) AS "Expect 1" FROM metadata_locks WHERE object_schema = "test" AND source <> "";
Expect 1
1

Connection con0

# Commit the open transaction from step 2.
COMMIT;

Connection default

# Clear tables with SOURCE columns.
TRUNCATE events_waits_current;
TRUNCATE events_waits_history;
TRUNCATE events_waits_history_long;
TRUNCATE events_stages_current;
TRUNCATE events_stages_history;
TRUNCATE events_stages_history_long;
TRUNCATE events_statements_current;
TRUNCATE events_statements_history;
TRUNCATE events_statements_history_long;
TRUNCATE events_transactions_current;
TRUNCATE events_transactions_history;
TRUNCATE events_transactions_history_long;
# metadata_locks is not truncatable

Connection con0

#
# 5) Verify that the SOURCE columns are re-enabled after the reset.
#

# Re-install the plugin.
INSTALL PLUGIN pfs_example_plugin_employee SONAME 'pfs_example_plugin_employee.so';

# Generate new wait, stage, statement and transaction events. Leave the transaction open.
INSERT INTO performance_schema.pfs_example_employee_name VALUES (99, "First", "Last");
UPDATE performance_schema.pfs_example_employee_name SET employee_number = 100 where employee_number = 99;
DELETE FROM performance_schema.pfs_example_employee_name;

START TRANSACTION;
INSERT INTO t1 VALUES (4), (5), (6);

Connection default

#
# 6) Verify that each table is non-empty and that the SOURCE columns are populated.
#
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_current AS t1 WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_history WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_waits_history_long WHERE source <> "";
Expect 1
1
# May or may not contain rows, but shares same code path as _history and _history_long
# SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_current WHERE source <> "";

SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_stages_history_long WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_current WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_history WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_statements_history_long WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_current WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_history WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM events_transactions_history_long WHERE source <> "";
Expect 1
1
SELECT (COUNT(*) > 0) AS "Expect 1" FROM metadata_locks WHERE object_schema = "test" AND source <> "";
Expect 1
1

Connection con0

#
# 7) Clean up
#

# Commit the open transaction from step 5.
COMMIT;

Connection default
DROP TABLE test.t1;
UNINSTALL PLUGIN pfs_example_plugin_employee;
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';