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 '='
SELECT s.* FROM score_items s where
(s.p_is_plu_b = 'F') and
isTestProduct(s.p_upc_st) = 'N' and
order by s.nc_name_st, s.p_upc_st
show table status
show variables like "collation_database";
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:
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.