'mysql' 태그의 글 목록

mysql

728x90

[mysql] Incorrect string value: '\xF0\x9F\x98\x99 "...' 2022.04.04
[spring, mysql] java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ....... 2021.11.09
[mysql] Error Code: 1215. Cannot add foreign key constraint 2021.05.20
[weblogic, mysql] weblogic에서 배포시 net.sf.log4jdbc.sql.jdbcapi.DriverSpy 에러 2020.07.21
[tomcat] memory leak 에러 2019.11.05

728x90

[mysql] Incorrect string value: '\xF0\x9F\x98\x99 "...'

2022. 4. 4. 11:27

728x90

mysql에 데이터를 insert하는 과정에서 발생된 에러다.

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x99 "...' for column 'DSC' at row 1

원인은 '😙'와 같은 이모지를 기존에 사용하던 DB에 insert하려 했다는 문제다.

웹에서 검색한 대부분은 답변은 character set을 변경하라는 내용이다.

MySQL utf8에서 utf8mb4로 마이그레이션 하기

Emoji같은 글자들은 utf8 인코딩 되는경우 글자당 최대 4bytes까지 필요하다. 하지만 기존 MySQL의 utf8 필드의 경우 글자당 최대 3bytes 까지만 지원하는 한계점이 있었다. 때문에 MySql database에서 utf8mb4

www.letmecompile.com

물론 변경하는 것이 가장 베스트의 선택이다.

그러나 나는 이미 운영중인 서버이기 때문에 함부로 선택할 수 없는 문제였고,

이런 이모지가 꼭 필요한 요소인가를 봤을때 아니라고 생각했다.

그래서 이모지를 제거 또는 변환하는 방법을 사용하기로 했다.

[Java] Emoji 문자 제거

현재 웹에서 제공하는 이모지 [ win + . ] 또는 모바일 단말기에서 제공하는 특수문자들이 유니코드이지만 DB에 저장 시 지원하지 않는 경우 에러를 유발하게 됩니다. 😊 정확하게는 uft8mb4를 지원

string.tistory.com

이렇게 문제가 해결되는 줄 알았다. (분명 로컬에서는 잘 돌았거든..)

그런데 tomcat 서버에서 실행될 때 문제가 발생 했다.

좀 큰 덩어리의 스케줄로 돌아가는 부분이기 때문에 자세한 검토는 하지 못했지만

짐작하기로는 해당 라이브러리가 내 운영서버 환경에서는 적용되지 않는 것이 아닌가 짐작하고 있다.

아쉽지만 운영서버에서 발생되는 문제의 원인은 아직 확인하지 못했다.

다만, 항상 발생되는 문제가 아니고, 중요한 요소가 아닌지라 이것으로 인해 에러가 발생되면 패스하도록

예외처리를 통해 피하고 있는 중이다.

728x90

저작자표시

'코딩 삽질' 카테고리의 다른 글

docker 컨테이너의 환경설정 (0)	2022.04.05
docker 컨테이너로 tomcat 실행하기 (0)	2022.04.04
[spring, jpa] org.springframework.beans.factory.UnsatisfiedDependencyExceptio (0)	2022.04.04
[java, spring] mybatis의 foreach 셈플 (0)	2022.03.01
[java] ArrayList.remove(Integer index) 사용시 이상 (0)	2022.01.17

[spring, mysql] java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near .......

2021. 11. 9. 16:33

728x90

mysql 사용시 종종 겪을 수 있는 문제입니다.

해당 문제는 문법적인 에러이고, 아래 에러문에서 해당되는 문제는 condition 이라는 컬럼명을 사용했기 때문입니다.

* 에러 전문

20211108 14:20:51.623 [http-nio-8091-exec-6] ERROR j.sqltiming - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)

1. insert into table_name (crt_by, crt_dt, del_by, del_dt, del_yn, upd_by, upd_dt, condition, menu_seq, model_nm) values (0, '11/08/2021 14:20:51.585', NULL, NULL, 'N', 0, '11/08/2021 14:20:51.585', 'Y', 57, 'AAAA')

{FAILED after 16 msec}

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition, menu_seq, model_nm) values (0, '2021-11-08 14:20:51.585', null, null,' at line 1

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)

at net.sf.log4jdbc.sql.jdbcapi.PreparedStatementSpy.executeUpdate(PreparedStatementSpy.java:1080)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)

at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)

at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:43)

at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3200)

at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3806)

at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:84)

at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:645)

at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282)

at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263)

at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317)

at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:330)

at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:287)

at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:193)

at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:123)

at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:185)

at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:128)

at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:55)

at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:93)

at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:720)

at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:706)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362)

at com.sun.proxy.$Proxy89.persist(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:311)

at com.sun.proxy.$Proxy89.persist(Unknown Source)

at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:557)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:289)

at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)

at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)

at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:524)

at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285)

at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:531)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:156)

at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:131)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)

at com.sun.proxy.$Proxy154.save(Unknown Source)

at packageName.front.webtools.service.WebtoolsService.insert(WebtoolsService.java:146)

at packageName.front.webtools.web.Controller.insert(Controller.java:71)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)

at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)

at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)

at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684)

at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Unknown Source)

20211108 14:20:51.625 [http-nio-8091-exec-6] WARN o.h.e.j.s.SqlExceptionHelper - SQL Error: 1064, SQLState: 42000

20211108 14:20:51.625 [http-nio-8091-exec-6] ERROR o.h.e.j.s.SqlExceptionHelper - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition, menu_seq, model_nm) values (0, '2021-11-08 14:20:51.585', null, null,' at line 1

20211108 14:20:51.646 [http-nio-8091-exec-6] ERROR c.d.b.c.a.ExceptionControllerAdvice -

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)

at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)

at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)

at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)

at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)