Resolving Column Length Expansion Errors in OceanBase: Charset and Collation Considerations

1. Background

When attempting to increase the length of a column, the error "ERROR 1235 (0A000): Alter charset or collation type not supported" is returned, indicating that the operation is not supported for the current charset or collation. The database version is OceanBase 3.2.3.0.

2. Reproduction Steps

2.1 Create Table

create table user_info (
  id bigint not null auto_increment primary key COMMENT '自增主键',
  user_name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
  address varchar(200) COMMENT '地址',
  create_time datetime default current_timestamp COMMENT '创建时间',
  update_time datetime default current_timestamp on update current_timestamp COMMENT '更新时间'
) default charset utf8mb4;

2.2 Modify Table Structure

alter table user_info modify user_name varchar(50);
ERROR 1235 (0A000): Alter charset or collation type not supported

3. Analysis

3.1 Check Default Charset and Collation

show variables like '%character%';
show variables like '%collation%';

3.2 Check Table and Column Charset/Collation

SELECT TABLE_NAME, CCSA.character_set_name AS charset, CCSA.collation_name AS collation
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY CCSA
  ON T.table_collation = CCSA.collation_name
WHERE T.table_name = 'user_info';

SELECT COLUMN_NAME, CHARACTER_SET_NAME AS charset, COLLATION_NAME AS collation
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'user_info' AND TABLE_SCHEMA = 'mydb';

3.3 Root Cause

During column expansion, the column inherits the table's collation (utf8mb4_general_ci) because the column definition does not explicitly specify a collation. OceanBase 3.2.3 does not support online modification of charset or collation, leading to the error.

3.4 Solution

Specify the desired charset and collation in the ALTER statement.

alter table user_info modify user_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名';

4. Verification on OceanBase 4.2.1

In version 4.2.1 the same ALTER succeeds, but the column's collation changes from utf8mb4_bin to utf8mb4_general_ci, which is an online DDL operation.

-- Check database version
select @@version;
-- Modify column length
alter table user_info modify user_name varchar(50);
Query OK, 0 rows affected (1.706 sec);
-- Verify change
SELECT COLUMN_NAME, CHARACTER_SET_NAME AS charset, COLLATION_NAME AS collation
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'user_info' AND TABLE_SCHEMA = 'mydb';

+-----------+---------+--------------------+
| user_name | utf8mb4 | utf8mb4_general_ci |
+-----------+---------+--------------------+

Changing the collation to a case‑insensitive one (utf8mb4_general_ci) may affect data consistency because it ignores case differences.

5. Summary

When expanding a column, ensure that the original charset and collation are explicitly declared in the ALTER statement to avoid unexpected changes and errors across different OceanBase versions.