南大通用GBase 8a自增列不允许insert报错auto_increment column should not be specified in insert

发表于 作者 laozizhu

GBase 8a从V95版本开始支持自增列，同时也限制该列被手工insert,update等操作，否则会报错 auto-increment column should not be specified in insert。虽然有参数临时放开这个限制，但还是不建议作为常规操作。

参考

GBase 8a 在V95版本对表自增列auto_increment的支持样例

报错样例

insert values报错

gbase> insert into t_inc values(1,'First');
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: auto-increment column should not be specified in insert(...)

insert select报错

gbase> insert into t_inc select * from t_inc;
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: auto-increment column should not be specified in insert select

update报错

gbase> update t_inc set id=1 where id=1;
ERROR 1235 (42000): This version of GBase doesn't yet support 'update/merge on auto_increment column'

原因

GBase 8a的自增列，不支持insert,update等操作，除了设置为default,0,null外。 

gbase> show create table t3_inc;
| Table  | Create Table                                                                                                                                                                                  |
| t3_inc | CREATE TABLE "t3_inc" (
  "id" bigint(20) NOT NULL AUTO_INCREMENT,
  "name" varchar(100) DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
1 row in set (Elapsed: 00:00:00.00)

gbase> insert into t3_inc  values(0,'9999');
Query OK, 1 row affected (Elapsed: 00:00:00.10)

gbase> select * from t3_inc;
+----+----------+
| id | name     |
+----+----------+
|  1 | t3_first |
|  2 | t3_first |
|  3 | t3_first |
|  6 | 333      |
|  9 | 444      |
| 10 | t3_first |
| 11 | 9999     |
+----+----------+
7 rows in set (Elapsed: 00:00:00.01)


gbase> insert into t3_inc  values(null,'9999');
Query OK, 1 row affected (Elapsed: 00:00:00.10)

gbase> select * from t3_inc;
+----+----------+
| id | name     |
+----+----------+
|  1 | t3_first |
|  2 | t3_first |
|  3 | t3_first |
|  6 | 333      |
|  9 | 444      |
| 10 | t3_first |
| 11 | 9999     |
| 12 | 9999     |
+----+----------+
8 rows in set (Elapsed: 00:00:00.01)

gbase> insert into t3_inc  values(default,'9999');
Query OK, 1 row affected (Elapsed: 00:00:00.10)

gbase> select * from t3_inc;
+----+----------+
| id | name     |
+----+----------+
|  1 | t3_first |
|  2 | t3_first |
|  3 | t3_first |
|  6 | 333      |
|  9 | 444      |
| 10 | t3_first |
| 11 | 9999     |
| 12 | 9999     |
| 13 | 9999     |
+----+----------+
9 rows in set (Elapsed: 00:00:00.01)

解决方案

避免更新自增列

既然选择了自增列，那么就不要指定该列的值，也就自动避免了【重复】。在insert update操作时不要对自增列做主动修改即可。

gbase> select * from t_inc;
+----+-------+
| id | name  |
+----+-------+
|  1 | First |
+----+-------+
1 row in set (Elapsed: 00:00:00.01)


gbase> insert into t_inc (name) values ('Second');
Query OK, 1 row affected (Elapsed: 00:00:00.11)

gbase> select * from t_inc;
+----+--------+
| id | name   |
+----+--------+
|  1 | First  |
|  2 | Second |
+----+--------+
2 rows in set (Elapsed: 00:00:00.01)

临时允许参数

当表初始化时，也是有可能要按照预设的值插入数据的，此时可以通过一个参数临时打开这个限制。

_gbase_auto_increment_allow_insert

该参数默认为0，不允许insert

gbase> show variables like '%increment%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| _gbase_auto_increment_allow_insert | OFF   |
| _gbase_auto_increment_base         | 0     |
| auto_increment_increment           | 1     |
| auto_increment_offset              | 1     |
| div_precision_increment            | 4     |
+------------------------------------+-------+
5 rows in set (Elapsed: 00:00:00.00)

通过set 语句设置该参数，支持session级。

gbase> set _gbase_auto_increment_allow_insert=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> show variables like '%increment%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| _gbase_auto_increment_allow_insert | ON    |
| _gbase_auto_increment_base         | 0     |
| auto_increment_increment           | 1     |
| auto_increment_offset              | 1     |
| div_precision_increment            | 4     |
+------------------------------------+-------+
5 rows in set (Elapsed: 00:00:00.00)

之后就可以对该表做insert了，但注意，不再保证自增列数据的唯一性。请用户自行避免自增列的数值重复。

gbase> select * from t_inc;
+----+--------+
| id | name   |
+----+--------+
|  1 | First  |
|  2 | Second |
|  3 | Second |
+----+--------+
3 rows in set (Elapsed: 00:00:00.02)

gbase> set _gbase_auto_increment_allow_insert=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> insert into t_inc values (1,'First2');
Query OK, 1 row affected (Elapsed: 00:00:00.10)

gbase> select * from t_inc;
+----+--------+
| id | name   |
+----+--------+
|  1 | First  |
|  2 | Second |
|  3 | Second |
|  1 | First2 |
+----+--------+
4 rows in set (Elapsed: 00:00:00.01)

如果恢复参数，则下一个自增列的值，会自动计算。如下手工insert到了id=100，关闭参数后，自增的列会保证下一个数值比当前最大的更大，确保自增。

gbase> insert into t_inc values (100,'First100');
Query OK, 1 row affected (Elapsed: 00:00:00.10)

gbase> select * from t_inc;
+-----+----------+
| id  | name     |
+-----+----------+
|   1 | First    |
|   2 | Second   |
|   3 | Second   |
|   1 | First2   |
| 100 | First100 |
+-----+----------+
5 rows in set (Elapsed: 00:00:00.01)

gbase> set _gbase_auto_increment_allow_insert=0;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> insert into t_inc (name) values ('101test');
Query OK, 1 row affected (Elapsed: 00:00:00.10)

gbase> select * from t_inc;
+-----+----------+
| id  | name     |
+-----+----------+
|   1 | First    |
|   2 | Second   |
|   3 | Second   |
|   1 | First2   |
| 100 | First100 |
| 101 | 101test  |
+-----+----------+
6 rows in set (Elapsed: 00:00:00.00)
Post Views: 1,012

相关文章:

  1. 南大通用GBase 8a集群打补丁时需要关闭文件检查功能的参数skip_file_check
  2. 南大通用GBase 8a集群运行报错BLK_TEMP: return NULL in alloc
  3. 南大通用GBase 8a集群常见报错ERROR [CLM ] port_scanning error sockfd:81 time:1(ms) cfg_connect_timeout:5000(ms) error
  4. 南大通用GBase 8a集群查询报错The query includes syntax that is not supported by the Express engine
  5. 南大通用GBase 8a集群V95新安装注意事项
  6. 南大通用GBase 8a V95版本，操作系统盘损坏的快速恢复
  7. 南大通用GBase 8a递归查询Start with connect by 报错 Restrict: Connect by clause must be used with table not deleted
  8. 南大通用GBase 8a V95客户端gccli连接报错No VC selected
  9. 南大通用GBase 8a 9.5.3多实例版本的服务状态注册和检测机制
  10. 南大通用GBase 8a 多实例cluster_common.cnf配置文件介绍