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.

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.

> 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.

Could you describe the _ImportTable please?

Or see:
http://bugs.mysql.com/bug.php?id=4719
for an idea on how to work around it

> Could you describe the _ImportTable please?

It's all varchar fields.

For this line (which I suspect is the issue):

IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,bu_stgrad,CONCAT (DATE_FORMAT(DATE_ADD(bu_stdob,INTERVAL
18 YEAR),'%Y'),"0630"))

bu_stgrad is varchar(8)

if it's null, it takes bu_stdob (also varchar(8)), adds 18 years and
then sets it to 0630YYYY where YYYY is the date of birth plus 18
years.

This table also contains parent info for the student, and that data is
importing correct. Hence my conclusion that these 3 lines of the
query:

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), ''),

are not problematic as I've got 3 similar lines for the parent phone
that I just omitted from the query for clarity's sake here.

Regarding the question of what character set was used during the
install - that would be the default. So unless the default changed
from 5.0.18 to 5.0.45, it should be the same.

Also, shouldn't show variables; show the config? If so, they're
exactly the same on the following items:

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

Thanks,

Steve

BTW my quick workaround was to run the exact same query that fails
directly in mysql through ColdFusion 8 and it worked like a charm.
However, we prefer to do our imports in mysql and not through the web
server...

On 2008-01-03, Steve <steve.logan@gmail.com> wrote:
>> Could you describe the _ImportTable please?
>
> It's all varchar fields.
>
> For this line (which I suspect is the issue):
>
> IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,bu_stgrad,CONCAT (DATE_FORMAT(DATE_ADD(bu_stdob,INTERVAL
> 18 YEAR),'%Y'),"0630"))
>
> bu_stgrad is varchar(8)
>
> if it's null, it takes bu_stdob (also varchar(8)), adds 18 years and
> then sets it to 0630YYYY where YYYY is the date of birth plus 18
> years.

Please post the result of SHOW CREATE TABLE _ImportTable, so that I
can attempt to duplicate the problem. I am particularly interested
in the default charset for the table.

Best regards,

Charles

On Thu, 03 Jan 2008 03:41:13 +0100, Charles Polisher
<cpolish@nonesuch.com> wrote:

> On 2008-01-03, Steve <steve.logan@gmail.com> wrote:
>>> Could you describe the _ImportTable please?
>>
>> It's all varchar fields.
>>
>> For this line (which I suspect is the issue):
>>
>> IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,bu_stgrad,CONCAT (DATE_FORMAT(DATE_ADD(bu_stdob,INTERVAL
>> 18 YEAR),'%Y'),"0630"))
>>
>> bu_stgrad is varchar(8)
>>
>> if it's null, it takes bu_stdob (also varchar(8)), adds 18 years and
>> then sets it to 0630YYYY where YYYY is the date of birth plus 18
>> years.
>
> Please post the result of SHOW CREATE TABLE _ImportTable, so that I
> can attempt to duplicate the problem. I am particularly interested
> in the default charset for the table.

Or SHOW FULL COLUMNS FROM <tablename>
--
Rik Wasmus

> Please post the result of SHOW CREATE TABLE _ImportTable, so that I
> can attempt to duplicate the problem. I am particularly interested
> in the default charset for the table.
>
> Best regards,
>
> Charles

V 5.0.45-community-nt

CREATE TABLE `_bpsponsorupdate` (
`bu_bpid` varchar(9) default NULL,
`bu_date` varchar(8) default NULL,
`bu_amount` varchar(9) default NULL,
`bu_ssn` varchar(9) default NULL,
`bu_first` varchar(25) default NULL,
`bu_last` varchar(25) default NULL,
`bu_address1` varchar(50) default NULL,
`bu_address2` varchar(50) default NULL,
`bu_city` varchar(50) default NULL,
`bu_state` varchar(2) default NULL,
`bu_zip` varchar(5) default NULL,
`bu_phone` varchar(10) default NULL,
`bu_email` varchar(50) default NULL,
`bu_user` varchar(50) default NULL,
`bu_pass` varchar(9) default NULL,
`bu_flags` varchar(6) default NULL,
`bu_stssn` varchar(9) default '(NULL)',
`bu_stfirst` varchar(25) default NULL,
`bu_stlast` varchar(25) default NULL,
`bu_staddr1` varchar(50) default NULL,
`bu_staddr2` varchar(50) default NULL,
`bu_stcity` varchar(50) default NULL,
`bu_ststate` varchar(2) default NULL,
`bu_stzip` varchar(5) default NULL,
`bu_stphone` varchar(10) default NULL,
`bu_stemail` varchar(50) default NULL,
`bu_stflags` varchar(6) default NULL,
`bu_stgrad` varchar(8) default NULL,
`bu_stdob` varchar(8) default NULL,
`bu_stsex` varchar(1) default NULL,
`bu_stamount` varchar(9) default NULL,
`bu_spid` int(11) default NULL,
`bu_spidnew` int(11) default NULL,
`bu_stid` int(11) default NULL,
`bu_stnew` tinyint(4) default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

V 5.0.18-nt (working)

CREATE TABLE `_bpsponsorupdate` (
`bu_bpid` varchar(9) default NULL,
`bu_date` varchar(8) default NULL,
`bu_amount` varchar(9) default NULL,
`bu_ssn` varchar(9) default NULL,
`bu_first` varchar(25) default NULL,
`bu_last` varchar(25) default NULL,
`bu_address1` varchar(50) default NULL,
`bu_address2` varchar(50) default NULL,
`bu_city` varchar(50) default NULL,
`bu_state` varchar(2) default NULL,
`bu_zip` varchar(5) default NULL,
`bu_phone` varchar(10) default NULL,
`bu_email` varchar(50) default NULL,
`bu_user` varchar(50) default NULL,
`bu_pass` varchar(9) default NULL,
`bu_flags` varchar(6) default NULL,
`bu_stssn` varchar(9) default '(NULL)',
`bu_stfirst` varchar(25) default NULL,
`bu_stlast` varchar(25) default NULL,
`bu_staddr1` varchar(50) default NULL,
`bu_staddr2` varchar(50) default NULL,
`bu_stcity` varchar(50) default NULL,
`bu_ststate` varchar(2) default NULL,
`bu_stzip` varchar(5) default NULL,
`bu_stphone` varchar(10) default NULL,
`bu_stemail` varchar(50) default NULL,
`bu_stflags` varchar(6) default NULL,
`bu_stgrad` varchar(8) default NULL,
`bu_stdob` varchar(8) default NULL,
`bu_stsex` varchar(1) default NULL,
`bu_stamount` varchar(9) default NULL,
`bu_spid` int(11) default NULL,
`bu_stid` int(11) default NULL,
`bu_stnew` tinyint(4) default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And this is the student table that the data is being imported into:

CREATE TABLE `student` (
`st_stid` int(11) NOT NULL auto_increment,
`st_ssn` varchar(9) default NULL,
`st_first` varchar(25) default NULL,
`st_last` varchar(50) default NULL,
`st_address1` varchar(50) default NULL,
`st_address2` varchar(50) default NULL,
`st_city` varchar(50) default NULL,
`st_state` varchar(25) default NULL,
`st_zip` varchar(12) default NULL,
`st_phonearea` varchar(3) default NULL,
`st_phoneexchange` varchar(3) default NULL,
`st_phonenumber` varchar(4) default NULL,
`st_dob` date default NULL,
`st_email` varchar(50) default NULL,
`st_graduation` datetime default NULL,
`st_source` varchar(45) default NULL,
`st_active` tinyint(4) default '1',
`st_deleted` tinyint(4) default '0',
`st_add` datetime default NULL,
`st_dla` datetime default NULL,
`st_gender` varchar(1) default NULL,
`st_working` tinyint(4) default NULL,
`st_gpa` varchar(4) default NULL,
`st_satm` varchar(3) default NULL,
`st_satr` varchar(3) default NULL,
`st_satw` varchar(3) default NULL,
`st_act` varchar(4) default NULL,
`st_major1` varchar(50) default NULL,
`st_smajor1` varchar(50) default NULL,
`st_major2` varchar(50) default NULL,
`st_smajor2` varchar(50) default NULL,
`st_ethnicity` tinyint(4) default NULL,
PRIMARY KEY (`st_stid`),
UNIQUE KEY `st_stid` (`st_stid`),
KEY `st_ssn` (`st_ssn`)
) ENGINE=InnoDB AUTO_INCREMENT=134126 DEFAULT CHARSET=latin1

> > Please post the result of SHOW CREATE TABLE _ImportTable, so that I
> > can attempt to duplicate the problem. I am particularly interested
> > in the default charset for the table.
>
> Or SHOW FULL COLUMNS FROM <tablename>
> --
> Rik Wasmus

Rik / Charles

Here's a link to a text file with the outputs of both commands on both
servers:

http://www.jabberwonk.com/dump/mysql_error.txt

On Jan 3, 12:05 pm, Steve <steve.lo...@gmail.com> wrote:
> > > Please post the result of SHOW CREATE TABLE _ImportTable, so that I
> > > can attempt to duplicate the problem. I am particularly interested
> > > in the default charset for the table.
>
> > Or SHOW FULL COLUMNS FROM <tablename>
> > --
> > Rik Wasmus
>
> Rik / Charles
>
> Here's a link to a text file with the outputs of both commands on both
> servers:
>
> http://www.jabberwonk.com/dump/mysql_error.txt

I also included the full query in the .txt file (I had trimmed some
stuff out for clarity here)

Steve

Steve <steve.logan@gmail.com> wrote:
<snip>

min(IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,bu_stgrad,CO NCAT(DATE_FORMAT(DATE_ADD(bu_stdob, INTERVAL 18 YEAR),'%Y'),"0630"))),

should this actually be:

min(IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,bu_stgrad,CO NCAT(DATE_FORMAT(DATE_ADD(bu_stdob, INTERVAL 18 YEAR),'%Y'),_utf8'0630'))),

?

Best regards,

Charles

On Jan 3, 12:27 pm, Charles Polisher <cpol...@nonesuch.com> wrote:
> Steve <steve.lo...@gmail.com> wrote:
>
> <snip>
>
> min(IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,bu_stgrad,CO NCAT(DATE_FORMAT(DATE_ADD(bu_stdob, INTERVAL 18 YEAR),'%Y'),"0630"))),
>
> should this actually be:
>
> min(IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,bu_stgrad,CO NCAT(DATE_FORMAT(DATE_ADD(bu_stdob, INTERVAL 18 YEAR),'%Y'),_utf8'0630'))),
>
> ?
>
> Best regards,
>
> Charles

That didn't work, but that line is definitely the problem - when I
removed that line and the corresponding column from the insert it ran
fine.

Steve

On Jan 3, 12:27 pm, Charles Polisher <cpol...@nonesuch.com> wrote:
> Steve <steve.lo...@gmail.com> wrote:
>
> <snip>
>
> min(IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,bu_stgrad,CO NCAT(DATE_FORMAT(DATE_ADD(bu_stdob, INTERVAL 18 YEAR),'%Y'),"0630"))),
>
> should this actually be:
>
> min(IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,bu_stgrad,CO NCAT(DATE_FORMAT(DATE_ADD(bu_stdob, INTERVAL 18 YEAR),'%Y'),_utf8'0630'))),
>
> ?
>
> Best regards,
>
> Charles


min(IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,bu_stgrad this condition is
true (bu_strgrad) is 8, so it should insert it as is. Do you think
maybe that needs to be forced to utf8?

Solved!

IF(CHAR_LENGTH(TRIM(bu_stgrad))=8,DATE(bu_stgrad), CONCAT(DATE_FORMAT(DATE_ADD(bu_stdob,
INTERVAL 18 YEAR),'%Y'),'0630'))

I tested it with fields with no value for bu_stgrad - 2nd condtion of
the if, and fields with YYYYMMDD for bu_stgrad - first condition of
the if.


Thank you both very much for your with this. Hopefully if
someone else comes across this error message - which in this case
didn't seem to have a whole lot to do with character encoding - just a
missing DATE() around a value being inserted.


Steve