目录1.KingbaseES sqlserver 模式迁移SQLServer 模式提示“cannot insert multiple commands into a prepared statement”2.sys_backup.sh init时提示can not connect the primary node3.设置逻辑备份运行脚本时提示错误are not allowed to use this program (crontab)4.修改表字段类型bit为int失败提示SQL 错误[42804]: ERROR: default for column prescript cannot be cast automaticallyto type integer5.string_agg和group_concat函数6.部署KFS时提示“ Unable to find any host conigurations for the data senice speced”7.本地虚拟环境KES V8R6集群物理备份初始化时提示more than one primary cluster found8.com.kingbase8.util.KSQLException: An I/O error occurred while sending to the backend.9.could not open shared memory segment /kingbase.1416331144: No such file or directory (SQLSTATE 510.海光方德环境安装时提示“Exception in threadmain java.lang.UnsupportedclassVersionError: com/zerog/lax/LAX : Unsupported major .minor version 52.011.业务表与系统表冲突时修改search_path12.ksql test/kingbase system 登录后无权限建库建表“can not create database in current database”13.迁移GIS数据提示append_sridb标识符无效14.空间地理信息库报错“ Cant infer the s0L type to use for an instance of org.postgis.PGgeometry”15.sys_dump导出提示“ error: could not parse default ACL list ”16.物理备份初始化提示“must located in local”17.mysql 模式下查询横线的模式或表提示SOL错淏[426011: ERROR: svntax error at or near gz-papb 18.处理系统表冲突的两种方法19.主备集群repmgr cluster show 出现多条备机记录20.如何设置保留6个月的日志1.KingbaseES sqlserver 模式迁移SQLServer 模式提示“cannot insert multiple commands into a prepared statement”在kes中执行以下语句 alter user xxx set enable_automatic_block to on;2.sys_backup.sh init时提示can not connect the primary node# pre-condition: check the non-archived WAL files ERROR: can not connect the primary node 192.168.56.112 by ksql HINT: may use sys_encpwd to setup .encpwd *****注意配置中的_single_bin_dir 参数是Server/bin3.设置逻辑备份运行脚本时提示错误are not allowed to use this program (crontab)情景1HiSilicon Kunpeng-920 / ky10/etc/cron.allow 文件添加kingbase用户后依然提示报错。问题[kingbasekylinv10sp1 R6logic_backup-final]$ crontab -l You (kingbase) are not allowed to use this program (crontab) See crontab(1) for more information --注意权限检查/usr/bin/crontab是否有特权 [rootkylinv10sp1 bin]# ls -al crontab -rwxr-xr-x 1 root root 68248 Apr 1 2020 crontab [rootkylinv10sp1 bin]# chmod us /usr/bin/crontab [rootkylinv10sp1 bin]# ls -al crontab -rwsr-xr-x 1 root root 68248 Apr 1 2020 crontab [rootkylinv10sp1 bin]# su - kingbase Last login: Wed Feb 21 11:41:48 CST 2024 on pts/3 [kingbasekylinv10sp1 ~]$ crontab -l no crontab for kingbase情景2统信操作系统先给777权限再给s权限[rootlocalhost insatll]# chmod 777 /usr/bin/crontab [rootlocalhost insatll]# chmod us /usr/bin/crontab [rootlocalhost insatll]# ls -rtl /usr/bin/crontab -rwsrwxrwx 1 root root 63160 3月 14 05:47 /usr/bin/crontab4.修改表字段类型bit为int失败提示SQL 错误[42804]: ERROR: default for column prescript cannot be cast automaticallyto type integer原bit字段有一个默认值导致修改失败先取消默认值再执行修改字段即可。ALTER TABLE tc_geo_address_catalogALTER COLUMN prescriptSET DEFAULT NULL;alter table tc_geo_address_catalog alter column prescript type int using id ::int;5.string_agg和group_concat函数pg模式用string_agg(),oracle模式用group_concat(),注意参数为text,若传入的参数类型不正确则会报错。 重写 CREATE OR REPLACE FUNCTION F_CONCAT(TEXT,TEXT,TEXT) RETURNS TEXT AS $$ SELECT $1||$3||$2; $$ LANGUAGE SQL STRICT; CREATE AGGREGATE group_concat(TEXT,TEXT)(SFUNCF_CONCAT,STYPETEXT);6.部署KFS时提示“ Unable to find any host conigurations for the data senice speced”检查配置文件中的ip 是否正确特别时使用代理的服务器登录IP并部署真实IP。7.本地虚拟环境KES V8R6集群物理备份初始化时提示more than one primary cluster found经排查本地测试服务器是采用nathost-only模式且虚拟机时复制的方式nat网卡的mac地址和ip都没有变化修改或者删除网卡信息后执行初始化脚本成功。8.com.kingbase8.util.KSQLException: An I/O error occurred while sending to the backend.show tcp_keepalives_idle; 修改为tcp_keepalives_idle 609.could not open shared memory segment /kingbase.1416331144: No such file or directory (SQLSTATE 5/data/kingbase/data/kingbase.conf 里面找到dynamic_shared_memory_type 参数改为dynamic_shared_memory_type sysv重启数据库10.海光方德环境安装时提示“Exception in threadmain java.lang.UnsupportedclassVersionError: com/zerog/lax/LAX : Unsupported major .minor version 52.0通过Unsupported major.minor version 52.0信息基本可以确定是由于JDK版本不匹配导致的安装报错根据错误信息52得知,要求的JDK对应版本号码为52.52对应JDK 1.8版本(Unsupported major.minor version 52.0信息为要求JDK对应的版本,而不是报错的JDK版本).高于52的jdk都可以使用。11.业务表与系统表冲突时修改search_path当 Kingbase 数据库中的业务表与系统表发生名称冲突时可以通过修改 search_path 来解决这个问题。进入到对应的库执行alter database dbname set search_pathschema_name,$user,public,sys,sys_catalog,pg_catalog; ---必须加上sys,sys_catalog,pg_catalogselect sys_reload_conf();重连客户端重启应用后生效若应用重启后依然没有生效则检查jdbc url ,url 里不再需要配置Currentschema 参数因为带模式名时忽略search_path。jdbc.master.urljdbc:kingbase8://xxx.xxx.xxx.xxx:54321/db1?currentSchemaschenam_name 修改为jdbc.master.urljdbc:kingbase8://xxx.xxx.xxx.xxx:54321/db1或者修改应用URL中的search_path,类似jdbc:kingbase8://xxx.xxx.xxx.xxx:54321/test?currentSchema$user,public,sys,sys_catalog,pg_cataloguseUnicodetruetcpKeepAlivetruecharacterEncodingutf8serverTimezoneAsia/ShanghaireWriteBatchedlnsertstrue12.ksql test/kingbase system 登录后无权限建库建表“can not create database in current database”该金仓库兼容模式为SQLSERVER 单兼容模式为sqlserver 时需要登录master 库进行建库手动新建模式后再进行建表13.迁移GIS数据提示append_sridb标识符无效gis数据中的srid拼接到gis数据中以前查询出来。在源端数据库中创建以下函数-- 创建函数 CREATE OR REPLACE FUNCTION append_srid(srid IN INTEGER,kwb IN blob) RETURN blob AS re blob; srid_ integer; BEGIN IF kwb IS NULL then RETURN NULL; END IF; re : to_blob(UTL_RAW.cast_from_binary_integer(srid,utl_raw.little_endian)); dbms_lob.append(re,kwb); RETURN re; END; -- 赋权 GRANT EXECUTE ON my_schema.append_sridTO ueser_xxx;14.空间地理信息库报错“ Cant infer the s0L type to use for an instance of org.postgis.PGgeometry”用数据安装目录复制出来的 postgresql-42.2.9.jar 写入数据库正常用 kingbase8-8.6.0.jar 报错15.sys_dump导出提示“ error: could not parse default ACL list ”环境cpu:Kunpeng-920操作系统UnionTech OS Server 20版本V008R006C008M001B0025模式mysql[kingbase0002 ~]$ sys_dump -Usystem -dcasdb2 -n statistics_data -Fc -f /home1/kingbase/temp/statistics_data.dmpsys_dump: error: could not parse default ACL list ({mhke_devrwU/system,user_linlier/system})版本已知问题需要找技术人员申请补丁包16.物理备份初始化提示“must located in local”统信操作系统在做物理备份初始化时提示“ERROR: repo_ip [127.0.0.1] must located in local, and sys_backup.sh be executed at REPO host.”处理[kingbaseiZ0pu01nu7k81z6qmthnd7Z share]$ sys_backup.sh initERROR: repo_ip [127.0.0.1] must located in local, and sys_backup.sh be executed at REPO host.-rwsr-x--- 1 root root 79848 Jun 25 2023 /usr/bin/ping[rootiZ0pu01nu7k81z6qmthnd7Z install]# chmod 777 /usr/bin/ping17.mysql 模式下查询横线的模式或表提示SOL错淏[426011: ERROR: svntax error at or near gz-papb 检查sql_mode 参数是否存在“ANSI_QUOTES” 不存在该值的话则无法识别引号。18.处理系统表冲突的两种方法1.直接修改url: jdbc:kingbase8://xxx.xxx.xxx.xxx:54321/test?currentSchema$user,public,sys,sys_catalog,pg_catalog 2.修改search_path alter database db-kance-management-app set search_path db-kance-management-app,$user,public,sys,sys_catalog,pg_catalog 修改后jdbc url 不再需要配置currentSchema19.主备集群repmgr cluster show 出现多条备机记录主备集群repmgr cluster show 出现多条备机记录查询repmgr.nodes 出现“ WARING:transaction 25117898 was hinted as committed. but was not marked as committed in the transaction 1oe”,重启后依然存在处理方法systemctl stop securecmdd.service 并且kill repmgrd 进程重启数据库进入到esrep 库清除repmgr.nodes 和repmgr.events 表主库和备库重新加入集群repmgr primary register -f /home/kingbase/cluster/xxx/kingbase/etc/repmgr.conf --force20.如何设置保留6个月的日志log_filenamekingbase-%m-%d.log --日志按天保留0 3 * * * find /data/kingbase/data/sys_log/ -name kingbase-*.log -mtime 180 -delete --删除180天前的日志