470,638 Members | 1,608 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,638 developers. It's quick & easy.

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?

Mar 9 '06 #1
7 1214
"bingomanatee" <ed******@manateebay.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.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.
Mar 9 '06 #2
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)

Mar 10 '06 #3
"bingomanatee" <ed******@manateebay.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.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.
Mar 10 '06 #4
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'

Mar 10 '06 #5
"bingomanatee" <ed******@manateebay.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.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.
Mar 10 '06 #6
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...?

Mar 10 '06 #7
"bingomanatee" <ed******@manateebay.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.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.
Mar 10 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Simon Stewart | last post: by
7 posts views Thread by Chris Curvey | last post: by
reply views Thread by Sundeep Gawande | last post: by
3 posts views Thread by bob | last post: by
1 post views Thread by Frank Rizzo | last post: by
3 posts views Thread by Michael Tissington | last post: by
18 posts views Thread by Scott David Daniels | last post: by
reply views Thread by a_rajanikanth | last post: by
3 posts views Thread by noon | last post: by
???
1 post views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.