Hello. Some times the cause of these problems is that some fields have different character sets or it is a bug. I suggest you the following steps: check if the problem still exists in 4.1.16. Then provide the create statements of your tables to the list (they include the character set information). See: http://dev.mysql.com/doc/refman/4.1/en/charset-collation-charset.html
schlubediwup wrote: > Hi mysqllers, > > 1. following installation > > > localhost.addresses2> show global variables like "version%"; > +-------------------------+------------------------------------------+ > | Variable_name | Value | > +-------------------------+------------------------------------------+ > | version | 4.1.14-standard | > | version_comment | MySQL Community Edition - Standard (GPL) | > | version_compile_machine | i686 | > | version_compile_os | pc-linux-gnu | > +-------------------------+------------------------------------------+ > 4 rows in set (0.00 sec) > > localhost.addresses2> > > > > 2. following two tables are involved: > > localhost.addresses2> describe contacts2; > +------------------+------------------+------+-----+-------------------+----------------+ > > | Field | Type | Null | Key | Default | > Extra | > +------------------+------------------+------+-----+-------------------+----------------+ > > | name | text | YES | | NULL > | | > | firm | text | YES | | NULL > | | > | title | text | YES | | NULL > | | > | phone | text | YES | | NULL > | | > | mail | text | YES | | NULL > | | > | comment | text | YES | | NULL > | | > | status | text | YES | | NULL > | | > | url | text | YES | | NULL > | | > | businesscategory | text | YES | | NULL > | | > | address | text | YES | | NULL > | | > | kanton | text | YES | | NULL > | | > | addon | text | YES | | NULL > | | > | givenname | text | YES | | NULL > | | > | history | text | YES | | NULL > | | > | favorit | text | YES | | NULL > | | > | last_update | timestamp | YES | | CURRENT_TIMESTAMP > | | > | counter | int(10) unsigned | | PRI | NULL | > auto_increment | > +------------------+------------------+------+-----+-------------------+----------------+ > > 17 rows in set (0.00 sec) > > localhost.addresses2> > > > localhost.addresses2> describe tasks2; > +------------------+------------------+------+-----+-------------------+----------------+ > > | Field | Type | Null | Key | Default | > Extra | > +------------------+------------------+------+-----+-------------------+----------------+ > > | task_name | text | YES | | NULL > | | > | actiony | text | YES | | NULL > | | > | date_start | date | YES | | NULL > | | > | date_end | date | YES | | NULL > | | > | date_last_action | date | YES | | NULL > | | > | date_next_action | date | YES | | NULL > | | > | start_time | time | YES | | NULL > | | > | end_time | time | YES | | NULL > | | > | task_address | text | YES | | NULL > | | > | task_comment | text | YES | | NULL > | | > | task_responsible | text | YES | | NULL > | | > | project | text | YES | | NULL > | | > | task_history | text | YES | | NULL > | | > | task_last_update | timestamp | YES | | CURRENT_TIMESTAMP > | | > | contact_link | int(11) | YES | | NULL > | | > | task_counter | int(10) unsigned | | PRI | NULL | > auto_increment | > +------------------+------------------+------+-----+-------------------+----------------+ > > 16 rows in set (0.00 sec) > > localhost.addresses2> > > > > 3. following character-sets and collations: > > localhost.addresses2> show session variables like "character%"; > +--------------------------+----------------------------+ > | Variable_name | Value | > +--------------------------+----------------------------+ > | character_set_client | utf8 | > | character_set_connection | utf8 | > | character_set_database | latin1 | > | character_set_results | utf8 | > | character_set_server | latin1 | > | character_set_system | utf8 | > | character_sets_dir | /usr/share/mysql/charsets/ | > +--------------------------+----------------------------+ > 7 rows in set (0.00 sec) > > localhost.addresses2> show session variables like "collat%"; > +----------------------+-------------------+ > | Variable_name | Value | > +----------------------+-------------------+ > | collation_connection | utf8_general_ci | > | collation_database | latin1_swedish_ci | > | collation_server | latin1_swedish_ci | > +----------------------+-------------------+ > 3 rows in set (0.00 sec) > > localhost.addresses2> > > > > 4. following problem: > > when i do a union statement between the two tables, where obviousely at > least in one select of the union some fields are left open by the > placeholder "" or '' , these left-off fields are acted on with the > collation_database (latin1_swedish_ci), wherease all non-left-off fields > (i.e. those with a real column value in it are acted on with the > collation_connection (utf8_general_ci), which then turns out in the > error 1267. > if i give those left off fields a _latin1 converter, everything works > fine. to find out, however, if the _latin1 converter is necessary, is > not easy. > is there a better solution to solve this problem? straight away: is this > a bug? > > > > 5. following examples: > > is accepted: > (SELECT name,task_name,actiony,date_last_action,counter,task_counter > FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = > contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) > UNION (SELECT name,firm,status,'', counter,'' FROM contacts2 WHERE name > LIKE '%meier%' ORDER BY counter LIMIT 100) ORDER BY counter LIMIT 100; > > is accepted: > (SELECT > name,firm,status,businesscategory,addon,date_last_action,counter,task_counter > FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = > contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) > UNION (SELECT name,firm,status,businesscategory,addon, '', counter,'' > FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) > ORDER BY counter LIMIT 100; > > > is not accepted: > (SELECT > name,firm,status,businesscategory,addon,actiony,date_last_action,counter,task_counter > FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = > contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) > UNION (SELECT name,firm,status,businesscategory,addon, '','', counter,'' > FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) > ORDER BY counter LIMIT 100; > ERROR 1267 (HY000): Illegal mix of collations > (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for > operation 'UNION' > > > is accepted: > (SELECT > name,firm,status,businesscategory,addon,actiony,date_last_action,counter,task_counter > FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = > contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) > UNION (SELECT name,firm,status,businesscategory,addon, _latin1'','', > counter,'' FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter > LIMIT 100) ORDER BY counter LIMIT 100; > > > > > > suomi -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
