jjennings
I'm new here

FS database from latin1 to utf8: throws Exception when trying to save element in data source

Hi,

Originally the FS Server and databases were set up using latin1 encoding. Our client wants to encode the database to utf8. We changed the encoding on the database and added this to the Server Properties -> Databases -> JDBC parameter:

jdbc:mysql://localhost:3307/cms?useUnicode=true&characterEncoding=UTF8

But when trying to save an element in a data source, we receive the following error (see below).

Does anyone have an idea the steps that need to be made to switch our database from latin1 to utf8 and what we need to configure on the FS Server so that this works? Do we need to convert the database to utf8 or is changing the character encoding enough?

Any experience or help on this would be greatly appreciated!

Here is an example Exception:

FSVersion=4.2.446.45868#2706;JDK=1.6.0_20 32bit Sun Microsystems Inc.;OS=Windows 7 6.1 x86;Date=11.10.2011 15:57:27

de.espirit.or.ORException: Error code: 1366, state: HY000

at de.espirit.or.impl.TemporalCommandHandler.update(TemporalCommandHandler.java:624)

at de.espirit.or.impl.TemporalCommandHandler.handle(TemporalCommandHandler.java:249)

at de.espirit.or.impl.UpdateCommand.accept(UpdateCommand.java:54)

at de.espirit.or.impl.AbstractSessionHandler.commit(AbstractSessionHandler.java:628)

at de.espirit.or.impl.AbstractSessionHandler.commit(AbstractSessionHandler.java:75)

at de.espirit.firstspirit.content.ContentManagerImpl$TemporalSessionHandler.commit(ContentManagerImpl.java:1207)

at de.espirit.firstspirit.content.ContentManagerImpl.commit(ContentManagerImpl.java:630)

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

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at de.espirit.firstspirit.io.DefaultServerHandler.callManager(DefaultServerHandler.java:62)

at de.espirit.firstspirit.server.io.handler.ManagerCall.doCall(ManagerCall.java:91)

at de.espirit.firstspirit.server.io.handler.CompactCall.handle(CompactCall.java:67)

at de.espirit.firstspirit.server.io.ManagerCallWorker.run(ManagerCallWorker.java:108)

at de.espirit.firstspirit.server.ExecutionManagerImpl$RunnableWrapper.call(ExecutionManagerImpl.java:553)

at de.espirit.firstspirit.server.ExecutionManagerImpl$ExtendedCallable.call(ExecutionManagerImpl.java:520)

at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

at java.util.concurrent.FutureTask.run(FutureTask.java:138)

at de.espirit.common.util.BoundedExecutorService$RunnableWrapper.run(BoundedExecutorService.java:419)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)

at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

at java.util.concurrent.FutureTask.run(FutureTask.java:138)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

at java.lang.Thread.run(Thread.java:662)

at de.espirit.common.util.SuspendableThread.run(SuspendableThread.java:36)

Caused by: java.sql.SQLException: Incorrect string value: '\xA7" set...' for column 'anzeigen' at row 1

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)

at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)

at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2219)

at de.espirit.or.impl.TemporalCommandHandler.setEntryInvalid(TemporalCommandHandler.java:519)

at de.espirit.or.impl.TemporalCommandHandler.update(TemporalCommandHandler.java:620)

at de.espirit.or.impl.TemporalCommandHandler.handle(TemporalCommandHandler.java:249)

at de.espirit.or.impl.UpdateCommand.accept(UpdateCommand.java:54)

at de.espirit.or.impl.AbstractSessionHandler.commit(AbstractSessionHandler.java:628)

at de.espirit.or.impl.AbstractSessionHandler.commit(AbstractSessionHandler.java:75)

at de.espirit.firstspirit.content.ContentManagerImpl$TemporalSessionHandler.commit(ContentManagerImpl.java:1207)

at de.espirit.firstspirit.content.ContentManagerImpl.commit(ContentManagerImpl.java:630)

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

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at de.espirit.firstspirit.io.DefaultServerHandler.callManager(DefaultServerHandler.java:62)

at de.espirit.firstspirit.server.io.handler.ManagerCall.doCall(ManagerCall.java:91)

at de.espirit.firstspirit.server.io.handler.CompactCall.handle(CompactCall.java:67)

at de.espirit.firstspirit.server.io.ManagerCallWorker.run(ManagerCallWorker.java:108)

at de.espirit.firstspirit.server.ExecutionManagerImpl$RunnableWrapper.call(ExecutionManagerImpl.java:553)

at de.espirit.firstspirit.server.ExecutionManagerImpl$ExtendedCallable.call(ExecutionManagerImpl.java:520)

at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

at java.util.concurrent.FutureTask.run(FutureTask.java:138)

at de.espirit.common.util.BoundedExecutorService$RunnableWrapper.run(BoundedExecutorService.java:419)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)

at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

at java.util.concurrent.FutureTask.run(FutureTask.java:138)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

at de.espirit.firstspirit.client.io.ServerCaller.callManager(ServerCaller.java:372)

at de.espirit.firstspirit.io.DefaultClientHandler.callManager(DefaultClientHandler.java:49)

at de.espirit.firstspirit.client.io.RemoteManagerCaller.invoke(RemoteManagerCaller.java:52)

at de.espirit.firstspirit.server.$Proxy32.commit(Unknown Source)

at de.espirit.firstspirit.store.access.templatestore.SessionHandlerImpl.commit(SessionHandlerImpl.java:113)

at de.espirit.or.impl.SessionImpl.doCommit(SessionImpl.java:191)

at de.espirit.or.impl.AbstractSession.commit(AbstractSession.java:525)

at de.espirit.firstspirit.store.access.contentstore.DatasetImpl.save(DatasetImpl.java:928)

at de.espirit.firstspirit.client.gui.tree.store.contentstore.AbstractGuiXmlPanel.save(AbstractGuiXmlPanel.java:460)

at de.espirit.firstspirit.client.gui.tree.store.contentstore.ContentGuiXmlPanel.persist(ContentGuiXmlPanel.java:392)

at de.espirit.firstspirit.client.gui.tree.store.contentstore.ContentGuiXmlPanel.persist(ContentGuiXmlPanel.java:362)

at de.espirit.firstspirit.client.gui.tree.store.contentstore.ContentGuiXmlPanel.save(ContentGuiXmlPanel.java:357)

at de.espirit.firstspirit.client.gui.tree.store.contentstore.ContentGuiXmlPanel$GuiXmlToolbar$2.actionPerformed(ContentGuiXmlPanel.java:547)

at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)

at de.espirit.firstspirit.common.gui.FsButton.fireActionPerformed(FsButton.java:86)

at de.espirit.firstspirit.common.gui.StoreHoverButton.fireActionPerformed(StoreHoverButton.java:97)

at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)

at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)

at javax.swing.DefaultButtonModel.setPressed(Unknown Source)

at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)

at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)

at java.awt.Component.processMouseEvent(Unknown Source)

at javax.swing.JComponent.processMouseEvent(Unknown Source)

at java.awt.Component.processEvent(Unknown Source)

at java.awt.Container.processEvent(Unknown Source)

at java.awt.Component.dispatchEventImpl(Unknown Source)

at java.awt.Container.dispatchEventImpl(Unknown Source)

at java.awt.Component.dispatchEvent(Unknown Source)

at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)

at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)

at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)

at java.awt.Container.dispatchEventImpl(Unknown Source)

at java.awt.Window.dispatchEventImpl(Unknown Source)

at java.awt.Component.dispatchEvent(Unknown Source)

at java.awt.EventQueue.dispatchEvent(Unknown Source)

at de.espirit.firstspirit.client.AWTDispatchingEventQueue.defaultDispatchEvent(AWTDispatchingEventQueue.java:130)

at de.espirit.firstspirit.client.AWTDispatchingEventQueue._dispatchEvent(AWTDispatchingEventQueue.java:115)

at de.espirit.firstspirit.client.AWTDispatchingEventQueue.dispatchEvent(AWTDispatchingEventQueue.java:108)

at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)

at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)

at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)

at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

0 Kudos
3 Replies
andre
I'm new here

you need to convert the db or table.

...something like may work

Convert existing MySQL database from one charset encoding to another

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Convert existing table from one charset encoding to another

ALTER TABLE db_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

0 Kudos

Hi Andre,

Thanks for the suggestion. Just a note - I added our JDBC configuration to the entry above:

We changed the encoding on the database and added this to the Server Properties -> Databases -> JDBC parameter:

jdbc:mysql://localhost:3307/cms?useUnicode=true&characterEncoding=UTF8

Is this configuration correct?

Could this have anything to do with the problem?

Thanks,

Josh

0 Kudos

Hi,

as Andre pointed out, you need to convert all existing data from latin1 to utf8. It's not enough to just change the JDBC configuration, because it won't affect the existing data stored in the database. Please consult the MySQL documentation on how to convert a database from latin to utf8. There are several ways to do it.

Here's an introduction to the problem space.

0 Kudos