哈喽我们现在测试环境 用的社区版4.3.5.1
SELECT owner, mview_name, container_name, query, refresh_mode
FROM oceanbase.DBA_MVIEWS;
显示没有物化视图
但truncate table sku_gross_margin
truncate table with materialized view log is not supported
1 个赞
执行下删除物化视图日志
DROP MATERIALIZED VIEW LOG ON [database.] table;
dropping a building or dropping index is not supported
show create table sku_gross_margin;
desc sku_gross_margin;
CREATE TABLE sku_gross_margin (
id int(11) NOT NULL AUTO_INCREMENT,
hash char(32) DEFAULT NULL COMMENT ‘hash’,
batchNo varchar(50) DEFAULT NULL COMMENT ‘定时任务批次号’,
operateTime datetime NOT NULL COMMENT ‘操作时间’,
whId int(11) DEFAULT NULL COMMENT ‘仓库Id’,
shId int(11) DEFAULT NULL COMMENT ‘店铺Id’,
orderCreateType varchar(255) DEFAULT NULL COMMENT ‘建单类型:PAY:普通订单，APPEND手工建单’,
type varchar(255) DEFAULT NULL COMMENT ‘单据类型： 销售、售后、退件’,
aftsvrType varchar(255) DEFAULT NULL COMMENT ‘退货处理方式（售后—对应登记处理方式；退件：默认写死退件入库）’,
aftsvrReason varchar(255) DEFAULT NULL COMMENT ‘售后原因’,
aftsvrStockInNo varchar(30) DEFAULT NULL COMMENT ‘退货入库单号’,
b2bOrderType varchar(255) DEFAULT NULL COMMENT ‘B2B订单类型’,
recordNo varchar(50) DEFAULT NULL COMMENT ‘单据号 销售/退件单据：eid 售后：售后单号’,
subRecordNo varchar(200) DEFAULT NULL COMMENT ‘子单据号’,
sourceNo varchar(50) DEFAULT NULL COMMENT ‘来源单号’,
nickName varchar(255) DEFAULT NULL COMMENT ‘买家昵称’,
buyerOpenUid varchar(128) DEFAULT NULL COMMENT ‘平台用户唯一标识’,
specode varchar(255) DEFAULT NULL COMMENT ‘sku编码’,
spuCode varchar(20) DEFAULT NULL COMMENT ‘SPU编码’,
title varchar(255) DEFAULT NULL COMMENT ‘商品名称’,
specname varchar(255) DEFAULT NULL COMMENT ‘规格’,
brandId int(11) DEFAULT NULL COMMENT ‘品牌Id’,
brandName varchar(255) DEFAULT NULL COMMENT ‘品牌’,
categoryLevel1 varchar(255) DEFAULT NULL COMMENT ‘一级类目’,
categoryLevel2 varchar(255) DEFAULT NULL COMMENT ‘二级类目’,
categoryLevel3 varchar(255) DEFAULT NULL COMMENT ‘三级类目’,
origin varchar(255) DEFAULT NULL COMMENT ‘性质’,
newBrandCode varchar(255) DEFAULT NULL,
newBrandName varchar(255) DEFAULT NULL COMMENT ‘新品牌名称’,
departmentId int(11) DEFAULT NULL,
departmentName varchar(255) DEFAULT NULL COMMENT ‘事业部’,
channelName varchar(255) DEFAULT NULL COMMENT ‘渠道’,
channelCode varchar(255) DEFAULT NULL,
currency varchar(10) DEFAULT NULL COMMENT ‘币种’,
shipCost decimal(32,16) DEFAULT NULL COMMENT ‘运费（含税）’,
shipCostExcludingTax decimal(32,16) DEFAULT NULL COMMENT ‘运费（不含税）’,
num decimal(40,16) DEFAULT NULL COMMENT ‘销售/退件数量’,
aftsvrNum decimal(40,16) DEFAULT NULL COMMENT ‘售后数量’,
price decimal(32,16) DEFAULT NULL COMMENT ‘销售/退款单价（含税）’,
totalPrice decimal(32,16) DEFAULT NULL COMMENT ‘销售/退款金额（含税）’,
purCost decimal(32,16) DEFAULT NULL COMMENT ‘单位成本（不含税）’,
totalPurCost decimal(32,16) DEFAULT NULL COMMENT ‘成本金额（不含税）’,
perGrossMargin decimal(32,16) DEFAULT NULL COMMENT ‘单位毛利’,
totalGrossMargin decimal(32,16) DEFAULT NULL COMMENT ‘毛利总额’,
grossMarginRate int(10) DEFAULT NULL COMMENT ‘毛利率’,
remark varchar(500) DEFAULT NULL COMMENT ‘卖家备注’,
itemType varchar(255) DEFAULT NULL COMMENT ‘购买类型’,
payChannel varchar(255) DEFAULT NULL COMMENT ‘支付方式’,
appendReason varchar(255) DEFAULT NULL COMMENT ‘手工建单原因’,
kolId varchar(64) DEFAULT ‘’ COMMENT ‘直播主播id（达人）’,
created datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘记录创建时间’,
confirm_receipt_time datetime DEFAULT NULL COMMENT ‘确认收货时间’,
sign_time datetime DEFAULT NULL COMMENT ‘签收时间’,
plat_subsidy decimal(32,16) DEFAULT ‘0.0000000000000000’ COMMENT ‘平台补贴金额’,
talent_subsidy decimal(32,16) DEFAULT ‘0.0000000000000000’ COMMENT ‘达人承担金额’,
department_group_id int(11) DEFAULT NULL COMMENT ‘部门分组id’,
order_type_id int(11) DEFAULT NULL COMMENT ‘订单类型’,
PRIMARY KEY (id),
UNIQUE KEY HASH_INDEX (hash) BLOCK_SIZE 16384 LOCAL,
KEY idx_whId_operateTime (whId, operateTime) BLOCK_SIZE 16384 LOCAL,
KEY idx_shId_whId_type_operateTime (shId, whId, type, operateTime) BLOCK_SIZE 16384 LOCAL,
KEY batchNo (batchNo) BLOCK_SIZE 16384 LOCAL,
KEY idx_buyerOpenUid (shId, buyerOpenUid) BLOCK_SIZE 16384 LOCAL,
KEY SKU_GROSS_MARGIN_N2 (recordNo, type) BLOCK_SIZE 16384 LOCAL,
KEY INDEX_SOURCENO (sourceNo, shId) BLOCK_SIZE 16384 LOCAL,
KEY operateTime (operateTime) BLOCK_SIZE 16384 LOCAL,
KEY idx_confirm_receipt_time (confirm_receipt_time) BLOCK_SIZE 16384 LOCAL,
KEY idx_sign_time (sign_time) BLOCK_SIZE 16384 LOCAL,
KEY idx_aftsvrStockInNo (aftsvrStockInNo) BLOCK_SIZE 16384 LOCAL,
KEY idx_department_group_id (department_group_id) BLOCK_SIZE 16384 LOCAL,
KEY idx_order_type_id (order_type_id) BLOCK_SIZE 16384 LOCAL
) ORGANIZATION INDEX AUTO_INCREMENT = 89766313 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(all columns, each column)
| id | int(11) | NO | PRI | auto_increment | |
|---|---|---|---|---|---|
| hash | char(32) | YES | UNI | ||
| batchNo | varchar(50) | YES | MUL | ||
| operateTime | datetime | NO | MUL | ||
| whId | int(11) | YES | MUL | ||
| shId | int(11) | YES | MUL | ||
| orderCreateType | varchar(255) | YES | |||
| type | varchar(255) | YES | |||
| aftsvrType | varchar(255) | YES | |||
| aftsvrReason | varchar(255) | YES | |||
| aftsvrStockInNo | varchar(30) | YES | MUL | ||
| b2bOrderType | varchar(255) | YES | |||
| recordNo | varchar(50) | YES | MUL | ||
| subRecordNo | varchar(200) | YES | |||
| sourceNo | varchar(50) | YES | MUL | ||
| nickName | varchar(255) | YES | |||
| buyerOpenUid | varchar(128) | YES | |||
| specode | varchar(255) | YES | |||
| spuCode | varchar(20) | YES | |||
| title | varchar(255) | YES | |||
| specname | varchar(255) | YES | |||
| brandId | int(11) | YES | |||
| brandName | varchar(255) | YES | |||
| categoryLevel1 | varchar(255) | YES | |||
| categoryLevel2 | varchar(255) | YES | |||
| categoryLevel3 | varchar(255) | YES | |||
| origin | varchar(255) | YES | |||
| newBrandCode | varchar(255) | YES | |||
| newBrandName | varchar(255) | YES | |||
| departmentId | int(11) | YES | |||
| departmentName | varchar(255) | YES | |||
| channelName | varchar(255) | YES | |||
| channelCode | varchar(255) | YES | |||
| currency | varchar(10) | YES | |||
| shipCost | decimal(32,16) | YES | |||
| shipCostExcludingTax | decimal(32,16) | YES | |||
| num | decimal(40,16) | YES | |||
| aftsvrNum | decimal(40,16) | YES | |||
| price | decimal(32,16) | YES | |||
| totalPrice | decimal(32,16) | YES | |||
| purCost | decimal(32,16) | YES | |||
| totalPurCost | decimal(32,16) | YES | |||
| perGrossMargin | decimal(32,16) | YES | |||
| totalGrossMargin | decimal(32,16) | YES | |||
| grossMarginRate | int(10) | YES | |||
| remark | varchar(500) | YES | |||
| itemType | varchar(255) | YES | |||
| payChannel | varchar(255) | YES | |||
| appendReason | varchar(255) | YES | |||
| kolId | varchar(64) | YES | |||
| created | datetime | YES | ON UPDATE CURRENT_TIMESTAMP | ||
| confirm_receipt_time | datetime | YES | MUL | ||
| sign_time | datetime | YES | MUL | ||
| plat_subsidy | decimal(32,16) | YES | 0.0000000000000000 | ||
| talent_subsidy | decimal(32,16) | YES | 0.0000000000000000 | ||
| department_group_id | int(11) | YES | MUL | ||
| order_type_id | int(11) | YES | MUL |
SELECT * FROM oceanbase.DBA_MVIEW_LOGS WHERE MASTER = ‘sku_gross_margin’;
查询基表table id
SELECT tenant_id, table_id, table_name, database_id
FROM oceanbase.__all_virtual_table
WHERE table_name = ‘sku_gross_margin’;
看下是否真的有卡住的DDL
SELECT task_id, object_id, target_object_id, ddl_type, task_status, ret_code, trace_id, gmt_create
FROM oceanbase.__all_virtual_ddl_task_status
WHERE tenant_id = {TENANT_ID}
AND object_id = {TABLE_ID} ;
加上ddl type字段查询试试
SELECT * FROM oceanbase.__all_virtual_ddl_task_status
WHERE tenant_id = 1004 AND object_id = 513426 AND ddl_type IN (8, 9);
然后oceanbase也重启试过。也不行。 是不是底层什么的卡住了
把所有DDL类型都查一下试试
SELECT * FROM oceanbase.__all_virtual_ddl_task_status
WHERE tenant_id = 1004 AND object_id = 513426 AND ddl_type IN (1, 6, 8, 9, 10);
系统租户下select task_id, object_id, target_object_id, ddl_type, trace_id, status, message from __all_virtual_ddl_task_status
这边改了权限 你现在应该可以在论坛回复了
解决了吗，这个问题比较棘手
这是一个很值得探讨的话题