报错 table and tablegroup use different partition options not allowed

问题描述

OceanBase 数据库中将表添加到表组时，报错信息为 table and tablegroup use different partition options not allowed，复现问题现象如下。

1. 查看数据库支持的字符序。

   obclient [(none)]> SHOW VARIABLES LIKE '%coll%';
   

   输出结果如下。

   +----------------------+--------------------+
   | Variable_name        | Value              |
   +----------------------+--------------------+
   | collation_connection | utf8mb4_general_ci |
   | collation_database   | utf8mb4_general_ci |
   | collation_server     | utf8mb4_general_ci |
   +----------------------+--------------------+
   3 rows in set (0.002 sec)
   

2. 创建 hwtest 数据库。

   obclient [(none)]> CREATE DATABASE hwtest DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_bin;
   Query OK, 1 row affected (0.024 sec)
   

3. 使用创建的数据库。

   obclient [(none)]> USE hwtest;
   Database changed
   

4. 查看创建的数据库支持的字符序。

   obclient [hwtest]> SHOW VARIABLES LIKE '%coll%';
   

   输出结果如下。

   +----------------------+--------------------+
   | Variable_name        | Value              |
   +----------------------+--------------------+
   | collation_connection | utf8mb4_general_ci |
   | collation_database   | utf8mb4_bin        |
   | collation_server     | utf8mb4_general_ci |
   +----------------------+--------------------+
   3 rows in set (0.002 sec)
   

5. 创建表组。

   obclient [hwtest]> CREATE TABLEGROUP tgl_l PARTITION PARTITION BY HIST PARTITION BY LIST COLUMN 1
   (
   PARTITION p0 VALUES values in ('1','2'),
   PARTITION p1 values in ('3','4'),
   PARTITION p2 values in ('5','6'));
   Query OK, 0 rows affected (0.033 sec)
   

6. 创建表 tl-lc。

   obclient [hwtest]> CREATE TABLE tl_lc (id VARCHAR(20) NOT NULL,name VARCHAR(50),t_date DATE NOT NULL)
   PARTITION BY LIST COLUMNS(id)
   (PARTITION p0 VALUES IN ('1','2'),
   PARTITION p1 VALUES IN ('3','4'),
   PARTITION p2 VALUES IN ('5','6'));
   Query OK, 0 rows affected (0.046 sec)
   

7. 表添加至表组中。

   obclient [hwtest]> ALTER TABLEGROUP tgl_lc ADD TABLE tl_lc;
   

   输出结果如下。

   ERROR 4179 (HY000): table and tablegroup use different partition options not allowed
   

8. 查看建表语句。

   obclient [hwtest]> SHOW CREATE TABLE tl_lc;
   

   输出结果如下。

   +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
   +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | tl_lc | CREATE TABLE `tl_lc` (
   `id` varchar(20) COLLATE utf8mb4_bin NOT NULL,
   `name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
   `t_date` date NOT NULL
   ) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by list columns(id)
   (partition p0 values in ('1','2'),
   partition p1 values in ('3','4'),
   partition p2 values in ('5','6')) |
   +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.003 sec)
   

适用版本

OceanBase 数据库 V2.x 和 V3.x 版本。

问题原因

创建 table 使用的是 database 的字符集，创建 table group 使用的是当前会话的字符集，两者的字符序不同。

解决方法

使用相同的字符序。

规避方式

将当前会话的字符集设置为 utf8mb4，排序规则为 utf8mb4_general_ci，命令如下。

obclient> SET NAMES 'utf8mb4' COLLATE 'utf8mb4_bin';