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

UTF8MB4, MariaDB, Sequel Ace, Emoji

Oct 08, 2024

Contents

1. 문제 상황 2. 문제 원인 파악 가설 1 : mariadb의 버전 차이로 인한 에러 가설 2 : 이모지를 쿼리 파라미터로 파싱하는 과정에서 에러 가설 3 : MariaDB Node.js Connector에서 이모지를 처리할 때 에러 가설 4 : 문자집합 문제 3. 문제의 정확한 원인 4. 해결 5. Sequel Ace에서는 에러가 나지 않았던 이유

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;
    }
    
    ...