目录
4、SHOW CREATE PROCEDURE （存储过程信息）
13、SHOW GCLUSTER NODES（调度节点信息）
一、环境信息
| 名称 | 值 |
| CPU | Intel(R) Core(TM) i5-1035G1 CPU @ 1.00GHz |
| 操作系统 | CentOS Linux release 7.9.2009 (Core) |
| 内存 | 3G |
| 逻辑核数 | 2 |
| Gbase8a版本 | 8.6.2-R43.34.27468a27 |
二、show功能参数
这些参数不一定所有show命令都支持的，注意哦。
| 参数名 | 描述 |
| full | 显示更加完整的信息。 |
| like | 模糊匹配。 |
| where | 过滤性更强，支持对返回结果进行过滤，就像我们平时用SQL一样。 |
三、show命令示例
1、SHOW LOAD LOGS（加载日志报错）
测试数据
[gbase@czg2 ~]$ cat TestData_2023_09_14.txt
sun
czg建表语句
gbase> CREATE TABLE CZG.TEST_2023_09_14 (DATA CHAR(1));
Query OK, 0 rows affected (Elapsed: 00:00:00.65)加载错误
gbase> LOAD DATA INFILE 'file://192.168.142.12///home/gbase/TestData_2023_09_14.txt' into table CZG.TEST_2023_09_14 DATA_FORMAT 3 FIELDS TERMINATED BY '|'
-> MAX_BAD_RECORDS 0;
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: Task 8650775 failed, [192.168.142.12:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: (GBA-01-600) Gbase internal error: Task 8650775, Too many bad records!
SQL: LOAD /*+ TID('19333279') */ DATA INFILE '///home/gbase/TestData_2023_09_14.txt' INTO TABLE `czg`.`test_2023_09_14_n1` DATA_FORMAT 3 FILE_FORMAT UNDEFINED FIELDS TERMINATED BY '|' MAX_BAD_RECORDS 0 HOST '::ffff:192.168.142.12' CURRENT_TIMESTAMP 1694679966 SCN_NUMBER 8650775 GCLUSTER_PORT 5258 INTO SERVER (H
通过show load logs查看报错信息。
gbase> show load logs 8650775 \G;
*************************** 1. row ***************************
task_id: 8650775
db_name: czg
tb_name: test_2023_09_14
err_data_ip: ::ffff:192.168.142.12
file_name: ///home/gbase/TestData_2023_09_14.txt
file_offset: 0
record_len: 4
err_column: 1
err_reason: Data truncated
err_data: sun
*************************** 2. row ***************************
task_id: 8650775
db_name: czg
tb_name: test_2023_09_14
err_data_ip: ::ffff:192.168.142.12
file_name: ///home/gbase/TestData_2023_09_14.txt
file_offset: 4
record_len: 4
err_column: 1
err_reason: Data truncated
err_data: czg
2 rows in set (Elapsed: 00:00:00.00)
ERROR:
No query specified2、SHOW COLUMNS（列信息）
语法树
SHOW [FULL] COLUMNS FROM table_name [FROM database_name] [LIKE 'pattern']例子
gbase> show columns from testtab from czg;
+-------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-------------------+-----------------------------+
| a | int(11) | YES | | NULL | |
| b | double | YES | | NULL | |
| c | varchar(100) | YES | MUL | NULL | |
| d | text | YES | | NULL | |
| e | blob | YES | | NULL | |
| f | longblob | YES | | NULL | |
| g | date | YES | | NULL | |
| h | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+--------------+------+-----+-------------------+-----------------------------+
8 rows in set (Elapsed: 00:00:00.00)
gbase> show full columns from testtab from czg;
+-------+--------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+-----------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+--------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+-----------+
| a | int(11) | NULL | YES | | NULL | | select,insert,update,references | 总金额 |
| b | double | NULL | YES | | NULL | | select,insert,update,references | |
| c | varchar(100) | utf8_general_ci | YES | MUL | NULL | | select,insert,update,references | |
| d | text | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| e | blob | NULL | YES | | NULL | | select,insert,update,references | |
| f | longblob | NULL | YES | | NULL | | select,insert,update,references | |
| g | date | NULL | YES | | NULL | | select,insert,update,references | |
| h | timestamp | NULL | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | select,insert,update,references | |
+-------+--------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+-----------+
8 rows in set (Elapsed: 00:00:00.00)
gbase> show full columns from testtab from czg where Field = 'a';
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+-----------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+-----------+
| a | int(11) | NULL | YES | | NULL | | select,insert,update,references | 总金额 |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+-----------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show full columns from testtab from czg like 'b';
+-------+--------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+--------+-----------+------+-----+---------+-------+---------------------------------+---------+
| b | double | NULL | YES | | NULL | | select,insert,update,references | |
+-------+--------+-----------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (Elapsed: 00:00:00.00)
3、SHOW CREATE DATABASE（建库信息）
语法树
SHOW CREATE {DATABASE | SCHEMA} database_name;
例子
gbase> SHOW CREATE DATABASE CZG;
+----------+--------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------+
| czg | CREATE DATABASE "czg" DEFAULT CHARACTER SET utf8 |
+----------+--------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> SHOW CREATE SCHEMA CZG;
+----------+--------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------+
| czg | CREATE DATABASE "czg" DEFAULT CHARACTER SET utf8 |
+----------+--------------------------------------------------+
1 row in set (Elapsed: 00:00:00.31)4、SHOW CREATE PROCEDURE （存储过程信息）
语法树
SHOW CREATE PROCEDURE [database_name.]proc_name;
例子
gbase> SHOW CREATE PROCEDURE czg.GenerateTestData;
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| GenerateTestData | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH | CREATE DEFINER="root"@"%" PROCEDURE "GenerateTestData"(num int)
begin
declare tempval int;
set tempval = 1;
set autocommit = off;
label: loop
insert into czg values(tempval);
if tempval >= num then
leave label;
else
set tempval = tempval + 1;
end if;
end loop label;
commit;
end | utf8 | utf8_general_ci | utf8_general_ci |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (Elapsed: 00:00:00.16)
5、SHOW CREATE TABLE （建表信息）
语法树
SHOW CREATE TABLE [database_name.]table_name;例子
gbase> show create table czg.czg;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------+
| czg | CREATE TABLE "czg" (
"a" int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.30)
gbase> show full create table czg.czg;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| czg | CREATE TABLE "czg" (
"a" int(11) DEFAULT NULL
) ENGINE=EXPRESS TID(24577) UID(1) DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' COLUMN_IDS(0) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.13)6、SHOW TABLE LOCKS （表锁信息）
测试版本没模拟出来，后面再摸索一下。
gbase> LOCK TABLE CZG.TESTTAB WRITE;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> desc CZG.TESTTAB;
+-------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-------------------+-----------------------------+
| a | int(11) | YES | | NULL | |
| b | double | YES | | NULL | |
| c | varchar(100) | YES | MUL | NULL | |
| d | text | YES | | NULL | |
| e | blob | YES | | NULL | |
| f | longblob | YES | | NULL | |
| g | date | YES | | NULL | |
| h | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+--------------+------+-----+-------------------+-----------------------------+
8 rows in set (Elapsed: 00:00:00.10)
gbase> INSERT INTO CZG.TESTTAB(A) VALUES(1);
Query OK, 1 row affected (Elapsed: 00:00:00.74)
gbase> SHOW TABLE LOCKS CZG.TESTTAB;
Empty set (Elapsed: 00:00:00.00)7、SHOW ERRORS （上一条命令错误信息）
例子
gbase> set global autocommit = 0;
ERROR 1228 (HY000): Variable 'autocommit' is a SESSION variable and can't be used with SET GLOBAL
gbase> show errors;
+-------+------+-------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------+
| Error | 1228 | Variable 'autocommit' is a SESSION variable and can't be used with SET GLOBAL |
+-------+------+-------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)8、SHOW STATUS （连接的状态信息）
语法树
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern'];
GLOBAL参数可以获取所有连接集群的信息，SESSION 只能获得当前连接的信息。
默认值为 SESSION。LOCAL 和 SESSION 意义相同。
注意一些状态变量只有全局值，这样无论使用 GLOBAL 还是 SESSION， 都只能得到相同的值。
例子
gbase> SHOW STATUS LIKE 'Meminfo%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Meminfo_cache_access_times | 0 |
| Meminfo_cache_hit_rate_% | 0.000000 |
| Meminfo_cache_hit_times | 0 |
| Meminfo_cache_miss_times | 0 |
| Meminfo_cache_removed_list_size | 0 |
| Meminfo_dc_heap_availble_size | 536870912 |
| Meminfo_dc_heap_malloc_size | 0 |
| Meminfo_dc_heap_total_size | 536870912 |
| Meminfo_dc_heap_used_size | 0 |
| Meminfo_large_heap_availble_size | 268435456 |
| Meminfo_large_heap_malloc_size | 0 |
| Meminfo_large_heap_total_size | 268435456 |
| Meminfo_large_heap_used_size | 0 |
| Meminfo_temp_heap_availble_size | 268430064 |
| Meminfo_temp_heap_malloc_size | 0 |
| Meminfo_temp_heap_total_size | 268435456 |
| Meminfo_temp_heap_used_size | 5392 |
+----------------------------------+-----------+
17 rows in set (Elapsed: 00:00:00.00)
gbase> SHOW GLOBAL STATUS LIKE 'Meminfo%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Meminfo_cache_access_times | 0 |
| Meminfo_cache_hit_rate_% | 0.000000 |
| Meminfo_cache_hit_times | 0 |
| Meminfo_cache_miss_times | 0 |
| Meminfo_cache_removed_list_size | 0 |
| Meminfo_dc_heap_availble_size | 536870912 |
| Meminfo_dc_heap_malloc_size | 0 |
| Meminfo_dc_heap_total_size | 536870912 |
| Meminfo_dc_heap_used_size | 0 |
| Meminfo_large_heap_availble_size | 268435456 |
| Meminfo_large_heap_malloc_size | 0 |
| Meminfo_large_heap_total_size | 268435456 |
| Meminfo_large_heap_used_size | 0 |
| Meminfo_temp_heap_availble_size | 268430064 |
| Meminfo_temp_heap_malloc_size | 0 |
| Meminfo_temp_heap_total_size | 268435456 |
| Meminfo_temp_heap_used_size | 5392 |
+----------------------------------+-----------+
17 rows in set (Elapsed: 00:00:00.00)9、SHOW VARIABLES （参数信息）
语法树
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern'];
GLOBAL参数可以获取所有集群连接的参数变量值，SESSION 只能获得当前连接的参数变量值。
例子
gbase> SHOW VARIABLES LIKE '%COMMIT%';
+-----------------------------------------------------------+----------+
| Variable_name | Value |
+-----------------------------------------------------------+----------+
| _t_gcluster_commit_revert_flag_test | 1 |
| autocommit | ON |
| gbase_tcmalloc_aggressive_decommit_threadhold_load_factor | 0.900000 |
| gbase_tcmalloc_balanced_decommit_threadhold_load_factor | 0.600000 |
| gcluster_kafka_batch_commit_dml_count | 100000 |
| gcluster_kafka_parallel_commit | 1 |
+-----------------------------------------------------------+----------+
6 rows in set (Elapsed: 00:00:00.00)10、SHOW GRANTS （权限信息）
语法树
SHOW GRANTS FOR user_name;
例子
gbase> SHOW GRANTS FOR czg;
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for czg@% |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'czg'@'%' IDENTIFIED BY PASSWORD '*D75CC763C5551A420D28A227AC294FADE26A2FF2' WITH TASK_PRIORITY 2 |
| GRANT USAGE ON "czg".* TO 'czg'@'%' WITH GRANT OPTION |
| GRANT SELECT ON "czg"."test" TO 'czg'@'%' |
+----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (Elapsed: 00:00:00.02)11、SHOW INDEX （索引信息）
语法树
SHOW INDEX FROM table_name [FROM database_name];例子
gbase> show index from czg from czg;
Empty set (Elapsed: 00:00:00.00)12、SHOW ENGINES （存储引擎信息）
例子
gbase> show engines;
+-----------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+-----------+---------+-----------------------------------------------------------+--------------+------+------------+
| MRG_GSSYS | YES | Collection of identical GsSYS tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| EXPRESS | DEFAULT | Express storage engine | YES | YES | NO |
| GsSYS | YES | GsSYS engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+-----------+---------+-----------------------------------------------------------+--------------+------+------------+
5 rows in set (Elapsed: 00:00:00.32)13、SHOW GCLUSTER NODES（调度节点信息）
例子
gbase> SHOW GCLUSTER NODES ;
+-----------+----------------+--------------+--------+-----------+
| Id | ip | name | status | datastate |
+-----------+----------------+--------------+--------+-----------+
| 210675904 | 192.168.142.12 | coordinator1 | online | 0 |
+-----------+----------------+--------------+--------+-----------+
1 row in set (Elapsed: 00:00:00.00)14、SHOW NODES（数据节点信息）
例子
gbase> SHOW NODES ;
+-----------+----------------+-------+--------------+----------------+--------+-----------+
| Id | ip | name | primary part | duplicate part | status | datastate |
+-----------+----------------+-------+--------------+----------------+--------+-----------+
| 210675904 | 192.168.142.12 | node1 | n1 | | online | 0 |
+-----------+----------------+-------+--------------+----------------+--------+-----------+
1 row in set (Elapsed: 00:00:00.00)