💥 문제 상황

Test data 삽입 시 foreign key constraint fails 오류가 발생했다.

Error during test data insertion: QueryFailedError: Cannot add or update a child row: a foreign key constraint fails

삽입 query 전, 후로 await this.dataSource.query('SET FOREIGN_KEY_CHECKS = 0;')와 await this.dataSource.query('SET FOREIGN_KEY_CHECKS = 1;')를 실행해 삽입 query가 실행될 때에는 foreign key checks를 비활성화해주었는데도 오류가 발생한다.

🧐 문제 원인

먼저 foreign key checks 변수가 잘 설정되었는지 확인해보았다.

다음과 같이 query 실행 전에 SELECT @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks;로 현재 foreign_key_checks 변수 값을 출력해보았다.

[2024-06-28T14:23:09.690Z][QUERY]: SET FOREIGN_KEY_CHECKS = 0;
[2024-06-28T14:23:09.691Z][QUERY]: SELECT @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks;
[2024-06-28T14:23:09.701Z][QUERY]: INSERT INTO `dogs`(`id`, `walk_day_id`, `today_walk_time_id`, `name`, …
[2024-06-28T14:23:09.702Z][QUERY]: INSERT INTO `users_dogs`(`user_id`, `dog_id`) VALUES (?, ?), (?, ?), …
[2024-06-28T14:23:09.702Z][QUERY]: INSERT INTO `dog_walk_day`(`id`, `mon`, `tue`, `wed`, `thr`, `fri`, …
[2024-06-28T14:23:09.703Z][QUERY]: INSERT INTO `today_walk_time`(`id`, `duration`, `updated_at`) VALUES …
[2024-06-28T14:23:09.757Z][QUERY]: SET FOREIGN_KEY_CHECKS = 1;
[2024-06-28T14:23:09.764Z][QUERY]: SELECT `usersDogs`.`user_id` AS `userId`, …
[2024-06-28T14:23:09.774Z][QUERY]: SET FOREIGN_KEY_CHECKS = 0;
[2024-06-28T14:23:09.776Z][QUERY]: SELECT @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks;
[2024-06-28T14:23:09.903Z][QUERY]: INSERT INTO `journals`(`id`, `user_id`, `distance`, …
[2024-06-28T14:23:09.915Z][QUERY]: INSERT INTO `journals_dogs`(`journal_id`, `dog_id`) VALUES …
[2024-06-28T14:23:09.916Z][QUERY]: INSERT INTO `journal_photos`(`id`, `journal_id`, `photo_url`) VALUES …
[2024-06-28T14:23:09.918Z][QUERY]: INSERT INTO `excrements`(`id`, `journal_id`, `dog_id`, `type` …
[2024-06-28T14:23:10.226Z][QUERY]: SET FOREIGN_KEY_CHECKS = 1;

{
    '@@GLOBAL.foreign_key_checks': '1',
    '@@SESSION.foreign_key_checks': '0'
}

역시 잘 설정되어 있다. (참고로 session 변수가 global 변수보다 먼저 적용된다.)

하지만 그럼에도 foreign key constraint fails 오류가 발생했기 때문에 INSERT마다 다른 session을 가져 foreign key checks가 비활성화되지 않았다고 유추할 수 있다.

새로운 session 생성 시 foreign_key_checks 변수의 default 값은 1이다.

그렇다면 어떻게 모든 session의 foreign key checks를 비활성화시킬 수 있을까.

✅ 해결 방안

Transaction을 사용하면 된다. Transaction을 사용해 TestData를 삽입하는 method를 실행하면 같은 session에서 INSERT가 실행되므로 해결된다.

@Transactional()
async insertTestData(n: number): Promise<void> {
    await this.dataSource.query('SET FOREIGN_KEY_CHECKS = 0;');

    const mockEntityCreator = new CreateMockEntity(this.dataSource, n);

    const startTime = Date.now();

    const [userResults, dogResults] = await Promise.all([
        mockEntityCreator.createMockUsers(),
        mockEntityCreator.createMockDogs(),
    ]);
    const journalResults = await mockEntityCreator.createMockJournals();

    const endTime = Date.now();
    const duration = endTime - startTime;

    console.log(`Successfully inserted test data (${color(`+${duration}ms`, 'Cyan')}):`);

    await this.dataSource.query('SET FOREIGN_KEY_CHECKS = 1;');

    const table = [...userResults, ...dogResults, ...journalResults];
    const totalSize = table.reduce((count, entity) => count + entity.Count, 0);

    console.table(table);
    console.log('Total data size:', totalSize);
}

[2024-06-28T14:59:43.249Z][QUERY]: START TRANSACTION
[2024-06-28T14:59:43.250Z][QUERY]: SET FOREIGN_KEY_CHECKS = 0;
[2024-06-28T14:59:43.262Z][QUERY]: INSERT INTO `users`(`id`, `nickname`, …
[2024-06-28T14:59:43.263Z][QUERY]: INSERT INTO `dogs`(`id`, `walk_day_id`, …
[2024-06-28T14:59:43.264Z][QUERY]: INSERT INTO `users_dogs`(`user_id`, …
[2024-06-28T14:59:43.265Z][QUERY]: INSERT INTO `dog_walk_day`(`id`, `mon`, …
[2024-06-28T14:59:43.265Z][QUERY]: INSERT INTO `today_walk_time`(`id`, …
[2024-06-28T14:59:43.376Z][QUERY]: SELECT `usersDogs`.`user_id` AS `userId`, …
[2024-06-28T14:59:43.475Z][QUERY]: INSERT INTO `journals`(`id`, `user_id`, …
[2024-06-28T14:59:43.484Z][QUERY]: INSERT INTO `journals_dogs`(`journal_id`, …
[2024-06-28T14:59:43.485Z][QUERY]: INSERT INTO `journal_photos`(`id`, …
[2024-06-28T14:59:43.487Z][QUERY]: INSERT INTO `excrements`(`id`, …
[2024-06-28T14:59:43.804Z][QUERY]: SET FOREIGN_KEY_CHECKS = 1;
[2024-06-28T14:59:43.808Z][QUERY]: COMMIT