OB社区版 - lower_case_table_names学习笔记

1. OceanBase集群环境（版本）

测试集群使用1-1-1模式搭建，版本为v3.1.1（社区版）。

root [oceanbase]> show variables like 'version%';
+-------------------------+--------------------------------------------------------------------------------------------+
| Variable_name           | Value                                                                                      |
+-------------------------+--------------------------------------------------------------------------------------------+
| version                 | 3.1.1                                                                                      |
| version_comment         | OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14) |
| version_compile_machine |                                                                                            |
| version_compile_os      |                                                                                            |
+-------------------------+--------------------------------------------------------------------------------------------+
4 rows in set (0.004 sec)

2. 变量的官方解释

关于lower_case_table_names变量的官方解释，参考：https://open.oceanbase.com/docs/community/oceanbase-database/V3.1.1/lower_case_table_names

该参数用于设置是否对大小写敏感。参数为int类型，可取0/1/2三个值，默认取值为1，其中设置变量为：

• 0：表示表名将按指定存储，并且比较区分大小写
• 1：表示表名以小写形式存储在磁盘上，并且比较不区分大小写
• 2：表示表名按指定存储，但以小写形式进行比较

变量的生效范围：GLOBAL（全局生效）

是否允许修改：否，仅在OBServer启动的命令行上指定，启动后不能再通过SQL语句修改。

3. 学习记录

3.1 先创建一个租户

#创建资源规格：
create resource unit unit_config_1 max_cpu=2, min_cpu=2, max_memory='4G', min_memory='4G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='16G';
select * from __all_unit_config;

#使用该unit创建资源池：
create resource pool rsp_mysql unit=unit_config_1, unit_num=1;
select * from __all_resource_pool;

#创建租户：
create tenant tn_mysql charset='utf8mb4', replica_num=1, zone_list=('zone1,zone2,zone3'), primary_zone='zone1,zone2,zone3', resource_pool_list=('rsp_mysql') set ob_tcp_invited_nodes='%'; 
quit

#为租户管理员用户root设置密码
mysql -hocp.test.cn -P8005 -uroot@tn_mysql#obce_cluster_1
set password=password('******');
quit

#测试密码：
mysql -hocp.test.cn -P8005 -uroot@tn_mysql#obce_cluster_1 -p******
select user();

3.2 使用新建的租户管理员root@tn_mysql登录集群，检查下lower_case_table_names参数默认值：

[root@ocp ~]# mysql -hocp.test.cn -P8005 -uroot@tn_mysql#obce_cluster_1 -p******
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.02 sec)

3.3 使用root@tn_mysql登录OB集群，尝试修改该变量的值：

MySQL [(none)]> select user();
+------------------+
| user()           |
+------------------+
| root@172.31.0.69 |
+------------------+
1 row in set (0.01 sec)

MySQL [(none)]> set global lower_case_table_names=0;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable

发现，该变量处于只读模式下，不允许修改。

3.4 使用root@sys登录OB集群，尝试修改租户tn_mysql的该变量：

[root@ocp ~]# obclient -h172.31.0.69 -P8005 -uobce_cluster_1.sys.root -p****** -Doceanbase -A
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> alter tenant tn_mysql set variables lower_case_table_names=0;
Query OK, 0 rows affected (0.068 sec)

MySQL [oceanbase]> quit
Bye

发现，该变量可以在sys租户模式下修改，登录root@tn_mysql用户确认一下：

[root@ocp ~]# mysql -hocp.test.cn -P8005 -uroot@tn_mysql#obce_cluster_1 -p******
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show global variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.02 sec)

发现，该变量值已被修改。

lower_case_table_names变量属于租户初始化变量，不能在业务租户里直接修改。需要在SYS租户里修改。

4. 变量修改的记录

对lower_case_table_names变量的修改，可以在当前租户查看，也可以登录root@sys用户查看：

4.1 sys租户下查看：

MySQL [oceanbase]> select * from __all_virtual_sys_variable where name like 'lower%' and tenant_id = 1001\G;
*************************** 1. row ***************************
   tenant_id: 1001
        zone: 
        name: lower_case_table_names
  gmt_create: 2021-12-20 11:23:52.226319
gmt_modified: 2021-12-20 11:23:52.226319
   data_type: 5
       value: 0
        info: how table database names are stored and compared, 0 means stored using the lettercase in the CREATE_TABLE or CREATE_DATABASE statement. Name comparisons are case sensitive; 1 means that table and database names are stored in lowercase abd name comparisons are not case sensitive.
       flags: 133
     min_val: 0
     max_val: 2
1 row in set (0.020 sec)

4.2 业务租户下查看：

MySQL [oceanbase]> show tables like '%var%';
+-----------------------------+
| Tables_in_oceanbase (%var%) |
+-----------------------------+
| __all_sys_variable          |
| __all_sys_variable_history  |
+-----------------------------+
2 rows in set (0.01 sec)

# 变量的当前生效值：
MySQL [oceanbase]> select * From  __all_sys_variable where name like 'lower%'\G;
*************************** 1. row ***************************
  gmt_create: 2021-12-20 11:23:52.226319
gmt_modified: 2021-12-20 11:23:52.226319
   tenant_id: 0
        zone: 
        name: lower_case_table_names
   data_type: 5
       value: 0
        info: how table database names are stored and compared, 0 means stored using the lettercase in the CREATE_TABLE or CREATE_DATABASE statement. Name comparisons are case sensitive; 1 means that table and database names are stored in lowercase abd name comparisons are not case sensitive.
       flags: 133
     min_val: 0
     max_val: 2
1 row in set (0.00 sec)

# 变量的修改历史：
MySQL [oceanbase]> select * From  __all_sys_variable_history where name like 'lower%'\G;
*************************** 1. row ***************************
    gmt_create: 2021-12-20 10:58:39.304294
  gmt_modified: 2021-12-20 10:58:39.304294
     tenant_id: 0
          zone: 
          name: lower_case_table_names
schema_version: 1639969119162392
    is_deleted: 0
     data_type: 5
         value: 1
          info: how table database names are stored and compared, 0 means stored using the lettercase in the CREATE_TABLE or CREATE_DATABASE statement. Name comparisons are case sensitive; 1 means that table and database names are stored in lowercase abd name comparisons are not case sensitive.
         flags: 133
       min_val: 0
       max_val: 2
*************************** 2. row ***************************
    gmt_create: 2021-12-20 11:23:52.236100
  gmt_modified: 2021-12-20 11:23:52.236100
     tenant_id: 0
          zone: 
          name: lower_case_table_names
schema_version: 1639970632217408
    is_deleted: 0
     data_type: 5
         value: 0
          info: how table database names are stored and compared, 0 means stored using the lettercase in the CREATE_TABLE or CREATE_DATABASE statement. Name comparisons are case sensitive; 1 means that table and database names are stored in lowercase abd name comparisons are not case sensitive.
         flags: 133
       min_val: 0
       max_val: 2
2 rows in set (0.01 sec)

参考资料：https://open.oceanbase.com/blog/8600161