Spring Boot Jdbc Bulk Update 삽질기 (Feat. VALUES Parsing Problem)

본문

회사에서 Excel을 이용하여 다량의 계정을 생성하는 태스크를 진행하게 되었습니다.

다량의 데이터를 저장하기 위해 JdbcTemplate의 bulkUpdate를 사용했습니다.

기본 구성

예시로 사용하기 위한 Entity

/**
 * 계정 엔티티
 */
@Entity
class Account(
    val displayName: String,
    val email: String,
    val value: String,
) {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Long = 0

    @OneToMany(mappedBy = "account")
    val infos: MutableList<AccountInfo> = mutableListOf()

    fun addInfo(info: AccountInfo) {
        infos.add(info)
    }
}

• bulkUpdate를 사용하기 전 Query

insert into account (display_name, email, value) values ('test1', 'test1@example.com', '메모메모')
insert into account (display_name, email, value) values ('test2', 'test2@example.com', '메모메모')
insert into account (display_name, email, value) values ('test3', 'test3@example.com', '메모메모')
insert into account (display_name, email, value) values ('test4', 'test4@example.com', '메모메모')
insert into account (display_name, email, value) values ('test5', 'test5@example.com', '메모메모')
insert into account (display_name, email, value) values ('test6', 'test6@example.com', '메모메모')
insert into account (display_name, email, value) values ('test7', 'test7@example.com', '메모메모')
insert into account (display_name, email, value) values ('test8', 'test8@example.com', '메모메모')

• bulkUpdate를 사용하여 얻고 싶은 기대 쿼리

insert into account (display_name, email, value) values ('test1', 'test1@example.com', '메모메모'), ('test2', 'test2@example.com', '메모메모'), ('test3', 'test3@example.com', '메모메모'), ('test4', 'test4@example.com', '메모메모'), ('test5', 'test5@example.com', '메모메모'), ('test6', 'test6@example.com', '메모메모'), ('test7', 'test7@example.com', '메모메모'), ('test8', 'test8@example.com', '메모메모')

Batch 를 이용하기

위의 기대하는 쿼리를 이용하기 위해서는 여러 가지 방법이 있었는데.

1. JPA Batch Insert
2. JDBC Template

우리에게 익숙한 JPA 를 이용하여 Bulk 연산을 진행하려 했으나 문제가 있었으니..

현재 Entity에서 Primary Key 를 생성하는 방식을 DB에 위임하는 방식인 IDENTIFY를 이용하고 있었습니다.

Hibernate 공식 문서에서는 @GeneratedValue(strategy = GenerationType.IDENTITY) 방식의 경우 Batch Insert를 지원하지 않습니다. 라는 언급이 있었습니다.

Hibernate User Guide: 12.2. Session batching

Hibernate disables insert batching at the JDBC level transparently if you use an identity identifier generator.

그래서 결국 2번 방법인 Jdbc Template 을 이용하여 Bulk 연산을 진행하게 되는데

Jdbc Template을 이용한 Bulk 연산은 생각보다 어렵지는 않았습니다.

JdbcTemplate을 이용하여 작성한 코드는 아래와 같습니다.

@Repository
class AccountRepository(
    private val jdbcTemplate: JdbcTemplate,
) {
    fun bulkInsert(accounts: List<Account>) {
        jdbcTemplate.batchUpdate(
            "INSERT INTO account (display_name, email, value) VALUES (?, ?, ?)",
            object : BatchPreparedStatementSetter {
                override fun setValues(ps: PreparedStatement, i: Int) {
                    ps.setString(1, accounts[i].displayName)
                    ps.setString(2, accounts[i].email)
                    ps.setString(3, accounts[i].value)
                }

                override fun getBatchSize(): Int = accounts.size
            }
        )
    }
}

이후 application.yaml 의 spring.datasource.url 에 파라미터( rewriteBatchedStatements=true ) 만 추가해주면 되었습니다.

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:53306/account?rewriteBatchedStatements=true
    username: root
    password: 1234

  jpa:
    hibernate:
      ddl-auto: create
    generate-ddl: true
    show-sql: true
    properties:
      hibernate:
        format_sql: true

이제 bulk 연산을 요청했는데 아니나 다를까 이전에 수행했던 쿼리와 동일하게 나오게 되는데..

INSERT INTO account (display_name, email, value) VALUES ('test1', 'test1@example.com', '메모메모');
2024-02-14T10:38:58.911463Z       170 Query     INSERT INTO account (display_name, email, value) VALUES ('test2', 'test2@example.com', '메모메모');
2024-02-14T10:38:58.911793Z       170 Query     INSERT INTO account (display_name, email, value) VALUES ('test3', 'test3@example.com', '메모메모');
2024-02-14T10:38:58.911925Z       170 Query     INSERT INTO account (display_name, email, value) VALUES ('test4', 'test4@example.com', '메모메모');
2024-02-14T10:38:58.912096Z       170 Query     INSERT INTO account (display_name, email, value) VALUES ('test5', 'test5@example.com', '메모메모');
2024-02-14T10:38:58.912273Z       170 Query     INSERT INTO account (display_name, email, value) VALUES ('test6', 'test6@example.com', '메모메모');

이유 모름

원인을 알 수 없어 공식문서, 구글, 임시 테이블을 만들어 디버깅도 해보고 이런 저런 삽질을 좀 하다가 어느 상황에서 bulk 연산이 안되는지를 찾게 되었고 그 이유로는

Table 에 속한 Column 의 이름에 value 라는 단어가 포함 되어 있으면 해당 bulk 연산이 동작을 하지 않는 것을 알게 되었고

그래서 한참 디버깅을 하게 되었고 jdbcTemplate에서 작성한 쿼리문을 파싱하는 부분인 mysql-connector-j 의 QueryInfo 까지 내려오게 되었는데

문제의 QueryInfo

QueryInfo Class 는 아래와 같이 구성되어 있습니다.

QueryInfo 은 생성자에서 JdbcTemplate에서 작성한 string 형태의 sql 구문을 받아 쿼리 또는 다중 쿼리를 분석하여 객체로 구성 하는 역활

package com.mysql.cj;

import com.mysql.cj.conf.PropertyKey;
import com.mysql.cj.exceptions.ExceptionFactory;
import com.mysql.cj.exceptions.WrongArgumentException;
import com.mysql.cj.util.SearchMode;
import com.mysql.cj.util.StringInspector;
import com.mysql.cj.util.StringUtils;
import java.nio.ByteBuffer;
import java.util.ArrayList;

/**
     * Constructs a {@link QueryInfo} object for the given query or multi-query. The parsed result of this query allows to determine the location of the
     * placeholders, the query static parts and whether this query can be rewritten as a multi-values clause query.
     * 
     * @param sql
     *            the query SQL string to parse and analyze
     * @param session
     *            the {@link Session} under which the query analysis must be done.
     * @param encoding
     *            the characters encoding to use when extracting the query static parts as byte arrays.
     */

public class QueryInfo {
    ........

    // 생성자
    //이때 생성자의 sql에는 우리가 작성한 query가 담겨져 있음 ( `INSERT INTO account (display_name, email, value) VALUES (?, ?, ?)` )
    public QueryInfo(String sql, Session session, String encoding) {
            ....
    }

    .....
}

QueryInfo class 코드 안에 아래와 같이 VALUE 를 찾는 분기 코드를 찾게 되었는데

아래 코드에서 string 으로 작성된 sql 구문에서 현재 단어의 위치를 하나씩 증가 하며 VALUE 라는 키워드를 찾는 과정이 있었습니다.

public QueryInfo(String sql, Session session, String encoding) {
        ..............
    else if (valuesClauseBegin == -1 && strInspector.matchesIgnoreCase("VALUE") != -1) {
        strInspector.incrementPosition("VALUE".length());
        if (strInspector.matchesIgnoreCase("S") != -1) {
            strInspector.incrementPosition();
        }
        ...........
}

디버깅을 하며 설마 작성된 sql의 column을 찾는건 아니겠지? 라는 생각을 하며 디버깅을 진행하게 되었고 의심은 점점 확신으로 변하고 있었습니다.

위의 코드에서 VALUE 를 찾는 코드인 strInspector.matchesIgnoreCase(”VALUE”) 를 들여다 보면 코드는 아래와 같습니다. 아래 코드에서 실질적으로 sql 에서 VALUE 의 존재 유무를 찾는 구문은 StringUtils.regionMatchesIgnoreCase(this.source, this.pos, toMatch) 인데

여기서 this.source 는 작성한 sql query, this.pos는 sql query의 문자열 단위의 현재 위치 toMatch 는 찾고자 하는 단어 입니다.

public int matchesIgnoreCase(String toMatch) {
        if (toMatch == null) {
            return -1;
        } else {
            int toMatchLength = toMatch.length();
            int localStopAt = this.srcLen - toMatchLength + 1;
            if (localStopAt > this.stopAt) {
                localStopAt = this.stopAt;
            }

            if (this.pos < localStopAt && toMatchLength != 0) {
                char firstCharOfToMatchUc = Character.toUpperCase(toMatch.charAt(0));
                char firstCharOfToMatchLc = Character.toLowerCase(toMatch.charAt(0));
                return StringUtils.isCharEqualIgnoreCase(this.getChar(), firstCharOfToMatchUc, firstCharOfToMatchLc) && StringUtils.regionMatchesIgnoreCase(this.source, this.pos, toMatch) ? this.pos + toMatchLength : -1;
            } else {
                return -1;
            }
        }
    }

디버깅을 하다 보니 자꾸 this.pos가 42에서 StringUtils.regionMatchesIgnoreCase(this.source, this.pos, toMatch) 의 결과가 true로 반환이 되고 있었습니다.

INSERT INTO account (display_name, email, value) VALUES (?, ?, ?) 의 42번째는 column 으로 사용 했던 value 였습니다.)

해당 문제를 구글링 하다보니 이미 해당 문제를 제기한 개발자가 몇몇 있었고 이미 해당 문제는 해결이 되었다는 답변이 달려있는 글을 찾게 되었습니다. (8.0.33 버전에서 해결 되었다 함 )https://bugs.mysql.com/bug.php?id=107577

수정된 commit (https://github.com/mysql/mysql-connector-j/commit/7b8592546b797056ca36b42586c218bd3394cdfa)

해결 방법을 찾아보자

디버깅 하면서 의심 했던 부분이 확신 되면서 여러 해결 방안을 찾게 되는데

1. table의 column name을 변경하자
2. 해당 library의 version을 올리자.

1번의 table의 column name을 변경하자는 현실적으로 어려웠습니다. 이미 table에 상당히 많은 데이터가 적재 되어 있었고 코드에서도 해당 field를 여기 저기에서 사용하고 있어 변경이 쉽지 않았습니다.

그래서 2번의 방법으로 진행하기 위해 현재 spring boot project 에서 사용하고 있는 library version을 확인하게 되는데

회사에서는 gcp sql을 이용하고 있었고 spring 과 gcp sql을 연결하기 위한 라이브러리로 [cloud-sql-jdbc-socket-factory](https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory) v1.15.2를 사용하고 있었습니다.

그런데 이미 우리 프로젝트에서는 cloud-sql-jdbc-socket-factory 를 최신 버전으로 사용하고 있었으며 최신 버전에서 문제가 있는 library(mysql-connector-java:8.0.28)를 사용 하고 있어 해당 라이브러리만을 이용해서는 해결할 수 없었고 exclude 를 이용하여 cloud-sql-jdbc-socket-factory 에서 mysql-connector-java를 제외 한 후 버그가 해결된 mysql-connector-java를 사용하도록 진행 했습니다.

implementation("com.google.cloud.sql:mysql-socket-factory-connector-j-8:1.15.2")
        exclude(group = "com.mysql", module = "mysql-connector-j")
}
implementation("com.mysql:mysql-connector-j:8.0.33")

위의 방법으로 수정 한 후 다시 쿼리를 요청 하니 의도한대로 쿼리가 동작하게 되었습니다

2024-02-15T01:33:07.220054Z       520 Query     INSERT INTO account (display_name, email, value) VALUES ('test1', 'test1@example.com', '메모메모'),('test2', 'test2@example.com', '메모메모'),('test3', 'test3@example.com', '메모메모'),('test4', 'test4@example.com', '메모메모'),('test5', 'test5@example.com', '메모메모'),('test6', 'test6@example.com', '메모메모'),('test7', 'test7@example.com', '메모메모'),('test8', 'test8@example.com', '메모메모'),('test9', 'test9@example.com', '메모메모'),('test10', 'test10@example.com', '메모메모')

번외

위의 방식으로 문제를 해결하고 끝낼 수 있었으나 다른 개발자가 해당 문제를 겪지 않게 하고 싶었고 우리가 사용하고 있는 최신 버전의 cloud-sql-jdbc-socket-factory가 버그 수정된 mysql-connector-j 버전을 사용하면 되지 않을까 라는 생각을 하게 되었습니다.

그래서 cloud-sql-jdbc-socket-factory 의 github에 issue를 남기게 되었고 (이슈 링크) 컨트리뷰터가 제가 남긴 이슈를 확인하여 해당 내용을 반영해줬습니다.

약간 아쉬운점은 사실 pom.xml만 수정하면 되는거 였는데 생각만 하다 오픈소스에 기여할 수 있는 첫 기회를 놓친게 아쉽긴한데 그래도 좋은 경험을 한것 같습니다.