ERROR 1235 (0A000): uncertain plan

培训中心 实战分享
#1

【产品名称】 OceanBase

【产品版本】Server version: 5.7.25 OceanBase 3.1.0 (r-) (Built May 30 2021 11:21:29)

【问题描述】

MySQL [test002]> delete t1, t2 from t1, t2 where t1.c1 = t2.c1;

ERROR 1235 (0A000): uncertain plan violating external consistency not supported

MySQL [test002]> delete from t1 order by c1 limit 1;

ERROR 1235 (0A000): uncertain plan violating external consistency not supported

MySQL [test002]> show create table t1\G;

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int(11) NOT NULL,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`)

) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = COMPACT COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0

partition by key(c1)

(partition p0,

partition p1,

partition p2,

partition p3)

1 row in set (0.003 sec)

-----------------------------------------------------------------

MySQL [test002]> show create table t2\G;

*************************** 1. row ***************************

Table: t2

Create Table: CREATE TABLE `t2` (

`c1` int(11) NOT NULL,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`)

) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = COMPACT COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0

partition by key(c1)

(partition p0,

partition p1,

partition p2,

partition p3)

--------------------------------------------------------

#2

我猜测你应该是在OceanBase的sys租户中执行了上述的分区表delete操作，我在sys租户模式中复现了你的问题：

OceanBase(root@test)>CREATE TABLE `t1` (
  -> 
  -> `c1` int(11) NOT NULL,
  -> 
  -> `c2` int(11) DEFAULT NULL,
  -> 
  -> PRIMARY KEY (`c1`)
  -> 
  -> ) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = COMPACT COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  -> 
  -> partition by key(c1)
  -> 
  -> (partition p0,
  -> 
  -> partition p1,
  -> 
  -> partition p2,
  -> 
  -> partition p3);
Query OK, 0 rows affected (0.09 sec)

OceanBase(root@test)>CREATE TABLE `t2` (
  -> 
  -> `c1` int(11) NOT NULL,
  -> 
  -> `c2` int(11) DEFAULT NULL,
  -> 
  -> PRIMARY KEY (`c1`)
  -> 
  -> ) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = COMPACT COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  -> 
  -> partition by key(c1)
  -> 
  -> (partition p0,
  -> 
  -> partition p1,
  -> 
  -> partition p2,
  -> 
  -> partition p3);
Query OK, 0 rows affected (0.06 sec)

OceanBase(root@test)>delete t1, t2 from t1, t2 where t1.c1 = t2.c1;
ERROR 1235 (0A000): uncertain plan violating external consistency not supported

OceanBase sys租户不支持依赖GTS的分布式事务，因此无法支持对多分区表的读写操作，

sys租户是OceanBase系统用来做一些内部状态监控和运维的租户，拥有很高的权限，建议不要在这个租户下做功能性测试，可以单独创建一个普通租户，连接到普通租户下执行你的case

如果你是通过OB  deployer启动的系统，会默认为你创建一个普通的MySQL租户，可以在这个租户下完成你刚才的测试：

在系统租户下查看你的普通租户：

#3

https://open.oceanbase.com/docs/community/oceanbase-database/V3.1.0/create-a-user-tenant


please refer to this document to show how to create a user tenant.