Collate 不一致导致添加表组报错

适用版本

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

问题现象

给表添加表组报错。

table and tablegroup use different partition options not allowed

问题原因

错误示例如下。

1. 查询 OceanBase 数据库版本。

   MySQL [(none)]> select @@version;
   +---------------------------+
   | @@version                 |
   +---------------------------+
   | 5.7.25-OceanBase-v3.2.3.3 |
   +---------------------------+
   1 row in set (0.002 sec)
   

2. 查询租户的字符集和排序规则。

   MySQL [(none)]> select * from information_schema.schemata ;
   +--------------+--------------------+----------------------------+------------------------+----------+
   | CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
   +--------------+--------------------+----------------------------+------------------------+----------+
   | def          | oceanbase          | utf8mb4                    | utf8mb4_general_ci     |     NULL |
   | def          | information_schema | utf8mb4                    | utf8mb4_general_ci     |     NULL |
   | def          | mysql              | utf8mb4                    | utf8mb4_general_ci     |     NULL |
   | def          | __public           | utf8mb4                    | utf8mb4_general_ci     |     NULL |
   | def          | test               | utf8mb4                    | utf8mb4_general_ci     |     NULL |
   | def          | node               | utf8mb4                    | utf8mb4_general_ci     |     NULL |
   | def          | comm               | utf8mb4                    | utf8mb4_general_ci     |     NULL |
   +--------------+--------------------+----------------------------+------------------------+----------+
   7 rows in set (0.023 sec)
   

3. 创建两张表，collate 分别不一样。

   MySQL [(none)]> use test
   Database changed
   CREATE TABLE t1 (
       partition_id varchar(2),
       id int(11) 
       ) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin 
       partition by list columns(partition_id)
       (partition p0 values in ('00','01'),
       partition p1 values in ('02','03'),
       partition p2 values in (DEFAULT));
   Query OK, 0 rows affected (0.067 sec)
   
   CREATE TABLE t2 (
       partition_id varchar(2),
       id int(11) 
       ) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci
       partition by list columns(partition_id)
       (partition p0 values in ('00','01'),
       partition p1 values in ('02','03'),
       partition p2 values in (DEFAULT));
   

4. 创建表组，使用租户默认的 collate。

   CREATE TABLEGROUP IF NOT EXISTS tg1 
       partition by list columns 1
       (partition p0 values in ('00','01'),
       partition p1 values in ('02','03'),
       partition p2 values in (DEFAULT));
   Query OK, 0 rows affected (0.027 sec)
   

5. 给表 t1 添加表组报错。

   MySQL [test]> alter TABLEGROUP tg1 add t1;
   ERROR 4179 (HY000): table and tablegroup use different partition options not allowed
   
   MySQL [test]> alter TABLEGROUP tg1 add t2;
   Query OK, 0 rows affected (0.029 sec)
   

具体原因：当表的 collate 与租户的 collate 不一致时，就会报这个错误。

解决方法

临时解决：设置会话级别的 collate 变量，使之与表的 collate 相同，之后再次创建表组完了添加表。

1. 设置会话级别的 collate 变量。

   SET NAMES 'utf8mb4' COLLATE 'utf8mb4_bin'
   

2. 创建表组。

   CREATE TABLEGROUP IF NOT EXISTS tg2 partition by list columns 1 (partition p0 values in ('00','01'), partition p1 values in ('02','03'), partition p2 values in (DEFAULT)); 
   

3. 将表添加到表组。

   alter TABLEGROUP tg2 add t1;