GBase 8a报错This version of GBase doesn’t yet support ‘ALTER column definition except increasing length of varchar

GBase 8a数据库集群不支持改动字段的定义，除了可以增加varchar的长度，其它的操作都会报如下的错误：ERROR 1702 (HY000): gcluster table error: This version of GBase doesn’t yet support ‘ALTER column definition except increasing length of varchar’.

原因

当前版本不支持除了varchar增加长度外的改动字段定义的操作。当然注释是可以随时修改的。

解决方案

新建列，将数据update过去，再将列重命名。

varchar类型增加长度

请一定保留原有的附加属性，包括not null, default 等。否则change时会报错。单独修改注释等，请用modify功能。

gbase> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> alter table t2 change name  name varchar(30);
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.98)
Records: 0  Duplicates: 0  Warnings: 0

gbase> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> 

其它类型变动

其它类型只有先建一个新的字段，然后把数据update过去，然后把老的删除，把新的change成老的字段名。

如下例子，把value bigint, 改成 value int.

gbase> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  | MUL | NULL    |       |
| value | bigint(20) | YES  |     | NULL    |       |
| birth | datetime   | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> alter table t1 add column value2 int after value;
Query OK, 4 rows affected (Elapsed: 00:00:00.65)
Records: 4  Duplicates: 4  Warnings: 0

gbase> desc t1;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id     | int(11)    | YES  | MUL | NULL    |       |
| value  | bigint(20) | YES  |     | NULL    |       |
| value2 | int(11)    | YES  |     | NULL    |       |
| birth  | datetime   | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
4 rows in set (Elapsed: 00:00:00.00)

gbase> update t1 set value2=value;
Query OK, 4 rows affected (Elapsed: 00:00:00.28)
Rows matched: 4  Changed: 4  Warnings: 0

gbase> alter table t1 drop value;
Query OK, 4 rows affected (Elapsed: 00:00:00.42)
Records: 4  Duplicates: 4  Warnings: 0

gbase> desc t1;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | YES  | MUL | NULL    |       |
| value2 | int(11)  | YES  |     | NULL    |       |
| birth  | datetime | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> alter table t1 change value2 value int;
Query OK, 0 rows affected (Elapsed: 00:00:00.21)
Records: 0  Duplicates: 0  Warnings: 0

gbase> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | MUL | NULL    |       |
| value | int(11)  | YES  |     | NULL    |       |
| birth | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.01)