This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new 7c6d280def4 [Test](manager_interface)append manager interface test.
(#35889) (#36912)
7c6d280def4 is described below
commit 7c6d280def40459a9d20a2fbdcd2d66acb3263e2
Author: Mingyu Chen <[email protected]>
AuthorDate: Fri Jun 28 18:38:33 2024 +0800
[Test](manager_interface)append manager interface test. (#35889) (#36912)
bp #35889
---------
Co-authored-by: daidai <[email protected]>
---
.../data/manager/test_manager_interface_1.out | 23 +
.../suites/manager/test_manager_interface_1.groovy | 798 +++++++++++++++++++++
.../suites/manager/test_manager_interface_2.groovy | 317 ++++++++
.../suites/manager/test_manager_interface_3.groovy | 613 ++++++++++++++++
4 files changed, 1751 insertions(+)
diff --git a/regression-test/data/manager/test_manager_interface_1.out
b/regression-test/data/manager/test_manager_interface_1.out
new file mode 100644
index 00000000000..0f50524d648
--- /dev/null
+++ b/regression-test/data/manager/test_manager_interface_1.out
@@ -0,0 +1,23 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !metadata_1 --
+internal test_manager_metadata_name_ids \N
+
+-- !metadata_2 --
+internal test_manager_metadata_name_ids test_metadata_name_ids
+
+-- !metadata_2 --
+
+-- !tables_1 --
+k1 TINYINT Yes true \N
+k2 DECIMAL(10, 2) Yes true 10.05
+k3 CHAR(10) Yes true \N BLOOM_FILTER
+k4 INT No false 1 NONE
+k5 TEXT Yes false \N NONE,BLOOM_FILTER
+
+-- !tables_2 --
+test_manager_tb_1 DUP_KEYS k1 TINYINT TINYINT Yes true
\N true
+ k2 DECIMAL(10, 2) DECIMALV3(10, 2) Yes true
10.05 true
+ k3 CHAR(10) CHAR(10) Yes true \N
BLOOM_FILTER true
+ k4 INT INT No false 1 NONE true
+ k5 TEXT TEXT Yes false \N
NONE,BLOOM_FILTER true
+
diff --git a/regression-test/suites/manager/test_manager_interface_1.groovy
b/regression-test/suites/manager/test_manager_interface_1.groovy
new file mode 100644
index 00000000000..55d9f655453
--- /dev/null
+++ b/regression-test/suites/manager/test_manager_interface_1.groovy
@@ -0,0 +1,798 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+import org.apache.doris.regression.suite.ClusterOptions
+import org.apache.doris.regression.util.NodeType
+
+import java.time.LocalDateTime
+import java.time.Duration
+import java.time.format.DateTimeFormatter
+
+
+
+suite('test_manager_interface_1',"p0") {
+
+
+
+ logger.info("test_manager_interface_1 start")
+
+ sql """ switch internal """
+
+
+ String jdbcUrl = context.config.jdbcUrl
+ def tokens = context.config.jdbcUrl.split('/')
+ jdbcUrl=tokens[0] + "//" + tokens[2] + "/" + "?"
+ String jdbcUser = context.config.jdbcUser
+ String jdbcPassword = context.config.jdbcPassword
+ String s3_endpoint = getS3Endpoint()
+ String bucket = getS3BucketName()
+ String driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/mysql-connector-java-8.0.25.jar";
+
+
+
+//select * from internal.information_schema.schemata
+ def test_schemata = {
+ logger.info("TEST select * from internal.information_schema.schemata");
+
+ List<List<Object>> schemata = sql """select * from
internal.information_schema.schemata"""
+ for (int i = 0; i < schemata.size(); i++) {
+ assertTrue(!schemata[i][0].isEmpty()) // CATALOG_NAME
+ assertTrue(schemata[i][0].toLowerCase() != "null")
+
+ assertTrue(!schemata[i][1].isEmpty()) // SCHEMA_NAME
+ assertTrue(schemata[i][1].toLowerCase() != "null")
+ }
+ List<List<Object>> schemata2 = sql """select * from
internal.information_schema.schemata where CATALOG_NAME = "internal" and
SCHEMA_NAME = "__internal_schema" """
+ assertTrue(schemata2.size() == 1)
+
+ sql """ drop database if exists internal.test_information_schemata_1;
"""
+ sql """ create database internal.test_information_schemata_1; """
+ List<List<Object>> schemata3 = sql """select * from
internal.information_schema.schemata where CATALOG_NAME = "internal" and
SCHEMA_NAME = "test_information_schemata_1" """
+ assertTrue(schemata3.size() == 1)
+
+ sql """ drop database internal.test_information_schemata_1; """
+ List<List<Object>> schemata4 = sql """select * from
internal.information_schema.schemata where CATALOG_NAME = "internal" and
SCHEMA_NAME = "test_information_schemata_1" """
+ assertTrue(schemata4.size() == 0)
+ }
+ test_schemata()
+
+
+//select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from
information_schema.tables
+ def test_information_tables = {
+ logger.info("TEST select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
TABLE_TYPE from information_schema.tables")
+
+ List<List<Object>> result = sql """select TABLE_CATALOG,
TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from information_schema.tables"""
+ for(int i = 0;i<result.size();i++) {
+ assertTrue(!result[i][0].isEmpty())
+ assertTrue(!result[i][1].isEmpty())
+ assertTrue(!result[i][2].isEmpty())
+ assertTrue(!result[i][3].isEmpty())
+ assertTrue(result[i][0].toLowerCase() != "null")
+ assertTrue(result[i][1].toLowerCase() != "null")
+ assertTrue(result[i][2].toLowerCase() != "null")
+ assertTrue(result[i][3].toLowerCase() != "null")
+
+ assertTrue( result[i][3] in ["SYSTEM VIEW","VIEW","BASE TABLE"] )
+ }
+
+ }
+ test_information_tables()
+
+//select * from information_schema.metadata_name_ids
+ def test_metadata_name_ids = {
+ logger.info("TEST select * from information_schema.metadata_name_ids")
+ List<List<Object>> result = sql """select * from
information_schema.metadata_name_ids """
+ def tableName = "internal.information_schema.metadata_name_ids"
+ sql """ create database if not exists test_manager_metadata_name_ids;
"""
+ sql """ use test_manager_metadata_name_ids ; """
+
+ qt_metadata_1 """ select CATALOG_NAME,DATABASE_NAME,TABLE_NAME from
${tableName}
+ where CATALOG_NAME="internal" and DATABASE_NAME
="test_manager_metadata_name_ids" """
+
+ sql """ create table if not exists test_metadata_name_ids (
+ a int ,
+ b varchar(30)
+ )
+ DUPLICATE KEY(`a`)
+ DISTRIBUTED BY HASH(`a`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ ); """
+
+ qt_metadata_2 """ select CATALOG_NAME,DATABASE_NAME,TABLE_NAME from
${tableName}
+ where CATALOG_NAME="internal" and DATABASE_NAME
="test_manager_metadata_name_ids" """
+
+ sql """ drop table test_metadata_name_ids """
+
+ qt_metadata_2 """ select CATALOG_NAME,DATABASE_NAME,TABLE_NAME from
${tableName}
+ where CATALOG_NAME="internal" and DATABASE_NAME
="test_manager_metadata_name_ids" and TABLE_NAME="test_metadata_name_ids";"""
+ }
+ test_metadata_name_ids()
+
+
+
+//show catalogs
+//alter catalog $old_name rename $new_name
+//drop catalog $catalog_name
+ def test_catalogs = {
+ logger.info("TEST show/rename/drop catalogs")
+ def catalog_name = "test_manager_catalogs_case"
+ // println jdbcUrl
+ sql """ drop catalog if exists ${catalog_name}"""
+ sql """ CREATE CATALOG `${catalog_name}` PROPERTIES(
+ "user" = "${jdbcUser}",
+ "type" = "jdbc",
+ "password" = "${jdbcPassword}",
+ "jdbc_url" = "${jdbcUrl}",
+ "driver_url" = "${driver_url}",
+ "driver_class" = "com.mysql.cj.jdbc.Driver"
+ )"""
+
+ List<List<Object>> result = sql """ show catalogs """
+ //CatalogName
+ def x = 0
+ for( int i =0 ;i < result.size();i++ ) {
+ assertTrue(result[i][1].toLowerCase() != "null")
+ if ( result[i][1].toLowerCase() == catalog_name) {
+ x = 1
+ }
+ }
+ assertTrue(x == 1)
+
+ x = 0
+ sql """ alter catalog ${catalog_name} rename ${catalog_name}_rename """
+ result = sql """ show catalogs """
+ for( int i =0 ;i < result.size();i++ ) {
+ assertTrue(result[i][1].toLowerCase() != "null")
+ if ( result[i][1].toLowerCase() == catalog_name + "_rename") {
+ x = 1
+ }
+ }
+ assertTrue(x == 1)
+
+ x = 0
+ sql """ drop catalog ${catalog_name}_rename"""
+ result = sql """ show catalogs """
+ for( int i =0 ;i < result.size();i++ ) {
+ assertTrue(result[i][1].toLowerCase() != "null")
+ if ( result[i][1].toLowerCase() == catalog_name + "_rename") {
+ x = 1
+ }
+ }
+ assertTrue(x == 0)
+ }
+ test_catalogs()
+
+
+
+//show databases
+//alter database $old_name rename $new_name
+//create database $database_name
+ def test_databases = {
+ logger.info("TEST show/rename/drop databases")
+ def databases_name = "test_manager_db_case"
+
+
+ sql """ switch internal """
+ sql """ drop database if exists ${databases_name} """
+ sql """ drop database if exists ${databases_name}_rename """
+
+ sql """ create database ${databases_name} """
+ List<List<Object>> result = sql """ show databases """
+
+ def x = 0
+ for( int i =0 ;i < result.size();i++ ) {
+ assert(result[i].size() == 1)
+ assertTrue(result[i][0].toLowerCase() != "null") //Database
+ if ( result[i][0].toLowerCase() == databases_name) {
+ x = 1
+ }
+ }
+ assertTrue(x == 1)
+
+ x = 0
+ sql """ alter database ${databases_name} rename
${databases_name}_rename """
+ result = sql """ show databases """
+ for( int i =0 ;i < result.size();i++ ) {
+ assertTrue(result[i][0].toLowerCase() != "null")
+ if ( result[i][0].toLowerCase() == databases_name + "_rename") {
+ x = 1
+ }
+ }
+ assertTrue(x == 1)
+
+ x = 0
+ sql """ drop database ${databases_name}_rename"""
+ result = sql """ show databases """
+ for( int i =0 ;i < result.size();i++ ) {
+ assertTrue(result[i][0].toLowerCase() != "null")
+ if ( result[i][0].toLowerCase() == databases_name + "_rename") {
+ x = 1
+ }
+ }
+ assertTrue(x == 0)
+ }
+ test_databases()
+
+
+
+
+// show tables && show tables like '$table_name'
+//desc $table_name && desc $table_name all
+//show create table '$table_name'
+//drop tables
+ def test_show_tables = {
+ sql """ drop database if exists test_manager_tb_case """
+ sql """create database test_manager_tb_case"""
+ sql """ use test_manager_tb_case """
+
+ List<List<Object>> result = sql """ show tables """
+ assertTrue(result.size() == 0)
+ result = sql """ show tables like 'test_manager_tb%' """
+ assertTrue(result.size() == 0)
+
+ sql """
+ create table test_manager_tb_1
+ (
+ k1 TINYINT,
+ k2 DECIMAL(10, 2) DEFAULT "10.05",
+ k3 CHAR(10) COMMENT "string column",
+ k4 INT NOT NULL DEFAULT "1" COMMENT "int column",
+ k5 STRING
+ ) COMMENT "manager_test_table"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1',
+ "bloom_filter_columns" = "k3,k5"
+ );"""
+
+ qt_tables_1 """ desc test_manager_tb_1 """
+ qt_tables_2 """ desc test_manager_tb_1 all"""
+
+
+ result = sql """ show tables """
+ assertTrue(result.size() == 1)
+ assertTrue(result[0].size() == 1)
+ assertTrue(result[0][0].toLowerCase() == "test_manager_tb_1")
+ result = sql """ show tables like 'test_manager_tb%' """
+ assertTrue(result.size() == 1)
+ assertTrue(result[0].size() == 1)
+ assertTrue(result[0][0].toLowerCase() == "test_manager_tb_1")
+
+
+ result = sql """ show create table test_manager_tb_1"""
+ assertTrue(result[0][0] == "test_manager_tb_1") // TABLE NAME
+ // assertTrue(result[0][1].substring() == "test_manager_tb_1") //DDL
+ def ddl_str = result[0][1]
+ def idx = ddl_str.indexOf("PROPERTIES")
+ assertTrue(idx != -1 );
+ assertTrue( ddl_str.startsWith("""CREATE TABLE `test_manager_tb_1` (
+ `k1` TINYINT NULL,
+ `k2` DECIMAL(10, 2) NULL DEFAULT "10.05",
+ `k3` CHAR(10) NULL COMMENT 'string column',
+ `k4` INT NOT NULL DEFAULT "1" COMMENT 'int column',
+ `k5` TEXT NULL
+) ENGINE=OLAP
+DUPLICATE KEY(`k1`, `k2`, `k3`)
+COMMENT 'manager_test_table'
+DISTRIBUTED BY HASH(`k1`) BUCKETS 1"""))
+
+ sql """ drop table test_manager_tb_1 """
+ result = sql """ show tables """
+ assertTrue(result.size() == 0)
+ result = sql """ show tables like 'test_manager_tb%' """
+ assertTrue(result.size() == 0)
+
+ sql """ drop database test_manager_tb_case """
+ }
+ test_show_tables()
+
+
+//alter table $table_name set ("$key" = "$value")
+ def test_tables_PROPERTIES = {
+ sql """ drop database if exists test_manager_tb_properties_case
FORCE"""
+ sql """create database test_manager_tb_properties_case"""
+ sql """ use test_manager_tb_properties_case """
+
+
+ sql """ create table test_manager_tb_2
+ (
+ k1 TINYINT,
+ k2 DECIMAL(10, 2) DEFAULT "10.05",
+ k3 CHAR(10) COMMENT "string column",
+ k4 INT NOT NULL DEFAULT "1" COMMENT "int column",
+ k5 STRING
+ ) COMMENT "manager_test_table"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1',
+ "bloom_filter_columns" = "k3"
+ );"""
+
+ List<List<Object>> result = sql """ show create table
test_manager_tb_2 """
+ assertTrue(result.size() == 1)
+ assertTrue(result[0][0] == "test_manager_tb_2")
+ def ddl_str = result[0][1]
+ def idx = ddl_str.indexOf("replication_allocation")
+ assertTrue(
ddl_str.substring(idx,ddl_str.length()).startsWith("""replication_allocation" =
"tag.location.default: 1"""))
+
+ idx = ddl_str.indexOf("min_load_replica_num")
+ assertTrue(
ddl_str.substring(idx,ddl_str.length()).startsWith("""min_load_replica_num" =
"-1"""))
+
+ sql """alter table test_manager_tb_2 set ("min_load_replica_num" =
"1")"""
+ result = sql """ show create table test_manager_tb_2 """
+ assertTrue(result[0][0] == "test_manager_tb_2")
+ ddl_str = result[0][1]
+ idx = ddl_str.indexOf("min_load_replica_num")
+ assertTrue(
ddl_str.substring(idx,ddl_str.length()).startsWith("""min_load_replica_num" =
"1"""))
+
+
+
+ sql """ DROP table test_manager_tb_2 FORCE"""
+ sql """ drop database test_manager_tb_properties_case FORCE"""
+
+ }
+ test_tables_PROPERTIES()
+
+
+// show table status from $db_name like '$table_name'
+ def test_tables_status = {
+
+ sql """ drop database if exists test_manager_tb_case_3 """
+ sql """create database test_manager_tb_case_3 """
+ sql """ use test_manager_tb_case_3 """
+
+ def formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
+ def now = LocalDateTime.now()
+ def formattedNow = now.format(formatter)
+ logger.info( " NOW TIME = ${formattedNow} " )
+
+ sql """ create table test_manager_tb
+ (
+ k1 TINYINT,
+ k2 CHAR(10) COMMENT "string column",
+ k3 INT NOT NULL DEFAULT "1" COMMENT "int column",
+ ) COMMENT "manager_test_table"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1');"""
+
+
+ List<List<Object>> result = sql """ show table status from
test_manager_tb_case_3 like 'test_manager_tb%' """
+ println result[0][4]
+ assertTrue(result[0][4] == 0 )// Rows
+
+ def create_time = result[0][11] //Create_time
+ def duration = Duration.between(now, create_time)
+ assertTrue(Math.abs(duration.toHours()) < 2)
+ logger.info( "table test_manager_tb Create TIME = ${create_time} " )
+
+ def update_time1 = result[0][12]//Update_time
+ duration = Duration.between(now, update_time1)
+ assertTrue(Math.abs(duration.toHours()) < 2)
+ logger.info( "table test_manager_tb Update TIME = ${update_time1} " )
+
+ assertTrue( "manager_test_table" == result[0][17] ) //Comment
+
+ result = sql """ insert into test_manager_tb values (1,"hell0",10);"""
+ assertTrue(result[0][0] == 1)
+ result = sql """insert into test_manager_tb values (2,"hell0",20); """
+ assertTrue(result[0][0] == 1)
+ result = sql """insert into test_manager_tb values (3,"hell0",30);"""
+ assertTrue(result[0][0] == 1)
+ result = sql """ insert into test_manager_tb values (4,"hell0",40);"""
+ assertTrue(result[0][0] == 1)
+ result = sql """ insert into test_manager_tb values (5,"hell0",50);
"""
+ assertTrue(result[0][0] == 1)
+
+ def j = 0 ;
+ def retryTime = 100;
+ for (j =0 ;j < retryTime;j++) {
+ sql """ select * from test_manager_tb_case_3.test_manager_tb; """
+ result = sql """ show table status from test_manager_tb_case_3
like 'test_manager_tb%' """
+ if ( result[0][4] == 5) {
+ assert( create_time == result[0][11])//Create_time
+ assertTrue( "manager_test_table" == result[0][17] ) //Comment
+ def update_time2 = result[0][12] //Update_time
+ duration = Duration.between(now, update_time2)
+ assertTrue(Math.abs(duration.toHours()) < 5)
+ logger.info( "table test_manager_tb Update TIME =
${update_time2} " )
+
+ break;
+ }
+ sleep(10000)
+ }
+ if (j == retryTime) {
+
+ logger.info(" TEST show table status from $db_name like
'$table_name';ROWS UPDATE FAIL.");
+ assertTrue(false);
+ }
+
+
+
+ sql """ drop table test_manager_tb """
+ sql """ drop database if exists test_manager_tb_case_3 """
+ }
+ test_tables_status()
+
+
+
+//show index from '$table_name'
+ def test_table_index = {
+ sql """ drop database if exists test_manager_tb_case_4 """
+ sql """create database test_manager_tb_case_4 """
+ sql """ use test_manager_tb_case_4 """
+
+ sql """
+ create table test_manager_tb
+ (
+ k1 TINYINT,
+ k2 CHAR(10) COMMENT "string column",
+ k3 INT NOT NULL DEFAULT "1" COMMENT "int column",
+ ) COMMENT "manager_test_table"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1',
+ "bloom_filter_columns" = "k2");"""
+
+
+
+ List<List<Object>> result = sql """ insert into test_manager_tb
values (5,"hell0",50); """
+ assertTrue(result[0][0] == 1)
+ result = sql """ insert into test_manager_tb values (5,"hell0",50);
"""
+ assertTrue(result[0][0] == 1)
+ result = sql """ insert into test_manager_tb values (5,"hell0",50);
"""
+ assertTrue(result[0][0] == 1)
+ result = sql """ insert into test_manager_tb values (5,"hell0",50);
"""
+ assertTrue(result[0][0] == 1)
+ result = sql """ insert into test_manager_tb values (5,"hell0",50);
"""
+ assertTrue(result[0][0] == 1)
+
+ sql """ CREATE INDEX bitmap_index_name ON test_manager_tb (k1) USING
BITMAP COMMENT 'bitmap_k1'; """
+
+ def j = 0 ;
+ def retryTime = 100;
+ for (j =0 ;j < retryTime;j++) {
+ result = sql """ show index from test_manager_tb; """
+
+ if (result.size() == 1){
+ break;
+ }
+ sleep(1000);
+ }
+
+ if (j == retryTime) {
+ logger.info(" TEST show index from '$table_name' FAIL.");
+ assertTrue(false);
+ }
+
+
+
+ assertTrue(result[0][2] == "bitmap_index_name" )//Key_name
+ assertTrue(result[0][4] == "k1" )//Column_name
+ assertTrue(result[0][10] == "INVERTED" ) // branch 2.1 is INVERTED,
master is BITMAP
+ assertTrue(result[0][11] == "bitmap_k1" ) //bitmap_siteid
+
+ sql """ drop INDEX bitmap_index_name on test_manager_tb;"""
+
+
+
+
+ sql """ drop table test_manager_tb FORCE """
+ sql """ drop database if exists test_manager_tb_case_4 FORCE"""
+
+ }
+ test_table_index()
+
+
+
+
+
+// show proc '/current_query_stmts'
+// show proc '/current_queries'
+// show processlist
+// kill query $query_id
+// SHOW PROC '/cluster_health/tablet_health'
+ def test_proc = {
+
+ def futures = []
+
+
+ futures.add( thread {
+
+ try{
+ sql """ select sleep(9809); """
+ }catch(Exception e){
+
+ }
+ })
+ futures.add( thread {
+ sleep(1000);
+ List<List<Object>> result = sql """ show proc
'/current_query_stmts' """
+ def x = 0
+ def queryid = ""
+ logger.info("result = ${result}")
+
+ for( int i = 0;i<result.size();i++) {
+ if (result[i][7] != null &&
result[i][7].contains("sleep(9809)") )//Statement
+ {
+ x = 1
+ queryid = result[i][0]
+ logger.info("query ID = ${queryid}")
+ assertTrue(result[i][0]!= null) //QueryId
+ assertTrue(result[i][1]!= null) //ConnectionId
+ assertTrue(result[i][2]!= null)//Catalog
+ assertTrue(result[i][3]!= null)//Database
+ assertTrue(result[i][4]!= null)//User
+ assertTrue(result[i][5]!= null)//ExecTime
+ assertTrue(result[i][5].isNumber())//ExecTime
+ assertTrue(result[i][6]!= null)//SqlHash
+ }
+ }
+ assertTrue(x == 1)
+
+ x = 0
+ result = sql """ show proc '/current_queries' """
+ logger.info("result = ${result}")
+ for( int i = 0;i<result.size();i++) {
+ if (result[i][0] == queryid )//QueryId
+ {
+ x = 1
+ assertTrue(result[i][5]!= null)//ScanBytes
+ assertTrue(result[i][6]!= null)//ProcessBytes
+ }
+ }
+ assertTrue(x == 1)
+
+ result = sql """ show processlist """
+ logger.info("result = ${result}")
+ for( int i =0 ;i < result.size();i++ ){
+ assertTrue( result[i][2].toLowerCase() != "null" )//User
+ assertTrue( result[i][3].toLowerCase() != "null" )//Host
+ assertTrue( result[i][5].toLowerCase() != "null" )//Catalog
+ assertTrue( result[i][6].toLowerCase() != "null" )//Db
+ assertTrue( result[i][10].toLowerCase() != "null" )//QueryId
+ if (result[i][10] == queryid) {
+ x = 1
+ }
+ }
+
+ assertTrue(x == 1)
+ sql """ kill query "${queryid}" """
+
+ x = 0
+ sleep(5000)
+
+ result = sql """ show proc '/current_queries' """
+ logger.info("result = ${result}")
+ for( int i = 0;i<result.size();i++) {
+ if (result[i][0] == queryid )//QueryId
+ {
+ x = 1
+ }
+ }
+ assertTrue(x == 0)
+ })
+ futures.each { it.get() }
+
+
+ def tablet_num = 0;
+ def healthy_num = 0;
+ def total_tablet_num = 0;
+ def total_healthy_num = 0;
+ result = sql """ SHOW PROC '/cluster_health/tablet_health' """
+ for( int i =0 ;i < result.size();i++ ){
+ assertTrue(result[i][0].toLowerCase() != null ) // DbId
+ if (result[i][0].toLowerCase() == "total") {
+ total_tablet_num = result[i][2].toBigInteger();
+ total_healthy_num = result[i][3].toBigInteger();
+ }else {
+ tablet_num += result[i][2].toBigInteger();
+ healthy_num += result[i][3].toBigInteger();
+
+ }
+ // assertTrue(result[i][2]()) // TabletNum
+ // assertTrue(result[i][3]()) // HealthyNum
+ }
+ assertTrue(total_healthy_num == healthy_num )
+ assertTrue(total_healthy_num == healthy_num )
+
+
+
+ }
+ test_proc();
+
+
+
+//select a.*, b.*, c.NAME as WORKLOAD_GROUP_NAME from
information_schema.active_queries a left join
information_schema.backend_active_tasks b on a.QUERY_ID = b.QUERY_ID left join
information_schema.workload_groups c on a.WORKLOAD_GROUP_ID = c.ID
+ def test_active_query = {
+
+ List<List<Object>> result = sql """ select 1;"""
+
+
+ def futures = []
+ futures.add( thread {
+
+ try{
+ sql """ select sleep(87676); """
+ }catch(Exception e){
+ }
+ })
+
+ futures.add( thread {
+ sleep(3000)
+
+ result = sql """
+ select a.*, b.*, c.NAME as WORKLOAD_GROUP_NAME from
information_schema.active_queries a left join
+ information_schema.backend_active_tasks b on a.QUERY_ID =
b.QUERY_ID left join information_schema.workload_groups c on
a.WORKLOAD_GROUP_ID = c.ID
+ """
+ logger.info("result = ${result}")
+
+ def x = 0
+ def queryId = ""
+ for( int i =0 ;i < result.size();i++ ){
+ assertTrue(result[i][0] != null ) // QueryId
+
+ if ( result[i][9].contains("sleep(87676)") ){
+ x = 1
+ queryId = result[i][0]
+ logger.info("result = ${queryId}}")
+
+ assertTrue(result[i][2]!=null) // QUERY_TIME_MS
+ assertTrue(result[i][14]!=null) // TASK_CPU_TIME_MS
+ assertTrue(result[i][15].toBigInteger() ==0 ) // SCAN_ROWS
+ assertTrue(result[i][16].toBigInteger() ==0)//SCAN_BYTES
+ assertTrue(result[i][19].toBigInteger() ==0) //
SHUFFLE_SEND_BYTES
+ assertTrue(result[i][20].toBigInteger() ==0) //
SHUFFLE_SEND_ROWS
+ assertTrue(result[i][18]!=null) //
CURRENT_USED_MEMORY_BYTES
+ assertTrue(result[i][22]!=null) // WORKLOAD_GROUP_NAME
+ }
+ }
+ assertTrue(x == 1)
+ sql """ kill query "${queryId}" """
+ })
+ futures.each { it.get() }
+ }
+ test_active_query()
+
+
+
+//select * from __internal_schema.audit_log
+ def test_audit_log = {
+
+ sql """ set global enable_audit_plugin = true; """
+ List<List<Object>> result =sql """ show create table
__internal_schema.audit_log; """
+
+ assertTrue(result[0][0] == "audit_log")
+
+ assertTrue(result[0][1].contains("CREATE TABLE `audit_log`"))
+ assertTrue(result[0][1].contains("`query_id` VARCHAR(48) NULL,"))
+ assertTrue(result[0][1].contains("`time` DATETIME(3) NULL,"))
+ assertTrue(result[0][1].contains("`client_ip` VARCHAR(128) NULL,"))
+ assertTrue(result[0][1].contains("`user` VARCHAR(128) NULL,"))
+ assertTrue(result[0][1].contains("`catalog` VARCHAR(128) NULL"))
+ assertTrue(result[0][1].contains("`db` VARCHAR(128) NULL,"))
+ assertTrue(result[0][1].contains("`state` VARCHAR(128) NULL"))
+ assertTrue(result[0][1].contains("`error_code` INT NULL,"))
+ assertTrue(result[0][1].contains("`error_message` TEXT NULL,"))
+ assertTrue(result[0][1].contains("`query_time` BIGINT NULL,"))
+ assertTrue(result[0][1].contains("`scan_bytes` BIGINT NULL,"))
+ assertTrue(result[0][1].contains("`scan_rows` BIGINT NULL,"))
+ assertTrue(result[0][1].contains("`return_rows` BIGINT NULL,"))
+ assertTrue(result[0][1].contains("`stmt_id` BIGINT NULL,"))
+ assertTrue(result[0][1].contains("`is_query` TINYINT NULL,"))
+ assertTrue(result[0][1].contains("`frontend_ip` VARCHAR(128) NULL,"))
+ assertTrue(result[0][1].contains("`cpu_time_ms` BIGINT NULL,"))
+ assertTrue(result[0][1].contains("`sql_hash` VARCHAR(128) NULL,"))
+ assertTrue(result[0][1].contains("`sql_digest` VARCHAR(128) NULL,"))
+ assertTrue(result[0][1].contains("`peak_memory_bytes` BIGINT NULL,"))
+ assertTrue(result[0][1].contains("`workload_group` TEXT NULL,"))
+ assertTrue(result[0][1].contains("`stmt` TEXT NULL"))
+
+ assertTrue(result[0][1].contains("ENGINE=OLAP"))
+
+ assertTrue(result[0][1].contains("DUPLICATE KEY(`query_id`, `time`,
`client_ip`)"))
+ assertTrue(result[0][1].contains("""PARTITION BY RANGE(`time`)"""))
+ assertTrue(result[0][1].contains("""dynamic_partition.enable" =
"true"""))
+ assertTrue(result[0][1].contains("""dynamic_partition.time_unit" =
"DAY"""))
+ assertTrue(result[0][1].contains("""dynamic_partition.start" =
"-30"""))
+
+
+ sql """ set global enable_audit_plugin = false; """
+ }
+ test_audit_log()
+
+
+// admin show frontend config
+//show frontend config
+// admin set frontend config($key = $value)
+// set global $key = $value
+// show global variables like '%$key'
+//show variables like "%version_comment%";
+ def test_config = {
+
+ List<List<Object>> result = sql """
+ admin show frontend config
+ """
+ def x = 0;
+
+ def val = 0;
+
+ for(int i = 0 ;i<result.size();i++) {
+ if (result[i][0] == "query_metadata_name_ids_timeout"){
+ x = 1
+ val = result[i][1].toBigInteger() + 2
+ assertTrue( result[i][2] =="long" )
+ assertTrue( result[i][3] =="true" )
+ assertTrue( result[i][4] == "false")
+ }
+ }
+ assertTrue(x == 1);
+
+
+ sql """ admin set frontend config("query_metadata_name_ids_timeout"=
"${val}")"""
+ result = sql """
+ admin show frontend config
+ """
+ x = 0
+ for(int i = 0 ;i<result.size();i++) {
+ if (result[i][0] == "query_metadata_name_ids_timeout"){
+ x = 1
+ assertTrue( result[i][1] =="${val}" )
+ assertTrue( result[i][2] =="long" )
+ assertTrue( result[i][3] =="true" )
+ assertTrue( result[i][4] == "false")
+ }
+ }
+ assertTrue(x == 1);
+
+ val -= 2
+ sql """ admin set frontend config("query_metadata_name_ids_timeout"=
"${val}")"""
+
+
+
+ x = 0
+ result = sql """ show global variables like
"create_table_partition_max_num" """
+ assert(result[0][0] == "create_table_partition_max_num")
+ val = result[0][1].toBigInteger() + 1 ;
+ assert(result[0][2] == "10000")
+ sql """ set global create_table_partition_max_num = ${val} """
+ result = sql """ show global variables like
"create_table_partition_max_num" """
+ assert(result[0][1].toBigInteger() == val)
+ val -= 1
+ sql """ set global create_table_partition_max_num = ${val} """
+
+
+ result = sql """ show frontend config """
+ x = 0
+ for(int i = 0 ;i<result.size();i++) {
+ if (result[i][0] == "edit_log_type") {
+ assertTrue( result[i][1] =="bdb" )
+ assertTrue( result[i][2] =="String")
+ assertTrue( result[i][3] =="false" )
+ assertTrue( result[i][4] == "false")
+ x = 1
+ }
+ }
+ assert(x == 1)
+
+
+ result = sql """ show variables like "%version_comment%"; """
+ assertTrue(result.size() == 1)
+ assertTrue(result[0][0] == "version_comment")
+ }
+ test_config();
+
+
+ logger.info("test_manager_interface_1 end")
+}
diff --git a/regression-test/suites/manager/test_manager_interface_2.groovy
b/regression-test/suites/manager/test_manager_interface_2.groovy
new file mode 100644
index 00000000000..329285364d7
--- /dev/null
+++ b/regression-test/suites/manager/test_manager_interface_2.groovy
@@ -0,0 +1,317 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+import org.apache.doris.regression.suite.ClusterOptions
+import org.apache.doris.regression.util.NodeType
+
+
+
+
+suite('test_manager_interface_2',"p0") {
+
+// show data && show data from 'table_name'"
+ def test_table_data = {
+ sql """ drop database if exists test_manager_tb_case_5 """
+ sql """create database test_manager_tb_case_5 """
+ sql """ use test_manager_tb_case_5 """
+
+ sql """
+ create table test_manager_tb
+ (
+ k1 TINYINT,
+ k2 DECIMAL(10, 2) DEFAULT "10.05",
+ k3 CHAR(10) COMMENT "string column",
+ k4 INT NOT NULL DEFAULT "1" COMMENT "int column",
+ k5 STRING
+ ) COMMENT "manager_test_table"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1',
+ "bloom_filter_columns" = "k3,k5"
+ );"""
+
+
+ result =sql """ show data """
+ for(int i = 0 ; i < result.size();i++) {
+ assert(result[i][0].toLowerCase() != "null") //TableName
+ assert(result[i][1].toLowerCase() != "null") //Size
+ assert(result[i][2].isNumber()) //ReplicaCount
+ }
+
+ result =sql """ show data from test_manager_tb """
+ for(int i = 0 ; i < result.size();i++) {
+ if (i +1 != result.size()) {
+ assert(result[i][0].toLowerCase() != "null") //TableName
+ }
+
+ assert(result[i][2].toLowerCase() != "null") //Size
+ assert(result[i][3].isNumber()) //ReplicaCount
+ }
+
+ sql """ drop database if exists test_manager_tb_case_5 """
+ }
+ test_table_data()
+
+
+// show partitions from '$table'
+ def test_partitions = {
+
+ sql """ drop database if exists test_manager_partitions_case """
+ sql """create database test_manager_partitions_case """
+ sql """ use test_manager_partitions_case """
+
+ sql """ create table test_manager_tb
+ (
+ k1 TINYINT,
+ k2 CHAR(10) COMMENT "string column",
+ k3 INT NOT NULL DEFAULT "1" COMMENT "partition column",
+ ) COMMENT "manager_test_load_table"
+ PARTITION by Range(k3)
+ (
+ PARTITION `less100` VALUES LESS THAN (100),
+ PARTITION `less200` VALUES LESS THAN (200),
+ PARTITION `less2000` VALUES LESS THAN (2000)
+ )
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1');
+ """
+
+ List<List<Object>> result = sql """ show partitions from
test_manager_tb """
+
+ for( int i =0 ; i <result.size();i++) {
+ assertTrue( result[i][1] in ["less100","less200","less2000"])
+ assertTrue( result[i][9].toBigInteger() == 1) // ReplicationNum
+ }
+
+
+ sql """ drop database if exists test_manager_partitions_case """
+
+ }
+ test_partitions()
+
+
+
+// show load from "$db" where state='$status'
+ def test_load = {
+
+ sql """ drop database if exists test_manager_load_case """
+ sql """create database test_manager_load_case """
+ sql """ use test_manager_load_case """
+
+ sql """ create table test_manager_tb
+ (
+ k1 TINYINT,
+ k2 CHAR(10) COMMENT "string column",
+ k3 INT NOT NULL DEFAULT "1" COMMENT "int column",
+ ) COMMENT "manager_test_load_table"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1');"""
+
+ List<List<Object>> result = sql """ insert into test_manager_tb values
(1,"hell0",10);"""
+ assertTrue(result[0][0] == 1)
+ result = sql """insert into test_manager_tb values (2,"hell0",20); """
+ assertTrue(result[0][0] == 1)
+ result = sql """insert into test_manager_tb values (3,"hell0",30);"""
+ assertTrue(result[0][0] == 1)
+ result = sql """ insert into test_manager_tb values (4,"hell0",40);"""
+ assertTrue(result[0][0] == 1)
+ result = sql """ insert into test_manager_tb values (5,"hell0",50);
"""
+ assertTrue(result[0][0] == 1)
+
+
+
+ sql """ create table test_manager_tb_2
+ (
+ k1 TINYINT,
+ k2 CHAR(10) COMMENT "string column"
+ ) COMMENT "manager_test_load_table_2"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1');"""
+
+ result = sql """ insert into test_manager_tb_2 values (1,"hell0");"""
+ assertTrue(result[0][0] == 1)
+ result = sql """insert into test_manager_tb_2 values (2,"hell0"); """
+ assertTrue(result[0][0] == 1)
+
+ result = sql """ show load from test_manager_load_case where
state='FINISHED' """
+ for(int i =0 ;i< result.size();i++) {
+ assertTrue(result[i][0].toLowerCase() != "null" )//JobId
+ assertTrue(result[i][0].toBigInteger() != 0 )//JobId
+ assertTrue(result[i][2] == "FINISHED") //State
+ assertTrue(result[i][3].contains( result[i][0]))// Progress
+ }
+
+
+
+
+ sql """ drop database if exists test_manager_load_case """
+ }
+ test_load();
+
+
+// show backends
+//alter system modify backend
+// show frontends
+// show broker
+// ALTER TABLE internal.__internal_schema.column_statistics SET
("replication_num" = "1")
+ def test_system = {
+
+
+ def address = "127.0.0.1"
+ def notExistPort = 12346
+ def notExistPort2 = 234567
+ try {
+ sql """ALTER SYSTEM DROPP BACKEND "${address}:${notExistPort}";"""
+ }catch (Exception e) {
+ }
+
+ sql """ALTER SYSTEM ADD BACKEND "${address}:${notExistPort}";"""
+
+ result = sql """SHOW BACKENDS;"""
+
+ def x = 0
+ for(int i =0 ;i<result.size();i++) {
+ //HeartbeatPort:
+ if (result[i][2].toBigInteger() == notExistPort) {
+ assertTrue(result[i][0]!=null)//name
+
+ assert(result[i][19].toLowerCase() != "null") // TAG
+ //Tag: {"location" : "default"}
+ def json = parseJson(result[i][19])
+ assertEquals("default", json.location)
+
+ assertEquals(result[i][3].toBigInteger(),-1)//BePort: -1
+ assertEquals(result[i][4].toBigInteger(),-1)//HttpPort: -1
+ assertEquals(result[i][5].toBigInteger(),-1)// BrpcPort: -1
+
assertEquals(result[i][6].toBigInteger(),-1)//ArrowFlightSqlPort: -1
+ assertEquals(result[i][7],null)//LastStartTime : NULL
+ assertEquals(result[i][8],null)//LastHeartbeat: NULL
+ assertEquals(result[i][9],"false")// Alive: false
+ assertEquals(result[i][10],"false")//SystemDecommissioned:
false
+ assertEquals(result[i][11].toBigInteger(),0)// TabletNum: 0
+ assertEquals(result[i][12],"0.000 ")// DataUsedCapacity:
0.000
+ assertEquals(result[i][13],"0.000 ")// TrashUsedCapacity:
0.000
+ assertEquals(result[i][14],"1.000 B")// AvailCapacity:
1.000 B
+ assertEquals(result[i][15],"0.000 ") //
TotalCapacity: 0.000
+ assertEquals(result[i][16],"0.00 %") //
UsedPct: 0.00 %
+ assertEquals(result[i][17],"0.00 %") //
MaxDiskUsedPct: 0.00 %
+
+ assertTrue(
result[i][22].contains("lastSuccessReportTabletsTime") )//Status
+ assertTrue(
result[i][22].contains("""isQueryDisabled":false""") )
+ assertTrue(
result[i][22].contains("""isLoadDisabled":false""") )
+ x++
+
+ }
+ }
+ assertTrue(x==1)
+ logger.info("result:${result}")
+
+ sql """ALTER SYSTEM MODIFY BACKEND "${address}:${notExistPort}" SET
("disable_query" = "true"); """
+ sql """ALTER SYSTEM MODIFY BACKEND "${address}:${notExistPort}" SET
("disable_load" = "true"); """
+ result = sql """SHOW BACKENDS;"""
+ x = 0
+ for(int i =0 ;i<result.size();i++) {
+ //HeartbeatPort:
+ if (result[i][2].toBigInteger() == notExistPort) {
+ x ++
+ assertTrue(
result[i][22].contains("lastSuccessReportTabletsTime") )//Status
+ assertTrue(
result[i][22].contains("""isQueryDisabled":true""") )
+ assertTrue( result[i][22].contains("""isLoadDisabled":true""")
)
+ }
+ }
+ assertTrue(x==1)
+ logger.info("result:${result}")
+ sql """ALTER SYSTEM DROPP BACKEND "${address}:${notExistPort}";"""
+
+ result = sql """SHOW BACKENDS;"""
+ logger.info("result:${result}")
+ x = 0
+ for(int i =0 ;i<result.size();i++) {
+ //HeartbeatPort:
+ if (result[i][2].toBigInteger() == notExistPort) {
+ x ++
+ }
+ }
+ assertTrue(x==0)
+
+
+ result = sql """ SHOW FRONTENDS """
+ x = 0
+ for(int i =0 ;i<result.size();i++) {
+ if (result[i][18]=="Yes") {
+ assertTrue(result[i][0]!=null) // name
+ assertTrue(result[i][1]!=null) // Host
+ assertTrue(result[i][2]!=null) // EditLogPort
+ assertTrue(result[i][2].isNumber()) // EditLogPort
+ assertTrue(result[i][3]!=null) // HttpPort
+ assertTrue(result[i][3].isNumber()) // HttpPort
+ assertTrue(result[i][4]!=null) // QueryPort
+ assertTrue(result[i][4].isNumber()) // QueryPort
+
+ assertTrue(result[i][5]!=null) // RpcPort
+ assertTrue(result[i][5].isNumber()) // RpcPort
+ assertTrue(result[i][6]!=null) // ArrowFlightSqlPort
+ assertTrue(result[i][6].isNumber()) // ArrowFlightSqlPort
+ assertTrue(result[i][7] in ["FOLLOWER","OBSERVER"]) // role
+ assertTrue(result[i][8] in ["true","false"]) // isMaster
+ assertTrue(result[i][9]!=null) // ClusterId
+ assertTrue(result[i][9].isNumber()) // ClusterId
+ assertTrue(result[i][10]!=null) // Join
+ assertTrue(result[i][10] in ["true","false"])
+ assertTrue(result[i][11]!=null) //Alive
+ assertTrue(result[i][11] in ["true","false"]) //Alive
+ assertTrue(result[i][17]!=null) //Version
+ x ++
+ }
+ }
+ assertTrue(x==1)
+
+ try{
+ sql """ALTER SYSTEM DROP BROKER test_manager_broker
"${address}:${notExistPort}";"""
+ }catch(Exception e){
+
+ }
+
+ sql """ALTER SYSTEM ADD BROKER test_manager_broker
"${address}:${notExistPort}";"""
+ result = sql """ show broker """
+ x = 0
+ for ( int i =0 ;i<result.size();i++){
+
+ assertTrue(result[i][3] in ["true","false"])//Alive
+
+
+ if (result[i][0]=="test_manager_broker"){
+ x ++
+ assertEquals(result[i][1].toString(),address) //Host
+ assertEquals(result[i][2].toString(),notExistPort.toString())
//Port
+ assertTrue(result[i][3] =="false")//Alive
+
+ assertTrue(result[i][4]==null)//LastStartTime:NULL
+ assertTrue(result[i][5]==null)//LastUpdateTime:NULL
+ }
+ }
+ assertTrue(x==1)
+ sql """ALTER SYSTEM DROP BROKER test_manager_broker
"${address}:${notExistPort}";"""
+
+
+
+ sql """ ALTER TABLE internal.__internal_schema.column_statistics SET
("replication_num" = "1") """
+
+ }
+ test_system()
+
+
+}
diff --git a/regression-test/suites/manager/test_manager_interface_3.groovy
b/regression-test/suites/manager/test_manager_interface_3.groovy
new file mode 100644
index 00000000000..74d5082bf7a
--- /dev/null
+++ b/regression-test/suites/manager/test_manager_interface_3.groovy
@@ -0,0 +1,613 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+import org.apache.doris.regression.suite.ClusterOptions
+import org.apache.doris.regression.util.NodeType
+
+import java.time.LocalDateTime
+import java.time.Duration
+import java.time.format.DateTimeFormatter
+
+suite('test_manager_interface_3',"p0") {
+
+
+ String jdbcUrl = context.config.jdbcUrl
+ def tokens = context.config.jdbcUrl.split('/')
+ jdbcUrl=tokens[0] + "//" + tokens[2] + "/" + "?"
+ String jdbcUser = context.config.jdbcUser
+ String jdbcPassword = context.config.jdbcPassword
+ String s3_endpoint = getS3Endpoint()
+ String bucket = getS3BucketName()
+ String driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/mysql-connector-java-8.0.25.jar";
+//create role $role_name
+//drop role $role_name
+// create user $user_name identified by "$password" default role "$role_name"
+// grant $privileges on $privilege_level to $user_name
+// grant $privileges on $privilege_level to role $role_name
+// grant "$role_name" to "$user_name"
+// revoke $privileges on $privilege_level from "$user_name"
+// revoke $privileges on $privilege_level from role $role_name
+// set password for $user = PASSWORD("$password")
+// revoke "$role_name" from "$user_name"
+// show all grants
+// show grants
+// show roles
+ def test_role_grant = {
+ def user1 = 'test_manager_role_grant_user1'
+ def user2 = 'test_manager_role_grant_user2'
+ def role1 = 'test_manager_role_grant_role1'
+ def pwd = '123456'
+ def new_pwd = 'Ab1234567^'
+ def dbName = 'test_manager_role_grant_db'
+ def dbName2 = 'test_manager_role_grant_db2'
+
+ def tbName = 'test_manager_tb'
+
+ def url=tokens[0] + "//" + tokens[2] + "/" + dbName + "?"
+
+ sql """drop user if exists ${user1}"""
+ sql """drop user if exists ${user2}"""
+ sql """drop role if exists ${role1}"""
+ sql """DROP DATABASE IF EXISTS ${dbName}"""
+ sql """DROP DATABASE IF EXISTS ${dbName2}"""
+
+ sql """CREATE DATABASE ${dbName}"""
+ sql """CREATE DATABASE ${dbName2}"""
+
+ sql """ create table ${dbName}.${tbName}(
+ k1 TINYINT,
+ k2 CHAR(10) COMMENT "string column"
+ ) COMMENT "manager_test_table"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1',
+ "bloom_filter_columns" = "k2"
+ ) """
+
+ sql """ insert into ${dbName}.${tbName} values(1,"abc") """
+
+ sql """CREATE ROLE ${role1}"""
+ sql """grant SELECT_PRIV on ${dbName} TO ROLE '${role1}' """
+
+ sql """CREATE USER '${user1}' IDENTIFIED BY '${pwd}' default role
'${role1}' """
+ sql """CREATE USER '${user2}' IDENTIFIED BY '${pwd}' """
+
+ connect(user=user1, password="${pwd}", url=url) {
+ test {
+ sql """ select 1"""
+ result(
+ [[1]]
+ )
+ }
+ test {
+ sql """ select * from ${dbName}.${tbName} """
+ result(
+ [[1,"abc"]]
+ )
+ }
+ test {
+ sql """ use ${dbName2} """
+ exception "Access denied for user"
+ }
+ test {
+ sql """ Drop table ${dbName}.${tbName} """
+ exception "Access denied; you need (at least one of) the
(DROP) privilege(s) for this operation"
+ }
+ test {
+ sql """ create table test_manager_tb_2 (
+ k1 TINYINT,
+ k2 CHAR(10) COMMENT "string column"
+ ) COMMENT "manager_test_table_2"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1;
+ """
+ exception "Access denied; you need (at least one of) the
(CREATE) privilege(s) for this operation"
+ }
+ test {
+ sql """ insert into test_manager_tb values(1,"2"); """
+ exception """LOAD command denied to user"""
+ }
+ }
+ sql """grant DROP_PRIV on ${dbName} TO ROLE '${role1}' """
+ sql """grant CREATE_PRIV on ${dbName} TO '${user1}' """
+
+ connect(user=user1, password="${pwd}", url=url) {
+
+ sql """ create table test_manager_tb_2 (
+ k1 TINYINT,
+ k2 CHAR(10) COMMENT "string column"
+ ) COMMENT "manager_test_table_2"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1');
+ """
+ sql """ Drop table test_manager_tb_2 """
+ }
+
+ sql """grant LOAD_PRIV on ${dbName} TO '${user2}' """
+ sql """ grant "${role1}" to '${user2}' """
+ connect(user=user2, password="${pwd}", url=url) {
+
+ test {
+ sql """ create table test_manager_tb_2 (
+ k1 TINYINT,
+ k2 CHAR(10) COMMENT "string column"
+ ) COMMENT "manager_test_table_2"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1');
+ """
+ exception "Access denied; you need (at least one of) the
(CREATE) privilege(s) for this operation"
+ }
+ test {
+ sql """ select * from ${dbName}.${tbName} """
+ result(
+ [[1,"abc"]]
+ )
+ }
+
+ sql """ insert into ${tbName} values (2,"adc") """
+ sql """ insert into ${tbName} values (3,"ttt") """
+
+ test {
+ sql """ select * from ${dbName}.${tbName} order by k1"""
+ result(
+ [[1,"abc"],[2,"adc"],[3,"ttt"]]
+ )
+ }
+
+ test {
+ sql """ use ${dbName2} """
+ exception "Access denied for user"
+ }
+ }
+
+ List<List<Object>> result = sql """show all grants """
+ def x = 0
+ for(int i = 0;i < result.size(); i++ ) {
+
+ // Roles: test_manager_role_grant_role1
+ if ( result[i][3] == "${role1}") {
+ //UserIdentity:
+ println result[i][0]
+ if (result[i][0].contains("test_manager_role_grant_user1")){
+ //DatabasePrivs
+ assertTrue(result[i][6] == "internal.information_schema:
Select_priv; internal.mysql: Select_priv; internal.test_manager_role_grant_db:
Select_priv,Create_priv,Drop_priv")
+ x ++
+ }else if
(result[i][0].contains("test_manager_role_grant_user2")) {
+ assertTrue(result[i][6] == "internal.information_schema:
Select_priv; internal.mysql: Select_priv; internal.test_manager_role_grant_db:
Select_priv,Load_priv,Drop_priv")
+ x ++
+
+ }else {
+ assertTrue(false." only ${user1} and ${user2}, no
${result[i][0]}")
+ }
+ }
+ else if ( result[i][3] =="admin"){
+ if (result[i][0] == """'admin'@'%'"""){
+ x++
+ }
+
+ } else if (result[i][3] =="operator") {
+ if (result[i][0] =="""'root'@'%'""" ){
+ x++
+ }
+ }
+ }
+ assertTrue(x == 4)
+
+ sql """ revoke CREATE_PRIV on ${dbName} from '${user1}' """
+ connect(user=user1, password="${pwd}", url=url) {
+ test {
+ sql """ create table test_manager_tb_2 (
+ k1 TINYINT,
+ k2 CHAR(10) COMMENT "string column"
+ ) COMMENT "manager_test_table_2"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1');
+ """
+ exception "Access denied; you need (at least one of) the
(CREATE) privilege(s) for this operation"
+ }
+ }
+
+ sql """ revoke LOAD_PRIV on ${dbName} from '${user2}' """
+ connect(user=user2, password="${pwd}", url=url) {
+ test{
+ sql """ insert into test_manager_tb values(1,"2"); """
+ exception """LOAD command denied to user"""
+ }
+ }
+
+ result = sql """show all grants """
+ x = 0
+ for(int i = 0;i < result.size(); i++ ) {
+
+ // Roles: test_manager_role_grant_role1
+ if ( result[i][3] == "${role1}") {
+ //UserIdentity:
+ println result[i][0]
+ if (result[i][0].contains("test_manager_role_grant_user1")){
+ //DatabasePrivs
+ assertTrue(result[i][6] == "internal.information_schema:
Select_priv; internal.mysql: Select_priv; internal.test_manager_role_grant_db:
Select_priv,Drop_priv")
+ x ++
+ }else if
(result[i][0].contains("test_manager_role_grant_user2")) {
+
assertTrue(result[i][6].contains("internal.information_schema: Select_priv;
internal.mysql: Select_priv; internal.test_manager_role_grant_db:
Select_priv,Drop_priv"))
+ x ++
+
+ }else {
+ assertTrue(false." only ${user1} and ${user2}, no
${result[i][0]}")
+ }
+ }
+ else if ( result[i][3] =="admin"){
+ if (result[i][0] == """'admin'@'%'"""){
+ x++
+ }
+
+ } else if (result[i][3] =="operator") {
+ if (result[i][0] =="""'root'@'%'""" ){
+ x++
+ }
+ }
+ }
+ assertTrue(x == 4)
+
+
+ result = sql """show grants """
+ x = 0
+ for(int i = 0;i < result.size(); i++ ) {
+ if (result[i][3] =="operator") {
+ if (result[i][0] =="""'root'@'%'""" ){
+ if (result[i][6] == "internal.information_schema:
Select_priv; internal.mysql: Select_priv"){
+ assertTrue(result[i][4]=="Node_priv,Admin_priv")
+ x++
+
+ }
+ }
+ }
+ }
+ assertTrue(x == 1)
+
+ result = sql """show roles """
+ x = 0
+ for(int i = 0;i < result.size(); i++ ) {
+ //NAME
+ assertTrue(result[i][0].toLowerCase() != "null")
+
+ if (result[i][0] =="test_manager_role_grant_role1") {
+ //Users
+
assertTrue(result[i][2].contains("test_manager_role_grant_user2'@'%"))
+
assertTrue(result[i][2].contains("test_manager_role_grant_user1'@'%"))
+ x ++
+ }else if (result[i][0] == "admin"){
+ assertTrue(result[i][2].contains("admin'@'%"))
+ x ++
+ }else if (result[i][0] == "operator"){
+ assertTrue(result[i][2].contains("root'@'%"))
+ x++
+ }
+ }
+ assertTrue(x == 3)
+
+
+
+
+ sql """revoke DROP_PRIV on ${dbName} FROM ROLE '${role1}' """
+ sql """create table ${dbName}.test_manager_tb_2 (
+ k1 TINYINT,
+ k2 CHAR(10) COMMENT "string column"
+ ) COMMENT "manager_test_table_2"
+ DISTRIBUTED BY HASH(k1) BUCKETS 1
+ PROPERTIES ('replication_num' = '1');"""
+
+
+ connect(user=user1, password="${pwd}", url=url) {
+ test {
+ sql """ Drop table ${dbName}.test_manager_tb_2"""
+ exception "Access denied; you need (at least one of) the
(DROP) privilege(s) for this operation"
+ }
+ }
+
+ connect(user=user2, password="${pwd}", url=url) {
+ test{
+ sql """ Drop table ${dbName}.test_manager_tb_2"""
+ exception "Access denied; you need (at least one of) the
(DROP) privilege(s) for this operation"
+ }
+ }
+
+ sql """set password for '${user2}' = password('${new_pwd}')"""
+ try {
+ connect(user =user2, password = '${pwd}', url = url) {}
+ assertTrue(false. "should not be able to login")
+ } catch (Exception e) {
+ assertTrue(e.getMessage().contains("Access denied for user"),
e.getMessage())
+ }
+
+ connect(user=user2, password="${new_pwd}", url=url) {
+ result = sql """ select k1 from ${dbName}.${tbName} order by k1
desc limit 1"""
+ assertTrue(result[0][0] == 3)
+
+
+ result = sql """ select * from ${dbName}.${tbName} order by k1"""
+ assertTrue(result[0][0] ==1)
+ assertTrue(result[0][1] =="abc")
+
+ assertTrue(result[1][0] ==2)
+ assertTrue(result[1][1] =="adc")
+
+ assertTrue(result[2][0] ==3)
+ assertTrue(result[2][1] =="ttt")
+ }
+
+
+ sql """ revoke "${role1}" from "${user2}" """
+
+ try {
+ connect(user =user2, password = '${pwd}', url = url) {}
+ assertTrue(false. "should not be able to login")
+ } catch (Exception e) {
+ assertTrue(e.getMessage().contains("Access denied for user"),
e.getMessage())
+ }
+
+ sql """ drop database ${dbName} """
+ sql """ drop database ${dbName2} """
+
+ sql """drop user ${user1}"""
+ sql """drop user ${user2}"""
+ sql """drop role ${role1}"""
+
+ }
+ test_role_grant()
+
+
+
+
+// grant $privileges on resource $resource_name to $user
+// grant $privileges on resource $resource_name to role $role_name
+// revoke $privileges on resource $resource_name from $user_name
+ def test_resource = {
+ def user = 'test_manager_resource_user'
+ def role = 'test_manager_resource_role'
+ def resource_name = "test_manager_resource_case"
+ def pwd = "123456"
+ def url=tokens[0] + "//" + tokens[2] + "/" + "?"
+
+ sql """ drop RESOURCE if exists ${resource_name} """
+ sql """ CREATE RESOURCE ${resource_name} PROPERTIES(
+ "user" = "${jdbcUser}",
+ "type" = "jdbc",
+ "password" = "${jdbcPassword}",
+ "jdbc_url" = "${url}",
+ "driver_url" = "${driver_url}",
+ "driver_class" = "com.mysql.cj.jdbc.Driver"
+ )"""
+
+
+
+ sql """drop user if exists ${user}"""
+ sql """drop role if exists ${role}"""
+
+ sql """CREATE ROLE ${role}"""
+ sql """grant USAGE_PRIV on RESOURCE ${resource_name} TO ROLE
'${role}' """
+
+ sql """CREATE USER '${user}' IDENTIFIED BY '${pwd}' default role
'${role}' """
+
+ List<List<Object>> result = sql """ show resources """
+
+ def x = 0
+ for(int i = 0;i<result.size();i++) {
+ assert(result[i][0].toLowerCase() != "null") //Name
+ if (result[i][0] == resource_name) {
+ x ++
+ }
+ }
+ assertTrue(x == 20)
+
+ connect(user=user, password="${pwd}", url=url) {
+ result = sql """ show resources """
+ x = 0
+ for(int i = 0;i<result.size();i++) {
+ assert(result[i][0].toLowerCase() != "null") //Name
+ if (result[i][0] == resource_name) {
+ x ++
+ }
+ }
+ assertTrue(x == 20)
+ }
+
+
+ result = sql """ show all grants"""
+ x = 0
+ for(int i = 0;i < result.size(); i++ ) {
+
+ if ( result[i][3] == "${role}") {
+ //UserIdentity:
+ if (result[i][0].contains(user)){
+ //DatabasePrivs
+ assertTrue(result[i][9] == "test_manager_resource_case:
Usage_priv")
+ x ++
+ }
+ }
+ }
+ assertTrue(x == 1)
+
+
+ sql """ revoke USAGE_PRIV on RESOURCE ${resource_name} FROM ROLE
'${role}' """
+ connect(user=user, password="${pwd}", url=url) {
+ result = sql """ show resources """
+ x = 0
+ for(int i = 0;i<result.size();i++) {
+ assert(result[i][0].toLowerCase() != "null") //Name
+ if (result[i][0] == resource_name) {
+ x ++
+ }
+ }
+ assertTrue(x == 0)
+ }
+
+ sql """grant USAGE_PRIV on RESOURCE ${resource_name} TO '${user}' """
+ connect(user=user, password="${pwd}", url=url) {
+ result = sql """ show resources """
+ x = 0
+ for(int i = 0;i<result.size();i++) {
+ assert(result[i][0].toLowerCase() != "null") //Name
+ if (result[i][0] == resource_name) {
+ x ++
+ }
+ }
+ assertTrue(x == 20)
+ }
+ sql """ drop RESOURCE if exists ${resource_name} """
+ sql """drop user if exists ${user}"""
+ sql """drop role if exists ${role}"""
+
+ /*
+ mysql> show resources where name = "test_manager_resource_case"\G ;
+ *************************** 1. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: test_connection
+ Value: true
+ *************************** 2. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: driver_class
+ Value: com.mysql.cj.jdbc.Driver
+ *************************** 3. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: connection_pool_max_size
+ Value: 10
+ *************************** 4. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: connection_pool_max_life_time
+ Value: 1800000
+ *************************** 5. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: create_time
+ Value: 2024-06-04 17:35:19.097481994
+ *************************** 6. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: meta_names_mapping
+ Value:
+ *************************** 7. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: only_specified_database
+ Value: false
+ *************************** 8. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: driver_url
+ Value: mysql-connector-java-8.0.25.jar
+ *************************** 9. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: type
+ Value: jdbc
+ *************************** 10. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: connection_pool_min_size
+ Value: 1
+ *************************** 11. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: jdbc_url
+ Value:
jdbc:mysql://127.0.0.1:55557/?&yearIsDateType=false&tinyInt1isBit=false&useUnicode=true&rewriteBatchedStatements=true&characterEncoding=utf-8
+ *************************** 12. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: lower_case_meta_names
+ Value: false
+ *************************** 13. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: connection_pool_max_wait_time
+ Value: 5000
+ *************************** 14. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: password
+ Value:
+ *************************** 15. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: use_meta_cache
+ Value: false
+ *************************** 16. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: exclude_database_list
+ Value:
+ *************************** 17. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: include_database_list
+ Value:
+ *************************** 18. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: checksum
+ Value: fdf55dcef04b09f2eaf42b75e61ccc9a
+ *************************** 19. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: connection_pool_keep_alive
+ Value: false
+ *************************** 20. row ***************************
+ Name: test_manager_resource_case
+ ResourceType: jdbc
+ Item: user
+ Value: root
+ 20 rows in set (0.00 sec)
+ */
+
+ }
+ test_resource()
+
+
+// show property like '%$resource_tag%'
+// show property for $user like '%$resource_tag%'
+// set property for $user 'resource_tags.location' = '$tags'
+ def test_property = {
+ def user = 'test_manager_property_user'
+ def pwd = "123456"
+ def url=tokens[0] + "//" + tokens[2] + "/" + "?"
+
+ sql """drop user if exists ${user}"""
+
+ sql """CREATE USER '${user}' IDENTIFIED BY '${pwd}'"""
+
+ connect(user=user, password="${pwd}", url=url) {
+ List<List<Object>> result = sql """ show property like
"max_query_instances" """
+ assertTrue(result[0][0]=="max_query_instances")
+ assertTrue(result[0][1]=="-1")
+ }
+
+ List<List<Object>> result = sql """ show property for ${user} like
"max_query_instances" """
+ assertTrue(result[0][0]=="max_query_instances")
+ assertTrue(result[0][1]=="-1")
+
+ sql """ set property for ${user} 'max_query_instances' ="100000"; """
+ result = sql """ show property for ${user} like "max_query_instances"
"""
+ assertTrue(result[0][0]=="max_query_instances")
+ assertTrue(result[0][1]=="100000")
+
+ sql """ drop user ${user} """
+ }
+ test_property()
+
+
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
