Jack BeNimble Jack BeNimble - 1 year ago 100
MySQL Question

Where is "latin1_german1_ci" collation coming from?

I'm receiving the following error message from a Java/Spring/Hibernate application when it tries to execute a prepared statement against a mysql database :

Caused by: java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin1_german1_ci,COERCIBLE) for operation '='

The select statement which generates this (as shown in the tomcat log) is:

SELECT s.* FROM score_items s where
s.s_score_id_l=299 and
(s.p_is_plu_b = 'F') and
isTestProduct(s.p_upc_st) = 'N' and
order by s.nc_name_st, s.p_upc_st

The table collation per the
show table status
command is:


The collation for all the char, varchar and text fields is "
". It's null for the bigint, int and datetime fields.

The database collation is
as displayed by the command:

show variables like "collation_database";

Edit: I was able to successfully run this from my local machine using Eclipse/STS and a Tomcat 6 instance. The local process is reading the from the same database as the process on the production server which generated the error. The server where the error occurs is a Tomcat 7. instance is an Amazon Linux server.

Edit 2: I was also able to successfully run the report when I ran it from our QA environment, with the JDBC statement in server.xml reset to point at the production database. QA is essentially a mirror of the production environment, with some dev work going on. I should also note that I saw a similar error last month, but it disappeared when I reran the report. Finally, I'm not sure why it would make a difference, but the table being queried is huge, with over 7 million rows and probably 100 fields per row.

How do I found out which field is "

Why is the comparison using "
" when the table and fields are either "
or null?

Could the problem be related to function character set, and if so how do I identify which character set/collation it's using?

How do I narrow down which field/function is causing the problem?

Answer Source

This has got nothing to do with java or hibernate, this is purely down to mysql and perhaps to the connection string.

In mysql you can define character set and collation at multiple levels, which can cause a lot of issues:

  • server
  • database
  • table
  • column
  • connection

See mysql documentation on character sets and collations for more details.

To sum up: the higher level defaults kick in if and only if at lower level you do not specify charater set or collation. So, a column level definition overrides a table level definition. show table status command show the table level defaults, but these may have been overridden on column level. show full columns or show create table commands will show you the true character sets and collations used by any given field.

Connection level character set / collation definitions further complicate the picture because string constants used in the sql statements will use the connection character set / collation, unless they have an explicit declaration.

However, mysql uses coercibility values to avoid most issues arising from the use of various character sets and expressions as described in mysql documentation on character sets / collations used in expressions.

From you mentioning that the query works when executed from another computer indicates that the issue is with the connection character set / collation. I think it will be around the isTestProduct() call.

The only way to really determine which condition causes the isdue is to eliminate the conditions one by one and when the error is gone, then the last eliminated condition was the culprit. But defining appropriate connection character set and collation that is in line with what is used in the fields will also help.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download