SET failing

I have a simple database table "doctypes"

CREATE TABLE `xoops_viking_doctypes` (
`ID` int(11) NOT NULL auto_increment,
`menu_title` varchar(20) NOT NULL default '',
`name` varchar(40) NOT NULL default '',
`title` varchar(200) NOT NULL default '',
`description` text NOT NULL,
`categories` set('marketing','support') NOT NULL default 'marketing',
PRIMARY KEY (`ID`),
KEY `categories` (`categories`),
FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii AUTO_INCREMENT=56 ;

this simple sql is simply crashing!

SELECT * FROM `xoops_viking_doctypes` WHERE FIND_IN_SET('marketing',
categories) > 0

What is wrong with that?

Re: SET failing

"bingomanatee" wrote in message
news:1141943680.733483.183530@z34g2000cwc.googlegroups.com.. .
> this simple sql is simply crashing!
>
> SELECT * FROM `xoops_viking_doctypes` WHERE FIND_IN_SET('marketing',
> categories) > 0
>
> What is wrong with that?

Are you serious that this is literally crashing? Or does it simply not
return the result you expect?
"Crashing" means that the MySQL client or server terminated abnormally. Is
this the case?

What version of MySQL are you using? (4.0, 4.1, 5.0)
What operating system are you using? (Windows, Linux, etc.)
What client interface are you using? (mysql CLI, PHP, Java, ODBC, etc.)

Regards,
Bill K.

Re: SET failing

sorry: more specifically the sql is not returning the result I expect
-- in fact it is not parsing properly!

MySQL 4.1,
Solaris,
PHP 5.0.5

( am testing the results in phpMyAdmin as well)

Re: SET failing

"bingomanatee" wrote in message
news:1141948565.340387.296300@i39g2000cwa.googlegroups.com.. .
> sorry: more specifically the sql is not returning the result I expect
> -- in fact it is not parsing properly!
>
> MySQL 4.1,
> Solaris,
> PHP 5.0.5

Okay, I have tried the same table and query in my test database. I see no
error, and it returns results that I would expect. But I am using MySQL
5.0.

Do you have an error message that displays when you try to execute the
query?

What do you get when you omit the condition, that is:
SELECT id, categories FROM `xoops_viking_doctypes`;

Regards,
Bill K.

Re: SET failing

yes; I didn't see it before because I am using a different server now.)

Illegal mix of collations (utf8_general_ci,COERCIBLE) and
(latin1_swedish_ci,IMPLICIT) for operation 'find_in_set'

Re: SET failing

"bingomanatee" wrote in message
news:1141962229.545732.304690@u72g2000cwu.googlegroups.com.. .
> yes; I didn't see it before because I am using a different server now.)
>
> Illegal mix of collations (utf8_general_ci,COERCIBLE) and
> (latin1_swedish_ci,IMPLICIT) for operation 'find_in_set'

So one character set was used when the schema was defined, and a different
character set is being used as you're doing your query. You need to use the
same, or a compatible, character set as that which is used in the schema
definition for the SET field.

Regards,
Bill K.

Re: SET failing

I don't get it; I am using a literal string that I embed in the sql to
compare to the set field value; the database collation is the same as
the filed definition (latin1_swedish_ci; not a conscious choice on my
part but if it ain't broke ... cept, I guess it is. )

WHy is it interepreting a raw string imbedded in a SQL query as udf8,
and how do I change it to latin1...?

Re: SET failing

"bingomanatee" wrote in message
news:1141992337.343457.199150@i40g2000cwc.googlegroups.com.. .
> WHy is it interepreting a raw string imbedded in a SQL query as udf8,
> and how do I change it to latin1...?

I haven't worked very much with character sets. I think you can specify a
character set during your connection. You can also use the CONVERT function
to force a given string literal into a character set you specify.

These pages may help:
http://dev.mysql.com/doc/refman/5.0/en/charset-connection.ht ml
http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

Regards,
Bill K.