Hi!

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

Thanks!

Set all character set variables (database, tables, connections) to UTF-8.

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)

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?