[Devkor-Ontime][트러블슈팅] 유저계정삭제 API 호출시 외래키 제약조건으로 인한 오류 해결 (500 Internel 서버 에러)

얼마 전 유저 계정삭제 API에서 500 Internel Server Error가 발생한다는 제보가 FE 측에서 들어왔다.

 
로그를 확인해 보니 User 데이터 삭제 시 Preparation_User 테이블 데이터가 같이 삭제되는데(∵ @OnDelete롬복의 Cascade옵션) 이때 Preparation_User의 next_preparation_id 필드가 다른 Preparation_User를 참조하고 있어 외래키 제약조건으로 인해 삭제가 안 되는 현상이었다.
 
당시에는 FE에서 급하게 요청을 해 시급히 해결해야 하는 상황이었기 때문에, 아래 사진처럼 userRepository에서 user를 삭제하기 전에 preparationUserRepository에서 문제가 되는 필드인, 해당 유저의 Preparation_User의 next_preparation_id 필드를 NULL로 초기화하는 방식으로 문제를 해결하였다.

그러나 위 코드는 DB서버에 SQL문을 여러 번 날려야 한다는 단점이 명확하였다. 우선은 FE에서 급하게 요청해서 응급처치는 해두었지만 완전한 치료는 아니었다. 따라서 차후 리팩토링을 해야겠다고 생각하고 잠에 들었다.
 
 
그러나 다음날..!!

또다시 유사한 에러가 발생한다는 제보가 FE에서 들어왔다.
 

java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (ontime.preparation_schedule, CONSTRAINT FK2p6mwf9sk904k166sl3jteagb FOREIGN KEY (schedule_id) REFERENCES schedule (schedule_id))

동일한 종류의 에러로, 이번에는 Schedule 데이터를 삭제하려는데 Preparation_Schedule 데이터가 schedule_id를 외래키로 참조하고 있어 삭제가 불가능한 상황이었다. 이를 통해 이전에 작성한 코드의 불완전성을 다시금 느꼈고 근본적인 원인을 해결해야겠다는 생각이 들었다.
 
 
그런데 위 오류들을 해결함에 있어 드는 의문이 한 가지 있었다. schedule_id 외래키 오류는 로컬서버에서도 발생하였으나 next_preparation_id 외래키 오류는 로컬서버에서 발생한 적이 없는 오류였고, 로컬서버에서 테스트를 해봐도 발생하지 않았다. 왜 프로덕션 서버에서만 뜨는지 의문이 들었다. 두 서버에 어떤 차이가 있어 한 서버는 오류가 나지 않고 한 서버는 오류가 난 것일까? 구글링 및 테스트 결과, application.properties(application.yml)의 spring.jpa.hibernate.ddl-auto 옵션이 로컬서버에서는 create인데 프로덕션서버에서는 update인 것이 그 차이의 원인이었던 것으로 추정 중이다. create옵션에서는 스프링서버를 실행할 때마다 테이블을 초기화하지만 update에서는 초기화하지 않고 데이터를 유지해 Preparation_User 데이터가 꼬일 수 있는 여지가 있는 것 같다. 프로덕션 서버에서 발생한 오류고, 해결 이후 코드를 원상태로 돌리고 같은 과정을 실험해 봐도 오류가 발생하지 않아, 일관성 있는 오류가 아니라 오류의 원인이 무엇인지는 정확히 파악할 수 없다. 그러나 가장 높은 확률로 ddl-auto 옵션이 update일 때 데이터가 꼬일 수 있는 여지가 있어 오류가 발생한 것 같다고 추정하고 있다.
(사실 프로덕션 서버에서 ddl-auto 옵션을 update로 하는 것도 지양해야 한다. 이는 뒤에서 자세히 다루겠다.)
 
 

따라서 일관성이 없는 오류인 next_preparation_id는 우선 차치하고 schedule_id 외래키 오류를 해결하는데 집중하였다.
우선 현재의 상황은, Schedule 데이터들을 삭제하여야 하는데, User_Preparation 데이터가 Schedule을 참조하고 있어 Schedule 데이터들을 삭제를 하지 못하는 상황이다.
만약 Schedule 테이블에 Schedule_Preparation에 해당하는 필드가 있었다면 CASCADE옵션을 해당 필드에 주면 되는데 현재 엔티티 구성에서는 Schedule_Preparation -> Schedule로 단방향 연관관계를 가지고 있어 이는 불가한 방법이다.
(양방향으로 바꾸고 CASCADE 옵션을 주는 방법도 고려할 수 있으나, 양방향으로 바꾸게 되면 순환 참조 문제와 연관된 엔티티를 직접 관리해야 하는 부담 증가, repository.save시 예상치 못한 추가 update 쿼리 발생, 유지보수의 어려움 등의 단점이 있어 양방향으로 바꾸는 것은 지양해야 한다고 판단하였다)
 
이러한 상황에서 외래키 제약조건 오류를 해결하는 방법은 크게 2가지이다.
1. DB단에서 ON DELETE CASCADE 설정
2. 유저 삭제 API 호출 시 유저 삭제 이전에 Schedule_Preparation을 먼저 삭제하는 코드를 추가 (이렇게 하면 User데이터 삭제하는 리포지토리 메서드가 실행되면 Schedule 데이터들이 삭제될 수 있음.)
 
결과적으로는 1번을 선택하였다.
그 이유는 만약 2번을 선택하게 되면, user_id를 바탕으로 schedule_id를 조회하고 조회한 schedule_id를 바탕으로 Preparation_Schedule을 삭제해야 해 스프링 서버에서 db로 명시적으로 날려야 하는 쿼리가 2회이고 암시적으로 JPA특성상 추가 쿼리가 더 날아갈 수 있기 때문에 응답시간이 배로 증가하게 된다.
반면 1번 방법의 경우에는 userRepository.delete(userId) 코드 한 줄 만으로 즉, 쿼리를 한방만 날려서 user와 연관된 데이터들을 모두 안정적으로 삭제할 수 있기 때문에 1번 방법을 선택하였다.
 

ALTER TABLE preparation_schedule
DROP FOREIGN KEY FK2p6mwf9sk904k166sl3jteagb;

ALTER TABLE preparation_schedule
ADD CONSTRAINT FK2p6mwf9sk904k166sl3jteagb
FOREIGN KEY (schedule_id) REFERENCES schedule(schedule_id) ON DELETE CASCADE;

위 코드를 배포한 서버의 MySQL서버에서 실행해 Preparation_Schedule의 schedule_id 칼럼에 ON DELETE CASCADE 설정을 해주어, Schedule데이터가 삭제될 때 그에 해당하는 Preparation_Schedule 데이터를 모두 삭제하게 설정하였다.
 

계정이 성공적으로 삭제된 것을 확인할 수 있다!
 
 
 
 

---

+) 위 에러에 대해 트러블 슈팅을 하는 과정에서 운영서버의 ddl-auto 옵션이 update인 것을 확인하였다.

• 운영서버에서 ddl-auto 옵션을 update로 설정하는 것은 지양해야 하는 것으로 알고 있어, (예: 기존에 데이터가 있는 테이블에 not null인 필드를 추가하면, 기존 데이터가 삽입이 안 되는 치명적 오류가 발생할 수 있음)
• ddl-auto 옵션을 update -> validate로 변경하는 것을 협업하는 팀원에게 제안하였고 변경하였음.
• 다만 이렇게 옵션을 변경하면 스프링 엔티티에 변동사항이 생기면 수동으로 DDL(SQL)을 작성해 DB서버에 반영해줘야 하는 점이 불편하다는 점이 단점인데, 이는 Flyway나 Liquibase 같은 DB마이그레이션도구로 일정 부분 해소가능함. 차후 적용하면 좋을 것으로 보임.

+) 응급처치용으로 작성해 두었던 next_preparation_id를 null로 초기화하는 코드는 주석처리해 두었다. ddl-auto 옵션을 update로 하면서 데이터가 꼬여 발생한 현상으로 추정하고 있어 현재 validate로 설정을 바꾼 상태에서는 오류가 나지 않을 것으로 판단하였고 실제로 현재 오류가 나고 있지 않다. 만약 FE에서 동일한 오류를 제보한다면 그 원인에 대해 재고찰이 필요할 것으로 보이나, 당장은 문제가 없고 앞으로도 문제가 없을 확률이 크다.