전자정부프레임워크에는 EgovIdGnrService 라는 id를 생성해 주는 기능이 있다.

가끔 이 기능을 사용할때 다음과 같은 에러가 발생할 수 있다.(mysql)

 [IDGeneration Service] ORDER_ID 테이블을 업데이트하는 예외가 발생했습니다.

다음의 update query 문에서 발생하는 에러이다.

UPDATE COMTECOPSEQ SET next_id = ? WHERE table_name = ?

에러 로그를 보면

egovframework.rte.fdl.idgnr.impl.EgovTableIdGnrServiceImpl

클래스 223 줄에서 exception이 찍히는데, 에러 내용만 봐서는 어떤 에러인지 알기 힘들다.

전자정부프레임워크 사이트에 이 문제에 대한 답변이 하나 있는데

COMTECOPSEQ table에 lock이 걸려서 발생하는 문제이니 lock을 해결하라고 돼 있다
(https://open.egovframe.go.kr/cop/bbs/selectBoardArticle.do?bbsId=BBSMSTR_000000000013&nttId=18793)


사용자 삽입 이미지

전자정부프레임워크 답변



SELECT ORDER_ID FROM COMTECOPSEQ WHERE = ?

쪽에서 exception이 발생하는 경우는 DataAccessException 을 로깅해 주기 때문에

바로 디비 에러 사항을 확인할 수 있는데,

update에서는 DataAccessException 을 FdlException 로 throw 하기 때문에 디비에서 발생한 에러 내용을 바로 확인할 수 없다.

디버깅 해서 update시 발생한 DataAccessException 로그를 확인해 보면 아래와 같다

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorizedSQLExceptionfor SQL [UPDATE COMTECOPSEQ SET next_id = ? WHERE table_name = ?]; SQL state [HY000]; error code [1665]; Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.; nested exception isjava.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
      at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:909)
      at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:970)
      at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:980)
      at egovframework.rte.fdl.idgnr.impl.EgovTableIdGnrServiceImpl$1.doInTransaction(EgovTableIdGnrServiceImpl.java:218)
      at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
      at egovframework.rte.fdl.idgnr.impl.EgovTableIdGnrServiceImpl.allocateIdBlock(EgovTableIdGnrServiceImpl.java:160)
      at egovframework.rte.fdl.idgnr.impl.EgovTableIdGnrServiceImpl.allocateLongIdBlock(EgovTableIdGnrServiceImpl.java:257)
      at egovframework.rte.fdl.idgnr.impl.AbstractDataBlockIdGnrService.getNextLongIdInner(AbstractDataBlockIdGnrService.java:120)
      at egovframework.rte.fdl.idgnr.impl.AbstractIdGnrService.getNextLongIdChecked(AbstractIdGnrService.java:151)
      at egovframework.rte.fdl.idgnr.impl.AbstractIdGnrService.getNextIntegerId(AbstractIdGnrService.java:203)
      at
      at
      at
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:606)
      at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
      at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
      at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:747)
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:676)
      at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
      at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
      at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
      at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
      at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
      at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
      at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
      at egovframework.burgerking.api.comm.filter.LogSessionIdFilter.doFilter(LogSessionIdFilter.java:32)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
      at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
      at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:958)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:452)
      at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1087)
      at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
      at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
      at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
      at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)
      at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
      at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
      at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:916)
      at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:909)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
      ... 57 more








에러는 transaction isolation level이 READ_COMMITTED (or READ_UNCOMMITTED)인데 BINLOG_FORMAT 이 statement 이기 때문에 발생하는 오류다.  


egovframework.rte.fdl.idgnr.impl.EgovTableIdGnrServiceImpl  클래스 115줄에 보면

transaction isolation level을 강제로 READ_COMMITTED로 변경하는 코드가 있다.
 
this.transactionTemplate.setIsolationLevelName("ISOLATION_READ_COMMITTED");



이 코드에 있는 transaction isolation level을 변경하거나,

BINLOG를 사용하지 않거나

BINLOG_FORMAT을 mixed로 변경하면  [IDGeneration Service] ORDER_ID 테이블을 업데이트하는 예외가 발생했습니다.


에러는 해결할 수 있다.



P.S
 * 다른 이유로 에러나는 경우도 있을 수 있으나 경험해 보지 못해, 우선 경험하고 해결한 것만 포스팅
 * select 쪽 에러시에는 DataAccessException을 로깅하는데, update에는 왜 로깅하지 않을까?? 이 로깅만 돼 있었어도 고생안하고 바로 알았을텐데....






















Posted by incree

2017/08/22 14:42 2017/08/22 14:42
,
Response
0 Trackbacks , 0 Comments
RSS :
http://www.incree.com/tc/incree/rss/response/382

Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다

« Previous : 1 : 2 : 3 : 4 : 5 : 6 : ... 133 : Next »

Notices

Archives

Authors

  1. incree

Recent Trackbacks

Calendar

«   2017/12   »
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31            

Site Stats

Total hits:
436147
Today:
3
Yesterday:
155