SET failing

[bingomanatee]

I have a simple database table "doctypes"

CREATE TABLE `xoops_viking_d octypes` (
`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_d octypes` WHERE FIND_IN_SET('ma rketing',
categories) > 0

What is wrong with that?

[Bill Karwin]

"bingomanat ee" <ed******@manat eebay.com> wrote in message
news:11******** **************@ z34g2000cwc.goo glegroups.com.. .

this simple sql is simply crashing!

SELECT * FROM `xoops_viking_d octypes` WHERE FIND_IN_SET('ma rketing',
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.

[bingomanatee]

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)

[Bill Karwin]

"bingomanat ee" <ed******@manat eebay.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.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_d octypes`;

Regards,
Bill K.

[bingomanatee]

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

Illegal mix of collations (utf8_general_c i,COERCIBLE) and
(latin1_swedish _ci,IMPLICIT) for operation 'find_in_set'

[Bill Karwin]

"bingomanat ee" <ed******@manat eebay.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.com.. .

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

Illegal mix of collations (utf8_general_c i,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.

[bingomanatee]

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

[Bill Karwin]

"bingomanat ee" <ed******@manat eebay.com> wrote in message
news:11******** **************@ i40g2000cwc.goo glegroups.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/...onnection.html
http://dev.mysql.com/doc/refman/5.0/...t-convert.html

Regards,
Bill K.