| Bug #4762 | illegal mix of collations error | ||
|---|---|---|---|
| Submitted: | 26 Jul 2004 21:49 | Modified: | 3 Aug 2004 22:37 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / J | Severity: | S1 (Critical) |
| Version: | OS: | Windows (Windows) | |
| Assigned to: | Mark Matthews | CPU Architecture: | Any |
[3 Aug 2004 17:58]
Mark Matthews
What is the DDL for these tables?
[3 Aug 2004 21:20]
[ name withheld ]
I exported these two tables, think that's what you asked. # MySQL-Front Dump 2.5 # # Host: localhost Database: algos # -------------------------------------------------------- # Server version 4.1.3-beta # # Table structure for table 'jobs' # CREATE TABLE jobs ( sqlCode int(11) NOT NULL default '0', clientSqlCode int(11) default NULL, month varchar(7) character set latin1 default NULL, workerSqlCode int(11) default NULL, algaBendra int(11) default NULL, algaOficiali int(11) default NULL, sodra int(11) default NULL, pajamuMok33 int(11) default NULL, atostoginiai int(11) default NULL, priedas int(11) default NULL, premija int(11) default NULL, kuras int(11) default NULL, telefonas int(11) default NULL, baudos int(11) default NULL, avansas int(11) default NULL, avansoData varchar(10) character set cp1251 default NULL, PRIMARY KEY (sqlCode), KEY NewIndex (clientSqlCode), KEY NewIndex2 (month), KEY NewIndex3 (workerSqlCode) ) ENGINE=InnoDB DEFAULT CHARSET=cp1257; # # Table structure for table 'payments' # CREATE TABLE payments ( sqlCode int(11) NOT NULL default '0', workerSqlCode int(11) default NULL, clientSqlCode int(11) default NULL, type tinyint(4) default NULL, date varchar(10) character set latin1 default NULL, month varchar(7) default NULL, avansas int(11) default NULL, oficiali int(11) default NULL, oficialiIsmoketa tinyint(4) default NULL, likutis int(11) default NULL, likutisIsmoketa tinyint(4) default NULL, byOrder tinyint(4) default NULL, PRIMARY KEY (sqlCode), KEY NewIndex (workerSqlCode), KEY NewIndex2 (date) ) ENGINE=InnoDB DEFAULT CHARSET=cp1257;
[3 Aug 2004 22:37]
Mark Matthews
Please look at your DDL, you've mixed latin1 columns in your Cp1257 table, but are telling the server that _everything_ you send will be in Cp1257. Either declare the other columns to be Cp1257, or don't specify them (and they will inherit from DEFAULT CHARSET).
[9 Aug 2004 10:24]
[ name withheld ]
How could the columns change the charset? I didn't specify it, I didn't even know that there's such possibility to have columns in different charset. And of course, because I didn't know, I was unable to find such a bug. I'd suppose that there's problem with sql server and only maybe - with mysqlfront..
Description: MySql server 4.1.3 beta. Connector/J 3.0.14, 3.1.3 beta, 3.0-nightly-20040722. Using two different connection strings results in two different "illegal mix of collations" errors. Both tables are InnoDB, collation cp1257_general_ci. First connection string: connection = DriverManager.getConnection("jdbc:mysql://"+server+"/"+dbName, userName, password); java code: PreparedStatement ps = connection().prepareStatement("SELECT sqlCode FROM payments WHERE `clientSqlCode` = ? AND `month` = ?"); ps.setInt(1, job.getClient().getSqlCode()); ps.setString(2, Utils.formatMonth(job.getMonth())); // ResultSet rs = ps.executeQuery(); thrown exception: java.sql.SQLException: General error, message from server: "Illegal mix of collations (cp1257_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='" at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1979) ....... Second connection string: connection = DriverManager.getConnection("jdbc:mysql://"+server+"/"+dbName+"?user="+userName+"&password="+password+"&useUnicode=true&characterEncoding=Cp1257"); That's it, in second connection I try to tell to use unicode and character encoding, in which tables in database are set. java code: PreparedStatement psMonth = ds.getSqlData().getConnection().prepareStatement( "SELECT DISTINCT month FROM jobs WHERE month >= ? AND month <= ? ORDER BY month"); psMonth.setString(1, Utils.formatMonth(from)); psMonth.setString(2, Utils.formatMonth(to)); ResultSet rsMonth = psMonth.executeQuery(); I get the same exception. The problem is, both sql statements are on the same database in the same program, and I cannot find a way to make both of them to work. What causes such behavior I'm unaware, both sql statements seem similar but only one on different connection string cause exception. Maybe the order of types in WHERE clause make sense? (int, String & String, String)? How to repeat: Both tables InnoDB, collation cp1257_general_ci first connection: connection = DriverManager.getConnection("jdbc:mysql://"+server+"/"+dbName, userName, password); second connection: connection = DriverManager.getConnection("jdbc:mysql://"+server+"/"+dbName+"?user="+userName+"&password="+password+"&useUnicode=true&characterEncoding=Cp1257"); first sql statement: PreparedStatement ps = connection().prepareStatement("SELECT sqlCode FROM payments WHERE `clientSqlCode` = ? AND `month` = ?"); ps.setInt(1, 111); ps.setString(2, "2004/07"); ResultSet rs = ps.executeQuery(); second sql statement: PreparedStatement psMonth = ds.getSqlData().getConnection().prepareStatement( "SELECT DISTINCT month FROM jobs WHERE month >= ? AND month <= ? ORDER BY month"); psMonth.setString(1, "2004/07"); psMonth.setString(2, "2004/07"); ResultSet rsMonth = psMonth.executeQuery();