Hi!
Could someone tell me please how to configure MySQL DB for storing both latin and cyrillic data sets in the same table?
Thanks!
LianaN 0 Posting Whiz in Training
smantscheff 265 Veteran Poster
Set all character set variables (database, tables, connections) to UTF-8.
LianaN 0 Posting Whiz in Training
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)
smantscheff 265 Veteran Poster
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?
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.