Steve wrote:
> Over the holiday we moved our production mysql database to a new
> server, and in the process went from v. 5.0.18-nt to 5.0.45-community-
> nt.
>
> An import query (fixed with text file gets imported to a holding
> table, and then a series of queries is run to insert the data into the
> correct tables for our system) that has worked fine for 2 years all of
> a sudden is giving the following error:
>
> Illegal mix of collations (utf8_general_ci,IMPLICIT) and
> (latin1_swedish_ci,COERCIBLE) for operation 'if'
>
> I've been reading all morning on this and have yet to find a
> workaround for it.
>
> I checked the show variables; for both systems and both systems match
> on the following variables:
>
> character_set_client: utf8
> character_set_connection: utf8
> character_set_database: latin1
> character_set_results: utf8
> character_set_server: latin1
> character_set_system: utf8
> collation_connection: utf8_general_ci
> collation_database: latin1_swedish_ci
> collation_server: latin1_swedish_ci
>
> As far as I can tell, the only thing that has changed is the mysql
> version (and O/S went form W2K to W2K3).
>
> query is as follows (shortened for clarity - non 'if' statements
> removed)
>
> INSERT INTO student (st_phonearea, st_phoneexchange, st_phonenumber,
> st_graduation, st_dob)
> SELECT
> IF(CHAR_LENGTH(bu_stphone)=10,left(bu_stphone,3),' '),
> IF(CHAR_LENGTH(bu_stphone)=10,mid(bu_stphone,4,3), ''),
> IF(CHAR_LENGTH(bu_stphone)=10,right(bu_stphone,4), ''),
> IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,bu_stgrad,CONCAT (DATE_FORMAT(DATE_ADD(bu_stdob,
> INTERVAL 18 YEAR),'%Y'),"0630")),
> IF(CHAR_LENGTH(TRIM(bu_stdob))=8,bu_stdob,NULL)
> FROM _ImportTable
> WHERE bu_stid IS NULL
>
> I'm pretty sure the problem lies in the 4th 'IF' line where the
> DATE_ADD is being done.
>
> Dates are in the _ImportTable as YYYYMMDD. If the bu_stgrad is 8
> characters long, add it as is. Otherwise, take the bu_stdob and add
> 18 years to it to guess at the high school grad date.
>
> A similar query using the same data but into a different table - but
> only using the phone number portions works fine - so that's why I'm
> pretty sure it's not the phone number fields in this query.
>
>
>
when you installed the new mysql on the new server, what character set
you choose can impact this. if this is different than what you had
before, then there's your problem.