今天遇到一个 OceanBase 里 help 功能无法使用的问题,这篇博客简单记录了一下分析的过程和解决的方法。
背景
今天遇到有用户在问答区问到了一个问题:OceanBase 的 help 功能怎么用不了?我之前也从来没在 OB 里用过 help 这个功能,试了一下,果然如用户所说。而且用不了的时候提示也很“有意思”,先让执行 help contents 看看,然后提示 Please check if 'help tables' are loaded。
obclient [(none)]> help
General information about OceanBase can be found at
https://www.oceanbase.com
List of all client commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
conn (\) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to OceanBase server, display result vertically.
exit (\q) Exit mysql. Same as quit.
For server side help, type 'help contents'
obclient [(none)]> HELP SELECT
Nothing found
Please try to run 'help contents' for a list of all accessible topics
obclient [(none)]> help contents
Nothing found
Please check if 'help tables' are loaded.
这时候用户和我都以为要再执行 help tables 看看(实际并不是这个意思),执行 help tables 之后,又让再去执行 help contents,然后就死循环了……
obclient [(none)]> help tables
Nothing found
Please try to run 'help contents' for a list of all accessible topics
我不禁开始怀疑,OB 是不是偷懒没实现 help 这个功能?
排查
翻了眼代码,发现是有这个功能的,实现也超级简单,从几张名字叫 help_xxx 的内部表里查一把 keyword 对应的 description 就 OK 了。
查了一把,发现这几张表里啥关键字也没有,都是空的。
obclient [oceanbase]> select * from mysql.help_category;
Empty set (0.002 sec)
obclient [oceanbase]> select * from mysql.help_keyword;
Empty set (0.002 sec)
obclient [oceanbase]> select * from mysql.help_topic;
Empty set (0.002 sec)
解决
回想起刚才的 Please check if 'help tables' are loaded,猜测这句话的意思应该是需要让用户去手动执行一些操作,往这些 help 内部表里 load 进一批数据,才能正常使用 OB 的 help 功能。然后在 OB 官网查到了下面的内容:
根据提示 source ./src/sql/fill_help_tables-ob.sql 之后,help 功能就可以正常使用了。
[admin@obvos-dev-d3 /home/xiaofeng.lby/work/obn/code_master]
$find . -name fill*sql
./src/sql/fill_help_tables-ob.sql
[admin@obvos-dev-d3 /home/xiaofeng.lby/work/obn/code_master]
$obclient -h 11.124.9.58 -u root@sys -P2881 -Doceanbase -A
obclient> source ./src/sql/fill_help_tables-ob.sql
obclient> help contents;
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Data Types
Functions
Operator
Escape character
Data Definition
Data Manipulation
Transaction Statements
Prepared Statements
Compound Statements
Administration
Utility
obclient [oceanbase]> help 'Data Types';
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
categories:
Numeric Types
Date and Time Types
String Types
Bool Types
obclient [oceanbase]> help 'Numeric Types';
You asked for help about help category: "Numeric Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
TINYINT
BOOL,BOOLEAN
SMALLINT
MEDIUMINT
INT
INTEGER
BIGINT
FLOAT
DOUBLE
DOUBLE PRECISION
FLOAT(p)
DECIMAL
NUMERIC
obclient [oceanbase]> help 'DECIMAL';
Name: 'DECIMAL'
Description:
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
压缩的“严格”定点数。M是小数位数(精度)的总数,D是小数点(标度)后面的位数。小数点和(负数)的‘-’符号不包括在M中。
如果D是0,则值没有小数点或分数部分。DECIMAL整数最大位数(M)为90。支持的十进制数的最大位数(D)是45。如果D被省略, 默认是0。如果M被省略, 默认是10。
如果指定UNSIGNED,不允许负值。
所有DECIMAL列的基本计算(+,-,*,/)用65位精度完成。
DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]是DECIMAL的同义词。FIXED同义词适用于与其它服务器的兼容性。
其他
这里想到了一个好玩儿的东西,既然需要用户自己往 help tables 里加载数据,那是不是可以通过执行一些 insert 语句定制自己的 help 功能?
试了一下,可以。
help tables 这几张表的表结构长这样:
obclient [oceanbase]> desc mysql.help_category;
+--------------------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+----------------+------+-----+---------+-------+
| help_category_id | bigint(20) | NO | PRI | NULL | |
| name | varchar(64) | NO | | NULL | |
| parent_category_id | bigint(20) | YES | | NULL | |
| url | varchar(65535) | NO | | NULL | |
+--------------------+----------------+------+-----+---------+-------+
4 rows in set (0.041 sec)
obclient [oceanbase]> desc mysql.help_keyword;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| help_keyword_id | bigint(20) | NO | PRI | NULL | |
| name | varchar(64) | NO | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
2 rows in set (0.041 sec)
obclient [oceanbase]> desc mysql.help_topic;
+------------------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------+------+-----+---------+-------+
| help_topic_id | bigint(20) | NO | PRI | NULL | |
| name | varchar(64) | NO | | NULL | |
| help_category_id | bigint(20) | NO | | NULL | |
| description | varchar(65535) | NO | | NULL | |
| example | varchar(65535) | NO | | NULL | |
| url | varchar(65535) | NO | | NULL | |
+------------------+----------------+------+-----+---------+-------+
6 rows in set (0.041 sec)
我往 help tables 里 insert 了一个词条叫 ZLATAN IBRAHIMOVIC,通过执行 help 'ZLATAN IBRAHIMOVIC' 就可以看到这个兹拉坦的个人介绍了。
by the way:大家如果不都像我这么无聊的话,没必要去改动 help tables 里的数据,万一改错了或改坏了就得不偿失了~
TODO
截至 2023.09.26 为止,OB 中 help 功能的易用性暂时还是要比 MySQL 差一些。
希望后面 OB 可以做到和 MySQL 一样,在 MySQL 模式租户创建的时候就自动去 load 一下这些 help tables 的数据,让 help 这个功能更易用~