人大金仓数据库常见问题（持续更新）

番茄你个西红4

已于 2025-01-15 10:11:27 修改

阅读量2.8k

点赞数 4

文章标签：数据库

于 2024-02-19 10:35:07 首次发布

本文链接：https://blog.csdn.net/qq_21608393/article/details/136164818

版权

1.查看大小是否敏感写参数，提示：未认可的配置参数 "case_sensitive"

2.sys_backup.sh init时提示can not connect the primary node

3.设置逻辑备份运行脚本时提示错误are not allowed to use this program (crontab)

4.修改表字段类型bit为int失败，提示SQL 错误[42804]: ERROR: default for column "prescript" cannot be cast automaticallyto type integer

5.string_agg和group_concat函数

6.查询锁表语句

7.KES V8R6集群物理备份初始化时提示more than one primary cluster found

8.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 5

1.查看大小是否敏感写参数，提示：未认可的配置参数 "case_sensitive"

在V8R6C005之后的版本查询大小写敏感参参数为show enable_ci;之前的版本用show case_sensitive

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/bin

3.设置逻辑备份运行脚本时提示错误are not allowed to use this program (crontab)

环境：HiSilicon Kunpeng-920 / ky10
/etc/cron.allow 文件添加kingbase用户后依然提示报错。
问题：

[kingbase@kylinv10sp1 R6logic_backup-final]$ crontab -l
You (kingbase) are not allowed to use this program (crontab)
See crontab(1) for more information

--注意权限，检查/usr/bin/crontab是否有特权
[root@kylinv10sp1 bin]# ls -al crontab
-rwxr-xr-x 1 root root 68248 Apr  1  2020 crontab
[root@kylinv10sp1 bin]# chmod u+s /usr/bin/crontab
[root@kylinv10sp1 bin]# ls -al crontab
-rwsr-xr-x 1 root root 68248 Apr  1  2020 crontab
[root@kylinv10sp1 bin]# su - kingbase
Last login: Wed Feb 21 11:41:48 CST 2024 on pts/3
[kingbase@kylinv10sp1 ~]$ crontab -l
no crontab for kingbase

4.修改表字段类型bit为int失败，提示SQL 错误[42804]: ERROR: default for column "prescript" cannot be cast automaticallyto type integer

原bit字段有一个默认值，导致修改失败，先取消默认值，再执行修改字段即可。

ALTER TABLE tc_geo_address_catalog

ALTER COLUMN prescript

SET 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)(SFUNC=F_CONCAT,STYPE=TEXT);

6.查询锁表语句

SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process,
         blocked_locks.mode ,
         blocked_locks.locktype
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  
        ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity 
        ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.granted;