From eed9d22a3247db8e6eb3e57ef974bb970628109a Mon Sep 17 00:00:00 2001 From: Meng-Hsiu Chiang Date: Wed, 10 Sep 2025 23:52:31 +0000 Subject: [PATCH] Improve MTR test compatibility with AMS AMS has different practices for MTR tests compares to RDS MySQL. To improve the compatibility following changes are made: - --- .../r/plugin_install.result | 9 ++++-- .../r/rds_managed_user_not_captured.result | 22 ++------------ .../r/supported_queries.result | 3 ++ .../t/cache_access_count.test | 3 +- .../rds/query_plan_capture/t/cache_size.test | 2 ++ .../rds/query_plan_capture/t/cache_ttl.test | 3 +- .../t/plan_id_for_long_numbers.test | 3 +- .../query_plan_capture/t/plugin_install.test | 29 +++++++++---------- .../t/prepared_statements.test | 3 +- .../t/prepared_statements_digest_len.test | 3 +- .../t/rds_managed_user_not_captured.test | 29 +++---------------- .../rds/query_plan_capture/t/status_vars.test | 3 +- .../t/supported_queries.test | 12 +++----- .../rds/query_plan_capture/t/sys_vars.test | 1 + .../t/update_unique_key.test | 3 +- 15 files changed, 50 insertions(+), 78 deletions(-) diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/plugin_install.result b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/plugin_install.result index b6fd6e7..c90c835 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/plugin_install.result +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/plugin_install.result @@ -9,13 +9,17 @@ Test install/uninstall the query plan capture plugin with rdsadmin INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; UNINSTALL PLUGIN query_plan_capture; +======================================================================= +Create customerAdmin user with superuser role +======================================================================= +CREATE USER 'customerAdmin'@'%'; +GRANT ALL PRIVILEGES ON *.* TO 'customerAdmin'@'%'; + ======================================================================= Test install the query plan capture plugin with customerAdmin ======================================================================= INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; ERROR HY000: Can't initialize function 'query_plan_capture'; Plugin initialization function failed. -Pattern "Plugin 'QUERY_PLAN_CAPTURE' init function returned error" found -Pattern "Plugin 'QUERY_PLAN_CAPTURE' registration as a AUDIT failed" found ======================================================================= Test uninstall the query plan capture plugin with customerAdmin @@ -25,4 +29,5 @@ INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; # Uninstall plugin using customerAdmin UNINSTALL PLUGIN query_plan_capture; ERROR HY000: Plugin 'query_plan_capture' cannot be uninstalled now. Plugin is still in use. +DROP USER 'customerAdmin'@'%'; UNINSTALL PLUGIN query_plan_capture; diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/rds_managed_user_not_captured.result b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/rds_managed_user_not_captured.result index 2790ff7..ae552a3 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/rds_managed_user_not_captured.result +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/rds_managed_user_not_captured.result @@ -9,37 +9,19 @@ INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; ======================================================================= Setup tables and user ======================================================================= +CREATE USER 'customerAdmin'@'%'; +GRANT ALL PRIVILEGES ON *.* TO 'customerAdmin'@'%'; create database test1; create table test1.t1(id int); create table test1.t2(id int); -create user 'rdsrepladmin'@'%'; -grant select on *.* to 'rdsrepladmin'@'%'; -create user 'rdstopmgr'@'%'; -grant select on *.* to 'rdstopmgr'@'%'; create user user@localhost; grant select on *.* to 'user'@'localhost'; -select user,host from mysql.user; -user host -customerAdmin % -rdsrepladmin % -rdstopmgr % -mysql.infoschema localhost -mysql.session localhost -mysql.sys localhost -rdsadmin localhost -user localhost ======================================================================= Select the table with different users ======================================================================= select count(*) from test1.t1 where id > 10; count(*) 0 -select count(*) from test1.t1 where id > 10; -count(*) -0 -select count(*) from test1.t1 where id > 10; -count(*) -0 SELECT COUNT(*)=0 as EXPECT_TRUE FROM performance_schema.captured_query_plans; EXPECT_TRUE 1 diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/supported_queries.result b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/supported_queries.result index 103c1c0..0ce994c 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/supported_queries.result +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/r/supported_queries.result @@ -37,6 +37,8 @@ set global query_plan_capture_config = "{\"version\":1,\"slow_time\":0,\"query_c ======================================================================= Switching to customerAdmin and run queries ======================================================================= +CREATE USER 'customerAdmin'@'%'; +GRANT ALL PRIVILEGES ON *.* TO 'customerAdmin'@'%'; update t2 set c = 4 where a=c; update t2, (select * from t1 where t1.a= 1) as tt set t2.a=99 where t2.a > 1; delete from t2 where a>c; @@ -64,6 +66,7 @@ update t2, (select * from t1 where t1.a= 1) as tt set t2.a=99 where t2.a > 1 update t2 set c = 4 where a=c set global query_plan_capture_config = DEFAULT; drop database if exists qpc_supported_queries_test; +DROP USER customerAdmin; UNINSTALL PLUGIN query_plan_capture; Warnings: Warning 1620 Plugin is busy and will be uninstalled on shutdown diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_access_count.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_access_count.test index 3217514..c7aa3d9 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_access_count.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_access_count.test @@ -5,6 +5,7 @@ --echo ======================================================================= --echo Install the query plan capture plugin --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; --echo ======================================================================= @@ -53,7 +54,7 @@ SELECT COUNT(*)=1 as EXPECT_FALSE FROM performance_schema.captured_query_plans W --echo ======================================================================= --echo Clean up --echo ======================================================================= -connect(adminCon, localhost, rdsadmin); +--connection rdsadmin drop database test1; drop user user@localhost; uninstall plugin query_plan_capture; diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_size.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_size.test index 37f3174..69e8d5e 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_size.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_size.test @@ -5,6 +5,7 @@ --echo ======================================================================= --echo Install the query plan capture plugin --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; --echo ======================================================================= @@ -46,6 +47,7 @@ select Query from performance_schema.captured_query_plans; SELECT COUNT(*)=3 as HAS_THREE_RESULTS FROM performance_schema.captured_query_plans WHERE query like 'select count(*) from test1.t%'; #cleanup +--connection rdsadmin drop database test1; drop user user@localhost; uninstall plugin query_plan_capture; diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_ttl.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_ttl.test index 56f8738..be38dd8 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_ttl.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/cache_ttl.test @@ -5,6 +5,7 @@ --echo ======================================================================= --echo Install the query plan capture plugin --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; --echo ======================================================================= @@ -48,7 +49,7 @@ SELECT COUNT(*)=1 as EXPECT_TRUE FROM performance_schema.captured_query_plans WH SELECT COUNT(*)=1 as EXPECT_FALSE FROM performance_schema.captured_query_plans WHERE query like 'select count(*) from test1.t1 where id > 10'; #cleanup -connect(adminCon, localhost, rdsadmin); +--connection rdsadmin drop database test1; drop user user@localhost; uninstall plugin query_plan_capture; diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/plan_id_for_long_numbers.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/plan_id_for_long_numbers.test index 1879a2b..6cf64fa 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/plan_id_for_long_numbers.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/plan_id_for_long_numbers.test @@ -5,6 +5,7 @@ --echo ======================================================================= --echo Install the query plan capture plugin and setup --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; --echo ======================================================================= @@ -102,7 +103,7 @@ WHERE query LIKE '%12345678901234567890123456789012345678901%'; --echo Clean up --echo ======================================================================= disconnect con1; -connect(adminCon, localhost, rdsadmin); +--connection rdsadmin DROP DATABASE test1; DROP USER user@localhost; UNINSTALL PLUGIN query_plan_capture; diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/plugin_install.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/plugin_install.test index e1ea389..bd5c4df 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/plugin_install.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/plugin_install.test @@ -2,22 +2,23 @@ --echo ======================================================================= --echo Test of Query Plan Capture plugin --echo ======================================================================= - ---disable_query_log ---disable_result_log ---source include/have_rds_init.sql_and_customerAdmin.inc ---enable_query_log ---enable_result_log ---let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err - --echo --echo ======================================================================= --echo Test install/uninstall the query plan capture plugin with rdsadmin --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; UNINSTALL PLUGIN query_plan_capture; +--echo +--echo ======================================================================= +--echo Create customerAdmin user with superuser role +--echo ======================================================================= + +CREATE USER 'customerAdmin'@'%'; +GRANT ALL PRIVILEGES ON *.* TO 'customerAdmin'@'%'; + --echo --echo ======================================================================= --echo Test install the query plan capture plugin with customerAdmin @@ -26,25 +27,20 @@ UNINSTALL PLUGIN query_plan_capture; connect(customerAdmin, localhost, customerAdmin); --disable_query_log -call mtr.add_suppression("\\[ERROR\\] \\[MY-010202\\] \\[Server\\] Plugin 'QUERY_PLAN_CAPTURE' init function returned error."); -call mtr.add_suppression("\\[ERROR\\] \\[MY-010734\\] \\[Server\\] Plugin 'QUERY_PLAN_CAPTURE' registration as a AUDIT failed."); +call mtr.add_suppression("Plugin 'QUERY_PLAN_CAPTURE' init function returned error."); +call mtr.add_suppression("Plugin 'QUERY_PLAN_CAPTURE' registration as a AUDIT failed."); --enable_query_log --error ER_CANT_INITIALIZE_UDF INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; ---let SEARCH_PATTERN=Plugin 'QUERY_PLAN_CAPTURE' init function returned error ---source include/search_pattern.inc ---let SEARCH_PATTERN=Plugin 'QUERY_PLAN_CAPTURE' registration as a AUDIT failed ---source include/search_pattern.inc - --echo --echo ======================================================================= --echo Test uninstall the query plan capture plugin with customerAdmin --echo ======================================================================= --echo # Install plugin with rdsadmin first -connect(rdsadmin, localhost, rdsadmin); +--connection rdsadmin INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; --echo # Uninstall plugin using customerAdmin @@ -55,4 +51,5 @@ UNINSTALL PLUGIN query_plan_capture; # Cleanup --connection rdsadmin +DROP USER 'customerAdmin'@'%'; UNINSTALL PLUGIN query_plan_capture; diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/prepared_statements.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/prepared_statements.test index 9296447..70fbd21 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/prepared_statements.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/prepared_statements.test @@ -5,6 +5,7 @@ --echo ======================================================================= --echo Install the query plan capture plugin and setup --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; --echo ======================================================================= @@ -70,7 +71,7 @@ WHERE query LIKE 'SELECT * FROM t1 WHERE id = ?'; --echo ======================================================================= DEALLOCATE PREPARE stmt1; disconnect con1; -connect(adminCon, localhost, rdsadmin); +--connection rdsadmin DROP DATABASE test1; DROP USER user@localhost; UNINSTALL PLUGIN query_plan_capture; diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/prepared_statements_digest_len.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/prepared_statements_digest_len.test index cd08bad..94eb846 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/prepared_statements_digest_len.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/prepared_statements_digest_len.test @@ -9,6 +9,7 @@ SELECT @@global.max_digest_length = 128; --echo ======================================================================= --echo Install the query plan capture plugin and setup --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; --echo ======================================================================= @@ -60,7 +61,7 @@ SELECT access_count = 2 AS expect_true FROM performance_schema.captured_query_pl DEALLOCATE PREPARE stmt1; disconnect con1; -connect(adminCon, localhost, rdsadmin); +--connection rdsadmin DROP DATABASE test1; DROP USER user@localhost; UNINSTALL PLUGIN query_plan_capture; diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/rds_managed_user_not_captured.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/rds_managed_user_not_captured.test index 2f91868..dc5fc86 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/rds_managed_user_not_captured.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/rds_managed_user_not_captured.test @@ -5,49 +5,29 @@ --echo ======================================================================= --echo Install the query plan capture plugin --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; --echo ======================================================================= --echo Setup tables and user --echo ======================================================================= ---disable_query_log ---disable_result_log ---source include/have_rds_init.sql_and_customerAdmin.inc ---enable_query_log ---enable_result_log +CREATE USER 'customerAdmin'@'%'; +GRANT ALL PRIVILEGES ON *.* TO 'customerAdmin'@'%'; create database test1; create table test1.t1(id int); create table test1.t2(id int); -create user 'rdsrepladmin'@'%'; -grant select on *.* to 'rdsrepladmin'@'%'; - -create user 'rdstopmgr'@'%'; -grant select on *.* to 'rdstopmgr'@'%'; - create user user@localhost; grant select on *.* to 'user'@'localhost'; -select user,host from mysql.user; - --echo ======================================================================= --echo Select the table with different users --echo ======================================================================= # Select as rdsadmin -connect(rdsadmin, localhost, rdsadmin, ,test1); -select count(*) from test1.t1 where id > 10; - -# Select as rdsrepladmin -connect(rdsrepladmin, localhost, rdsrepladmin, ,test1); -select count(*) from test1.t1 where id > 10; - -# Select as rdstopmgr -connect(rdstopmgr, localhost, rdstopmgr, ,test1); +--connection rdsadmin select count(*) from test1.t1 where id > 10; - # No plan should be captured ---connection rdsadmin SELECT COUNT(*)=0 as EXPECT_TRUE FROM performance_schema.captured_query_plans; # Select as normal user @@ -65,5 +45,4 @@ SELECT COUNT(*)=2 as EXPECT_TRUE FROM performance_schema.captured_query_plans; drop database test1; drop user user@localhost; drop user customerAdmin@'%'; - uninstall plugin query_plan_capture; diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/status_vars.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/status_vars.test index c267804..1fc4ea4 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/status_vars.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/status_vars.test @@ -5,6 +5,7 @@ --echo ======================================================================= --echo Install the query plan capture plugin --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; # start test with clean metrics by restarting engine @@ -24,7 +25,7 @@ create table test1.t5(id int); create user user@localhost; grant select on *.* to user@localhost; -connect(rdsadmin, localhost, rdsadmin); +--connection rdsadmin show global variables like 'query_plan_capture_%'; show global status like 'Qpc_%'; diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/supported_queries.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/supported_queries.test index b5a24ee..5324c9f 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/supported_queries.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/supported_queries.test @@ -5,6 +5,7 @@ --echo ======================================================================= --echo Install the query plan capture plugin --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; --echo ======================================================================= @@ -12,14 +13,7 @@ INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; --echo so the plan won't be captured --echo ======================================================================= ---disable_query_log ---disable_result_log ---source include/have_rds_init.sql_and_customerAdmin.inc ---enable_query_log ---enable_result_log - show global variables like 'query_plan_capture_%'; -connect(rdsadmin, localhost, rdsadmin); --disable_warnings drop database if exists qpc_supported_queries_test; @@ -46,6 +40,8 @@ set global query_plan_capture_config = "{\"version\":1,\"slow_time\":0,\"query_c --echo ======================================================================= --echo Switching to customerAdmin and run queries --echo ======================================================================= +CREATE USER 'customerAdmin'@'%'; +GRANT ALL PRIVILEGES ON *.* TO 'customerAdmin'@'%'; connect(customerAdmin, localhost, customerAdmin,, qpc_supported_queries_test); update t2 set c = 4 where a=c; update t2, (select * from t1 where t1.a= 1) as tt set t2.a=99 where t2.a > 1; @@ -68,5 +64,5 @@ set global query_plan_capture_config = DEFAULT; --disable_warnings drop database if exists qpc_supported_queries_test; --enable_warnings - +DROP USER customerAdmin; UNINSTALL PLUGIN query_plan_capture; diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/sys_vars.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/sys_vars.test index ff077f1..b8099ae 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/sys_vars.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/sys_vars.test @@ -8,6 +8,7 @@ --echo ======================================================================= --echo Install the query plan capture plugin --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; ### Test JSON parsing and validation of schema for query_plan_capture_config diff --git a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/update_unique_key.test b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/update_unique_key.test index af6ea6e..fa0f59f 100644 --- a/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/update_unique_key.test +++ b/RDSMySQL/mysql-test/suite/rds/query_plan_capture/t/update_unique_key.test @@ -5,6 +5,7 @@ --echo ======================================================================= --echo Install the query plan capture plugin and setup --echo ======================================================================= +--connect(rdsadmin, localhost, rdsadmin) INSTALL PLUGIN query_plan_capture SONAME 'query_plan_capture.so'; --echo ======================================================================= @@ -54,7 +55,7 @@ EXECUTE stmt1 USING @new_ref, @target_id; DEALLOCATE PREPARE stmt1; disconnect con1; -connect(adminCon, localhost, rdsadmin); +--connection rdsadmin DROP DATABASE test1; DROP USER user@localhost; UNINSTALL PLUGIN query_plan_capture; -- 2.47.3