타워 디펜스 온라인 1

트러블슈팅

 

 

데이터베이스 테이블 생성 참조 에러 트러블 슈팅

 

• 문제상황

게임 종료시 기록을 저장하기 위한 데이터 테이블을 생성해야하는데 유저의 정보는 user_db에 있으므로 
게임기록은 record_db에 넣어주려 했지만 마이그레이션 실행시 알수없는 에러가 발생함

 

• 참조에러

참조에러

 

현제 데이터베이스 테이블에서 user를 참조할수 없다는듯하다.

//user_db.sql 테이블 생성코드

CREATE TABLE IF NOT EXISTS user (
    id INT(11) AUTO_INCREMENT PRIMARY KEY 
    userId VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    createAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


//recode_db.sql 테이블 생성코드

CREATE TABLE IF NOT EXISTS Record(
   id INT(11) AUTO_INCREMENT PRIMARY KEY,
    userId VARCHAR(255) NOT NULL UNIQUE,  -- 플레이어 1 ID
    opponentId VARCHAR(255) NOT NULL,  -- 플레이어 2 ID
    isWin BOOLEAN NOT NULL,  -- 승/패 여부
    gameDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 게임 날짜
    FOREIGN KEY (userId) REFERENCES user (userId)
);

위와 같은 코드로 2개의 파일에서 별도로 실행되는데 mysql에서 기본적으로 user라는 테이블이 있는지 코드가 보라색으로 뜨는 현상을 확인했다.

이름이 겹쳐서 다른곳에서 참조하는건가? 싶어서 이름을 수정해줌

//user_db.sql 테이블 생성코드

CREATE TABLE IF NOT EXISTS `User` ( <===여기 수정
    id INT(11) AUTO_INCREMENT PRIMARY KEY 
    userId VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    createAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


//record_db.sql 테이블 생성코드

CREATE TABLE IF NOT EXISTS Record(
   id INT(11) AUTO_INCREMENT PRIMARY KEY,
    userId VARCHAR(255) NOT NULL UNIQUE,  -- 플레이어 1 ID
    opponentId VARCHAR(255) NOT NULL,  -- 플레이어 2 ID
    isWin BOOLEAN NOT NULL,  -- 승/패 여부
    gameDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 게임 날짜
    FOREIGN KEY (userId) REFERENCES `User` (userId) <===여기수정
);

하지만 여전히 같은상황이 발생했다.

이것저것 해보던중 참조할 경우 기본키로 지정되지 않은 userId는 참조할 수 없다고한다.

user테이블의 id를 삭제하고 userId를 기본키로 지정함 

//user_db.sql 테이블 생성코드

CREATE TABLE IF NOT EXISTS `User` ( 
    userId VARCHAR(255) NOT NULL PRIMARY KEY , <===여기수정
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    createAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


//record_db.sql 테이블 생성코드

CREATE TABLE IF NOT EXISTS Record(
   id INT(11) AUTO_INCREMENT PRIMARY KEY,
    userId VARCHAR(255) NOT NULL UNIQUE,  -- 플레이어 1 ID
    opponentId VARCHAR(255) NOT NULL,  -- 플레이어 2 ID
    isWin BOOLEAN NOT NULL,  -- 승/패 여부
    gameDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 게임 날짜
    FOREIGN KEY (userId) REFERENCES `User` (userId) 
);

이제 기본키값을 record에서 참조하여 새로운 테이블이 생성..되지않았다.

여전히 같은에러가 발생한다.

번뜩 순서문제가 아닐까? 싶은 생각이듬

user에 테이블이 생성되기전 record테이블에서 user를 참조하려하기때문에 에러가 나는것이 분명하다.

record파일을 지우고 user_db.sql에서 순서대로 쿼리를 날려주기로함

 

// USER_DB에 user 테이블 생성

    await executeSqlFile(pools.USER_DB, path.join(sqlDir, 'user_db.sql'));

// RECORD_DB에 record 테이블 생성

    await executeSqlFile(pools.RECORD_DB, path.join(sqlDir, 'record_db.sql'));

현재 이런 식으로 테이블을 생성해주는데

// USER_DB에 user 테이블 생성

    await executeSqlFile(pools.USER_DB, path.join(sqlDir, 'user_db.sql'));

// RECORD_DB에 record 테이블 생성

    await executeSqlFile(pools.RECORD_DB, path.join(sqlDir, 'user_db.sql'));<==여기수정

 

//user_db.sql 테이블 생성코드

CREATE TABLE IF NOT EXISTS `User` ( 
    userId VARCHAR(255) NOT NULL PRIMARY KEY , <===여기수정
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    createAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS Record(
   id INT(11) AUTO_INCREMENT PRIMARY KEY,
    userId VARCHAR(255) NOT NULL UNIQUE,  -- 플레이어 1 ID
    opponentId VARCHAR(255) NOT NULL,  -- 플레이어 2 ID
    isWin BOOLEAN NOT NULL,  -- 승/패 여부
    gameDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 게임 날짜
    FOREIGN KEY (userId) REFERENCES `User` (userId) 
);

위처럼 하나의 파일에서 한번에 보내주지만 user테이블이 먼저 생성되도록 만들었다.

드디어 record 테이블이 생성되었다!

record테이블

user테이블

문제는 record와 user에 각각 테이블이 2개씩 생성된다.

 

하지만 이렇게라도 테이블이 생성 된다는건 참조를 하고있다는 소리아닌가?

그렇다면 생성 방식을 다시 고치고 순서만 조정해보자

기존 쿼리방식으로 돌리고 
쿼리 생성 코드에서 record에 await을 빼버리고 user가 실행된후 작동하게 수정

 // USER_DB에 user 테이블 생성

    console.log(`실행중`);

    await executeSqlFile(pools.USER_DB, path.join(sqlDir, 'user_db.sql'));

    console.log(`실행완료`);

    // RECORD_DB에 record 테이블 생성

    console.log(`실행중`);

    await executeSqlFile(pools.RECORD_DB, path.join(sqlDir, 'record_db.sql'));

    console.log(`실행완료`);

잘 작동하는지 확인하기위해 콘솔도 찍어주고 이제 실행해보자

await을 뺀 실행결과

 

두개다 실행이 되긴하지만 record쿼리는 실행 완료가 뜨지않음

await 뺀 결과

await 뺀 결과

 

user테이블은 생성되지만 record테이블은 생성되지않음

슬슬 눈이 다른곳으로 돌아가기 시작한다.

환경변수가 문제인가?

//.env

DB_NAME=USER_DB
DB_USER=root
DB_PASSWORD=asdfg159
DB_HOST=127.0.0.1
DB_PORT=3306

DB2_NAME=RECORD_DB
DB2_USER=root
DB2_PASSWORD=asdfg159
DB2_HOST=127.0.0.1
DB2_PORT=3306

//env.js

export const DB_NAME = process.env.DB_NAME || 'user_db';
export const DB_USER = process.env.DB_USER || 'root';
export const DB_PASSWORD = process.env.DB_PASSWORD || 'password';
export const DB_HOST = process.env.DB_HOST || 'localhost';
export const DB_PORT = process.env.DB_PORT || 3306;

export const DB2_NAME = process.env.DB2_NAME || 'record_db';
export const DB2_USER = process.env.DB2_USER || 'root';
export const DB2_PASSWORD = process.env.DB2_PASSWORD || 'password';
export const DB2_HOST = process.env.DB2_HOST || 'localhost';
export const DB2_PORT = process.env.DB2_PORT || 3306;


//config.js

  databases: {
    USER_DB: {
      name: DB_NAME,
      user: DB_USER,
      password: DB_PASSWORD,
      host: DB_HOST,
      port: DB_PORT,
    },
    RECORD_DB: {
      name: DB2_NAME,
      user: DB2_USER,
      password: DB2_PASSWORD,
      host: DB2_HOST,
      port: DB2_PORT,
    },
  },

오타를 찾지못하고 다른방법을 찾아보니 참조는 같은 테이블끼리만 할 수 있다고한다.

user_db에 있는 user테이블과 record테이블 끼리는 참조가 되지만 user_db에있는 user테이블과 record_db에있는 record테이블은 서로 참조할 수 없다는 사실에 부러질뻔 했으나 혹시나해서 그럼에도 db끼리 참조할 수 있는 방법이 있는지 찾아보기로함 

 

찾아보니 mysql에서는 innoDB라는 다중 데이터베이스를 지원하고 쿼리에서 데이터베이스 이름을 명시함으로써 서로 다른 데이터베이스 테이블에 접근할 수 있다고한다. 

쉽게말해 앞에 db이름을 붙여주면됨

 database2(참조할db이름).record(참조할값) (userId)

마지막이라 생각하고 쿼리를 작성

CREATE TABLE IF NOT EXISTS Record(
   id INT(11) AUTO_INCREMENT PRIMARY KEY,
    userId VARCHAR(255) NOT NULL UNIQUE,  -- 플레이어 1 ID
    opponentId VARCHAR(255) NOT NULL,  -- 플레이어 2 ID
    isWin BOOLEAN NOT NULL,  -- 승/패 여부
    gameDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 게임 날짜
    FOREIGN KEY (userId) REFERENCES USER_DB.`User`(userId) <===여기
);

성공!

에러없이 user와 record테이블이 실행 완료했다는 콘솔출력!

 

실제 데이터베이스에도 잘 생성된것을 확인했다.

이제 데이터 저장하러가자..