1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
|
#################
# INITIAL STATE #
#################
update performance_schema.setup_objects
set ENABLED = "YES", TIMED = "YES"
where OBJECT_TYPE = "TABLE" and OBJECT_SCHEMA = "performance_schema";
FLUSH TABLES;
select * from INFORMATION_SCHEMA.tables
where TABLE_NAME like "pfs_example_%";
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
describe performance_schema.pfs_example_employee_name;
ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
show create table performance_schema.pfs_example_employee_name;
ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
select * from performance_schema.pfs_example_employee_name;
ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
###########
# INSTALL #
###########
INSTALL PLUGIN pfs_example_plugin_employee SONAME 'pfs_example_plugin_employee.xxx';
select name from mysql.plugin
where name like 'pfs_example_%';
name
pfs_example_plugin_employee
select TABLE_SCHEMA, TABLE_NAME, ENGINE, VERSION, TABLE_COMMENT
from INFORMATION_SCHEMA.tables
where TABLE_NAME like "pfs_example_%"
order by table_name;
TABLE_SCHEMA TABLE_NAME ENGINE VERSION TABLE_COMMENT
performance_schema pfs_example_employee_name PERFORMANCE_SCHEMA 10
performance_schema pfs_example_employee_salary PERFORMANCE_SCHEMA 10
performance_schema pfs_example_machine PERFORMANCE_SCHEMA 10
performance_schema pfs_example_machine_by_employee_by_type PERFORMANCE_SCHEMA 10
describe performance_schema.pfs_example_employee_name;
Field Type Null Key Default Extra
EMPLOYEE_NUMBER int NO PRI NULL
FIRST_NAME char(20) YES MUL NULL
LAST_NAME varchar(20) YES NULL
show create table performance_schema.pfs_example_employee_name;
Table Create Table
pfs_example_employee_name CREATE TABLE `pfs_example_employee_name` (
`EMPLOYEE_NUMBER` int NOT NULL,
`FIRST_NAME` char(20) DEFAULT NULL,
`LAST_NAME` varchar(20) DEFAULT NULL,
PRIMARY KEY (`EMPLOYEE_NUMBER`),
KEY `FIRST_NAME` (`FIRST_NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
"===== In con1 : Issue query"
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
"===== In con2 : Starting transaction"
begin;
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
##############
# UN INSTALL #
##############
"===== In default : Sending UNINSTALL"
UNINSTALL PLUGIN pfs_example_plugin_employee;
"===== Check table lock status "
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME OWNER_THREAD_ID INTERNAL_LOCK EXTERNAL_LOCK
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
"===== Check Thread Status for default connection "
NAME PROCESSLIST_COMMAND PROCESSLIST_STATE PROCESSLIST_INFO
thread/sql/one_connection Query Waiting for table metadata lock UNINSTALL PLUGIN pfs_example_plugin_employee
"===== Check metadata lock status "
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
TABLE performance_schema pfs_example_employee_name SHARED_READ TRANSACTION GRANTED
TABLE performance_schema pfs_example_employee_name EXCLUSIVE TRANSACTION PENDING
select name from mysql.plugin
where name like 'pfs_example_%';
name
pfs_example_plugin_employee
"===== In con1 : Send a query. About to block"
select * from performance_schema.pfs_example_employee_name;
"===== In con2 : Still alive. Issue a query."
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
"===== Check table lock status "
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME OWNER_THREAD_ID INTERNAL_LOCK EXTERNAL_LOCK
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
"===== Check Thread Status for default connection "
NAME PROCESSLIST_COMMAND PROCESSLIST_STATE PROCESSLIST_INFO
thread/sql/one_connection Query Waiting for table metadata lock UNINSTALL PLUGIN pfs_example_plugin_employee
"===== Check metadata lock status "
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
TABLE performance_schema pfs_example_employee_name SHARED_READ TRANSACTION GRANTED
TABLE performance_schema pfs_example_employee_name EXCLUSIVE TRANSACTION PENDING
TABLE performance_schema pfs_example_employee_name SHARED_READ TRANSACTION PENDING
"===== In con2 : Closing transaction"
commit;
########
# CON1 #
########
ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
########################
# UNINSTALL completing #
########################
select name from mysql.plugin
where name like 'pfs_example_%';
name
select * from INFORMATION_SCHEMA.tables
where TABLE_NAME like "pfs_example_%";
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
describe performance_schema.pfs_example_employee_name;
ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
show create table performance_schema.pfs_example_employee_name;
ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
select * from performance_schema.pfs_example_employee_name;
ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
###############
# INSTALL (2) #
###############
INSTALL PLUGIN pfs_example_plugin_employee SONAME 'pfs_example_plugin_employee.xxx';
select name from mysql.plugin
where name like 'pfs_example_%';
name
pfs_example_plugin_employee
select TABLE_SCHEMA, TABLE_NAME, ENGINE, VERSION, TABLE_COMMENT
from INFORMATION_SCHEMA.tables
where TABLE_NAME like "pfs_example_%"
order by table_name;
TABLE_SCHEMA TABLE_NAME ENGINE VERSION TABLE_COMMENT
performance_schema pfs_example_employee_name PERFORMANCE_SCHEMA 10
performance_schema pfs_example_employee_salary PERFORMANCE_SCHEMA 10
performance_schema pfs_example_machine PERFORMANCE_SCHEMA 10
performance_schema pfs_example_machine_by_employee_by_type PERFORMANCE_SCHEMA 10
describe performance_schema.pfs_example_employee_name;
Field Type Null Key Default Extra
EMPLOYEE_NUMBER int NO PRI NULL
FIRST_NAME char(20) YES MUL NULL
LAST_NAME varchar(20) YES NULL
show create table performance_schema.pfs_example_employee_name;
Table Create Table
pfs_example_employee_name CREATE TABLE `pfs_example_employee_name` (
`EMPLOYEE_NUMBER` int NOT NULL,
`FIRST_NAME` char(20) DEFAULT NULL,
`LAST_NAME` varchar(20) DEFAULT NULL,
PRIMARY KEY (`EMPLOYEE_NUMBER`),
KEY `FIRST_NAME` (`FIRST_NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
"===== In con1 : Issue query."
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
"===== In con2 : Start transaction."
begin;
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
##################
# UN INSTALL (2) #
##################
"===== In default : Sending UNINSTALL"
UNINSTALL PLUGIN pfs_example_plugin_employee;
"===== Check table lock status "
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME OWNER_THREAD_ID INTERNAL_LOCK EXTERNAL_LOCK
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
"===== Check Thread Status for default connection "
NAME PROCESSLIST_COMMAND PROCESSLIST_STATE PROCESSLIST_INFO
thread/sql/one_connection Query Waiting for table metadata lock UNINSTALL PLUGIN pfs_example_plugin_employee
"===== Check metadata lock status "
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
TABLE performance_schema pfs_example_employee_name SHARED_READ TRANSACTION GRANTED
TABLE performance_schema pfs_example_employee_name EXCLUSIVE TRANSACTION PENDING
select name from mysql.plugin
where name like 'pfs_example_%';
name
pfs_example_plugin_employee
"===== In con1 : Send query. About to block"
select * from performance_schema.pfs_example_employee_name;
"===== In con2 : Still alive. Issue query."
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
"===== Check table lock status "
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME OWNER_THREAD_ID INTERNAL_LOCK EXTERNAL_LOCK
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
TABLE performance_schema pfs_example_employee_name NULL NULL NULL
"===== Check Thread Status for default connection "
NAME PROCESSLIST_COMMAND PROCESSLIST_STATE PROCESSLIST_INFO
thread/sql/one_connection Query Waiting for table metadata lock UNINSTALL PLUGIN pfs_example_plugin_employee
"===== Check metadata lock status "
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
TABLE performance_schema pfs_example_employee_name SHARED_READ TRANSACTION GRANTED
TABLE performance_schema pfs_example_employee_name EXCLUSIVE TRANSACTION PENDING
TABLE performance_schema pfs_example_employee_name SHARED_READ TRANSACTION PENDING
#########################
# KILLING UNINSTALL (2) #
#########################
kill query @pid;
"===== In con2 : Closing transaction"
commit;
########
# CON1 #
########
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
############################
# UNINSTALL (2) completing #
############################
ERROR 70100: Query execution was interrupted
show errors;
Level Code Message
Error 1317 Query execution was interrupted
Error 1883 Plugin 'pfs_example_plugin_employee' cannot be uninstalled now. Plugin is still in use.
select name from mysql.plugin
where name like 'pfs_example_%';
name
pfs_example_plugin_employee
select TABLE_SCHEMA, TABLE_NAME, ENGINE, VERSION, TABLE_COMMENT
from INFORMATION_SCHEMA.tables
where TABLE_NAME like "pfs_example_%"
order by table_name;
TABLE_SCHEMA TABLE_NAME ENGINE VERSION TABLE_COMMENT
performance_schema pfs_example_employee_name PERFORMANCE_SCHEMA 10
performance_schema pfs_example_employee_salary PERFORMANCE_SCHEMA 10
performance_schema pfs_example_machine PERFORMANCE_SCHEMA 10
performance_schema pfs_example_machine_by_employee_by_type PERFORMANCE_SCHEMA 10
describe performance_schema.pfs_example_employee_name;
Field Type Null Key Default Extra
EMPLOYEE_NUMBER int NO PRI NULL
FIRST_NAME char(20) YES MUL NULL
LAST_NAME varchar(20) YES NULL
show create table performance_schema.pfs_example_employee_name;
Table Create Table
pfs_example_employee_name CREATE TABLE `pfs_example_employee_name` (
`EMPLOYEE_NUMBER` int NOT NULL,
`FIRST_NAME` char(20) DEFAULT NULL,
`LAST_NAME` varchar(20) DEFAULT NULL,
PRIMARY KEY (`EMPLOYEE_NUMBER`),
KEY `FIRST_NAME` (`FIRST_NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
select * from performance_schema.pfs_example_employee_name;
EMPLOYEE_NUMBER FIRST_NAME LAST_NAME
1 foo1 bar1
2 foo2 bar2
3 foo3 bar3
##################
# UN INSTALL (3) #
##################
UNINSTALL PLUGIN pfs_example_plugin_employee;
select * from INFORMATION_SCHEMA.tables
where TABLE_NAME like "pfs_example_%";
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
describe performance_schema.pfs_example_employee_name;
ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
show create table performance_schema.pfs_example_employee_name;
ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
select * from performance_schema.pfs_example_employee_name;
ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist
###########
# CLEANUP #
###########
update performance_schema.setup_objects
set ENABLED = "NO", TIMED = "NO"
where OBJECT_TYPE = "TABLE" and OBJECT_SCHEMA = "performance_schema";
FLUSH TABLES;
|
