Skip to content →

Tag: Database

Docker Postgres “PANIC: could not locate a valid checkpoint record”

It seemed that my Postgres database was not properly shut down when rebooting and when I tried to use docker-compose to start it again, the following message was shown in `docker logs`:

PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 23) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure
LOG:  database system is shut down
LOG:  database system was interrupted; last known up at 2017-09-14 08:22:04 UTC
LOG:  unexpected pageaddr B/68B26000 in log segment 000000010000000B0000006D, offset 11689984
LOG:  invalid primary checkpoint record
LOG:  unexpected pageaddr B/688F2000 in log segment 000000010000000B0000006D, offset 9379840
LOG:  invalid secondary checkpoint record

To fix this, first shut down this container (docker-compose down), then start the container in interactive mode:

daoyuan.li:~/Projects/magic/stock$ docker run -it -v /Users/daoyuan.li/Projects/magic/postgres_data:/var/lib/postgres/data postgres:9.6 /bin/bash
root@c4d2fb7edcea:/# gosu postgres pg_resetxlog -f /var/lib/postgres/data
Transaction log reset
root@c4d2fb7edcea:/# exit

After the transaction log is reset, everything should be fine. Now you can start your containers again (docker-compose up -d).

Leave a Comment

Migrating JComments to WordPress

I switched back to WordPress from Joomla! after I registered this new domain. After migrated all the posts to WordPress using FG Joomla to WordPress, I noticed the comments have not been transferred. After some digging I didn’t find satisfactory plugin to do that, so I wrote my own PHP script to fulfill this task. The code is also available from GitHub.


13 Comments

每隔一定时间间隔查询是否有新消息

/**
 * 查询信息
 */
public void checkMessage(){
    TimerTask tt = new TimerTask() {
        public void run() {
            String unreadMsgNo = new CheckMessage().getUnreadMsgNo
                                    (URL, USER,
PASSWORD);
            if(unreadMsgNo.equals("0")){
                jLabelMsgTip.setText("您没有新信息!");
            } else{
                jLabelMsgTip.setText("您有" + unreadMsgNo + "条新信息!");
            }
        }
    };
    Timer t = new Timer();
    t.schedule(tt, 0, (long)1000*15);//每隔15秒执行一次
}
Leave a Comment

Oracle VPD策略实现行级安全性

VPD 即Virtual Private Databases,提供了对数据库的精密访问控制 (graind access control (FGAC) ),使用VPD,可以在数据记录集定义用户的访问权限。

通过VPD 策略,相当于用户操作数据库中的数据时隐式添加一些条件。比如我们要实现特定角色只能操作数据库表中的特定行的数据,这就可以用 VPD 策略实现。现在以车队长只能操作员工表中的属于他的车队的员工的数据信息为例具体说明:

1. 构造策略函数:

CREATE OR REPLACE FUNCTION "VPD"."FUN_MOTORCADE" (
 p_schema in varchar2, p_table in varchar2
)
return varchar2
as
 l_retstr varchar2(100);
 eid number(5, 0);
 did number(5, 0);
begin
 if lower(user)<> 'admin'
 then
 l_retstr := null;
 for user_rec in
 (
 select eid, did
 from dpmanager
 where loginname = lower(user)
 ) loop
 eid := user_rec.eid;
 did := user_rec.did;
 end loop;
 l_retstr := 'did = ' || did ;--|| 'eid = ' || eid;
 return l_retstr;
 else
 l_retstr := null;
 return l_retstr;
 end if;
end;

2. 授予执行策略函数的权限:

grant execute on FUN_MOTORCADE to public;

3. 向数据库表添加策略:

Begin
Dbms_Rls.Add_Policy(
Object_Schema =>'admin', --数据表(或视图)所在的Schema名称
Object_Name =>'employee', --数据表(或视图)的名称
Policy_Name =>'motorcade_ply', --POLICY的名称,主要用于将来对Policy的管理
Function_Schema =>'vpd', --返回Where子句的函数所在Schema名称
Policy_Function =>'fun_motorcade', --返回Where子句的函数名称
Statement_Types =>'Select,Insert,Update,Delete', --要使用该Policy的DML类型,如'Select,Insert,Update,Delete'
Update_Check =>True, --仅适用于Statement_Type为'Insert,Update',值为'True'或'False'
Enable =>True --是否启用,值为'True'或'False'
);
end;

最开始不明白策略函数的作用,花了好几天上网查资料才知道原来函数的返回值便是你想往表上加的隐式约束。
比如,你向EMP表添加了下面的策略函数:


CREATE OR REPLACE FUNCTION "VPD"."TEST" (
 p_schema in varchar2, p_table in varchar2
)
return varchar2
as
 l_retstr varchar2(100);
begin
  if user = ‘admin’
 then
 l_retstr := ‘1 = 1’;
 else
 l_retstr := ‘1 = 2’;
 end if;
 return l_retstr;
end;

现在,如果你以admin身份登录执行 ‘SELECT * FROM EMP’ , 则实际执行的是 ‘SELECT * FROM EMP WHERE 1 = 1’ , 其中 ‘1 = 1’ 这个条件是策略函数的返回值;如果你以其他身份登录执行 ‘SELECT * FROM EMP’ , 则实际执行的是 ‘SELECT * FROM EMP WHERE 1 = 2’ 。

Leave a Comment