删除表数据报错

OceanBase 技术问题
#1

DROP TABLE IF EXISTS `qrtz_job_details`; CREATE TABLE `qrtz_job_details` ( `SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `JOB_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `JOB_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `DESCRIPTION` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `JOB_CLASS_NAME` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `IS_DURABLE` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `IS_NONCONCURRENT` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `IS_UPDATE_DATA` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `REQUESTS_RECOVERY` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `JOB_DATA` blob NULL, PRIMARY KEY (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) USING BTREE, INDEX `IDX_QRTZ_J_REQ_RECOVERY`(`SCHED_NAME`, `REQUESTS_RECOVERY`) USING BTREE, INDEX `IDX_QRTZ_J_GRP`(`SCHED_NAME`, `JOB_GROUP`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci; DROP TABLE IF EXISTS `qrtz_triggers`; CREATE TABLE `qrtz_triggers` ( `SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `TRIGGER_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `TRIGGER_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `JOB_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `JOB_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `DESCRIPTION` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `NEXT_FIRE_TIME` bigint(13) NULL DEFAULT NULL, `PREV_FIRE_TIME` bigint(13) NULL DEFAULT NULL, `PRIORITY` int(11) NULL DEFAULT NULL, `TRIGGER_STATE` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `TRIGGER_TYPE` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `START_TIME` bigint(13) NOT NULL, `END_TIME` bigint(13) NULL DEFAULT NULL, `CALENDAR_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `MISFIRE_INSTR` smallint(2) NULL DEFAULT NULL, `JOB_DATA` blob NULL, PRIMARY KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) USING BTREE, INDEX `IDX_QRTZ_T_J`(`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) USING BTREE, CONSTRAINT `qrtz_triggers_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) REFERENCES `qrtz_job_details` (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci; qrtz_triggers表无数据、删除qrtz_job_details表中数据会报错1235 -uncertain plan violating external consistency not supported

#2

是系统租户吗？如果是在sys租户下的话会有这个报错，OceanBase sys租户不支持依赖GTS的分布式事务，因此无法支持对多分区表的读写操作。可参考这个：OceanBase 社区版


#3

SYS租户下面的test账户

#4

请创建一个业务租户，在业务租户里用。


sys租户是集群自己用的。

#5 
MySQL [oceanbase]> select t1.name resourcepoolname, t2.name unitconfigname, t2.maxcpu, t2.mincpu, round(t2.maxmemory/1024/1024/1024) maxmemgb, round(t2.minmemory/1024/1024/1024) minmemgb, t3.unitid, t3.zone, concat(t3.svrip,':',t3.svrport) observer,t4.tenantid, t4.tenantname from allresourcepool t1 join allunitconfig t2 on (t1.unitconfigid=t2.unitconfigid) join allunit t3 on (t1.resourcepoolid = t3.resourcepoolid) left join alltenant t4 on (t1.tenantid=t4.tenantid) order by t1.resourcepoolid, t2.unitconfigid , t3.unitid;
+--------------------+------------------+---------+---------+------------+------------+---------+-------+----------------+-----------+-------------+
| resourcepoolname | unitconfigname | maxcpu | mincpu | maxmemgb | minmemgb | unitid | zone | observer    | tenantid | tenant_name |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+----------------+-----------+-------------+
| syspool      | sysunit_config |    5 |    5 |     1 |     1 |    1 | zone1 | 127.0.0.1:2882 |     1 | sys     |
| test        | test       |    9 |    9 |     3 |     3 |  1001 | zone1 | 127.0.0.1:2882 |   1001 | test    |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+----------------+-----------+-------------+
2 rows in set (0.012 sec)

这里面的test不属于业务租户么


#7

从这个报错来看，可能是由于可能是由于没有开启 GTS。

执行这个命令查看一下是否开启 GTS
MySQL [test]> SHOW VARIABLES LIKE 'ob_timestamp_service';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| ob_timestamp_service | GTS   |
+----------------------+-------+
1 row in set (0.01 sec)

如果没有开启的话，可以执行一下语句开启
MySQL [test]> SET GLOBAL ob_timestamp_service='GTS';
Query OK, 0 rows affected (0.01 sec)


#8

那我最上面有外键删除数据报错就不是这个问题sys租户问题？


#9 
MySQL [oceanbase]> SHOW VARIABLES LIKE 'obtimestampservice';
+----------------------+-------+
| Variable_name    | Value |
+----------------------+-------+
| obtimestampservice | LTS  |
+----------------------+-------+
1 row in set (0.007 sec)

MySQL [oceanbase]> SET GLOBAL obtimestampservice='GTS';
ERROR 1231 (42000): Variable 'obtimestampservice' can't be set to the value of 'GTS'


#10

你连接的是sys租户还是test租户呢，连接串可以发一下？

#11

大概率原因是你现在连接的租户还是sys租户，sys租户下的这个值是不能更改的。

#13

这个test租户也是业务租户，但你现在连接的租户不是业务租户，可以参考这个查看连接方式：

OceanBase 企业级分布式关系数据库

@符号后面跟的是租户名。

查看租户可以直接在sys租户下查询表__all_tenant

select * from __all_tenant;
#14

找到问题了、代码里面确实是连接了sys