【报错现象】​报错语句：COPY dbadmin.xxx ( "mid","name","dws_updated_time" ) FROM STDIN WITH(FORMAT 'csv', DELIMITER ',', QUOTE '"', NULL 'null', ESCAPE '"', ENCODING 'UTF-8', COMPATIBLE_ILLEGAL_CHARS 'true')【问题背景】建表时没有合适的列作为分布列，为了防止数据倾斜，增加了一个sequence列作为分布列使用。【问题根因】1. 用户工具拼写COPY时对为指定的列，自动拼了NULL值进来。2. sequence列默认会有个NOT NULL约束。3. 只有当不指定值时才使用sequence的next_val获取序列值，如果指定了则直接用指定值。【解决办法】办法一：使用roundrobin分布方式，避免数据倾斜。办法二：多列组合作为分布列，避免数据倾斜。办法三：COPY时指定目标列，例如：copy t1(name) from stdin with delimiter ',';【相关问题】GaussDB(DWS)数据库设置主键后还需要设置分布键吗？仅设置主键即可，默认会选择主键的第一列作为分布键。如果两个同时设置，主键必须包含分布键。

【问题现象】COPY语句执行超时报错【内核版本】gaussdb (GaussDB 8.1.3 build 29305bc6) compiled at 2022-06-13 20:23:25 commit 3629 last mr 5138 release【问题日志】根据客户端的报错SQL在数据库后台日志中找到报错日志2023-01-18 14:13:40.576 database 139985690793728 0 cn_5003 217017229741661605 [BACKEND] LOG: copy from is in the INITIALIZING state. 2023-01-18 14:13:40.576 database 139985690793728 0 cn_5003 217017229741661605 [BACKEND] STATEMENT: COPY XXX 2023-01-18 14:13:40.579 database 139985690793728 0 cn_5003 217017229741661605 [BACKEND] LOG: [DYWLM]register dynamic info into dynamic_info_hashtbl, qid:[3, 3113970, 727366420575123], memory size:[118] 2023-01-18 14:13:40.579 database 139985690793728 0 cn_5003 217017229741661605 [BACKEND] STATEMENT: COPY XXX 2023-01-18 14:17:58.371 postgres 140089390778112 0 cn_5003 0 [BACKEND] LOG: PgstatCollectThreadStatus, node_name<cn_5003>, database_id<18609>, app_name<PostgreSQL JDBC Driver>, query_id<217017229741661605>, tid<139985690793728>, lw_tid<3113970>, parent_tid<0>, thread_level<0>, wait_status<wait ccn queue> 2023-01-18 14:21:00.566 database 139985690793728 0 cn_5003 217017229741661605 [BACKEND] LOG: copy from is transforming from INITIALIZING to TRANSFER_DATA state. 2023-01-18 14:21:00.566 database 139985690793728 0 cn_5003 217017229741661605 [BACKEND] STATEMENT: COPY XXX 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] LOG: could not receive data from client,remote nodename (null), detail:Connection reset by peer 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] CONTEXT: COPY cdm_zd_station_charge_power_info, line 901 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] STATEMENT: COPY XXX 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] LOG: incomplete message from client 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] CONTEXT: COPY cdm_zd_station_charge_power_info, line 901 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] STATEMENT: COPY XXX 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] LOG: An error in CopyFrom cannot be catched. 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] CONTEXT: COPY cdm_zd_station_charge_power_info, line 901 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] STATEMENT: COPY XXX 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] ERROR: unexpected EOF on client connection with an open transaction 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] CONTEXT: COPY cdm_zd_station_charge_power_info, line 901 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] LOCATION: CopyGetDataDefault, copy.cpp:338 2023-01-18 14:21:00.583 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] STATEMENT: COPY XXX 2023-01-18 14:21:00.584 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] LOG: could not send data to client [XXX]. detail:Broken pipe 2023-01-18 14:21:00.584 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] STATEMENT: COPY XXX 2023-01-18 14:21:00.584 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] LOG: [DYWLM]unregister dynamic info from dynamic_info_hashtbl, qid:[3, 3113970, 727366420575123], memory size:[118] 2023-01-18 14:21:00.584 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] STATEMENT: COPY XXX 2023-01-18 14:21:00.724 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] LOG: Failed to ABORT an implicitly PREPARED transaction status - RXACT_ABORT_FAILED:ABORT failed on all the nodes. 2023-01-18 14:21:00.724 database 139985690793728 5435493606 cn_5003 217017229741661605 [BACKEND] STATEMENT: COPY XXX 2023-01-18 14:21:00.726 database 139985690793728 0 cn_5003 0 [BACKEND] FATAL: connection to client lost主要逻辑14:13:40.576 copy from is in the INITIALIZING state 发起COPY语句14:17:58.371 后台PgstatCollect线程，显示139985690793728线程在'wait ccn queue'14:21:00.583 Connection reset by peer链接异常断开【业务侧逻辑】使用JDBC向数据库建链时，设置socket_timeout为5分钟。该COPY语句每小时会调起一次，正常时间段很快执行完。【问题分析】14:13:40到14:21:00语句端到端执行时间超过5分钟，连接因socket_timeout被JDBC断开。语句执行时间长是因为wait ccn queue在CCN等待排队因COPY语句最终没有执行完，没有被写入TOPSQLregister dynamic info into dynamic_info_hashtbl, qid:[3, 3113970, 727366420575123], memory size:[118]显示COPY语句本身只使用的118MB的内存CCN分快车道（估算内存>=32MB）和慢车道(估算内存<32MB)，COPY估算了118MB的内存，所以在慢车道里面排队，被其它人堵塞了。【慢车道分析】通过日志分析，COPY语句因在慢车道等待而超时，并未真正执行。 那当前为什么会在慢车道等待，我们看一下当前慢车道正在执行的SQL。postgres=# select start_time, finish_time, block_time, estimate_memory, max_peak_memory, substr(query,1,100) from pgxc_wlm_session_info where start_time >= '2023-01-18 22:00:00' and finish_time <= '2023-01-18 22:30:00' order by estimate_memory desc; start_time | finish_time | block_time | estimate_memory | max_peak_memory | substr -------------------------------+-------------------------------+------------+-----------------+-----------------+---------------------------------------------------------------- 2023-01-18 22:12:29.058331+08 | 2023-01-18 22:21:00.561384+08 | 6 | 19586 | 0 | --V0117\r + | | | | | with all_data as (\r + | | | | | select\r + | | | | | a.*,\r + | | | | | b.f_coupon_month,\r + | | | | | c.buy_time + 2023-01-18 22:04:51.585044+08 | 2023-01-18 22:09:24.23059+08 | 2 | 11165 | 0 | --V0117\r + | | | | | with all_data as (\r + | | | | | select\r + | | | | | a.*,\r + | | | | | b.f_coupon_month,\r + | | | | | c.buy_time 2023-01-18 22:21:00.569054+08 | 2023-01-18 22:23:13.520451+08 | 342461 | 128 | 28 | + | | | | | + | | | | | + | | | | | -- insert into zd_data.realtime_zd_station_spell_sell_info + | | | | | -- select + | | | | | -- if(DATE_FORMAT('2023-01 2023-01-18 22:05:19.461907+08 | 2023-01-18 22:09:38.63216+08 | 0 | 128 | 20 | + | | | | | + | | | | | insert into zd_data.realtime_zd_station_spell_sell_info_minute+ | | | | | select + | | | | | if(DATE_FORMAT('2023-01- 2023-01-18 22:24:46.341123+08 | 2023-01-18 22:27:48.66484+08 | 0 | 128 | 20 | + | | | | | + | | | | | insert into zd_data.realtime_zd_station_spell_sell_info_minute+ | | | | | select + | | | | | if(DATE_FORMAT('2023-01- 2023-01-18 22:10:06.422463+08 | 2023-01-18 22:13:57.675585+08 | 0 | 128 | 28 | + | | | | | + | | | | | + | | | | | -- insert into zd_data.realtime_zd_station_spell_sell_info + | | | | | -- select + | | | | | -- if(DATE_FORMAT('2023-01 2023-01-18 22:10:35.884899+08 | 2023-01-18 22:14:49.838399+08 | 0 | 128 | 16 | + | | | | | + | | | | | insert into zd_data.realtime_zd_station_spell_sell_info_minute+ | | | | | select + | | | | | if(DATE_FORMAT('2023-01- 2023-01-18 22:21:00.568848+08 | 2023-01-18 22:24:22.541598+08 | 342435 | 128 | 20 | + | | | | | + | | | | | insert into zd_data.realtime_zd_station_spell_sell_info_minute+ | | | | | select + | | | | | if(DATE_FORMAT('2023-01- 2023-01-18 22:21:00.567348+08 | 2023-01-18 22:21:01.778612+08 | 354437 | 128 | 28 | SELECT + | | | | | group_concat(f_czb_operator_id), + | | | | | COALESCE(array_length(regexp_split_to_array(group_concat(f_c 2023-01-18 22:05:12.738084+08 | 2023-01-18 22:09:28.064253+08 | 0 | 128 | 28 | + | | | | | + | | | | | + | | | | | -- insert into zd_data.realtime_zd_station_spell_sell_info + | | | | | -- select + | | | | | -- if(DATE_FORMAT('2023-01 (10 rows)当时有两个with all_data语句在执行，因内存估算超过32MB，都在慢车道里。执行时间覆盖了COPY语句的端到端时间。 所以，就是这两个语句执行时间长，导致COPY超时。【处理办法】1.需要客户优化近期新增复杂sql 2.资源池配置memory_limit参数，限制sql估算内存，避免单sql使用过多内存导致排队

DWS管控面之DMS-AGENT内存溢出 【问题现象】DMS-AGENT内存溢出 【常见版本】8.1.0以下版本,后续版本进行了优化改进 【定位思路】DMS-AGENT内存未做限制 1、console界面会出现集群“低性能” 2、查询集群状态，确定发生异常节点 3、登录节点，直接查询内存信息，找到dms-agent进程PID 4、规避措施与用户协商先停止此节点dms-agent

问题现象：日志未正常压缩回收日志盘打满问题原因：线下集群日志压缩和回收由FIM负责，若存在日志不压缩回收执行一下命令查看日志压缩回收进程是否存在1.执行命令，预期结果打印所有节点log.manager的进程号gs_ssh -c "ps -ef | grep log.manager | grep -v grep | awk '{print \$2}'"2.存在无log.manager进程的节点3.sh /opt/huawei/Bigdata/FusionInsight_MPPDB_XXXX/install/FusionInsight-MPPDB-XXXX/sbin/logManagerRun.sh start手动拉起后查看日志是否正常压缩回收

【问题现象】审计日志转储失败【常见版本】8.2.0以下版本,后续版本进行了优化改进【定位思路】失败原因是 python2 默认最大压缩4G 超过这个范围的导致无法压缩1、登录CN节点找到/home/Ruby/log/cloud-dws-deploy.log日志2、排查失败日志如下：Failed to pack file: Filesize would require ZIP64 extensions.3、修改/rds/datastore/dws/workspace/logStorageOBS.py文件，注意在Ruby用户下操作修改文件第294行，添加allowZip64=True原文件zf = zipfile.ZipFile(pack_path, 'w'）修改后文件zf = zipfile.ZipFile(pack_path, 'w', allowZip64=True)4、到RMS数据库修改最近一次失败记录为’SUCCESS’4.1 select * from audit_log_dump_records where clusterId='XXX' order by exectorTime desc limit 1;4.2 update audit_log_dump_records set status='SUCCESS' where  clusterId='XXX' and id='XXX';

一、版本：dws801二、定位过程 1.外表数据能查询，只是比较慢，但统计数量就报错 select count(1) from xx 2.在报错外表同样的region下的其他外表统计数量不报错 3.多次统计该外表，发现报错的dn是固定的几个 4.现场反馈hd集群做过纳管，怀疑是纳管后出现什么问题 4.使用 curl 远端主机ip 命令，发现有两台主机连不上hd主机 5.协调网络侧人排查，是目标端没有向DWS集群中的两个节点放通访问策略导致访问外表报错

一、版本：线下811二、定位过程 1.查看install_oms.log，报错encrypt pass failed 2.根据报错前的脚本installEX.sh，找到报错信息，将执行的命令拼接出来，单独执行，报错 illegal user3.去安装目录里查看文件，属主不是root4.os侧排查，更改为root属主后，安装manager成功

GaussDB 数据目录下的pg_rewind_bak文件作用是什么？文件是否需要手动删除？

像这种的是否可以作为安装包直接安装集群？

【问题现象】：安装GaussDB 失败，导致安装oms失败，查看gaussDB安装日志(/var/log/gaussdbinstall.log)，发现是初始化db失败，进而查看初始化db日志（/home/ommdba/gs_initdb-2016-06-13_162302.log）：【问题原因】：日志中显示创建信号量失败，原因为超过了操作系统信号量资源的使用限制。【检查方法】：执行如下命令，查看操作系统信号量资源的限制参数： sysctl kernel.semkernel.sem = 2500   32000       1000         128其中第4个数据对应系统内核的SEMMNI参数，该参数用于控制整个Linux系统中信号集的最大数量。就GaussDB而言，这个参数至少应该为(max_connections / 16 + 1)。【解决方法】：增大操作系统信号量资源的限制参数值（具体大小需要业务根据自己的资源使用来确定）：1.     修改内核配置文件"/etc/sysctl.conf"，在此文件的任意位置增加如下参数：kernel.sem = 2500 32000 1000 12802.     Suse系统执行如下命令，使SUSE Linux启动时自动读取内核参数。（redhat系统可忽略） /sbin/chkconfig boot.sysctl on3.     执行如下命令使内核参数生效。/sbin/sysctl –p

【问题现象】卸载oms卸载不掉【可能原因】1. 部分文件缺失，导致脚本无法正常运行。2.环境变量文件被修改或者删除，获取到的环境变量值错误，导致卸载失败【解决策略】  vi /etc/profile.d/sudoProfile.sh查看安装目录数据目录等环境变量是否和自己制定的目录一致，如果不一致请修改为自己指定的安装目录和数据目录，如果该文件不存在，则手动创建此文件，并添加如下信息：BIGDATA_HOME=/opt/VTBIGDATA_DATA_HOME=/srv/VTBIGDATA_LOG_HOME/var/log/BigdataCONTROLLER_HOME=/opt/VT/om-0.0.1各路径请修改为实际的安装目录和数据目录，然后重新执行卸载脚本如果依然不能卸载，则可以通过如下几步操作手动卸载：1.以root用户登录备管理节点。2.执行pkill -9 gaussdb命令，结束gaussdb进程。3. 执行su - omm命令，切换到omm用户。如果不存在该账户则不需要删除，直接执行步骤 5。4.执行以下命令，结束omm用户下的所有进程。pkill -9 java -u ommps -ef | grep omm将查询出来的omm用户进程，全部通过如下命令停止。kill -9 pid5.执行su - root命令并输入密码，切换为root用户。6.执行以下命令，删除FusionInsight集群的日志目录、安装目录和数据目录（以下路径为举例，具体以实际集群为准）。rm -rf /srv/Bigdatarm -rf /opt/huawei/Bigdatarm -rf /var/log/Bigdata7.删除系统内的omm和ommdba账户。如果不存在该账户则不需要删除。userdel ommuserdel ommdbarm -rf /home/ommrm -rf /home/ommdba8.执行ifconfig命令，删除集群OMSServer浮动IP地址和OMWebService浮动IP地址。如果不存在对应IP地址则不需要删除。ifconfig eth0:oms downifconfig eth0:ws down9. 登录主管理节点，重复以上步骤卸载主Manager。

使用root用户登录主管理节点。 切换到Setup软件包解压目录，例如“/opt/FusionInsight_SetupTool”。 cd /opt/FusionInsight_SetupTool 执行以下命令，安装diskmgt： sh preinstall/tools/genConfig/genConfigAndInstallDiskmgt.sh 界面提示以下信息表示安装成功： [INFO] [none:795] execute diskmgt.sh successfully.   [INFO] [main:608] Execute genConfigAndInstallDiskmgt.sh successfully. 默认只支持在单节点上安装进程，如果需要在多个节点执行任务，请参考《产品文档》的“如何在集群内多个节点上执行命令或者存取文件”。 其他节点 cd /opt/FusionInsight_SetupTool/preinstall/tools/cluster ./clusterscp.sh put /opt/FusionInsight_SetupTool /opt/FusionInsight_SetupTool 

1.查看集群和job的关系及错误信息select i.id,i.name,i.status,t.execution_status,t.fail_reason,t.job_id from rds_instance as i join taskmgr_job as t where i.name like '%集群名称%' and i.jobId = t.job_id group by i.name ;2.查看节点分类select distinct name , instType from rds_instance order by instType ;3.根据clusterid查看异步job结果select tbl_plugin.job_id, tbl_plugin.task_name, tbl_plugin.begin_time, tbl_plugin.listener_num, tbl_plugin.retry_num, tbl_plugin.execution_status from taskmgr_task as tbl_plugin left join taskmgr_job tbl_maintain on tbl_maintain.job_id = tbl_plugin.job_id where tbl_maintain.request like 'Î2c8e7a-7097-45ec-ac67-ec9c7297d5c9%' and (tbl_plugin.execution_status != 'SUCCESS' or tbl_plugin.execution_status is null) order by tbl_plugin.begin_time desc, tbl_plugin.job_id, tbl_plugin.task_index asc;4.根据集群名字查询实例的名字select ins.name from rds_instance ins ,rds_cluster clu where ins.clusterId=clu.id and clu.name='nodelete_test';5.根据集群名查找集群创建失败在哪一步select task.job_id, task.task_name, task.begin_time, task.listener_num, task.retry_num, task.execution_status from rds_instance ins left join taskmgr_task task on ins.jobId=task.job_id left JOIN taskmgr_job job on task.job_id=job.job_id where ins.`name` like '%集群名称%' And task.execution_status='FAIL' order by task.job_id,task.begin_time;6.根据集群名查找集群创建工作子任务执行情况select task.job_id, task.task_name, task.begin_time, task.listener_num, task.retry_num, task.execution_status from rds_instance ins left join taskmgr_task task on ins.jobId=task.job_id left JOIN taskmgr_job job on task.job_id=job.job_id where ins.`name` like '%集群名称%' order by task.job_id,task.begin_time;7.查看jobid，再根据jobid查看执行情况select jobId from rds_instance where name like '%name%'; select job_id,task_name,execution_status from taskmgr_task where job_id='xxx';8.根据租户名查询可用集群select name,status from rds_cluster where tenantId in (select realDomainId from rds_restenant where realDomainName in ('kingmedcloud','hw79054333','hw58886188')) and status='200';9.根据dws规格查询底层对应的规格select c.* from rds_cluster_spec a ,rds_cluster_instance_resspec b,rds_resSpecAttr c where a.id=b.cluster_spec_id and b.instance_spec_id=c.specId and a.`code`='console界面显示的规格名';10.查看实例id，实例名，manageIpselect id,name,manageIp from rds_instance where name like '%%' and status='xxxxx';11.修改底层规格update rds_resSpecAttr set value=959 where specId={ddd} and attrCode={xxx}; update rds_resSpecAttr set value=959 where specId= and (attrCode='dataDisk' or attrCode='diskSize'); update rds_resSpecAttr set value={新底层规格} where specId= and flavor=xxx; update rds_resSpecAttr set value='ULTRAHIGH' where specId='XXXX' and attrCode='logVolumeType';12.集群用户对应的obs ak sk信息获取select tenantId from rds_cluster where name='h30008669_406_01' select obsBucket,ak,sk from rds_restenant t where realDomainId='xxx' \G;13.查看实例名，host ip对应关系select id,name,internalIp,manageIP FROM rds_instance where name like '%集群名%' and status=200;14.获取快照工作任务和执行情况select job_id,begin_time,job_def_name,status from taskmgr_job where job_def_name like 'ºckup%' order by BEGIN_time desc select task_id,task_name,begin_time,execution_status from taskmgr_task where job_id='xxxx'15.根据serviceOM中的规格id查看dws规格idselect spcid from rds_resSpecAttr where value = {serviceOM规格id}; select cluster_spec_id from rds_cluster_instance_resspec where instance_spec_id={specid} select * from rds_spec_region where cluster_spec_id={cluster_spec_id}；16.修改管控面重分步状态update rds_cluster set statusDetail = 'Normal' where id = 'xxxx';删除动作记录delete from rds_action where odjid='集群id'；

扩容或重装主机时在第一步校验参数失败报Failed to verify nodes connection错误  操作场景  1、扩容主机失败，有如下提示信息： #发现节点未完成# Could not verify `ecdsa-sha2-nistp256` host key with fingerprint `31:90:be:5c:92:a4:62:90:7d:9a:d2:c2:4e:4f:d6:2a` for `xxx.xxx.xxx.xxx` on port 22 while Connecting via SSH to xxx.xxx.xxx.xxx using username root.  2、重装主机失败，提示： 重装主机失败，详细错误信息: Failed to verify nodes connection. Maybe the password is incorrect or the network is abnormal.  可能原因 待扩容或重装节点的HOSTKEY变更（重装OS或者重新生成HOSTKEY可能会导致HOSTKEY变更）。 处理步骤  1、使用PuTTY工具，以omm用户登录主管理节点。  2、执行vi /var/log/Bigdata/controller/controller_nodesetup.log命令，搜索“HOST_KEY_NOT_VERIFIABLE”关键字，有如下报错信息：  SSH Command Exectuion failed  3、执行vi ${BIGDATA_HOME}/om-server/om/etc/om/known_hosts命令，删除文件中包含待扩容或者重装节点主机名或者ip地址的行。  4、执行sh ${CONTROLLER_HOME}/sbin/restart-controller.sh命令重启controller，然后重新进行扩容或者重装主机。 

【问题现象】console页面修改配置项报错，显示系统繁忙【问题分析】1、进入dwscontroller容器，查看ossres-dws.log1）日志中大量打印lock wait timeout，排查dwscontroller所依赖mysql数据库，HCS环境在service om页面搜索虚拟机DWS-DB01/02登录后查看【措施】登录mysql数据库排查是否有大量阻塞进程或长连接，清理后重启dwscontroller容器并页面重试。2) 日志报错连不上mysql数据库，登录数据库检查磁盘空间占用，是否磁盘空间占满【措施】清理磁盘空间后重新拉起mysql数据库1.进入审计日志目录，清理审计日志文件（DWS-DB-01和DWS-DB-02）。cd /var/log/auditcd /data/ ;du -sh audit.log //查看对应的审计日志大小2.以root用户回到DWS-DB01节点，切到mysql用户，登录本地数据库。3.执行set global audit_log_rotations=50; set global audit_log_rotate_on_size=20971520；4.退出数据库，修改配置文件，保证重启也生效vim /data/mysql/etc/my.cnf增加审计日志轮转参数audit_log_rotations=50 audit_log_rotate_on_size=20971520保存退出。5.观察审计日志是否开始轮转。6.该参数是单节点生效，需要在每个节点执行。附：检查mysql数据库状态：1、查看mysql进程是否正常：ps -ef |grep mysql2、数据库主备状态：mysql用户下执行Get_db_status.py