레거시 프로젝트 장애 처리 ORA-00001

[장애발생]

기존 회사에는 20년이 넘은 메세지 관리자 레거시 프로젝트가 남아있다.
최근 해당 코드는 아에 업데이트를 하지않고 일부 계정만 사용을 하는데 관리자가 계정등록이 안된다는 메일을 확인하였다.

해당 계정이 등록되지 않은 이유는 0.1초 사이에 클릭이 2번 연속 되어 해당 DB에 값을 넣는 중 2번 넣어서 발생하는 문제였다.

TBL_STAT_CONFIG -> TBL_ROUTING -> TBL_CPINFO -> TBL_DETAIL_CPINFO
                                                TBL_STAT_CONFIG -> TBL_ROUTING -> TBL_CPINFO -> TBL_DETAIL_CPINFO

이런식으로 디비에 값이 들어가는 중 중복으로 값이 들어가는 문제였다.
이런 문제가 있으면 해당 트렌젝션을 전체 Rollback 하는게 맞다고 생각하지만 20년 넘는 시간동안 문제가 발생하지 않았고 이제 
새로운 관리자 사이트가 개발이 되고 조만간 없어지는 웹이기 때문에 문제만 해결해 달라고 이야기를 들었다.

[오류 로그]

CpInfoDao getCpInfoList complete.
cppwd           [       ]
############# CP PROCESS #############
LoginID         [       ]
Date            [2025-02-04 13:48]
ConnectIP       [       ]
action          [insert]
cpid            [        ]
use                     [0]
callflag        [0]
oldwebuse       []
webuse          [0]
######################################
[cohttp://m.spam.file.PhoneSpamDao.java : writeFile()] IO exception thrown:
java.io.FileNotFoundException: /home/mmsgw/cpinfo/p_   (Permission denied)
CpInfoDao.InsertCpInfo configFilePath[/home/webadmin
/tomcat9.0/webapps/msgstat/WEB-INF/classes/com/common/config/]
INFO :[2025-02-04 13:48:23,050] weblib.terashot.service.RegMember2Redis
- sentinelMasterId:mymaster2
INFO :[2025-02-04 13:48:23,050] weblib.terashot.service.RegMember2Redis 
- database:1
INFO :[2025-02-04 13:48:23,050] weblib.terashot.service.RegMember2Redis - 
INFO :[2025-02-04 13:48:23,050] weblib.terashot.service.RegMember2Redis -  
INFO :[2025-02-04 13:48:23,050] weblib.terashot.service.RegMember2Redis -  
CpInfoDao.InsertCpInfo apiAuthKey[              ==]
91976
hohoh91976
CpInfoDao InsertCpInfo SQLException[java.sql.
SQLIntegrityConstraintViolationException: ORA-00001: unique constraint 
(   .TBL_ROUTING_PK) violated

(Permission denied)가 떠서 해당 계정 생성시 스팸 번호 저장 파일이 생성되어야 하는데 해당 부분으로 들어가지 못한다고 생각을 하였다.

 

[해결방법]

-- routing 중복문제의 경우 TBL_STAT_CONFIG -> TBL_ROUTING  해당 데이터터 삭제

select * from TBL_STAT_CONFIG b where b.PREFIX ='91762';

select * from TBL_ROUTING b where systemid = '91762';

delete from TBL_ROUTING b where qid = '91762';


delete from TBL_STAT_CONFIG b where b.PREFIX ='91762';


commit;

-- mmsgw/cpinfo 파일은 굳이 처음에 만들 필요없다. 
-> 문제 발생 후