이모지(emoji) 에러(Incorrect string value)

1. 문제 상황

• 사용자가 메모를 입력하는 API에서 이모지를 입력할 때 에러가 발생했음
• 개발 서버에서는 에러가 발생하지 않았지만 프로덕션 서버에서 에러가 발생함
에러(보안을 위해 데이터베이스와 테이블 및 컬럼 이름 등 구체적인 정보는 감췄음)

(conn:512392, no: 1366, SQLState: 22007) Incorrect string value: '\xF0\x9F\x98\x80' for column `database_name`.`table_name`.`column_name` at row 1
sql: 
        UPDATE database_name.table_name
        SET 
            column_name     = '😀',
            updated_by      = :updatedBy,
            client_ip       = :clientIp,
           ...

2. 문제 원인 파악

• 왜 이런 문제가 발생하는지 알지 못해서 가설을 세우고 문제를 해결했음

가설 1 : mariadb의 버전 차이로 인한 에러

• 우선 개발 서버와 프로덕션 서버에서 눈에 띄는 차이는 MariaDB의 버전 차이

ㅤ	MariaDB 버전
개발 서버	10.11.7.
프로덕션 서버	10.11.9.

• 따라서 MariaDB의 Change Log를 살펴보았지만 이모지와 관련된 변경사항은 확인할 수 없었음

• 그리고 DB 클라이언트에서 쿼리로 직접 이모지를 넣었을 때, MariaDB 버전 10.11.7.과 10.11.9. 둘 다 잘 들어갔음
실행한 쿼리

        UPDATE database_name.table_name
        SET 
            column_name     = '😀',
            updated_by      = 'sp_procedure_name',
            client_ip       = :clientIp
            updated_at      = NOW()

화면 캡쳐 : 이모지가 정상적으로 UPDATE 됨

• 따라서 DB의 차이보단 백엔드 서버에서 차이가 발생해서 에러가 발생했을 것이라고 생각함
화면 캡쳐 : Swagger에서 이모지를 입력하려고 할 때 에러 발생

가설 2 : 이모지를 쿼리 파라미터로 파싱하는 과정에서 에러

• 해당 API는 쿼리 파라미터를 사용해서 사용자의 입력을 DB에 UPDATE하고 있음
코드

export const updateRecord = async (values: { recordIdx: number; description: string; updatedBy: string; clientIp: string }) => {
    return await query(
        `
        UPDATE database_name.table_name
        SET 
            column_name      = :description,
            updated_by       = :updatedBy,
            client_ip        = :clientIp,
            updated_at       = NOW()
        WHERE record_idx = :recordIdx
        `,
        values
    );
};

export const query = async (sql: string, values?: any) => {
    const connection = await pool.getConnection();
    try {
        if (Array.isArray(values)) {
            return await connection.batch(sql, values); // Bulk Insert
        } else {
            return await connection.query(sql, values); // Query
        }
    } catch (e) {
        throw e;
    } finally {
        connection.release();
    }
};

• 쿼리에 직접 이모지를 넣었지만 같은 에러가 발생했음
• 따라서 쿼리 파라미터로 파싱할 때 문제가 있기보단 Mariadb Node.js Connector에서 에러가 발생했다고 추정

가설 3 : MariaDB Node.js Connector에서 이모지를 처리할 때 에러

• MariaDB Node.js Connector에서 쿼리를 실행하는 코드를 확인
코드 : connection-promise.js

// connection-promise.js

  /**
   * Execute query using text protocol.
   *
   * @param sql     sql parameter. Object can be used to supersede default option.
   *                Object must then have sql property.
   * @param values  object / array of placeholder values (not mandatory)
   * @returns {Promise} promise
   */
  query(sql, values) {
    // sql: SQL 쿼리 또는 설정 객체를 나타냄.
    // values: 쿼리에 바인딩할 값들을 가진 객체 또는 배열 (선택 사항)

    // cmdParam에 sql과 values를 합쳐 SQL 쿼리 명령에 필요한 파라미터 설정
    const cmdParam = paramSetter(sql, values);

    // cmdParam을 캡처하여 내부적으로 기록
    this.#capture(cmdParam);

    // 새로운 Promise를 생성하여 쿼리를 비동기적으로 실행
    // `this.#conn.query`를 `this.#conn` 컨텍스트로 바인딩해서 cmdParam을 사용하여 호출
    return new Promise(this.#conn.query.bind(this.#conn, cmdParam));
  }

• query 함수는 DB에 연결된 소켓을 통해 쿼리를 전송 → 서버로부터 응답을 받으면 Promise를 통해 처리된 결과를 반환
 

• MariDB Node.js Connector 설정 확인
코드 : charset이 UTF8MB4라서 이상 없음

import mariadb from 'mariadb';

const pool = mariadb.createPool({
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT),
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_DATABASE,
    connectionLimit: parseInt(process.env.DB_POOL_SIZE),
    charset: process.env.DB_CHARSET || 'UTF8MB4'
});

• MariaDB Node.js Connector의 소스코드에서 이모지를 처리할 때 특별한 코드로 처리하지 않고 문자집합도 UTF8MB4라서 이모지를 처리하는데 문제가 없음
• 따라서 Connector가 아닌 다른 곳에서 문제가 발생했다고 추정

가설 4 : 문자집합 문제

• MariaDB Node.js Connector, MariaDB의 문자 집합은 UTF8MB4인 것을 확인했음
DB의 문자 집합 : UTF8MB4



• 다른 문자 집합도 확인

SHOW VARIABLES LIKE 'character_set%';

실행 결과 : MariaDB 서버의 문자 집합이 UTF8MB3인 것을 발견

• character_set_client : DB 클라이언트가 보낸 요청의 문자 집합

• character_set_connection : DB 클라이언트 - DB 서버의 연결의 문자 집합

• character_set_database : MariaDB의 특정 데이터베이스의 문자 집합

• character_set_filesystem : 파일 시스템에서 사용되는 문자 집합

• character_set_results : DB 서버가 DB 클라이언트에게 반환하는 결과의 문자 집합

• character_set_server : MariaDB 전체 서버의 문자 집합

• character_set_system : MariaDB 시스템 테이블의 문자 집합

3. 문제의 정확한 원인

• MariaDB Client(ex. MariaDB Node.js Connector) → MariaDB Server → MariaDB의 데이터베이스 순서로 쿼리가 실행됨
• MariaDB Client는 백엔드 서버에서 설정으로 문자 집합을 UTF8MB4로 설정
• MariaDB의 해당 데이터베이스는 문자 집합이 UTF8MB4임을 직접 확인함
• 그러나 MariaDB Server의 문자 집합이 UTF8MB3라서 이모지를 넣을 때 에러가 발생함

4. 해결

• DB 구성은 인프라팀에서 관리함
• ∴ 인프라팀에 요청해서 MariaDB Server의 문자 집합을 UTF8MB4로 변경

5. Sequel Ace에서는 에러가 나지 않았던 이유

• 문제의 원인을 찾을 때, Sequel Ace라는 DB Client로 이모지를 삽입하는 쿼리에서는 에러가 발생하지 않았음
• MariaDB Server의 문자 집합이 UTF8MB3 이라서 에러가 났다면, Sequel Ace로 쿼리를 실행했을 때도 에러가 발생했어야함
• Sequel Ace에서 에러가 발생하지 않아서 MariaDB의 문자 집합에는 문제가 없다고 잘못 판단했음

• Sequel Ace는 MariaDB Server의 문자 집합이 UTF8MB3임에도 UTF8MB4로 덮어써서, 이모지를 삽입하는 쿼리를 실행했을 때 에러가 발생하지 않았음
관련 Sequel Ace 코드

/**
 * utf8mb3에 해당하는 Encoding이 없어서 utf8mb4가 반환됨
 */
- (NSString *)mysqlEncodingFromEncodingTag:(NSNumber *)encodingTag
{
    NSDictionary *translationMap = [NSDictionary dictionaryWithObjectsAndKeys:
                                    @"ucs2",     [NSString stringWithFormat:@"%i", SPEncodingUCS2],
                                    @"utf8",     [NSString stringWithFormat:@"%i", SPEncodingUTF8],
                                    @"utf8-",    [NSString stringWithFormat:@"%i", SPEncodingUTF8viaLatin1],
                                    @"ascii",    [NSString stringWithFormat:@"%i", SPEncodingASCII],
                                    @"latin1",   [NSString stringWithFormat:@"%i", SPEncodingLatin1],
                                    @"macroman", [NSString stringWithFormat:@"%i", SPEncodingMacRoman],
                                    @"cp1250",   [NSString stringWithFormat:@"%i", SPEncodingCP1250Latin2],
                                    @"latin2",   [NSString stringWithFormat:@"%i", SPEncodingISOLatin2],
                                    @"cp1256",   [NSString stringWithFormat:@"%i", SPEncodingCP1256Arabic],
                                    @"greek",    [NSString stringWithFormat:@"%i", SPEncodingGreek],
                                    @"hebrew",   [NSString stringWithFormat:@"%i", SPEncodingHebrew],
                                    @"latin5",   [NSString stringWithFormat:@"%i", SPEncodingLatin5Turkish],
                                    @"cp1257",   [NSString stringWithFormat:@"%i", SPEncodingCP1257WinBaltic],
                                    @"cp1251",   [NSString stringWithFormat:@"%i", SPEncodingCP1251WinCyrillic],
                                    @"big5",     [NSString stringWithFormat:@"%i", SPEncodingBig5Chinese],
                                    @"sjis",     [NSString stringWithFormat:@"%i", SPEncodingShiftJISJapanese],
                                    @"ujis",     [NSString stringWithFormat:@"%i", SPEncodingEUCJPJapanese],
                                    @"euckr",    [NSString stringWithFormat:@"%i", SPEncodingEUCKRKorean],
                                    @"utf8mb4",  [NSString stringWithFormat:@"%i", SPEncodingUTF8MB4],
                                    nil];
    NSString *mysqlEncoding = [translationMap valueForKey:[NSString stringWithFormat:@"%i", [encodingTag intValue]]];

    if (!mysqlEncoding) return @"utf8mb4";

    return mysqlEncoding;
}

/** 
 * 데이터베이스 연결의 인코딩을 설정
 */
- (void)setConnectionEncoding:(NSString *)mysqlEncoding reloadingViews:(BOOL)reloadViews
{
	  ...
	  
    // MySQL 연결에 인코딩을 설정하려고 시도
    // 인코딩 설정에 실패할 경우, 에러 메시지를 로그로 출력하고 메서드 종료
    // mysqlEncoding = utf8mb4
    if (![mySQLConnection setEncoding:mysqlEncoding]) {
        NSLog(@"Error: could not set encoding to %@ nor fall back to database encoding on MySQL %@", mysqlEncoding, [self mySQLVersion]);
        return;
    }
    
    ...

• Sequel Ace와 같은 DB Client가 DB Server의 문자 집합을 덮어쓰는 것은 일반적이라고 함
• ∵ 데이터 일관성 및 호환성 문제 방지