|
Home > Archive > MySQL ODBC Connector > January 2006 > union/collation problem, error 1267: feature or bug?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
union/collation problem, error 1267: feature or bug?
|
|
| schlubediwup 2006-01-20, 3:23 am |
| 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_mach
ine | 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_connec
tion | utf8 |
| character_set_databa
se | latin1 |
| character_set_result
s | 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,actio
ny,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,bus
inesscategory,addon,
date_last_action,cou
nter,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,bus
inesscategory,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,bus
inesscategory,addon,
actiony,date_last_ac
tion,counter,task_co
unter
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,bus
inesscategory,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,I
MPLICIT) and (utf8_general_ci,COE
RCIBLE) for
operation 'UNION'
is accepted:
(SELECT
name,firm,status,bus
inesscategory,addon,
actiony,date_last_ac
tion,counter,task_co
unter
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,bus
inesscategory,addon,
_latin1'','',
counter,'' FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter
LIMIT 100) ORDER BY counter LIMIT 100;
suomi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Gleb Paharenko 2006-01-21, 7:23 am |
| 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...on-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_mach
ine | 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_connec
tion | utf8 |
> | character_set_databa
se | latin1 |
> | character_set_result
s | 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,actio
ny,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,bus
inesscategory,addon,
date_last_action,cou
nter,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,bus
inesscategory,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,bus
inesscategory,addon,
actiony,date_last_ac
tion,counter,task_co
unter
> 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,bus
inesscategory,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,I
MPLICIT) and (utf8_general_ci,COE
RCIBLE) for
> operation 'UNION'
>
>
> is accepted:
> (SELECT
> name,firm,status,bus
inesscategory,addon,
actiony,date_last_ac
tion,counter,task_co
unter
> 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,bus
inesscategory,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
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ 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/mysql? unsub...sie.nctu.edu.tw
| |
| schlubediwup 2006-01-26, 4:56 pm |
| Hi Gleb,
localhost.(none)> show session variables like "%version%";
+-------------------------+------------------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------------------+
| protocol_version | 10 |
| version | 4.1.16-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_mach
ine | i686 |
| version_compile_os | pc-linux-gnu |
+-------------------------+------------------------------------------+
5 rows in set (0.00 sec)
localhost.(none)>
with the new version , in a first view, the problem i reported on 20.
january 2006, does not occur anymore.
thanks for your suggestion an help.
suomi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| schlubediwup 2006-01-28, 7:23 am |
| Hi Gleb,
i finally found out a method to be entirely independent from any
character-set as well as collation-sequence problem, when forming a
UNION, where you occasionnally have to insert place-holders in one of
the SELECT statements:
as (text, varchar, char) placeholders use NULL instead of "" or '' if
your application permits it.
thanks for your suggestion an help.
suomi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|