title: Howto resolve "Illegal mix of collations" SQLException?
body: <p>I looked in the mysql reference, and here, in stackoverflow, and looks like a lot of people are having difficulties with character encoding, but I couldn't find a clear answer to this problem:</p>
  
  <p>A java program is using a mysql database, and when there are special characters in the query like ű,ő (but á,é,í,ó,ú works), it throws an SQLException: </p>
  
  <pre><code>    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Connection conn = DriverManager.getConnection(
              "jdbc:odbc:&lt;database&gt;", "&lt;user&gt;", "&lt;pass&gt;"
              );
  
      PreparedStatement stmt = conn.prepareStatement(
              " select username, priority " +
              " from users " +
              " where username like ?");
              //" where username like ? collate latin2_general_ci");
      stmt.setString(1, "Ernő");
      ResultSet rs = stmt.executeQuery();
      while(rs.next()) {
          System.out.println(rs.getString("username") + "  " + rs.getInt("priority"));
      }
  </code></pre>
  
  <p>Exception:</p>
  
  <pre><code>Exception in thread "main" java.sql.SQLException:
  [MySQL][ODBC 5.1 Driver][mysqld-5.1.63-0ubuntu0.10.04.1]
  Illegal mix of collations (latin2_hungarian_ci,IMPLICIT)
  and (latin1_swedish_ci,COERCIBLE) for operation 'like'
  </code></pre>
  
  <p>The table structure is:</p>
  
  <pre><code>CREATE TABLE `users` (
  `username` varchar(45) COLLATE latin2_hungarian_ci NOT NULL,
  `password` varchar(45) COLLATE latin2_hungarian_ci NOT NULL,
  `priority` tinyint(4) NOT NULL,
  `idCimlistaFK` int(10) unsigned DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`username`),
  KEY `fk_users_tbl_Cimlista1` (`idCimlistaFK`),
  CONSTRAINT `fk_users_tbl_Cimlista1` FOREIGN KEY (`idCimlistaFK`)
  REFERENCES `tbl_cimlista` (`id_Cimlista`) ON DELETE NO ACTION ON UPDATE NO ACTION
  ) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci 
  </code></pre>
  
  <p>When I tried to force a collation like in the commented line:</p>
  
  <pre><code>" where username like ? collate latin2_general_ci");
  </code></pre>
  
  <p>It also throws an exception:</p>
  
  <pre><code>COLLATION 'latin2_general_ci' is not valid for CHARACTER SET 'latin1'
  </code></pre>
  
  <p>Does anyone know, how to solve this (possibly within java)?  (I would prefer not to change the database, because it's a legacy system.)</p>
  
  <p><strong>UPDATE:</strong> </p>
  
  <p>I used the following url, as suggested by Rahul Agrawal:</p>
  
  <pre><code>"jdbc:mysql://localhost:3306/database?characterEncoding=latin2"
  </code></pre>
  
  <p>It did work, however, I noticed a sideeffect.
  When I updated and inserted data with the testserver, wich runs mysql on a win XP machine, it went fine. But when I updated and inserted the data to the production server running Ubuntu, I did get rows, wich had ?-signs instead ő,ű-letters. I resolved this with a workaround since, but it would be nice to see and understand, what went wrong, what are the issues here.</p>