| 1 |
|
| 2 |
## Test the Performance Schema Notification service WL#8881 |
| 3 |
## |
| 4 |
## 1. Install component to register callbacks for each API |
| 5 |
## 2. Launch multiple sessions to trigger callback events, log the events to file |
| 6 |
## 3. Import log file into a table |
| 7 |
## 4. Verify the contents of the table |
| 8 |
## Disable resource groups for this test. |
| 9 |
SET DEBUG='+d, disable_rg_pfs_notifications'; |
| 10 |
|
| 11 |
## Install test component |
| 12 |
INSTALL COMPONENT "file://component_test_pfs_notification"; |
| 13 |
|
| 14 |
## Create 2 users to control the test mode, and 3 users for testing |
| 15 |
CREATE USER PFS_MTR_MODE_ENABLE; |
| 16 |
CREATE USER PFS_MTR_MODE_DISABLE; |
| 17 |
CREATE USER PFS_MTR_REGISTER_INTERNAL; |
| 18 |
CREATE USER PFS_MTR_UNREGISTER_INTERNAL; |
| 19 |
CREATE USER PFS_MTR_NEGATIVE_TEST_CASES; |
| 20 |
CREATE USER PFS_USER1; |
| 21 |
CREATE USER PFS_USER2; |
| 22 |
CREATE USER PFS_USER3; |
| 23 |
GRANT ALL ON *.* TO PFS_MTR_MODE_ENABLE; |
| 24 |
GRANT ALL ON *.* TO PFS_MTR_MODE_DISABLE; |
| 25 |
GRANT ALL ON *.* TO PFS_MTR_REGISTER_INTERNAL; |
| 26 |
GRANT ALL ON *.* TO PFS_MTR_UNREGISTER_INTERNAL; |
| 27 |
GRANT ALL ON *.* TO PFS_MTR_NEGATIVE_TEST_CASES; |
| 28 |
GRANT ALL ON *.* TO PFS_USER1; |
| 29 |
GRANT ALL ON *.* TO PFS_USER2; |
| 30 |
GRANT ALL ON *.* TO PFS_USER3; |
| 31 |
|
| 32 |
## Enable MTR mode by connecting with a pre-defined username. |
| 33 |
|
| 34 |
|
| 35 |
## Test internal registration |
| 36 |
|
| 37 |
|
| 38 |
## Test negative test cases |
| 39 |
|
| 40 |
|
| 41 |
## Test unregister internal |
| 42 |
|
| 43 |
|
| 44 |
## Clean up special test cases |
| 45 |
## Disconnect internal registration user |
| 46 |
## Disconnect internal unregistration user |
| 47 |
## Disconnect negative test case user |
| 48 |
|
| 49 |
## Start 2 sessions to generate thread_create and session_connect events. |
| 50 |
## The session_connect callback assigns a resource group name for each user. |
| 51 |
|
| 52 |
## Session 1 |
| 53 |
|
| 54 |
## Verify PFS_USER1 resource group in PERFORMANCE_SCHEMA.THREADS |
| 55 |
SELECT name, type, processlist_user, processlist_host, processlist_db, resource_group |
| 56 |
FROM performance_schema.threads |
| 57 |
WHERE resource_group = 'RESOURCE_GROUP_3_2_1' AND processlist_user = 'PFS_USER1'; |
| 58 |
name type processlist_user processlist_host processlist_db resource_group |
| 59 |
thread/sql/one_connection FOREGROUND PFS_USER1 localhost test RESOURCE_GROUP_3_2_1 |
| 60 |
|
| 61 |
## Start session 2 |
| 62 |
connect (con2, localhost, PFS_USER2, , test) |
| 63 |
|
| 64 |
## Verify PFS_USER2 in PERFORMANCE_SCHEMA.THREADS |
| 65 |
SELECT name, type, processlist_user, processlist_host, processlist_db, resource_group |
| 66 |
FROM performance_schema.threads |
| 67 |
WHERE resource_group = 'RESOURCE_GROUP_3_2_1' AND processlist_user = 'PFS_USER2'; |
| 68 |
name type processlist_user processlist_host processlist_db resource_group |
| 69 |
thread/sql/one_connection FOREGROUND PFS_USER2 localhost test RESOURCE_GROUP_3_2_1 |
| 70 |
|
| 71 |
## Change user from PFS_USER2 to PFS_USER3 |
| 72 |
|
| 73 |
## Disconnect 2 sessions to generate session_disconnect and thread_destroy events. |
| 74 |
|
| 75 |
## Disconnect session 1 |
| 76 |
## Disconnect session 2 |
| 77 |
## Disconnect primary control session |
| 78 |
|
| 79 |
## Disable MTR mode and activate RQG mode by connecting with a pre-defined username. |
| 80 |
|
| 81 |
## Verify mode change |
| 82 |
|
| 83 |
SELECT name, type, processlist_user, processlist_host, processlist_db, resource_group |
| 84 |
FROM performance_schema.threads WHERE processlist_user = 'PFS_MTR_MODE_DISABLE'; |
| 85 |
name type processlist_user processlist_host processlist_db resource_group |
| 86 |
thread/sql/one_connection FOREGROUND PFS_MTR_MODE_DISABLE localhost test RESOURCE_GROUP_1 |
| 87 |
## Disconnect second control session |
| 88 |
|
| 89 |
## Uninstall component |
| 90 |
|
| 91 |
UNINSTALL COMPONENT 'file://component_test_pfs_notification'; |
| 92 |
|
| 93 |
## Create results table |
| 94 |
|
| 95 |
CREATE TABLE test.callback_events (callback TEXT, handle INT, ret_code INT, |
| 96 |
thread_id BIGINT UNSIGNED, plist_id BIGINT UNSIGNED, os_thread BIGINT UNSIGNED, |
| 97 |
resource_group TEXT, user TEXT, host TEXT, vcpu TEXT, priority TEXT); |
| 98 |
|
| 99 |
## Import log file |
| 100 |
|
| 101 |
LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/test_pfs_notification.log' INTO TABLE test.callback_events |
| 102 |
FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n' STARTING BY '*** ' |
| 103 |
(@label, callback, @label, handle, @label, ret_code, @label, thread_id, @label, plist_id, |
| 104 |
@label, os_thread, @label, resource_group, @label, user, @label, host, @label, vcpu, @label, priority); |
| 105 |
|
| 106 |
## Verify results |
| 107 |
|
| 108 |
SELECT * FROM test.callback_events WHERE callback = "session_change_user"; |
| 109 |
callback handle ret_code thread_id plist_id os_thread resource_group user host |
| 110 |
session_change_user ... 0 ... ... ... RESOURCE_GROUP_3_2_1 PFS_USER3 localhost |
| 111 |
|
| 112 |
SELECT * FROM test.callback_events WHERE callback = "session_connect; |
| 113 |
callback handle ret_code thread_id plist_id os_thread resource_group user host |
| 114 |
session_connect ... 0 ... ... ... RESOURCE_GROUP_3_2_1 PFS_USER1 localhost |
| 115 |
|
| 116 |
SELECT * FROM test.callback_events WHERE callback = "session_disconnect"; |
| 117 |
callback handle ret_code thread_id plist_id os_thread resource_group user host |
| 118 |
session_disconnect ... 0 ... ... ... RESOURCE_GROUP_3_2_1 PFS_USER1 localhost |
| 119 |
|
| 120 |
SELECT * FROM test.callback_events WHERE callback = "thread_create"; |
| 121 |
callback handle ret_code thread_id plist_id os_thread resource_group user host |
| 122 |
|
| 123 |
SELECT * FROM test.callback_events WHERE callback = "thread_destroy"; |
| 124 |
callback handle ret_code thread_id plist_id os_thread resource_group user host |
| 125 |
thread_destroy ... 0 ... ... ... RESOURCE_GROUP_3_2_1 PFS_USER1 localhost |
| 126 |
|
| 127 |
SELECT * FROM test.callback_events WHERE callback = "register_notification_internal"; |
| 128 |
callback handle ret_code thread_id plist_id os_thread resource_group user host |
| 129 |
register_notification_internal 3 ... ... ... ... PFS_MTR_REGISTER_INTERNAL localhost |
| 130 |
|
| 131 |
SELECT * FROM test.callback_events WHERE callback LIKE "unregister_notification_internal"; |
| 132 |
callback handle ret_code thread_id plist_id os_thread resource_group user host |
| 133 |
unregister_notification_internal 4 0 ... ... ... PFS_MTR_UNREGISTER_INTERNAL localhost |
| 134 |
|
| 135 |
SELECT * FROM test.callback_events WHERE callback = "register_notification(bad_cb)"; |
| 136 |
callback handle ret_code thread_id plist_id os_thread resource_group user host |
| 137 |
|
| 138 |
SELECT * FROM test.callback_events WHERE callback = "register_notification(nullptr)"; |
| 139 |
callback handle ret_code thread_id plist_id os_thread resource_group user host |
| 140 |
|
| 141 |
SELECT * FROM test.callback_events WHERE callback = "unregister_notification(bad_handle)"; |
| 142 |
callback handle ret_code thread_id plist_id os_thread resource_group user host |
| 143 |
|
| 144 |
## Clean up |
| 145 |
|
| 146 |
DROP USER PFS_MTR_MODE_ENABLE; |
| 147 |
DROP USER PFS_MTR_MODE_DISABLE; |
| 148 |
DROP USER PFS_MTR_REGISTER_INTERNAL; |
| 149 |
DROP USER PFS_MTR_UNREGISTER_INTERNAL; |
| 150 |
DROP USER PFS_MTR_NEGATIVE_TEST_CASES; |
| 151 |
DROP USER PFS_USER1; |
| 152 |
DROP USER PFS_USER2; |
| 153 |
DROP USER PFS_USER3; |
| 154 |
DROP TABLE test.callback_events; |
| 155 |
SET DEBUG='-d,disable_rg_pfs_notifications'; |