vertica-复杂sql导致crc校验错误

来源：互联网发布：2016炒股软件用户数编辑：程序博客网时间：2024/05/10 13:54

sql如下

create table tmp.Web_Rel_WebVIDUserID_All

(

select nextval('tmp.tmp_lvjian_seq') as EID,

e.UserID,

e.WebVID,

min(e.CalDate) as FirstDate,

max(e.CalDate) as LastDate,

min(e.FVisitTime) as FVisitTime,

max(e.LVisitTime) as LVisitTime,

Count(Distinct(e.IP)) as IPNum,

Count(Distinct(e.LogURL)) as LogUrlNum,

Count(Distinct(e.LogURL || e.LogUrlParam)) as LogUrlParamNum,

Count(Distinct(e.LogDomainNo)) as LogDomainNoNum,

Count(Distinct(e.ReferURL)) as ReferUrlNum,

Count(Distinct(e.ReferURL || e.ReferUrlParam)) as ReferUrlParamNum,

Count(Distinct(e.ReferDomainNo)) as ReferDomainNoNum,

Sum(e.PVNum) as PVNum,

Sum(e.PVNum * e.IsLoginVisit) as LoginPVNum,

SysDate() as EUTime

from tmp.Web_Tmp_UrlDetail e

where e.UserID is not null

and e.UserID <> '1'

group by e.UserID,

e.WebVID

);

报错如下

我们先检查一下CRC，两种方法检查

第一：

当数据库是down状态的时候

dbadmin@bd:/data1/EmBig/v_embig_node0001_catalog$/opt/vertica/bin/vertica -D /data1/EmBig/v_embig_node0001_catalog -v

于是在，/data1/EmBig/v_embig_node0001_catalog路径下，会生成，indextool.log日志，这个日志记录了操作的输出信息

第二：

当数据库是开启状态时

dbadmin=> select run_index_tool ('checkcrc', 'true');

run_index_tool

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

Index tool run.

(1 row)

他的写入信息将会记录在，当前节点的 vertica.log里面

这里模拟数据库是开启状态

当有CRC错误时候，vertica.log会记录以下信息

这里我截取其中4各部分查看

1，

2016-05-19 03:53:20.948 EEcmdq:0x7f5064a8b650 <LOG> @v_embigdata3_node0013: 00000/2940: CRC Check Failure Details:

File Name: /data1/EmBigData3/v_embigdata3_node0013_data/611/99079191814645611/99079191814645611_0.fdb

File Offset: 7129520078

Compressed size in file: 16035

Memory Address of Read Buffer: (nil)

Pointer to Compressed Data: 0x7f4cef8a2929

2，

2016-05-19 04:42:19.470 EEcmdq:0x7f50656ee590 <LOG> @v_embigdata3_node0013: 00000/2940: CRC Check Failure Details:

File Name: /data1/EmBigData3/v_embigdata3_node0013_data/611/99079191814645611/99079191814645611_0.fdb

File Offset: 7129520078

Compressed size in file: 16035

Memory Address of Read Buffer: (nil)

Pointer to Compressed Data: 0x7f4aa36a4309

3，

2016-05-19 11:55:03.169 nameless:0x7f4d30e68420 [EE] <INFO> Data corruption found when running index tool thread on oid 99079191814645611 projection Web_Tmp_UrlDetail_b1:

CRC Check Failure Details:

File Name: /data1/EmBigData3/v_embigdata3_node0013_data/611/99079191814645611/99079191814645611_0.fdb

File Offset: 7129520078

Compressed size in file: 16035

Memory Address of Read Buffer: 0x7f5c36afe000

Pointer to Compressed Data: 0x7f5c36b967ce

4，

2016-05-19 11:57:48.190 nameless:0x7f4d30e68420 [EE] <INFO> Data corruption found when running index tool thread on oid 99079191814645643 projection Web_Tmp_UrlDetail_b1:

CRC Check Failure Details:

File Name: /data1/EmBigData3/v_embigdata3_node0013_data/643/99079191814645643/99079191814645643_0.fdb

File Offset: 1043064623

Compressed size in file: 8202

Memory Address of Read Buffer: 0x7f5c36afe000

Pointer to Compressed Data: 0x7f5c36b5c72f

查看系统表分析

select * from storage_containers where node_name='v_embigdata3_node0013' and projection_name='Web_Tmp_UrlDetail_b1'

node_name schema_name projection_id projection_name storage_type storage_oid

v_embigdata3_node0013 tmp 108086391069294108 Web_Tmp_UrlDetail_b1 ROS 99079191814645559

v_embigdata3_node0013 tmp 108086391069294108 Web_Tmp_UrlDetail_b1 ROS 99079191814645839

发现报错ros_container和系统表记录的不一致

说明，在建表导数据的时候数据只是临时文件在磁盘里面并没有落地，并没有被系统所记录，然而就在此时出现了相关表的损坏

解决办法

重建Web_Tmp_UrlDetail表（在报错信息里有，里面提到了这个表出现问题），这种方法最好，这里可能会有多个fdb文件损坏

其次选择

1，可能是硬件损坏，需要更换硬件

2，如果使用vsql, ODBC, or JDBC查询的时候出现FileColumnReader ERROR错误，这种信息可能是CRC校验存储不配对。

此时需要删除该节点全部数据，重新rebalance

1 0