java.sql.SQLException: Incorrect string value: '\xAE Data...' for column 'XWR_PATCH' at row 1

Hi,

I’ve gone through quite a lot of work to move our XWiki 5.0 installation on JDK 6, Tomcat 7 and MySQL 5.6 up to XWiki 14.10.2 on JDK 11, Tomcat 9.0 against MySQL 8.0.

I took the following steps to get there taking a database backup between each step:-

  1. XWiki 5.0 on JDK 6u45, Tomcat 7.0.109 and MySQL 5.6.10 with Connector/J 5.1.23
  2. XWiki 8.0 on JDK 7u80, Tomcat 8.0.53 and MySQL 5.6.10 with Connector/J 5.1.23
  3. Moved to MySQL 5.7 and updated charset from utf8 → utf8mb4 and collation from utf8_bin → utf8mb4_bin as per MySQL Installation (XWiki.org)
  4. XWiki 8.2.2 on JDK 8u351, Tomcat 8.0.53 and MySQL 5.7.40 with Connector/J 5.1.23
  5. XWiki 14.10.2 on JDK 11.0.17, Tomcat 9.0.70 and MySQL 8.0.31 with Connector/J 8.0.31

The above was all done on a Windows 2019 server.

After this process I was able to view my pages in the wiki on a significantly newer version of XWiki. However, I’ve noticed that when I edit certain pages which have special characters like the “curly quotes” that Microsoft Word likes to put in, or registered trademark symbols the Wiki is failing to save, copy or move the document.

The characters were accepted before, so I’m not sure what to do. I’ve verified that the new database is utf8mb4 and that includes all the tables. What could be stopping it from accepting these characters? The stack trace is below:

2023-01-10 15:39:27,808 [http-nio-8080-exec-1 - http://ti-doc-01:8080/xwiki/bin/edit/OTBO_ReleaseNotes/OTBO_1100/WebHome?editor=wysiwyg] WARN  o.h.e.j.s.SqlExceptionHelper   - SQL Error: 1366, SQLState: HY000 
2023-01-10 15:39:27,808 [http-nio-8080-exec-1 - http://ti-doc-01:8080/xwiki/bin/edit/OTBO_ReleaseNotes/OTBO_1100/WebHome?editor=wysiwyg] ERROR o.h.e.j.s.SqlExceptionHelper   - Incorrect string value: '\xAE Data...' for column 'XWR_PATCH' at row 1 
2023-01-10 15:39:27,824 [http-nio-8080-exec-1 - http://ti-doc-01:8080/xwiki/bin/edit/OTBO_ReleaseNotes/OTBO_1100/WebHome?editor=wysiwyg] WARN  c.x.x.w.XWikiAction            - Uncaught exception: Error number 3201 in 3: Exception while saving document xwiki:OTBO_ReleaseNotes.OTBO_1100.WebHome 
com.xpn.xwiki.XWikiException: Error number 3201 in 3: Exception while saving document xwiki:OTBO_ReleaseNotes.OTBO_1100.WebHome
	at com.xpn.xwiki.store.XWikiHibernateStore.saveXWikiDoc(XWikiHibernateStore.java:729)
	at com.xpn.xwiki.store.XWikiCacheStore.saveXWikiDoc(XWikiCacheStore.java:259)
	at com.xpn.xwiki.store.XWikiCacheStore.saveXWikiDoc(XWikiCacheStore.java:219)
	at com.xpn.xwiki.XWiki.saveDocument(XWiki.java:2096)
	at com.xpn.xwiki.web.SaveAction.save(SaveAction.java:298)
	at com.xpn.xwiki.web.SaveAction.action(SaveAction.java:524)
	at com.xpn.xwiki.web.XWikiAction.execute(XWikiAction.java:597)
	at com.xpn.xwiki.web.XWikiAction.execute(XWikiAction.java:300)
	at com.xpn.xwiki.web.LegacyActionServlet.service(LegacyActionServlet.java:114)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:779)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at com.xpn.xwiki.web.ActionFilter.doFilter(ActionFilter.java:122)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.xwiki.wysiwyg.filter.ConversionFilter.doFilter(ConversionFilter.java:61)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	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.xwiki.container.servlet.filters.internal.SavedRequestRestorerFilter.doFilter(SavedRequestRestorerFilter.java:208)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.xwiki.container.servlet.filters.internal.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:111)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.xwiki.resource.servlet.RoutingFilter.doFilter(RoutingFilter.java:132)
	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.ApplicationDispatcher.invoke(ApplicationDispatcher.java:711)
	at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:459)
	at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:385)
	at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:313)
	at com.xpn.xwiki.web.ActionFilter.doFilter(ActionFilter.java:112)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.xwiki.wysiwyg.filter.ConversionFilter.doFilter(ConversionFilter.java:61)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	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.xwiki.container.servlet.filters.internal.SetHTTPHeaderFilter.doFilter(SetHTTPHeaderFilter.java:63)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.xwiki.container.servlet.filters.internal.SavedRequestRestorerFilter.doFilter(SavedRequestRestorerFilter.java:208)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.xwiki.container.servlet.filters.internal.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:111)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.xwiki.resource.servlet.RoutingFilter.doFilter(RoutingFilter.java:132)
	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:177)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:891)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1784)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1411)
	at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:489)
	at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3303)
	at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2438)
	at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:449)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
	at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
	at com.xpn.xwiki.internal.store.hibernate.HibernateStore.endTransaction(HibernateStore.java:907)
	at com.xpn.xwiki.store.XWikiHibernateBaseStore.endTransaction(XWikiHibernateBaseStore.java:602)
	at com.xpn.xwiki.store.XWikiHibernateStore.saveXWikiDoc(XWikiHibernateStore.java:712)
	... 71 common frames omitted
Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
	at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:46)
	at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3571)
	at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3438)
	at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3870)
	at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:202)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
	at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
	at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
	at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:344)
	at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
	at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107)
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1407)
	... 82 common frames omitted
Caused by: java.sql.SQLException: Incorrect string value: '\xAE Data...' for column 'XWR_PATCH' at row 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
	... 95 common frames omitted

Thanks,
Stuart

Looks a lot like Loading....

It seems that since 8.0.29 the mysql connector does not properly take into account encoding coming from the hibernate configuration or the MySQL server and always use the default encoding now. So a possible workaround is to make sure to start Tomcat with -Dfile.encoding=utf-8.

1 Like

Thanks, you hit the nail on the head.

I did start looking down this avenue, but was put off by the documentation here Encoding (XWiki.org) as it said that “UTF-8 will be used unless the org.apache.catalina.STRICT_SERVLET_COMPLIANCE system property is set to true in which case ISO-8859-1 will be used.” so I had assumed it was using UTF-8 by default.

This documentation seems to be only related to the encoding used to parse URIs and not the default system encoding.