1--echo # Create Resource Group Tests 2 3--echo # 1. Invalid Resource Group Name. 4--error 1064 5CREATE RESOURCE GROUP 'invalid_utf8¿ÿres' TYPE=USER VCPU=1-3, 1; 6 7--echo # 2. Invalid Resource Group Type 8--error 1064 9CREATE RESOURCE GROUP test TYPE=INVALID_TYPE VCPU=3-1, 0; 10 11--echo # 3. Invalid VCPU ID and VCPU Range 12--replace_result $num_vcpu MAX_CPU_NUM 13--error ER_INVALID_VCPU_ID 14--eval CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=$num_vcpu 15 16--echo # 3.1 Invalid VCPU Range. 17--error ER_INVALID_VCPU_RANGE 18CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=3-1; 19 20--echo # 4. Invalid Value of thread priority. 21if ($thr_prio_enable == 1) 22{ 23--error ER_INVALID_THREAD_PRIORITY 24CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=25; 25 26--echo # Create system resource group with invalid thread priority value. 27--error ER_INVALID_THREAD_PRIORITY 28CREATE RESOURCE GROUP system_resource TYPE=system THREAD_PRIORITY=25; 29} 30 31--echo # 5. Invalid Value of thread priority for user resource group type. 32if ($thr_prio_enable == 1) 33{ 34--error ER_INVALID_THREAD_PRIORITY 35CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=-5; 36} 37 38--echo # 5.1 Thread priority ignored warning. 39if ($thr_prio_enable == 0) 40{ 41CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=-5; 42SHOW WARNINGS; 43} 44 45--echo # 6. Create a valid user resource group. 46if ($thr_prio_enable == 1) 47{ 48CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=5; 49} 50SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME="user_resource"; 51 52--echo # 7. Resource group already exists error. 53--error ER_RESOURCE_GROUP_EXISTS 54CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=5; 55 56--echo # 7.1 Case sensitivity of RESOURCE GROUP. 57--error ER_RESOURCE_GROUP_EXISTS 58CREATE RESOURCE GROUP USER_resource TYPE=USER; 59DROP RESOURCE GROUP user_resource; 60 61--echo # 8. Privilege Error - user with default privileges can't create resource group. 62CREATE USER u1; 63--connect(con1,localhost,u1,'',) 64--error ER_SPECIFIC_ACCESS_DENIED_ERROR 65CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=5; 66 67 68--echo # 9. Privilege Error - user with RESOURCE_GROUP_USER privilege can't create resource group. 69--connection default 70GRANT RESOURCE_GROUP_USER ON *.* TO u1; 71--connection con1 72--error ER_SPECIFIC_ACCESS_DENIED_ERROR 73CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=5; 74--connection default 75--disconnect con1 76DROP USER u1; 77 78--echo # 10. Length of resource group name is greater than maximum allowed limit of identifier names. 79--error ER_TOO_LONG_IDENT 80CREATE RESOURCE GROUP user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaafffffffff TYPE=USER; 81 82--echo # ALTER resource group tests 83 84--echo # 1. Alter on non-existent resource group. 85--error ER_RESOURCE_GROUP_NOT_EXISTS 86ALTER RESOURCE GROUP user_resource VCPU=3; 87 88--echo # 2. Alter with a invalid CPU ID 89CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=5; 90--replace_result $num_vcpu MAX_CPU_NUM 91--error ER_INVALID_VCPU_ID 92--eval ALTER RESOURCE GROUP user_resource VCPU=$num_vcpu 93 94--echo # 3. Alter with invalid CPU range. 95--error ER_INVALID_VCPU_RANGE 96ALTER RESOURCE GROUP user_resource VCPU=3-1; 97 98--echo # 4. Alter with invalid thread_priority 99if ($thr_prio_enable == 1) 100{ 101--error ER_INVALID_THREAD_PRIORITY 102ALTER RESOURCE GROUP user_resource THREAD_PRIORITY=25; 103} 104 105--echo # 5. Alter with invalid thread priority for a user resource group. 106if ($thr_prio_enable == 1) 107{ 108--error ER_INVALID_THREAD_PRIORITY 109ALTER RESOURCE GROUP user_resource THREAD_PRIORITY=-5; 110} 111if ($thr_prio_enable == 0) 112{ 113ALTER RESOURCE GROUP user_resource THREAD_PRIORITY=-5; 114} 115 116--echo # 6. DISABLE and ENABLE the resource group. 117ALTER RESOURCE GROUP user_resource DISABLE; 118SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME="user_resource"; 119ALTER RESOURCE GROUP user_resource ENABLE; 120 121--echo # 7. Test FORCE option of ALTER 122SET RESOURCE GROUP user_resource; 123SELECT COUNT(*) = 1 FROM performance_schema.threads WHERE RESOURCE_GROUP="user_resource"; 124--error ER_INVALID_USE_OF_FORCE_OPTION 125ALTER RESOURCE GROUP user_resource FORCE; 126ALTER RESOURCE GROUP user_resource DISABLE; 127ALTER RESOURCE GROUP user_resource DISABLE FORCE; 128SELECT COUNT(*) = 0 FROM performance_schema.threads WHERE RESOURCE_GROUP="user_resource"; 129 130--echo # 8. Privilege Error - user with default privileges can't alter resource group. 131CREATE USER u1; 132--connect(con1,localhost,u1,'',) 133--error ER_SPECIFIC_ACCESS_DENIED_ERROR 134ALTER RESOURCE GROUP user_resource VCPU=0 THREAD_PRIORITY=5; 135 136 137--echo # 9. Privilege Error - user with RESOURCE_GROUP_USER privilege can't alter resource group. 138--connection default 139GRANT RESOURCE_GROUP_USER ON *.* TO u1; 140--connection con1 141--error ER_SPECIFIC_ACCESS_DENIED_ERROR 142ALTER RESOURCE GROUP user_resource VCPU=0 THREAD_PRIORITY=5; 143--connection default 144--disconnect con1 145DROP USER u1; 146 147--echo # 10. Length of resource group name is greater than maximum allowed limit of identifier names. 148--error ER_TOO_LONG_IDENT 149ALTER RESOURCE GROUP user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaafffffffff DISABLE; 150 151--echo # 11. Default resource groups can't be altered. 152--error ER_DISALLOWED_OPERATION 153ALTER RESOURCE GROUP SYS_default VCPU=0; 154 155--error ER_DISALLOWED_OPERATION 156ALTER RESOURCE GROUP USR_default VCPU=1; 157 158--echo # 11. Alter VCPU IDS and/or thread priority 159 160SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME='user_resource'; 161ALTER RESOURCE GROUP user_resource VCPU=0,2; 162SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME='user_resource'; 163if ($thr_prio_enable == 1) 164{ 165 ALTER RESOURCE GROUP user_resource THREAD_PRIORITY=3; 166 SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME='user_resource'; 167 ALTER RESOURCE GROUP user_resource VCPU=3 THREAD_PRIORITY=1; 168 SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME='user_resource'; 169} 170 171--echo # DROP resource group tests. 172 173--echo # 1. DROP on a non-existent resource group. 174--error ER_RESOURCE_GROUP_NOT_EXISTS 175DROP RESOURCE GROUP user_resource1; 176 177--echo # 2. DROP an existing resource group. 178SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME="user_resource"; 179DROP RESOURCE GROUP user_resource; 180 181--echo # 3. Test FORCE option of resource group. 182CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=7; 183SET RESOURCE GROUP user_resource; 184--error ER_RESOURCE_GROUP_BUSY 185DROP RESOURCE GROUP user_resource; 186SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME="user_resource"; 187DROP RESOURCE GROUP user_resource FORCE; 188SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME="user_resource"; 189 190--echo # 4. Privilege Error - user with RESOURCE_GROUP_USER privilege can't drop resource group. 191CREATE USER u1; 192GRANT RESOURCE_GROUP_USER ON *.* TO u1; 193CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3; 194--connect(con1,localhost,u1,'',) 195--error ER_SPECIFIC_ACCESS_DENIED_ERROR 196DROP RESOURCE GROUP user_resource; 197--connection default 198--disconnect con1 199DROP RESOURCE GROUP user_resource; 200DROP USER u1; 201 202--echo # 10. Length of resource group name is greater than maximum allowed limit of identifier names. 203--error ER_TOO_LONG_IDENT 204DROP RESOURCE GROUP user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaafffffffff; 205 206--echo # 11. Default resource groups can't be dropped. 207--error ER_DISALLOWED_OPERATION 208DROP RESOURCE GROUP USR_default; 209--error ER_DISALLOWED_OPERATION 210DROP RESOURCE GROUP SYS_default; 211 212--echo # SET resource group tests. 213# 1. Assigning a non-existing resource group. 214--error ER_RESOURCE_GROUP_NOT_EXISTS 215SET RESOURCE GROUP user_resource; 216 217--echo # 2. SET current session to a resource group. 218CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=7; 219SET RESOURCE GROUP user_resource; 220SELECT COUNT(*) = 1 FROM performance_schema.threads WHERE RESOURCE_GROUP="user_resource"; 221 222--echo # 3. Assign a system thread to a user resource group. 223let $pfs_system_thread_id=`select thread_id from performance_schema.threads where name='thread/innodb/io_write_thread'`; 224--replace_result $pfs_system_thread_id pfs_system_thread_id 225--error ER_RESOURCE_GROUP_BIND_FAILED 226--eval SET RESOURCE GROUP user_resource FOR $pfs_system_thread_id; 227 228--echo # 4. Assign a system thread to system resource group. 229CREATE RESOURCE GROUP system_resource TYPE=SYSTEM VCPU=0-2 THREAD_PRIORITY=-1; 230--replace_result $pfs_system_thread_id pfs_system_thread_id 231--eval SET RESOURCE GROUP system_resource FOR $pfs_system_thread_id; 232--replace_result $pfs_system_thread_id pfs_system_thread_id 233--eval SELECT THREAD_ID, NAME, RESOURCE_GROUP FROM performance_schema.threads WHERE THREAD_ID=$pfs_system_thread_id; 234 235--echo # 5. Assign a group of threads to a resource group. 236let $pfs_system_thread_id1=`select thread_id from performance_schema.threads where name='thread/innodb/io_read_thread'`; 237let $pfs_system_thread_id2=`select thread_id from performance_schema.threads where name='thread/innodb/io_ibuf_thread'`; 238--replace_result $pfs_system_thread_id1 pfs_system_thread_id1 $pfs_system_thread_id2 pfs_system_thread_id2 239--eval SET RESOURCE GROUP system_resource FOR $pfs_system_thread_id1, $pfs_system_thread_id2; 240--replace_result $pfs_system_thread_id1 pfs_system_thread_id1 $pfs_system_thread_id2 pfs_system_thread_id2 241--eval SELECT COUNT(*) = 2 FROM performance_schema.threads WHERE THREAD_ID IN ($pfs_system_thread_id1, $pfs_system_thread_id2); 242 243--echo # 6. Privilege Tests 244 245CREATE USER u1; 246--connect(con1, localhost, u1, '', ) 247--error ER_SPECIFIC_ACCESS_DENIED_ERROR 248SET RESOURCE GROUP user_resource; 249 250--connection default 251GRANT RESOURCE_GROUP_USER ON *.* TO u1; 252--connection con1 253SET RESOURCE GROUP user_resource; 254--error ER_SPECIFIC_ACCESS_DENIED_ERROR 255SET RESOURCE GROUP system_resource; 256--connection default 257--disconnect con1 258DROP USER u1; 259 260--echo # 7. SET RESOURCE GROUP on invalid thread id will give warning. 261--error ER_INVALID_THREAD_ID 262SET RESOURCE GROUP user_resource FOR 77777; 263 264--echo # 8. Disable resource group and assign to the current session - This should give error. 265ALTER RESOURCE GROUP user_resource DISABLE; 266--error ER_RESOURCE_GROUP_DISABLED 267SET RESOURCE GROUP user_resource; 268DROP RESOURCE GROUP user_resource FORCE; 269 270 271--echo # 10. Length of resource group name is greater than maximum allowed limit of identifier names. 272--error ER_TOO_LONG_IDENT 273SET RESOURCE GROUP user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaafffffffff; 274 275--echo # 11. Do not allow a system resource group to be assigned to user thread. 276--replace_regex /thread id \([0-9]+\)/thread id ID/ 277--error ER_RESOURCE_GROUP_BIND_FAILED 278SET RESOURCE GROUP SYS_default; 279 280--echo # Query Hint Tests 281 282--echo # 1. Hint on non-existent Resource Group. 283SELECT /*+ RESOURCE_GROUP(rg) */ 1; 284 285--echo # 2. Using system resource group in hint for a non-privilege user should give warning 286CREATE USER u1; 287--connect(con1, localhost, u1, '', ) 288--replace_regex /thread id \([0-9]+\)/thread id ID/ 289SELECT /*+ RESOURCE_GROUP(system_resource) */ 1; 290--connection default 291--disconnect con1 292DROP USER u1; 293 294--echo # 3. Length of resource group name is greater than maximum allowed limit of identifier names. 295--echo # length shall be truncated in this case and warning output to user. 296SELECT /*+ RESOURCE GROUP (user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaffffffff#f) */ 1; 297 298--echo # Information Schema and Privilege related tests. 299--replace_regex /0-[0-9]+/NUM_VCPUS/ 300SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS; 301CREATE USER u1; 302--connect(con1,localhost,u1,'',) 303--replace_regex /0-[0-9]+/NUM_VCPUS/ 304SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS; 305--connection default 306GRANT RESOURCE_GROUP_USER ON *.* TO u1; 307--connection con1 308--replace_regex /0-[0-9]+/NUM_VCPUS/ 309SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS; 310--connection default 311GRANT RESOURCE_GROUP_ADMIN ON *.* TO u1; 312--connection con1 313--replace_regex /0-[0-9]+/NUM_VCPUS/ 314SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS; 315--connection default 316--disconnect con1 317DROP USER u1; 318DROP RESOURCE GROUP system_resource FORCE; 319 320--echo # BUG 27638623 - DEBUG ASSERT WHEN EXECUTING CREATE RESOURCE GROUP 321--echo # IN STORED FUNCTION 322 323 324delimiter |; 325--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG 326CREATE FUNCTION func() RETURNS INT 327BEGIN 328 CREATE RESOURCE GROUP Batch TYPE = USER VCPU = 2-3; 329 RETURN 0; 330END| 331 332# Check SET RESOURCE GROUP is allowed in stored procedures. 333CREATE RESOURCE GROUP Batch TYPE = USER VCPU = 2-3| 334CREATE FUNCTION func() RETURNS INT 335BEGIN 336 SET RESOURCE GROUP Batch; 337 RETURN 0; 338END| 339delimiter ;| 340SELECT func(); 341SELECT COUNT(*) = 1 FROM performance_schema.threads WHERE RESOURCE_GROUP="Batch"; 342DROP RESOURCE GROUP Batch FORCE; 343DROP FUNCTION func; 344 345 346# 347# BUG#28448258 - PREPARED STATEMENT FAILS FOR SET RESOURCE GROUP 348# 349CREATE RESOURCE GROUP test TYPE = USER VCPU=2-3; 350SET @rg_name='test'; 351SET @cmd = CONCAT("SET RESOURCE GROUP ", @rg_name); 352SELECT @cmd; 353PREPARE stmt FROM @cmd; 354EXECUTE stmt; 355SELECT COUNT(*) = 1 FROM performance_schema.threads WHERE RESOURCE_GROUP="test"; 356DROP RESOURCE GROUP test FORCE; 357