Doris2.1.11异步物化视图的问题 - Doris
系统版本是Doris2.1.11出现一个比较令人疑惑的问题：
创建的异步物化视图不能删除：
先看创建DDL如下：
mysql> SHOW CREATE MATERIALIZED VIEW DW.dws_contract_contractpaymentsummary_view1;
+-------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Materialized View | Create Materialized View |
+-------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dws_contract_contractpaymentsummary_view1 | CREATE MATERIALIZED VIEW dws_contract_contractpaymentsummary_view1
(ContractCode,
Year
,
Month
,Amount)
BUILD IMMEDIATE REFRESH AUTO ON COMMIT
DUPLICATE KEY(`ContractCode`)
DISTRIBUTED BY RANDOM BUCKETS 5
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
)
AS select
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`ContractCode`,
year
(
ifnull(
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`ActualPaymentDate`,
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`PerPaymentDate`
)
) `
Year
`,
month
(
ifnull(
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`ActualPaymentDate`,
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`PerPaymentDate`
)
) `
Month
`,
SUM
(
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`Amount`
) `Amount`
from
`internal`.`DW`.`fact_contract_b_paymentinfo`
where
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`PayStateCode` = 3
GROUP BY
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`ContractCode`,
year
(
ifnull(
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`ActualPaymentDate`,
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`PerPaymentDate`
)
),
month
(
ifnull(
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`ActualPaymentDate`,
`internal`.`DW`.`fact_Contract_B_PaymentInfo`.`PerPaymentDate`
)
) |
+-------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)查询DW表中视图：
mysql> SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_TYPE,CREATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA='DW' AND TABLE_NAME LIKE '%contractpaymentsummary%';
+---------------+--------------+-------------------------------------------+--------+------------+---------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | ENGINE | TABLE_TYPE | CREATE_TIME |
+---------------+--------------+-------------------------------------------+--------+------------+---------------------+
| internal | DW | dws_contract_contractpaymentsummary_view | NULL | BASE TABLE | 2025-11-24 09:57:22 |
| internal | DW | dws_contract_contractpaymentsummary_view1 | NULL | BASE TABLE | 2025-11-21 17:29:48 |
+---------------+--------------+-------------------------------------------+--------+------------+---------------------+
2 rows in set (0.04 sec)删除这个物化视图报错：
mysql> DROP MATERIALIZED VIEW dws_contract_contractpaymentsummary_view1;
ERROR 1105 (HY000): errCode = 2, detailMessage = Syntax error in line 1:
...ntractpaymentsummary_view1
^
Encountered: EOF
Expected
-- 按标准语法删除
USE DW;
mysql> DROP MATERIALIZED VIEW dws_contract_contractpaymentsummary_view1 ON fact_Contract_B_PaymentInfo;
ERROR 1105 (HY000): errCode = 7, detailMessage = Materialized view [dws_contract_contractpaymentsummary_view1] does not exist in table [fact_Contract_B_PaymentInfo]用下面的语法删除可以执行，但是异步物化视图还存在：
mysql> DROP MATERIALIZED VIEW IF EXISTS dws_contract_contractpaymentsummary_view1 ON fact_Contract_B_PaymentInfo;
Query OK, 0 rows affected (0.04 sec)
mysql> desc dws_contract_contractpaymentsummary_view1;
+--------------+---------------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-------+---------+-------+
| ContractCode | varchar(36) | No | true | NULL | |
|
Year
| smallint | Yes | false | NULL | NONE |
|
Month
| tinyint | Yes | false | NULL | NONE |
| Amount | decimal(38,4) | Yes | false | NULL | NONE |
+--------------+---------------+------+-------+---------+-------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM dws_contract_contractpaymentsummary_view1 LIMIT 1;
+--------------------------------------+----------+-----------+-------------+
| ContractCode |
Year
|
Month
| Amount |
+--------------------------------------+----------+-----------+-------------+
| 000bcc49-45ab-466f-bd46-0fbcf3e52e01 | 2016 | 10 | 178486.0000 |
+--------------------------------------+----------+-----------+-------------+
1 row in set (0.02 sec)但是相同的表和异步物化视图在另一个集群doris-2.1.8-1中是可以创建和删除的:
同样的创建异步物化视图在不能正常删除的集群上亦不能创建成功，都是报错：
mysql> CREATE MATERIALIZED VIEW mv_1_0
-> BUILD IMMEDIATE
-> REFRESH AUTO
-> ON MANUAL
-> DISTRIBUTED BY RANDOM BUCKETS 2
-> AS
-> SELECT
-> l_linestatus,
-> to_date(o_orderdate) as date_alias,
-> o_shippriority
-> FROM
-> orders
-> LEFT JOIN lineitem ON l_orderkey = o_orderkey;
ERROR 1105 (HY000): errCode = 2, detailMessage = Syntax error in line 2:
BUILD IMMEDIATE
^
Encountered: BUILD
Expected
mysql> CREATE MATERIALIZED VIEW mv_1_1
-> BUILD DEFERRED
-> REFRESH COMPLETE
-> ON SCHEDULE EVERY 1 DAY STARTS '2026-12-30 20:30:00'
-> DISTRIBUTED BY RANDOM BUCKETS 2
-> PROPERTIES ('replication_num' = '1')
-> AS
-> SELECT
-> l_linestatus,
-> to_date(o_orderdate) as date_alias,
-> o_shippriority
-> FROM
-> orders
-> LEFT JOIN lineitem ON l_orderkey = o_orderkey;
ERROR 1105 (HY000): errCode = 2, detailMessage = Syntax error in line 2:
BUILD DEFERRED
^
Encountered: BUILD
Expected也就是有问题的这个集群不能正常创建异步物化视图也不能删除,暂时没定位到原因，先记录下·
附删除物化视图语法：
mysql> HELP DROP MATERIALIZED VIEW;
Name: 'DROP MATERIALIZED VIEW'
Description:该语句用于删除物化视图。同步语法
语法：
DROP MATERIALIZED VIEW [IF EXISTS] mv_name ON table_name;IF EXISTS:
如果物化视图不存在，不要抛出错误。如果不声明此关键字，物化视图不存在则报错。mv_name:
待删除的物化视图的名称。必填项。table_name:
待删除的物化视图所属的表名。必填项。Examples:
表结构为
mysql> desc all_type_table all;
+----------------+-------+----------+------+-------+---------+-------+
| IndexName | Field | Type | Null | Key | Default | Extra |
+----------------+-------+----------+------+-------+---------+-------+
| all_type_table | k1 | TINYINT | Yes | true | N/A | |
| | k2 | SMALLINT | Yes | false | N/A | NONE |
| | k3 | INT | Yes | false | N/A | NONE |
| | k4 | BIGINT | Yes | false | N/A | NONE |
| | k5 | LARGEINT | Yes | false | N/A | NONE |
| | k6 | FLOAT | Yes | false | N/A | NONE |
| | k7 | DOUBLE | Yes | false | N/A | NONE |
| | | | | | | |
| k1_sumk2 | k1 | TINYINT | Yes | true | N/A | |
| | k2 | SMALLINT | Yes | false | N/A | SUM |
+----------------+-------+----------+------+-------+---------+-------+1. 删除表 all_type_table 的名为 k1_sumk2 的物化视图
drop materialized view k1_sumk2 on all_type_table;物化视图被删除后的表结构
+----------------+-------+----------+------+-------+---------+-------+
| IndexName | Field | Type | Null | Key | Default | Extra |
+----------------+-------+----------+------+-------+---------+-------+
| all_type_table | k1 | TINYINT | Yes | true | N/A | |
| | k2 | SMALLINT | Yes | false | N/A | NONE |
| | k3 | INT | Yes | false | N/A | NONE |
| | k4 | BIGINT | Yes | false | N/A | NONE |
| | k5 | LARGEINT | Yes | false | N/A | NONE |
| | k6 | FLOAT | Yes | false | N/A | NONE |
| | k7 | DOUBLE | Yes | false | N/A | NONE |
+----------------+-------+----------+------+-------+---------+-------+2. 删除表 all_type_table 中一个不存在的物化视图
drop materialized view k1_k2 on all_type_table;
ERROR 1064 (HY000): errCode = 2, detailMessage = Materialized view [k1_k2] does not exist in table [all_type_table]
删除请求直接报错3. 删除表 all_type_table 中的物化视图 k1_k2，不存在不报错。
drop materialized view if exists k1_k2 on all_type_table;
Query OK, 0 rows affected (0.00 sec)
存在则删除，不存在则不报错。
Tag标签:「视图 doris 物化视图 异步」更新时间:「2025-12-31 17:22:04」阅读次数:「15」