You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
276 lines
15 KiB
276 lines
15 KiB
# Resource group tests with thread priority enabled.
|
|
# Create Resource Group Tests
|
|
# 1. Invalid Resource Group Name.
|
|
CREATE RESOURCE GROUP 'invalid_utf8¿ÿres' TYPE=USER VCPU=1-3, 1;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''invalid_utf8¿ÿres' TYPE=USER VCPU=1-3, 1' at line 1
|
|
# 2. Invalid Resource Group Type
|
|
CREATE RESOURCE GROUP test TYPE=INVALID_TYPE VCPU=3-1, 0;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INVALID_TYPE VCPU=3-1, 0' at line 1
|
|
# 3. Invalid VCPU ID and VCPU Range
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=MAX_CPU_NUM;
|
|
ERROR HY000: Invalid cpu id MAX_CPU_NUM
|
|
# 3.1 Invalid VCPU Range.
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=3-1;
|
|
ERROR HY000: Invalid VCPU range 3-1
|
|
# 4. Invalid Value of thread priority.
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=25;
|
|
ERROR HY000: Invalid thread priority value 25 for User resource group user_resource. Allowed range is [0, 19].
|
|
# Create system resource group with invalid thread priority value.
|
|
CREATE RESOURCE GROUP system_resource TYPE=system THREAD_PRIORITY=25;
|
|
ERROR HY000: Invalid thread priority value 25 for System resource group system_resource. Allowed range is [-20, 0].
|
|
# 5. Invalid Value of thread priority for user resource group type.
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=-5;
|
|
ERROR HY000: Invalid thread priority value -5 for User resource group user_resource. Allowed range is [0, 19].
|
|
# 5.1 Thread priority ignored warning.
|
|
# 6. Create a valid user resource group.
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=5;
|
|
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME="user_resource";
|
|
RESOURCE_GROUP_NAME RESOURCE_GROUP_TYPE RESOURCE_GROUP_ENABLED VCPU_IDS THREAD_PRIORITY
|
|
user_resource USER 1 1-3 5
|
|
# 7. Resource group already exists error.
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=5;
|
|
ERROR HY000: Resource Group 'user_resource' exists
|
|
# 7.1 Case sensitivity of RESOURCE GROUP.
|
|
CREATE RESOURCE GROUP USER_resource TYPE=USER;
|
|
ERROR HY000: Resource Group 'USER_resource' exists
|
|
DROP RESOURCE GROUP user_resource;
|
|
# 8. Privilege Error - user with default privileges can't create resource group.
|
|
CREATE USER u1;
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=5;
|
|
ERROR 42000: Access denied; you need (at least one of) the RESOURCE_GROUP_ADMIN privilege(s) for this operation
|
|
# 9. Privilege Error - user with RESOURCE_GROUP_USER privilege can't create resource group.
|
|
GRANT RESOURCE_GROUP_USER ON *.* TO u1;
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=5;
|
|
ERROR 42000: Access denied; you need (at least one of) the RESOURCE_GROUP_ADMIN privilege(s) for this operation
|
|
DROP USER u1;
|
|
# 10. Length of resource group name is greater than maximum allowed limit of identifier names.
|
|
CREATE RESOURCE GROUP user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaafffffffff TYPE=USER;
|
|
ERROR 42000: Identifier name 'user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long
|
|
# ALTER resource group tests
|
|
# 1. Alter on non-existent resource group.
|
|
ALTER RESOURCE GROUP user_resource VCPU=3;
|
|
ERROR HY000: Resource Group 'user_resource' does not exist.
|
|
# 2. Alter with a invalid CPU ID
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=5;
|
|
ALTER RESOURCE GROUP user_resource VCPU=MAX_CPU_NUM;
|
|
ERROR HY000: Invalid cpu id MAX_CPU_NUM
|
|
# 3. Alter with invalid CPU range.
|
|
ALTER RESOURCE GROUP user_resource VCPU=3-1;
|
|
ERROR HY000: Invalid VCPU range 3-1
|
|
# 4. Alter with invalid thread_priority
|
|
ALTER RESOURCE GROUP user_resource THREAD_PRIORITY=25;
|
|
ERROR HY000: Invalid thread priority value 25 for User resource group user_resource. Allowed range is [0, 19].
|
|
# 5. Alter with invalid thread priority for a user resource group.
|
|
ALTER RESOURCE GROUP user_resource THREAD_PRIORITY=-5;
|
|
ERROR HY000: Invalid thread priority value -5 for User resource group user_resource. Allowed range is [0, 19].
|
|
# 6. DISABLE and ENABLE the resource group.
|
|
ALTER RESOURCE GROUP user_resource DISABLE;
|
|
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME="user_resource";
|
|
RESOURCE_GROUP_NAME RESOURCE_GROUP_TYPE RESOURCE_GROUP_ENABLED VCPU_IDS THREAD_PRIORITY
|
|
user_resource USER 0 1-3 0
|
|
ALTER RESOURCE GROUP user_resource ENABLE;
|
|
# 7. Test FORCE option of ALTER
|
|
SET RESOURCE GROUP user_resource;
|
|
SELECT COUNT(*) = 1 FROM performance_schema.threads WHERE RESOURCE_GROUP="user_resource";
|
|
COUNT(*) = 1
|
|
1
|
|
ALTER RESOURCE GROUP user_resource FORCE;
|
|
ERROR HY000: Option FORCE invalid as DISABLE option is not specified.
|
|
ALTER RESOURCE GROUP user_resource DISABLE;
|
|
ALTER RESOURCE GROUP user_resource DISABLE FORCE;
|
|
SELECT COUNT(*) = 0 FROM performance_schema.threads WHERE RESOURCE_GROUP="user_resource";
|
|
COUNT(*) = 0
|
|
1
|
|
# 8. Privilege Error - user with default privileges can't alter resource group.
|
|
CREATE USER u1;
|
|
ALTER RESOURCE GROUP user_resource VCPU=0 THREAD_PRIORITY=5;
|
|
ERROR 42000: Access denied; you need (at least one of) the RESOURCE_GROUP_ADMIN privilege(s) for this operation
|
|
# 9. Privilege Error - user with RESOURCE_GROUP_USER privilege can't alter resource group.
|
|
GRANT RESOURCE_GROUP_USER ON *.* TO u1;
|
|
ALTER RESOURCE GROUP user_resource VCPU=0 THREAD_PRIORITY=5;
|
|
ERROR 42000: Access denied; you need (at least one of) the RESOURCE_GROUP_ADMIN privilege(s) for this operation
|
|
DROP USER u1;
|
|
# 10. Length of resource group name is greater than maximum allowed limit of identifier names.
|
|
ALTER RESOURCE GROUP user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaafffffffff DISABLE;
|
|
ERROR 42000: Identifier name 'user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long
|
|
# 11. Default resource groups can't be altered.
|
|
ALTER RESOURCE GROUP SYS_default VCPU=0;
|
|
ERROR HY000: Alter operation is disallowed on default resource groups.
|
|
ALTER RESOURCE GROUP USR_default VCPU=1;
|
|
ERROR HY000: Alter operation is disallowed on default resource groups.
|
|
# 11. Alter VCPU IDS and/or thread priority
|
|
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME='user_resource';
|
|
RESOURCE_GROUP_NAME RESOURCE_GROUP_TYPE RESOURCE_GROUP_ENABLED VCPU_IDS THREAD_PRIORITY
|
|
user_resource USER 0 1-3 0
|
|
ALTER RESOURCE GROUP user_resource VCPU=0,2;
|
|
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME='user_resource';
|
|
RESOURCE_GROUP_NAME RESOURCE_GROUP_TYPE RESOURCE_GROUP_ENABLED VCPU_IDS THREAD_PRIORITY
|
|
user_resource USER 0 0,2 0
|
|
ALTER RESOURCE GROUP user_resource THREAD_PRIORITY=3;
|
|
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME='user_resource';
|
|
RESOURCE_GROUP_NAME RESOURCE_GROUP_TYPE RESOURCE_GROUP_ENABLED VCPU_IDS THREAD_PRIORITY
|
|
user_resource USER 0 0,2 3
|
|
ALTER RESOURCE GROUP user_resource VCPU=3 THREAD_PRIORITY=1;
|
|
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME='user_resource';
|
|
RESOURCE_GROUP_NAME RESOURCE_GROUP_TYPE RESOURCE_GROUP_ENABLED VCPU_IDS THREAD_PRIORITY
|
|
user_resource USER 0 3 1
|
|
# DROP resource group tests.
|
|
# 1. DROP on a non-existent resource group.
|
|
DROP RESOURCE GROUP user_resource1;
|
|
ERROR HY000: Resource Group 'user_resource1' does not exist.
|
|
# 2. DROP an existing resource group.
|
|
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME="user_resource";
|
|
RESOURCE_GROUP_NAME RESOURCE_GROUP_TYPE RESOURCE_GROUP_ENABLED VCPU_IDS THREAD_PRIORITY
|
|
user_resource USER 0 3 1
|
|
DROP RESOURCE GROUP user_resource;
|
|
# 3. Test FORCE option of resource group.
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=7;
|
|
SET RESOURCE GROUP user_resource;
|
|
DROP RESOURCE GROUP user_resource;
|
|
ERROR HY000: Resource group user_resource is busy.
|
|
SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME="user_resource";
|
|
COUNT(*) = 1
|
|
1
|
|
DROP RESOURCE GROUP user_resource FORCE;
|
|
SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME="user_resource";
|
|
COUNT(*) = 0
|
|
1
|
|
# 4. Privilege Error - user with RESOURCE_GROUP_USER privilege can't drop resource group.
|
|
CREATE USER u1;
|
|
GRANT RESOURCE_GROUP_USER ON *.* TO u1;
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3;
|
|
DROP RESOURCE GROUP user_resource;
|
|
ERROR 42000: Access denied; you need (at least one of) the RESOURCE_GROUP_ADMIN privilege(s) for this operation
|
|
DROP RESOURCE GROUP user_resource;
|
|
DROP USER u1;
|
|
# 10. Length of resource group name is greater than maximum allowed limit of identifier names.
|
|
DROP RESOURCE GROUP user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaafffffffff;
|
|
ERROR 42000: Identifier name 'user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long
|
|
# 11. Default resource groups can't be dropped.
|
|
DROP RESOURCE GROUP USR_default;
|
|
ERROR HY000: Drop operation operation is disallowed on default resource groups.
|
|
DROP RESOURCE GROUP SYS_default;
|
|
ERROR HY000: Drop operation operation is disallowed on default resource groups.
|
|
# SET resource group tests.
|
|
SET RESOURCE GROUP user_resource;
|
|
ERROR HY000: Resource Group 'user_resource' does not exist.
|
|
# 2. SET current session to a resource group.
|
|
CREATE RESOURCE GROUP user_resource TYPE=USER VCPU=1-3 THREAD_PRIORITY=7;
|
|
SET RESOURCE GROUP user_resource;
|
|
SELECT COUNT(*) = 1 FROM performance_schema.threads WHERE RESOURCE_GROUP="user_resource";
|
|
COUNT(*) = 1
|
|
1
|
|
# 3. Assign a system thread to a user resource group.
|
|
SET RESOURCE GROUP user_resource FOR pfs_system_thread_id;;
|
|
ERROR HY000: Unable to bind resource group user_resource with thread id (pfs_system_thread_id).(Resource group type and thread type doesn't match.).
|
|
# 4. Assign a system thread to system resource group.
|
|
CREATE RESOURCE GROUP system_resource TYPE=SYSTEM VCPU=0-2 THREAD_PRIORITY=-1;
|
|
SET RESOURCE GROUP system_resource FOR pfs_system_thread_id;;
|
|
SELECT THREAD_ID, NAME, RESOURCE_GROUP FROM performance_schema.threads WHERE THREAD_ID=pfs_system_thread_id;;
|
|
THREAD_ID NAME RESOURCE_GROUP
|
|
pfs_system_thread_id thread/innodb/io_write_thread system_resource
|
|
# 5. Assign a group of threads to a resource group.
|
|
SET RESOURCE GROUP system_resource FOR pfs_system_thread_id1, pfs_system_thread_id2;;
|
|
SELECT COUNT(*) = 2 FROM performance_schema.threads WHERE THREAD_ID IN (pfs_system_thread_id1, pfs_system_thread_id2);;
|
|
COUNT(*) = 2
|
|
1
|
|
# 6. Privilege Tests
|
|
CREATE USER u1;
|
|
SET RESOURCE GROUP user_resource;
|
|
ERROR 42000: Access denied; you need (at least one of) the RESOURCE_GROUP_ADMIN OR RESOURCE_GROUP_USER privilege(s) for this operation
|
|
GRANT RESOURCE_GROUP_USER ON *.* TO u1;
|
|
SET RESOURCE GROUP user_resource;
|
|
SET RESOURCE GROUP system_resource;
|
|
ERROR 42000: Access denied; you need (at least one of) the RESOURCE_GROUP_ADMIN privilege(s) for this operation
|
|
DROP USER u1;
|
|
# 7. SET RESOURCE GROUP on invalid thread id will give warning.
|
|
SET RESOURCE GROUP user_resource FOR 77777;
|
|
ERROR HY000: Invalid thread id (77777).
|
|
# 8. Disable resource group and assign to the current session - This should give error.
|
|
ALTER RESOURCE GROUP user_resource DISABLE;
|
|
SET RESOURCE GROUP user_resource;
|
|
ERROR HY000: Resource group user_resource is disabled.
|
|
DROP RESOURCE GROUP user_resource FORCE;
|
|
# 10. Length of resource group name is greater than maximum allowed limit of identifier names.
|
|
SET RESOURCE GROUP user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaafffffffff;
|
|
ERROR 42000: Identifier name 'user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long
|
|
# 11. Do not allow a system resource group to be assigned to user thread.
|
|
SET RESOURCE GROUP SYS_default;
|
|
ERROR HY000: Unable to bind resource group SYS_default with thread id ID.(System resource group can't be applied to user thread.).
|
|
# Query Hint Tests
|
|
# 1. Hint on non-existent Resource Group.
|
|
SELECT /*+ RESOURCE_GROUP(rg) */ 1;
|
|
1
|
|
1
|
|
Warnings:
|
|
Warning 3651 Resource Group 'rg' does not exist.
|
|
# 2. Using system resource group in hint for a non-privilege user should give warning
|
|
CREATE USER u1;
|
|
SELECT /*+ RESOURCE_GROUP(system_resource) */ 1;
|
|
1
|
|
1
|
|
Warnings:
|
|
Warning 3661 Unable to bind resource group system_resource with thread id ID.(System resource group can't be bound with a session thread).
|
|
DROP USER u1;
|
|
# 3. Length of resource group name is greater than maximum allowed limit of identifier names.
|
|
# length shall be truncated in this case and warning output to user.
|
|
SELECT /*+ RESOURCE GROUP (user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaffffffff#f) */ 1;
|
|
1
|
|
1
|
|
Warnings:
|
|
Warning 1064 Optimizer hint syntax error near 'RESOURCE GROUP (user_resource_1234567890aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' at line 1
|
|
# Information Schema and Privilege related tests.
|
|
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS;
|
|
RESOURCE_GROUP_NAME RESOURCE_GROUP_TYPE RESOURCE_GROUP_ENABLED VCPU_IDS THREAD_PRIORITY
|
|
USR_default USER 1 NUM_VCPUS 0
|
|
SYS_default SYSTEM 1 NUM_VCPUS 0
|
|
system_resource SYSTEM 1 NUM_VCPUS -1
|
|
CREATE USER u1;
|
|
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS;
|
|
RESOURCE_GROUP_NAME RESOURCE_GROUP_TYPE RESOURCE_GROUP_ENABLED VCPU_IDS THREAD_PRIORITY
|
|
GRANT RESOURCE_GROUP_USER ON *.* TO u1;
|
|
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS;
|
|
RESOURCE_GROUP_NAME RESOURCE_GROUP_TYPE RESOURCE_GROUP_ENABLED VCPU_IDS THREAD_PRIORITY
|
|
USR_default USER 1 NUM_VCPUS 0
|
|
GRANT RESOURCE_GROUP_ADMIN ON *.* TO u1;
|
|
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS;
|
|
RESOURCE_GROUP_NAME RESOURCE_GROUP_TYPE RESOURCE_GROUP_ENABLED VCPU_IDS THREAD_PRIORITY
|
|
USR_default USER 1 NUM_VCPUS 0
|
|
SYS_default SYSTEM 1 NUM_VCPUS 0
|
|
system_resource SYSTEM 1 NUM_VCPUS -1
|
|
DROP USER u1;
|
|
DROP RESOURCE GROUP system_resource FORCE;
|
|
# BUG 27638623 - DEBUG ASSERT WHEN EXECUTING CREATE RESOURCE GROUP
|
|
# IN STORED FUNCTION
|
|
CREATE FUNCTION func() RETURNS INT
|
|
BEGIN
|
|
CREATE RESOURCE GROUP Batch TYPE = USER VCPU = 2-3;
|
|
RETURN 0;
|
|
END|
|
|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
|
|
CREATE RESOURCE GROUP Batch TYPE = USER VCPU = 2-3|
|
|
CREATE FUNCTION func() RETURNS INT
|
|
BEGIN
|
|
SET RESOURCE GROUP Batch;
|
|
RETURN 0;
|
|
END|
|
|
SELECT func();
|
|
func()
|
|
0
|
|
SELECT COUNT(*) = 1 FROM performance_schema.threads WHERE RESOURCE_GROUP="Batch";
|
|
COUNT(*) = 1
|
|
1
|
|
DROP RESOURCE GROUP Batch FORCE;
|
|
DROP FUNCTION func;
|
|
CREATE RESOURCE GROUP test TYPE = USER VCPU=2-3;
|
|
SET @rg_name='test';
|
|
SET @cmd = CONCAT("SET RESOURCE GROUP ", @rg_name);
|
|
SELECT @cmd;
|
|
@cmd
|
|
SET RESOURCE GROUP test
|
|
PREPARE stmt FROM @cmd;
|
|
EXECUTE stmt;
|
|
SELECT COUNT(*) = 1 FROM performance_schema.threads WHERE RESOURCE_GROUP="test";
|
|
COUNT(*) = 1
|
|
1
|
|
DROP RESOURCE GROUP test FORCE;
|
|
|