【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】v4.3.5.0
【问题描述】insert into … select … 语句无法使用PDML和旁路导入，报错 ERROR 1235 (0A000): PDML is disabled, direct load is not supported
【复现路径】问题出现前后相关操作
- 部署集群，选用 OLAP 场景创建租户
obd cluster autodeploy obtest -c ob.yaml
obd cluster tenant create obtest -n test --max-cpu=28 --memory-size=98G --zone-list=zone1,zone2,zone3 --primary-zone=RANDOM \
--locality=F@zone1,F@zone2,F@zone3 --charset=utf8 -s 'ob_tcp_invited_nodes="%"' --optimize=olap
- 调整配置参数
set global parallel_servers_target = 560; -- MIN_CPU * 20;
ALTER SYSTEM SET default_table_store_format = 'row';
SET GLOBAL parallel_degree_policy = MANUAL;
- 用 sysbench 准备3个表数据，每个表1000万行
create table tmp1 like sbtest1;- explain Insert into select 语句，无论有没有使用 hint 均无法执行
mysql> EXPLAIN EXTENDED insert /*+ enable_parallel_dml DIRECT(true, 0, 'full') parallel(16) */ into tmp1 select * from sbtest1;
ERROR 1235 (0A000): PDML is disabled, direct load is not supported
mysql> EXPLAIN EXTENDED insert /*+ enable_parallel_dml parallel(16) */ into tmp1 select * from sbtest1;
ERROR 1235 (0A000): PDML is disabled, direct load is not supported
mysql> EXPLAIN EXTENDED insert into tmp1 select * from sbtest1;
ERROR 1235 (0A000): PDML is disabled, direct load is not supported
- 手工设置参数，依然无效
SET _FORCE_PARALLEL_DML_DOP = 16; - 手工改表的并行度，依然无效
mysql> ALTER TABLE tmp1 PARALLEL 16;
Query OK, 0 rows affected (0.72 sec)
mysql> show create table tmp1
-> ;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tmp1 | CREATE TABLE `tmp1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARALLEL 16 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> ALTER TABLE sbtest1 PARALLEL 16;
Query OK, 0 rows affected (0.12 sec)
mysql> EXPLAIN EXTENDED insert into tmp1 select * from sbtest1;
ERROR 1235 (0A000): PDML is disabled, direct load is not supported
用hint 执行 select 语句是可以使用上并行执行的
mysql> explain extended select /*+ ENABLE_PARALLEL_DML PARALLEL(3) */ * from sbtest1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ========================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------- |
| |0 |PX COORDINATOR | |14246719|57799748 | |
| |1 |+-EXCHANGE OUT DISTR |:EX10000|14246719|25167643 | | | |2 | +-PX BLOCK ITERATOR| |14246719|691938 | |
| |3 | +-TABLE FULL SCAN|sbtest1 |14246719|691938 | |
| ========================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(sbtest1.id(0x7f2ad601fa50), sbtest1.k(0x7f2ad601fea0), sbtest1.c(0x7f2ad60202f0), sbtest1.pad(0x7f2ad6020740))(0x7f2ad60e6810)]), filter(nil), rowset=256 |
| 1 - output([INTERNAL_FUNCTION(sbtest1.id(0x7f2ad601fa50), sbtest1.k(0x7f2ad601fea0), sbtest1.c(0x7f2ad60202f0), sbtest1.pad(0x7f2ad6020740))(0x7f2ad60e6810)]), filter(nil), rowset=256 |
| dop=3 |
| 2 - output([sbtest1.id(0x7f2ad601fa50)], [sbtest1.k(0x7f2ad601fea0)], [sbtest1.c(0x7f2ad60202f0)], [sbtest1.pad(0x7f2ad6020740)]), filter(nil), rowset=256 |
| 3 - output([sbtest1.id(0x7f2ad601fa50)], [sbtest1.k(0x7f2ad601fea0)], [sbtest1.c(0x7f2ad60202f0)], [sbtest1.pad(0x7f2ad6020740)]), filter(nil), rowset=256 | | access([sbtest1.id(0x7f2ad601fa50)], [sbtest1.k(0x7f2ad601fea0)], [sbtest1.c(0x7f2ad60202f0)], [sbtest1.pad(0x7f2ad6020740)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([sbtest1.id(0x7f2ad601fa50)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| PARALLEL(3) |
| ENABLE_PARALLEL_DML |
| */ |
| Qb name trace: |
| -------------------------------------
| Qb name trace: | [116/605]
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| PARALLEL(@"SEL$1" "sbtest"."sbtest1"@"SEL$1" 3) |
| FULL(@"SEL$1" "sbtest"."sbtest1"@"SEL$1") |
| PARALLEL(3) |
| OPTIMIZER_FEATURES_ENABLE('4.3.5.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| sbtest1: |
| table_rows:6111888 |
| physical_range_rows:14246719 |
| logical_range_rows:14246719 |
| index_back_rows:0 |
| output_rows:14246719 |
| table_dop:3 |
| dop_method:Global DOP |
| avaiable_index_name:[k_1, sbtest1] |
| pruned_index_name:[k_1] |
| stats info:[version=2025-01-20 17:22:24.528913, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[OPTIMIZER STATISTICS, STORAGE] |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelism is 3 because of hint |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 58 rows in set (0.01 sec)
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：
mysql> show create table sbtest1;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 10000001 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！
