MyBatis + Oracle 에서 insert into ~ select 오류

데이터 추가 수정이 있을 때 히스토리를 남겨야 한다거나, 무언가 설정할 때 기본 값들을 복사해서 넣어주거나 할 때 일괄 insert 하는 쿼리문을 작성해서 사용하곤 한다. 개발 중 아주 단순한 쿼리인데 구문 오류가 났다. 어디 콤마를 잘못 찍었나 했는데 아무리 눈씻고 봐도 잘못된 곳이 없다. 심지어 SQL Developer 에 복사해서 실행하면 잘만 된다. 지금까지 10년 넘게 개발하면서 이런 적이 한 번도 없었는데 뭐지 이건. insert into ~ values 는 아무 이상이 없었고, insert into ~ select 할 때만 발생했다. 어딘가 설정상의 문제일 것 같아서 검색을 해보았다.

 

아주 단순한 insert into ~ select 구문. 이게 에러날 게 뭐 있어...?!

INSERT INTO XXX ( ... ) SELECT ... FROM YYY WHERE ~

 

java.sql.SQLSyntaxErrorException: ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at net.sf.log4jdbc.sql.jdbcapi.PreparedStatementSpy.execute(PreparedStatementSpy.java:443)
	at jdk.internal.reflect.GeneratedMethodAccessor87.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at jdk.proxy3/jdk.proxy3.$Proxy108.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:52)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:194)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:181)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:176)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
	at jdk.proxy2/jdk.proxy2.$Proxy88.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:264)

 

MyBatis 와 관련되어 몇 개의 블로그를 봤는데, sql 매퍼에서 <insert> 태그를 쓰지 말고 <update> 태그를 쓰라는 것이었다. 해보니까 에러 안나고 되긴 하는데, 상식적으로 이해가 되지 않았다. 무슨 상관이지 그게? 모든 DBMS 벤더들은 자바 어플리케이션용 드라이버를 제공할 때는 무조건 JDBC API 를 구현해야 한다. 그 틀을 벗어날 수 없다. 근데 JDBC API 는 insert 나 update 를 구분하지 않는다. 그냥 Statement 의 execute 나 executeUpdate 를 쓰는데, insert 든 update 든 delete 든 둘 중 아무 메서드나 써도 상관없다. MyBatis 도 쿼리 xml 작성할 때 <insert>, <update>, <delete> 태그를 쓴다고 해서 실제로 그게 insert 문인지 update 문인지 delete 문인지 체크해서 검사하거나 그러지도 않는다. 그런데 태그 차이로 이런 에러가 난다는 게 도무지 이해가 되지 않았다. 진짜 범인은 다른 곳에 있는 게 분명했다! 일단 오늘 할 일을 끝내놓고 나서 갓 오브 워 공략이나 볼까 했는데, 삽질하느라 열 좀 받고나니 이 자식부터 족쳐봐야겠다는 생각이 들었다. 이 녀석 잡을 생각에 후딱 일을 끝내고 본격적으로 디버깅을 해봤다.

 

개인적인 생각으론, Oracle 의 autoGeneratedKeys 관련한 PreparedStatement 구현 상의 제한인 것 같다는 결론이다. MyBatis 가 Proxy 를 하도 많이 써서 인터페이스나 추상클래스도 많아 소스만 보며 따라가기는 힘들다. 직접 브레이크 포인트를 찍어가면서 디버깅을 해야 실제 수행되는 소스를 따라갈 수 있는데, MyBatis 의 PreparedStatementHandler 에서 아래와 같은 부분을 만났다.

 

@Override
protected Statement instantiateStatement(Connection connection) throws SQLException {
  String sql = boundSql.getSql();
  if (mappedStatement.getKeyGenerator() instanceof Jdbc3KeyGenerator) {
    String[] keyColumnNames = mappedStatement.getKeyColumns();
    if (keyColumnNames == null) {
      return connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
    } else {
      return connection.prepareStatement(sql, keyColumnNames);
    }
  } else if (mappedStatement.getResultSetType() == ResultSetType.DEFAULT) {
    return connection.prepareStatement(sql);
  } else {
    return connection.prepareStatement(sql, mappedStatement.getResultSetType().getValue(), ResultSet.CONCUR_READ_ONLY);
  }
}

 

디버깅하는 중에 보니까 첫번째 if 에 걸려들었다. 이건 뭐지, 키 제네레이터? 이름에서 왠지 MySQL 에서 auto_increment 컬럼 있는 테이블에 insert 할 때 쓰는 useGeneratedKeys 옵션이 생각났다. 뭔가해서 자료를 찾아보니 그게 맞았다. 사실 그와 관련하여 Connection 의 prepareStatement 메서드가 하나만 있는 게 아니라 두번째 파라미터가 있는 녀석들이 있다는 것도 이번에 처음 알았다.

 

나는 개발 일을 처음 시작할 때부터 Oracle 쓰는 프로젝트만 해왔고, 최근 MySQL 몇 년 써보고 MSSQL 은 올 해 처음 써봤다. 개발팀 성향에 따라 DB 설계 방식이 다른 면이 있어서, PK 를 여러 개 만들어서 쓰는 곳이 있는가 하면, 무조건 자동증가 채번 값을 유일한 PK 로 만들어 쓰는 곳들도 있는데, 난 주로 PK 를 여러 개 만드는 솔루션 회사에서 일해왔다. Oracle이다보니 sequence 를 쓰고, NEXTVAL 로 채번하는 매퍼를 별도로 만들어서 그것을 사용했다. 예를 들어 사원 번호나 주문 번호 혹은 상품 번호 같은 것들은 코드 자체가 아무 의미없이 순번인 경우도 있지만, 고객사에 따라 자릿수를 특정 갯수로 맞춰 달라든가, 날짜나 어떤 의미를 담고 있는 코드 값을 접두어로 붙여달라는 곳들이 꼭 있어서, 쿼리에 직접 sequence 를 썼다가는 변경이 발생할 경우 손댈 곳이 많아질 수 있다. 데이터 insert 하는 곳이 꼭 한 군데라는 법은 없으니까. 그렇기 때문에 sequence 를 쓸 때는 꼭 별도 매퍼로 만들어 그 값을 리턴하는 메서드를 만들 후 그것을 가져다 쓰도록 만드는 것이 좋다. 그런 환경에서 일해 왔으니 MyBatis 를 쓰면서도 useGeneratedKeys 옵션을 써본 적이 없다. 사실 아직도 어색하다...

 

MySQL 을 쓰는 곳에서 일했을 때는, PK 가 서너 개 이상 많아질 경우에는 auto_increment 컬럼을 유일 PK 로 설계하는 그런 곳이었다. MySQL 은 sequence 가 없다보니 PK가 auto_increment 컬럼인 부모 테이블에 insert 하면서 자식 테이블에도 동시에 insert 할 경우 useGeneratedKeys 옵션을 쓸 수밖에 없다. 근데 이상하다. 지금 난 Oracle 쓰고 있는데. useGeneratedKeys 옵션도 쓴 적 없고..? 혹시나 해서 다시 <insert> 태그로 바꾼 다음 useGeneratedKeys 속성을 false 로 넣어주고 해보니까 이상없이 잘 되었다. 흠 근데 이거 디폴트가 true 란 얘긴가?

 

아 잠깐, 이거 기본 configuration 으로 설정되어 있나보다, 하는 생각이 문득 들어서 MyBatis config xml 파일을 보니까 역시나 useGeneratedKeys 옵션이 true 로 설정되어 있었다. 그래서 false 로 바꾸고 다시 해보니까 <insert> 태그로 insert into ~ select 를 해도 문제없이 잘 되었다. 이런 건 놓치기 쉽단 말이지. 기본 설정은 한 번 해놓으면 몇 년씩 그대로 아무 생각없이 복붙해서 쓰니까. 아마 MyBatis 는 <insert> 태그일 때만 useGeneratedKeys 기본 설정을 자동으로 적용시키는 것 같다. 재미있는 것은, 혹시나 해서 <update> 태그로 하고 수동으로 useGeneratedKeys 속성을 true 로 해줬더니 실제로 적용이 되어버려서 에러가 났다. 야 이거 update 일 때는 적용 안되게 막아야지 ㅋㅋ

<configuration>
    <settings>
        <setting name="cacheEnabled" value="false" />
        <setting name="lazyLoadingEnabled" value="true" />
        <setting name="aggressiveLazyLoading" value="true" />
        <setting name="useGeneratedKeys" value="false" />
        <setting name="defaultExecutorType" value="REUSE" />
        <setting name="mapUnderscoreToCamelCase" value="true" />
        <setting name="callSettersOnNulls" value="true"/>
        <setting name="jdbcTypeForNull" value="VARCHAR" /><!-- NULL, OTHER, VARCHAR -->
    </settings>
</configuration>

 

근데 아직도 의문점이 있다. 이 옵션이 왜 단 건 insert 는 문제 없는데 여러 건 동시 insert 할 때만 문제가 되는 걸까? API 를 다시 찾아봤는데 문제될 것이 없었다. 왜냐하면, PreparedStatment 의 getGeneratedKeys 메서드는 리턴 타입이 ResultSet 이기 때문이다. 당연히 여러 건을 리턴할 수 있다. 근데 왜 에러나지? 간단히 테스트 코드를 작성해서 Oracle 와 MariaDB 로 비교해보았다. MariaDB 는 여러 건 동시 insert 할 경우에도 auto_increment 컬럼 값을 잘 가져왔다. Oracle 일 때만 문제가 되는 것 같다. MSSQL 은 안해봤지만 왠지 걔도 문제 없을 것 같은 느낌이 든다.

 

DB 벤더라면, JDBC API 에 정의된 기능이 잘 수행되도록 드라이버를 만들어야 한다. 그게 개발자들과의 약속 아닌가? 근데 Oracle 이 그게 안된다는 건 계약 위반이다! API 명세를 따라야지 왜 지원을 안해주는 건데...아마 애초에 DB 설계가 그렇기 때문이 아닐까 싶기도 하다. Oracle 은  sequence 객체의 CURRVAL 속성으로 현재 값을 가져오는 방식이다. 여러 건 동시 insert 하면서 NEXTVAL 사용 가능하지만, 이후 CURRVAL 을 쓰면 마지막 채번된 값 하나만 가져온다. 그 외 insert into ~ values ~ returning ~ into ~ 구문도 있으나, 이 역시 단 건 insert 전용이다. 일단 현재까지 알아본 바로는, Oracle 은 복수 건 insert 시 자동 증가 채번 값을 여러 개 가져오는 기능 자체가 없는 것 같다. 그렇다면 JDBC API 의 getGeneratedKeys 메서드도 당연히 명세대로 제대로 구현할 수 없다. 그래, 명세대로 정확히 구현할 수 없는 것을 그냥 되는대로 할 수 있는 데까지만 구현해 놓느니, 차라리 에러를 내버리는 게 낫지. 정 필요한 경우라면 개발자들이 뭔가 다른 방법을 찾을테니까. MySQL(MariaDB) 은 SELECT LAST_INSERT_ID() 하면 마지막 인서트한 auto_incerement 컬럼 값을 가져오고, 만약 복수 건 insert 한 후라면 그 중 가장 먼저 insert 된 행에서 그 값을 가져온다. 그래서 어떻게든 API 를 구현할 수 있었던 것 같다. 여기까지 나름대로의 결론. 더 이상은 알 수 없음.

 

결론. MyBatis 기본 configuration 설정에서 useGeneratedKeys 옵션을 true 로 하지 말 것. 꼭 필요할 때만 <insert> 태그에 true 로 속성 값을 수동으로 지정해주자.