Professional Web Applications Themes

Illegal collation? - MySQL

Hi, I'm using MySQL 5 on Fedora Core 5 Linux and I'm getting an error I've never seen before. Here's the query SELECT C.NAME, P.MODEL_NUM, P.NAME, CASE ISNULL(PC.RETAIL_PRICE) WHEN 1 THEN P.RETAIL_PRICE ELSE PC.RETAIL_PRICE END AS RETAIL_PRICE, CASE ISNULL(PC.PRICE) WHEN 1 THEN P.PRICE ELSE PC.PRICE END AS PRICE, CASE ISNULL(PC.DESCRIPTION) WHEN 1 THEN P.DESCRIPTION ELSE PC.DESCRIPTION END AS DESCRIPTION FROM PROD_SUB_CATEGORIES S, PROD_PRODUCT_INK_TYPES I, PROD_CATEGORIES C, PROD_PRODUCTS P LEFT JOIN PROD_PRODUCT_CUSTOM_PRICES PC ON P.PRODUCT_ID = PC.PRODUCT_ID WHERE P.SUB_CATEGORY_ID = S.SUB_CATEGORY_ID AND S.CATEGORY_ID = C.CATEGORY_ID AND S.INK_TYPE_ID = I.INK_TYPE_ID AND I.INK_TYPE_ID = 1 and here's the error. Illegal mix of collations ...

  1. #1

    Default Illegal collation?

    Hi,

    I'm using MySQL 5 on Fedora Core 5 Linux and I'm getting an error I've
    never seen before. Here's the query

    SELECT C.NAME, P.MODEL_NUM, P.NAME, CASE ISNULL(PC.RETAIL_PRICE) WHEN
    1 THEN P.RETAIL_PRICE ELSE PC.RETAIL_PRICE END AS RETAIL_PRICE, CASE
    ISNULL(PC.PRICE) WHEN 1 THEN P.PRICE ELSE PC.PRICE END AS PRICE, CASE
    ISNULL(PC.DESCRIPTION) WHEN 1 THEN P.DESCRIPTION ELSE PC.DESCRIPTION
    END AS DESCRIPTION FROM PROD_SUB_CATEGORIES S, PROD_PRODUCT_INK_TYPES
    I, PROD_CATEGORIES C, PROD_PRODUCTS P LEFT JOIN
    PROD_PRODUCT_CUSTOM_PRICES PC ON P.PRODUCT_ID = PC.PRODUCT_ID WHERE
    P.SUB_CATEGORY_ID = S.SUB_CATEGORY_ID AND S.CATEGORY_ID =
    C.CATEGORY_ID AND S.INK_TYPE_ID = I.INK_TYPE_ID AND I.INK_TYPE_ID = 1

    and here's the error.

    Illegal mix of collations (latin1_general_ci,IMPLICIT) and
    (latin1_swedish_ci,IMPLICIT) for operation '='

    What is wrong with my query?

    Thanks, - Dave

    laredotornado@zipmail.com Guest

  2. #2

    Default Re: Illegal collation?

    com wrote: 


    Off hand I would suggest your tables have different collations. Some
    are latin1_general_ci and others are latin_swedish _ci.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Illegal collation?

    On Feb 22, 7:34 pm, Jerry Stuckle <net> wrote: 






    >
    > Off hand I would suggest your tables have different collations. Some
    > are latin1_general_ci and others are latin_swedish _ci.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    How do I find out what collation a table is? When I created my
    tables, I didn't say anything about collations, only that the tables
    were type = INNODB.

    Thanks, - Dave

    laredotornado@zipmail.com Guest

  4. #4

    Default Re: Illegal collation?

    > How do I find out what collation a table is? When I created my 

    It is the fields that have collations. The tables have collations also,
    but they are the default for the fields. Just issue

    SHOW FULL FIELDS FROM <table name>

    and it will show the column collations.

    Best regards
    --
    Willem Bogaerts

    Application smith
    Kratz B.V.
    http://www.kratz.nl/
    Willem Guest

  5. #5

    Default Re: Illegal collation?

    > It is the fields that have collations. The tables have collations also, 

    If you want to change the existing situation, look at the statement

    ALTER TABLE tbl_name CONVERT TO CHARACTER SET cht_name;

    (see http://dev.mysql.com/doc/refman/4.1/en/alter-table.html)
    This can save you a lot of work.
    --
    Willem Bogaerts

    Application smith
    Kratz B.V.
    http://www.kratz.nl/
    Willem Guest

  6. #6

    Default Re: Illegal collation?

    "com" <com> wrote: 

    You apparently try to compare two string-type columns having different
    collations. I suggest you familiarize yourself with collations first:

    http://dev.mysql.com/doc/refman/5.0/en/cht.html


    Unfortunately you have multiple '=' in your query. So you have to check
    each pair of columns used left and right of a '=' if their collations
    match. You have been told about SHOW FULL FIELDS already.

    BTW1: a column name ending in _ID is suspected to be of an integer
    type, usually. Maybe you want to check your column types.

    BTW2: you're mixing "," and JOIN. I suggest to switch to the JOIN
    keyword. Many people are cought by the fact that the JOIN keyword
    binds stronger than "," so in fact your JOIN is

    S, I, C, (P LEFT JOIN PC) and not
    (S, I, C, P) LEFT JOIN PC as you might have expected


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

Similar Threads

  1. UK collation sequence
    By voipfc in forum MySQL
    Replies: 5
    Last Post: December 21st, 05:10 AM
  2. Collation Problem
    By oj in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 8th, 06:23 PM
  3. MS says pirating is illegal, but are their PA rules illegal?
    By PCyr in forum Windows Setup, Administration & Security
    Replies: 17
    Last Post: August 7th, 03:45 AM
  4. Replies: 19
    Last Post: August 7th, 03:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139