0

Hi!

Could someone tell me please how to configure MySQL DB for storing both latin and cyrillic data sets in the same table?

Thanks!

2
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by smantscheff
0

Hi!

Thank you for the answer.

I set everything to UFT8, except table columns:
1) ALTER DATABASE 'MyDB' SET utf8 COLLATE utf8_general_ci (the same for all tables)
2) my.ini: here I did everything as it is described in multiple tuts
3) JAVA code: db = (Connection) DriverManager.getConnection("jdbc:mysql://" + url + "/" + databasename + "?useUnicode=true&characterEncoding=UTF8",login, pass);

When I run show variables like coll% , then I receive a list of variables shown in the attached file. So, everithing seems to be ok.

BUT when I run 'insert' query from JAVA, then the following error occurs (see below). What coudl cause the message Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' ? Does it mean that I must set ALL columns in ALL tables to UTF8?

java.sql.SQLException: Incorrect string value: '\xD0\x9F\xD0\xB0\xD0\xBF...' for column 'of_title' at row 1
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
      at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1664)
      at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1583)
      at SystClasses.QueryTableModel.setQueryDontChangeTable(QueryTableModel.java:124)
      at SystClasses.Folder.addOrgFolder(Folder.java:335)
      at SystClasses.Folder.access$700(Folder.java:40)
      at SystClasses.Folder$2.actionPerformed(Folder.java:120)
      at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
      at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
      at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
      at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
      at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
      at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:272)
      at java.awt.Component.processMouseEvent(Component.java:6263)
      at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
      at java.awt.Component.processEvent(Component.java:6028)
      at java.awt.Container.processEvent(Container.java:2041)
      at java.awt.Component.dispatchEventImpl(Component.java:4630)
      at java.awt.Container.dispatchEventImpl(Container.java:2099)
      at java.awt.Component.dispatchEvent(Component.java:4460)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4574)
      at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
      at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
      at java.awt.Container.dispatchEventImpl(Container.java:2085)
      at java.awt.Window.dispatchEventImpl(Window.java:2478)
      at java.awt.Component.dispatchEvent(Component.java:4460)
      at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
      at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
      at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
      java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512)
      at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1476)
      at SystClasses.Folder.returnDataFromSelectQuery(Folder.java:446)
      at SystClasses.TreeEditTest$3.valueChanged(TreeEditTest.java:328)
      at javax.swing.JTree.fireValueChanged(JTree.java:2820)
      at javax.swing.JTree$TreeSelectionRedirector.valueChanged(JTree.java:3191)
      at javax.swing.tree.DefaultTreeSelectionModel.fireValueChanged(DefaultTreeSelectionModel.java:629)
      at javax.swing.tree.DefaultTreeSelectionModel.notifyPathChange(DefaultTreeSelectionModel.java:1078)
      at javax.swing.tree.DefaultTreeSelectionModel.setSelectionPaths(DefaultTreeSelectionModel.java:287)
      at javax.swing.tree.DefaultTreeSelectionModel.setSelectionPath(DefaultTreeSelectionModel.java:170)
      at javax.swing.JTree.setSelectionPath(JTree.java:1598)
      at SystClasses.Folder.addOrgFolder(Folder.java:347)
      at SystClasses.Folder.access$700(Folder.java:40)
      at SystClasses.Folder$2.actionPerformed(Folder.java:120)
      at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
      at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
      at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
      at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
      at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
      at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:272)
      at java.awt.Component.processMouseEvent(Component.java:6263)
      at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
      at java.awt.Component.processEvent(Component.java:6028)
      at java.awt.Container.processEvent(Container.java:2041)
      at java.awt.Component.dispatchEventImpl(Component.java:4630)
      at java.awt.Container.dispatchEventImpl(Container.java:2099)
      at java.awt.Component.dispatchEvent(Component.java:4460)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4574)
      at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
      at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
      at java.awt.Container.dispatchEventImpl(Container.java:2085)
      at java.awt.Window.dispatchEventImpl(Window.java:2478)
      at java.awt.Component.dispatchEvent(Component.java:4460)
      at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
      at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
      at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
Attachments Untitled.png 8.12 KB
0

Your columns are already in UTF8, but in different collations, which means the internal order for sorting characters. The default collation for MySQL is latin1_swedish_ci. Have a look at the results of "show create table" to see where this default collation is set, and modify the table or column where it occurs to the collation utf8_general_ci. Changing the database collation does not affect tables and columns which are already in the database; it's only the default value for new tables.
And yes, I would strongly propose setting all text columns to utf-8 to avoid this kind of hassle. Are the columns which you would rather leave in lation1?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.